python操作数据库sqlserver_python操作数据库-SQLSERVER-pyodbc

刚开始学python时，大家都习惯用pymssql去读写SQLSERVER。但是实际使用过程中，pymssql的读写性能以及可靠性的确不如pyodbc来的好。

第一步、为pyodbc Python开发配置开发环境。

window根据需要 安装

linux以centos为例

sudo su#Download appropriate packageforthe OS version

#Choose only ONE of the following, corresponding to your OS version

#RedHat Enterprise Server6curl https://packages.microsoft.com/config/rhel/6/prod.repo > /etc/yum.repos.d/mssql-release.repo

#RedHat Enterprise Server7curl https://packages.microsoft.com/config/rhel/7/prod.repo > /etc/yum.repos.d/mssql-release.repo

#RedHat Enterprise Server8 and Oracle Linux 8curl https://packages.microsoft.com/config/rhel/8/prod.repo > /etc/yum.repos.d/mssql-release.repo

exitsudo yum remove unixODBC-utf16 unixODBC-utf16-devel #to avoid conflictssudo ACCEPT_EULA=Y yum installmsodbcsql17

# optional:forbcp and sqlcmdsudo ACCEPT_EULA=Y yum install mssql-toolsecho ‘export PATH="$PATH:/opt/mssql-tools/bin"‘ >> ~/.bash_profileecho ‘export PATH="$PATH:/opt/mssql-tools/bin"‘ >> ~/.bashrc

source~/.bashrc

# optional:forunixODBC development headerssudo yum install unixODBC-devel

完成odbc驱动的安装后，依赖环境就已经完成了

2、安装pyodbc

这个只要pip安装下就好了。

pip install pyodbc

3、如何使用pyodbc进行t-sql操作

1)简单查询

import pyodbc

# Some other example server values are

# server= ‘localhost\sqlexpress‘# 实列名称

# server= ‘myserver,port‘# 如果实例是有特殊端口号的，默认1433

server= ‘tcp:myserver.database.windows.net‘database= ‘mydb‘username= ‘myusername‘password= ‘mypassword‘cnxn= pyodbc.connect(‘DRIVER={ODBC Driver 17 for SQL Server};SERVER=‘+server+‘;DATABASE=‘+database+‘;UID=‘+username+‘;PWD=‘+password)

cursor=cnxn.cursor()

cursor.execute("SELECT @@version;")

row=cursor.fetchone()whilerow:

print(row[0])

row= cursor.fetchone()

2)执行操作

cursor.execute("""INSERT INTO SalesLT.Product (Name, ProductNumber, StandardCost, ListPrice, SellStartDate)

VALUES (?,?,?,?,?)""",‘SQL Server Express New 20‘, ‘SQLEXPRESS New 20‘, 0, 0, CURRENT_TIMESTAMP)

cnxn.commit()

row=cursor.fetchone()whilerow:print(‘Inserted Product key is‘ +str(row[0]))

row= cursor.fetchone()

3)如何使用window验证

cnxn = pyodbc.connect(‘DRIVER={ODBC Driver 17 for SQL Server};SERVER=‘+server+‘;DATABASE=‘+database+‘;Trusted_Connection=yes)