python pymssql — pymssql模块使用指南

前言

最近在学习python，发现好像没有对pymssql的详细说明，于是乎把官方文档学习一遍，重要部分做个归档，方便自己以后查阅。

pymssql是python用来连接Microsoft SQL Server的一个工具库(package)。其包含两个模块：

• pymssql：遵从DB-API¹规范的接口
• _mssql：性能更佳、更易于使用

从版本2.1.x起，整个库的实现基于FreeTDS²的db-lib组件。

pymssql的架构如下：

在Windows下的安装很简单，下载安装个相对新版的python，具体来说：

• Python 2.x: 2.7以上
• Python 3.x: 3.3以上

然后打开命令行：

pip install pymssql

然后就自动安装上了，好像FreeTDS之后的支持库都会自动帮忙安好。

另外注意为了后面的使用，需要自己安装好Microsoft SQL Server，2005以上的版本。

pymssql模块使用

以下示例程序基于官方的示例，并进行了一定的修改，主要是删改了些代码以及添加了很多注释。

基本使用流程

pymssql的使用十分简单，基本就如下几个步骤

1. 创建链接：使用connect()创建连接并获取Connection对象
2. 交互操作：获取Connection对象的Cursor对象，然后使用Cursor对象的各种方法与数据库进行交互
3. 关闭链接

本章假设你已经配置好环境并且在本地数据库中有一个叫tempdb的数据库，有一个用户的用户名为sa，密码为123456。
以下程序简单示例了上述步骤并创建好了测试用数据库。

import pymssql
#sql服务器名，这里(127.0.0.1)是本地数据库IP
serverName = '127.0.0.1'
#登陆用户名和密码
userName = 'sa'
passWord = '123456'
#建立连接并获取cursor
conn = pymssql.connect(serverName , userName , passWord, "tempdb")
cursor = conn.cursor()
# 创建测试表 persons，包含字段：ID、name、salesrep
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()
# 连接用完后记得关闭以释放资源
conn.close()

后面较为详细的分开讲解下各个步骤

创建连接

因为这篇文章讲的是pymssql模块(如上所述还有一个_mssql模块)所以要import pymssql。

为了创建连接，需要调用pymssql.connect()方法，这个方法会返回一个Connection对象。

用户验证登陆

可能比较常用的就是如上面程序中所用的用户名+密码的登陆方式了：

conn = pymssql.connect(serverName , userName , passWord, "tempdb")

等价于：

conn = pymssql.connect(server = serverName , user = userName , password = passWord, database = "tempdb")

第二种用关键字的方式可以按任意顺序传递各参数(好吧，这其实是python的基础知识)。

Windows身份认证登陆

登陆自己电脑上的数据库时，Windows身份认证的方式登陆是个很好用的方法。因为不需要账号密码：

conn = pymssql.connect(server = serverName , database = "tempdb")

简单来说，只要不填账号密码，就会自动用Windows身份认证的方式来登陆了。

其他几个常用选项

• database(str)：指定的是默认数据库，如果不需要的话，上述例子中其实可以不写。但是这样的话，那你的SQL语句中就得在最前面加上"USE tempdb ……"了，sql server用的多的应该知道怎么回事。
• as_dict(bool) ：如果设置为True，则后面的查询结果返回的是字典，关键字为查询结果的列名；否则(默认)返回的为list。
• autocommit(bool)：默认为False，这样如果对数据表进行更改，则需要手动调用commit来提交操作。
• port(str)：指定服务器的TCP端口，如果你没有改过的话使用默认的就好。

其他选项详见文档。

交互操作

在连接建立成功后，与数据库的交互主要是通过Cursor对象进行的：

cursor = conn.cursor()

提交sql命令

如上示例，sql指令通过execute系列方法来执行：

cursor.execute('sql语句')

调用存储过程

如果要调用存储过程，则使用Cursor对象的callproc方法

# 创建一个存储过程
cursor.execute("""
CREATE PROCEDURE FindPerson
     @name VARCHAR(100)
 AS BEGIN
     SELECT * FROM persons WHERE name = @name
END
""")
# 调用上面的存储过程
cursor.callproc('FindPerson', ('Jane Doe',))

提交修改

如果对数据进行了修改，且在连接时没有把autocommit设置为True，则需要手动调用commit进行提交修改。

conn.commit()

获取结果

如果执行的是有返回值的sql语句，则可以通过Cursor对象的fetch系列方法来获取结果，结果默认为元组类型：

# 查询persons表中记录数
cursor.execute("SELECT COUNT(*) FROM persons")
# 结果为3
cnt = cursor.fetchone()[0]

如果返回多条记录，可以像这样遍历所有结果：

cursor.execute('SELECT * FROM persons')
# 遍历输出persons表中所有数据
row = cursor.fetchone()
while row:
    print("ID=%d, Name=%s" % (row[0], row[1]))
    row = cursor.fetchone()

或者：

# 这里写sql语句的和上例不完全一样只是为了示例execute的其他用法
cursor.execute('SELECT * FROM persons WHERE salesrep=%s', 'John Doe')
for row in cursor:
    print('row = %r' % (row,))

如果指定了as_dict为True，则返回结果变为字典类型，这样就能通过列名来访问结果了：

# 除了在建立连接时指定，还可以在这里指定as_dict=True
cursor = conn.cursor(as_dict=True)

cursor.execute('SELECT * FROM persons')
for row in cursor:
    print("ID=%d, Name=%s" % (row['id'], row['name']))

另外，还可以使用fetchmany和fetchall来一次性获取指定数量或者所有的结果。

Cursor对象注意事项

一条链接在任何时候只会有一个Cursor对象处于查询状态，这是因为底层的TDS协议没有客户端侧的Cursor，协议要求客户端在进行下一次查询前先完成上一次的。因此如果你想同时在一个连接上进行两个查询的话可能会出现各种惊喜，也许在使用多线程等方式的时候需要特别注意这点：

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()

关闭链接

操作完成后应该调用close方法来关闭链接并释放资源：

conn.close()

可以使用with语句来处理Connection和cursor对象，这样就不需要手动关闭他们了：

with pymssql.connect(server, user, password, "tempdb") 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']))

文档

如果想要更加详细了解这个模块的话，请移步：
https://blog.csdn.net/lin_strong/article/details/82880806

参考文献

[1] http://www.pymssql.org/en/stable/intro.html

---

1. Python DB-API 是一个规范，它定义了一系列必须的对象和数据库存取方式，以便为各种各样的底层数据库系统和多种多样的数据库接口程序提供一致的访问接口。 ↩︎

2. FreeTDS是一个程序库，其实现了在Windows、Unix和Linux系统下访问Microsoft SQL Server和Sybase数据库，是TDS（表列数据流 ）协议的一个开源实现。 ↩︎