Python连接MSSQL基础教程：pymssql库使用详解
本文详细介绍如何使用Python的pymssql库连接MSSQL数据库。内容涵盖连接创建、游标操作、参数化查询注意事项、多游标冲突处理、字典模式返回、上下文管理器使用以及存储过程调用。通过具体代码示例，帮助开发者掌握Python与SQL Server交互的核心技巧。
前言
pymssql模块是用于连接SQL Server数据库的Python库。此外，pyodbc不仅支持SQL Server，还涵盖Oracle、MySQL、Access和Excel等多种数据源。
除了pymssql，Python中还有其他几种连接SQL Server的模块，感兴趣的用户可参考此链接获取更多信息：https://wiki.python.org/moin/SQL%20Server
本文将深入解析Python连接MSSQL所使用的pymssql库，以下是详细的技术介绍。
连接数据库
pymssql连接数据库的流程与SQLite类似，主要步骤如下：
- 使用connect函数创建连接对象。
- 通过connect.cursor方法创建游标对象，SQL语句的执行主要依赖游标进行。
- 利用cursor.executeXXX方法执行SQL语句，并通过cursor.fetchXXX方法获取查询结果。
- 最后调用close方法关闭游标和数据库连接，释放资源。
import pymssql
# server 数据库服务器名称或IP
# user 用户名
# password 密码
# database 数据库名称
conn = pymssql.connect(server, user, password, database)
cursor = conn.cursor()
# 新建、插入操作
cursor.execute("""
IF OBJECT_ID('persons', 'U') IS NOT NULL
DROP TABLE persons
CREATE TABLE persons (
id INT NOT NULL,
name VARCHAR(100),
salesrep VARCHAR(100),
PRIMARY KEY(id)
)
""")
cursor.executemany(
"INSERT INTO persons VALUES (%d, %s, %s)",
[(1, 'John Smith', 'John Doe'),
(2, 'Jane Doe', 'Joe Dog'),
(3, 'Mike T.', 'Sarah H.')])
# 如果没有指定autocommit属性为True的话就需要调用commit()方法
conn.commit()
# 查询操作
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()
# 也可以使用for循环来迭代查询结果
# for row in cursor:
# print("ID=%d, Name=%s" % (row[0], row[1]))
# 关闭连接
conn.close()
注意：在示例的查询操作中，参数占位符使用%s而非'%s'。当参数值为字符串时，执行语句时系统会自动添加单引号，无需手动添加。
游标使用注意事项
一个连接在同一时刻只能有一个游标的查询处于活跃状态。如果尝试在同一个连接上并行执行多个查询，可能会导致结果混乱或错误，如下例所示：
c1 = conn.cursor()
c1.execute('SELECT * FROM persons')
c2 = conn.cursor()
c2.execute('SELECT * FROM persons WHERE salesrep=%s', 'John Doe')
print( "all persons" )
print( c1.fetchall() ) # 显示出的是c2游标查询出来的结果
print( "John Doe" )
print( c2.fetchall() ) # 不会有任何结果
为避免上述问题，可采用以下两种解决方案：
- 创建多个独立的连接对象，确保多个查询可以在不同连接的游标上并行执行。
- 在使用fetchall方法获取完当前游标的查询结果后，再执行下一个查询操作。具体示例如下：
c1.execute('SELECT ...')
c1_list = c1.fetchall()
c2.execute('SELECT ...')
c2_list = c2.fetchall()
游标返回行为字典变量
在上述示例中，游标获取的查询结果每一行均为元组类型。
若希望在创建游标时指定as_dict参数为True，可使游标返回字典类型的变量。
此时，字典中的键名对应数据表的列名，便于通过列名访问数据。
conn = pymssql.connect(server, user, password, database)
cursor = conn.cursor(as_dict=True)
cursor.execute('SELECT * FROM persons WHERE salesrep=%s', 'John Doe')
for row in cursor:
print("ID=%d, Name=%s" % (row['id'], row['name']))
conn.close()
使用with语句（上下文管理器）
可以通过使用with语句来自动管理资源，省去显式调用close方法关闭连接和游标的步骤。
with pymssql.connect(server, user, password, database) as conn:
with conn.cursor(as_dict=True) as cursor:
cursor.execute('SELECT * FROM persons WHERE salesrep=%s', 'John Doe')
for row in cursor:
print("ID=%d, Name=%s" % (row['id'], row['name']))
调用存储过程
pymssql 2.0.0及以上版本支持通过cursor.callproc方法来调用数据库中的存储过程。
with pymssql.connect(server, user, password, database) as conn:
with conn.cursor(as_dict=True) as cursor:
# 创建存储过程
cursor.execute("""
CREATE PROCEDURE FindPerson
@name VARCHAR(100)
AS BEGIN
SELECT * FROM persons WHERE name = @name
END
""")
# 调用存储过程
cursor.callproc('FindPerson', ('Jane Doe',))
for row in cursor:
print("ID=%d, Name=%s" % (row['id'], row['name']))
总结
以上就是本文关于Python连接MSSQL及pymssql库使用的全部内容。希望这些内容能对您的学习或工作有所帮助。如有任何疑问，欢迎留言交流，感谢支持。
参考文档：http://pymssql.org/en/stable/pymssql_examples.html
本站声明
本文内容仅供参考，不代表本站观点。转载或引用请注明出处。如发现内容问题，请通过联系我们页面反馈。
