Hello everyone , I'm an old watch ～
Hello, everyone , Some time ago , A reader made a request , take excel Files are stored in batches to sqlserver, I don't know sqlserver How much do you use , I haven't used it since graduation , And basically mysql Dealing with .
There happens to be one windows The server , So use this to install sqlserver, Solve the reader's problem .
This article introduces you to windows How to quickly install sqlserver, And use python Connected to the , It's not as smooth as you think , There are some pits , So record it together , I hope it will be helpful for you to study .
We can directly visit the official website to download , You can select the developer version for your local test , More functions .
https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads
Here we recommend that you customize , Some other tools can be installed together , You can also choose basic as I do , It is equivalent to only installing the database , After configuring the environment, you can cmd Link operation in , But it's troublesome ！！！
Choose your own installation location , My server has only one drive letter , It didn't change , You can install the drive letter you want to install .
Come here , We have installed the basic database services （ Environment variables are automatically configured by default 、 Start the service ）, You can click directly Immediate connection Button test link , You can also click Customize Install other related tools , Or click install SSMS（sqlserver Database management tools , Recommended installation ）.
Server=localhost;Database=master;Trusted_Connection=True;
I said before. , We have only installed the basic sqlserver service , Now we can windwos cmd Use in osql Command to connect to the database for operation , But it's troublesome , So I recommend that you install SSMS, After selecting the installation directory , Just click Install .
Need to restart the computer , Before restarting, please press to make sure that you do not have any files you are editing and have not saved , Avoid data loss .
After restart , Turn on the computer , We started in the computer , You can find our newly installed database management tool , Double click to open .
Click on Connect -> Database engine , You can select the database to connect to .
Here we connect to the local database , First direct use Windows Authentication Sign in , There is no need to enter a user name or password .
After successful connection , Then we right click , Select new database , You can create a new study database , It is used to store the data table used in our test .
Then we need to create a new login , Convenient for remote or code use , choice Security -> Login name -> New login that will do .
Enter login name , And select SQL Server Authentication (S), Then enter the password , Select the default database as study.
After creating a new user , We also need to modify the database connection properties , Right click the database , Selected properties .
Check security , Server authentication needs to select SQL Server and Windows Authentication mode (S).
Click ok , You will be prompted to restart SQL Server service .
We can go to the system service , find SQL Server, Then right click and select restart service .
Then we create a new database connection , Enter the user name and password to successfully log in .
If you encounter the following problems in remote login 20009,b'DB-Lib error message 20009...
Looking for a long time , To find a solution to the problem , thank stackoverflow in @Hainan Zhao The solution provided .
https://stackoverflow.com/questions/19348255/pymssql-operationalerror-db-lib-error-message-20009-severity-9
This is because we did not open sqlserver Of TCP/IP Local connection ,
therefore , Just in SQL Server Open in the configuration manager 127.0.0.1:1433 You can visit .
1） Start -> All the procedures -> Microsoft SQL Server 2019 -> Configuration tool -> SQL Server Configuration manager
2）SQL Server The network configuration -> MSSQLSERVER The agreement TCP/IP -> attribute -> IP Address . find 127.0.0.1 And will “ Enabled ” Change to “ yes ”. If you need to pass ip Remote connection , You can also add on the server ip Address .
What I'm using here is ：pymssql+sqlalchemy+pandas Read and write sqlserver data .
pip3 install pymssql sqlalchemy pandas
from sqlalchemy import create_engine
import pandas as pd
# Initialize the database connection engine
# create_engine(" Database type + Database driven :// Database user name : Database password @IP Address : port / database ", The other parameters )
engine = create_engine("mssql+pymssql://sa:[email protected]/study?charset=GBK")
# Read the sql sentence testc The first in the table 3 Data
sql = f'select top 3 * from testc'
# The first parameter ： Inquire about sql sentence
# The second parameter ：engine, Database connection engine
pd_read_sql = pd.read_sql(sql, engine)
print(pd_read_sql)
There's a pit here ,sqlserver Creating a database defaults to gbk code , If... Is not specified when connecting above charset=GBK, So run sql When reading and writing , The following error will be reported 20002.
After adding the database character encoding , You can read and write the database normally , For example, the code above , I'm in the database study A new testc surface , The data is inserted casually , The above code is to read testc The first in the table 3 Data , The operation results are as follows ：
Come here , We're done Windows Next SQL Server Service and database management tools installation and simple configuration work , And how to use it python Connect read and write SQL Server Data table in , Later, we will analyze the needs of readers in detail , Batch write Excel Data into the SQL Server.
