This is the seventh day of my participation in the First Challenge 2022

Hello everyone, some time ago, a reader raised a requirement, to batch store excel files to SQLServer, I don’t know if you use SQLServer, I have not used it since graduation, basically dealing with mysql.

There doesn’t happen to be a Windows server, so use this to install sqlserver and solve your reader’s problem.

This article introduces how to quickly install sqlserver on Windows, and use python to connect to sqlserver.

1. Download the installation package

We can directly visit the official website to download, their own local test use can choose the developer version, more fully functional.

https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads
Copy the code

2. Install sqlserver

Here we suggest you to customize, you can support the installation of some other tools, you can also choose basic as I do, equivalent to only installed the database, configuration environment can be linked in CMD operation, but very troublesome!!

Choose your own installation location, I have only one drive letter server, did not change, you can install to their own want to install the drive letter.

Here, we installed the basic database services (default will automatically configure environment variables, start the service), you can directly click the connect now button test link, you can also click custom install other related tools, or click install SSMS (sqlserver database management tool, recommended installation).

Server=localhost; Database=master; Trusted_Connection=True;Copy the code

3. Install and use database management tools

As mentioned above, we only installed the basic sqlserver service, now we can use osql command in windwos CMD to connect to the database for operation, but it is very troublesome, so I recommend to install SSMS, after selecting the installation directory, directly click install.

Before restarting the PC, press to ensure that no files are being edited or saved to avoid data loss.

After the restart, open the computer, we start in the computer, we can find our newly installed database management tool, double-click directly to open.

Click Connect -> Database Engine to select the database to connect to.

Here we connect to our local database and log in directly using Windows authentication for the first time without entering a user name or password.

After the connection is successful, we can right-click and select New Database to create a New Study database that will hold the data table for our test.

Then we need to create a new login name for remote or code use, choose Security -> Login name -> New Login name.

Enter the login name and select SQL Server Authentication (S), then enter the password and select study as the default database.

After creating a new user, we also need to modify the database connection properties, right-click the database, select properties.

Select Security, Server authentication requires SQL Server and Windows authentication mode (S).

After you click OK, the system prompts you to restart the SQL Server service.

You can go to system Services, find SQL Server, right-click and choose Restart Service.

Then create a new database connection and enter the username and password to log in successfully.

20009, b ‘db-lib error message 20009…

It took a long time to find a solution to this problem. Thanks to @Hainan Zhao from StackOverflow.

https://stackoverflow.com/questions/19348255/pymssql-operationalerror-db-lib-error-message-20009-severity-9
Copy the code

This is because we did not enable TCP/IP local connection to sqlserver,

Therefore, simply open access to 127.0.0.1:1433 in SQL Server Configuration Manager.

1) Start -> All Programs -> Microsoft SQL Server 2019 -> Configuration Tools -> SQL Server Configuration Manager

2) SQL Server network configuration -> MSSQLSERVER protocol TCP/IP -> Properties -> IP address Find 127.0.0.1 and change enabled to Yes. If remote connection is required, you can add the IP address of the server.

4. Python connects to the SQLServer database

Here I use pyMSSQL + SQLAlchemy + PANDAS to read and write sqlServer data.

  • Install related third-party packages
pip3 install pymssql sqlalchemy pandas 
Copy the code
  • Connect to the database and read the table contents
from sqlalchemy import create_engine
import pandas as pd

Initialize the database connection engine
# create_engine(" database type + database driver :// database username: database password @IP address: port/database ", other parameters)
engine = create_engine("mssql+pymssql://sa:123456@localhost/study? charset=GBK")
SQL > select * from testC
sql = f'select top 3 * from testc'
First parameter: query SQL statement
The second parameter: engine, database connection engine
pd_read_sql = pd.read_sql(sql, engine)
print(pd_read_sql)
Copy the code

SQL > create database (charset=GBK, charset=GBK, charset=GBK)

Select * from testC; select * from testC; select * from testC; select * from study;

By now, we have completed the installation and simple configuration of SQL Server services and database management tools under Windows, as well as how to use Python to connect to read and write data tables in SQL Server. Later, we will analyze the needs of readers in detail and write Excel data in batches into SQL Server.

Like to see the message forwarding, four support, the original is not easy. Ok, see you next time, I love the cat love technology, more love si si’s old cousin Da Mian ଘ(˙꒳˙)ଓ Di Di