python Of pandas Library to read SQL sever There are two ways . A kind of use pymssql, Another use sqlalchemy. Here, the tables in the database are read as DataFrame, Do not modify tables .

Catalog

• python Of pandas Library to read SQL sever There are two ways . A kind of use pymssql, Another use sqlalchemy. Here, the tables in the database are read as DataFrame, Do not modify tables .
• One 、pymssql
• • 1. install
  • 2. Connect to database
  • • 1. Login settings
  • 3. Get database table
• Two 、sqlalchemy

One 、pymssql

1. install

You can directly use the following command to install pymssql.

pip install pymssql
pip install sqlalchemy

2. Connect to database

There are also two situations , One is the direct use of windows Verify login , The other is to SQL Sever Authentication means login with account and password . Settings can be made here , You can use both login methods at the same time .

1. Login settings

If you want to set two ways to log in at the same time , We use windows After verifying login

Click Security , Select the options shown in the figure and click OK , Close the window .

Then click Security , Right click in the login name sa, Then click the properties at the bottom of the menu bar .

First, click general , Set the password , Then click status , Enable login . Click OK to close .

Finally, we need to restart SQL Sever. Find... In the start menu bar SQL Sever Configuration manager . find SQL Sever The network configuration . stay MSSQL Sever Enabled in the protocol “Named Pipes” and “TCP/IP”. And then in SQL Sever In service , Right click on the SQL Sever（MSSQL Sever）, Then click restart . This completes the setup .

3. Get database table

import pymssql
import pandas as pd
""" If you use sql sever For authentication, use the following code conn = pymssql.connect(host, user, password, " Connection default database name ") """
# It's directly used here windows Verify login , No account or password required ,database The name of the database to be connected 
conn = pymssql.connect(host="",database="db_databsae", charset="utf8")
# Use pandas Library read_sql Method , Input select Sentence can be used 
df = pd.read_sql("select * from score",con=conn)
print(df)
conn.close()

there host The parameter is the server name , We usually connect to the local server as localhost, Namely ip Address plus slogan . It can be found as follows . Right click ip Protocol then click Properties , here host That is to say ip Address ： Port number . such as 127.0.0.1:1533 .127.0.0.1 by ip,1533 Is the port number . In this way, we can successfully obtain the data .

Two 、sqlalchemy

With the settings in the previous section , Here is the code , It's all similar .

import sqlalchemy as sqla
#windows Verify login 
# Here just put host Change to your own host and databsae Change to your own database name 
db = sqla.create_engine("mssql+pymssql://@host/databsae?charset=utf8")
pd.read_sql("select * from score;",db)
#sql sever Authentication login 
# Also replace host And database name , Replace at the same time sa Login password , Here for 123
db = sqla.create_engine("mssql+pymssql://sa:[email protected]/db_databsae?charset=utf8")
pd.read_sql("select * from test;",db)