Python使用pymssql连接与操作SQL Server数据库详解

Python操作SQL Server数据库概述

此前的文章曾探讨过利用Python抓取网页数据并存储至本地Excel文件的方法。在实际应用中，将抓取的数据持久化存储至数据库（如SQL Server、MySQL等）是更常见的需求。本文将重点讲解在Python中操作SQL Server数据库的基础用法，为后续将网页抓取数据存入SQL Server数据库打下基础。

通过搜索引擎查询“Python操作SQL Server数据库”，多数结果推荐使用pymssql库。本文将基于pymssql包，系统学习SQL Server数据库的基本操作方式，并结合前文构建的环境检测数据库进行实际的数据操作测试。

pymssql库支持离线和在线两种安装方式。若选择离线安装，需从参考文献[2]下载对应的包文件，并将其复制至Python安装目录下的Lib\site-packages文件夹中。若选择在线安装，只需在命令行界面执行相应的安装命令即可完成部署。

pip install pymssql

根据目前的学习内容，pymssql的核心机制是通过执行SQL语句来操作数据库，并利用cursor（游标）对象获取查询结果。本文主要涵盖以下三个基本操作环节：

• 数据库的连接与关闭流程；
• 数据查询方法；
• 数据插入技巧。

数据库连接与关闭

关于数据库的连接与关闭，pymssql通过调用pymssql.connect函数来建立数据库连接，该函数返回一个Connection对象。随后，调用Connection对象的cursor方法获取cursor对象，后续所有的SQL语句执行及数据获取均依赖该对象。当所有操作完成后，必须调用Connection对象的close方法以关闭连接。具体的测试程序及运行结果展示如下。

import pymssql

server = 'XXX.XXX.XXX.XXX'
user = 'XXXXXXX'
password = 'XXXXXXXXXX'
db='EC'

conn = pymssql.connect(server, user, password, db)

cursor = conn.cursor()
cursor.execute('SELECT Count(*) FROM EnvironmentRecord')
print('total data amount = %d' % cursor.fetchone())

conn.close()

数据查询方法

在数据查询方面，需调用cursor对象的execute函数来执行无参数或带参数的SQL语句。对于无参数的SQL语句，可直接参照前述示例调用execute函数；若语句包含参数，则需采用图中所示的第二种execute函数调用方式。获取查询结果有多种途径：cursor对象的fetchone函数用于获取结果集中的下一行数据，fetchmany函数用于获取指定数量的结果行，而fetchall函数则用于获取所有查询结果。此外，除了直接调用上述函数，还可以利用迭代器或结合fetchone进行循环遍历来获取全部结果。从参考文献[3]中找到了两种典型的使用示例，代码片段如下：

#使用示例1
cursor.execute('SELECT * FROM persons WHERE salesrep=%s', 'John Doe')
row = cursor.fetchone()
while row:
    print("ID=%d, Name=%s" % (row[0], row[1]))
    row = cursor.fetchone()
    
#使用示例2
cursor.execute('SELECT * FROM persons WHERE salesrep=%s', 'John Doe')
for row in cursor:
	#获取一条记录中的某个属性时，可以通过属性名称索引方式获取
    print("ID=%d, Name=%s" % (row['id'], row['name']))

基于上述示例编写的查询测试代码，其程序逻辑及执行结果如下所示。

import pymssql

server = 'XXX.XXX.XXX.XXX'
user = 'XXXXXXX'
password = 'XXXXXXXXXX'
db='EC'

conn = pymssql.connect(server, user, password, db)

cursor = conn.cursor()
cursor.execute('SELECT * FROM EnvironmentRecord WHERE CreateTime>=%s', '2021-6-19 16:12:00')

for row in cursor:
    print('row = %r' % (row,))

conn.close()

数据插入与提交

在数据插入操作中，调用cursor对象的execute函数执行INSERT语句可实现单条数据的插入；而调用executemany函数则支持批量插入多条数据，参考文献[3]中提供了多条数据插入的具体示例。需要注意的是，插入语句执行完毕后，必须调用Connection对象的commit函数进行提交，否则更改不会生效。此外，也可以通过配置Connection对象的autocommit属性，使语句自动提交并生效。

基于参考文献[3]及前述代码示例，我们对插入单条数据和多条数据进行了测试，具体的程序代码及执行结果如下所示。

import pymssql

server = 'XXX.XXX.XXX.XXX'
user = 'XXXXXXX'
password = 'XXXXXXXXXX'
db='EC'

conn = pymssql.connect(server, user, password, db)

cursor = conn.cursor()

##sqlStr="INSERT INTO EnvironmentRecord(CreateTime,DHTState,Humidity,Temperature,FlameValue,MQValue)VALUES('2022-05-23 10:00:00',1,54, 22, 962, 202)"
##cursor.execute(sqlStr)

cursor.executemany(
    "INSERT INTO EnvironmentRecord(CreateTime,DHTState,Humidity,Temperature,FlameValue,MQValue)VALUES(%s,%d,%d,%d,%d,%d)",
    [('2022-05-24 10:00:00',1,54, 22, 962, 202),
     ('2022-05-24 10:10:00',1,54, 22, 962, 202),
     ('2022-05-24 10:20:00',1,54, 22, 962, 202)])
conn.commit()

conn.close()

关于pymssql的更详细介绍，建议查阅参考文献[3]。本文完成了基于pymssql的SQL Server数据库基本操作方法的学习，后续计划尝试将之前保存至Excel文件中的网页抓取数据，正式存入SQL Server数据库中。

参考文献 [1]https://blog.csdn.net/XUMENGCAS/article/details/123419220 [2]https://github.com/pymssql/pymssql [3]https://pymssql.readthedocs.io/en/latest/index.html

至此，关于Python调用pymssql包操作SQL Server数据库的实现介绍完毕。如需了解更多关于Python使用pymssql操作SQL Server的相关内容，欢迎搜索本站历史文章或继续浏览后续相关文章。感谢大家的支持！