基本步骤:
- 导入pymssql库
- 建立连接conn
常用参数:
host:主机
user:用户名
password:密码
database:数据库
charset:字符集,‘utf8’
as_dict:查询结果list列表中的元素是否以字典返回(默认False,列表中元素为元组)
autocommit:自动提交事务(默认False,需要使用commit()提交事务) - 通过conn打开游标cursor(返回None打开失败),执行sql语句
- 使用获取查询结果,cursor.fetchall()获取所有未获取的记录,cursor.fetchone()获取第一条记录,cursor.fetchmany(i)获取前i条记录
- insert,update,delete操作后,需要conn.commit()提交事务,保存操作
- 操作结束,conn.close()关闭数据库连接
>>> import pymssql
# 建立连接
>>> conn=pymssql.connect(host='HOST',user='USER',password='PASSWORD',database='test',charset='utf8')
# 打开游标
>>> cur=conn.cursor()
# 执行sql查询语句
>>> cur.execute('select * from test1')
# 游标位置开始获取第一条记录
>>> rs=cur.fetchone()
>>> print(rs)
(1, 'A ')
# 游标位置开始获取多条记录
>>> rs=cur.fetchmany(2)
>>> print(rs)
[(2, 'B '), (3, 'C ')]
# 获取所有剩下未获取的记录,之前已经获取过的记录无法再次获取
>>> rs=cur.fetchall()
# 默认查询记录为list类型,元素为tuple类型
>>> print(rs)
[(4, 'D '), (5, 'E '), (6, 'F '), (7, 'G '), (8, 'H '), (9, 'I '), (10, 'J ')]
# 只有fetchall()获取记录后才能获取行数,未获取记录或者fetchone()和fetchmany()的rowcount都是-1
# rowcount不仅仅是fetchall()所获取的行数,还包括之前fetchone()和fetchmany()获取的行数,即整个查询结果的所有行数
>>> cur.rowcount
10
# 建立连接时添加参数as_dict=True,查询记录list里的元素则为dict类型
>>> conn=pymssql.connect(host='HOST',user='USER',password='PASSWORD',database='test',charset='utf8',as_dict=True)
>>> print(rs)
[{'id': 1, 'value': 'A '}, {'id': 2, 'value': 'B '}, {'id': 3, 'value': 'C '}, {'id': 4, 'value': 'D '}, {'id': 5, 'value': 'E '}, {'id': 6, 'value': 'F '}, {'id': 7, 'value': 'G '}, {'id': 8, 'value': 'H '}, {'id': 9, 'value': 'I '}, {'id': 10, 'value': 'J '}]
# insert,update,delete操作
>>> cur.execute("insert into test1 (value) values ('k')")
# lastrowid,获取插入记录的id号,id需在数据库中设置为自动增量标识
>>> int(cur.lastrowid)
11
# 返回受影响的行数
>>> cur.rowcount
1
# 事务回滚,撤销未commit的更改
>>> conn.rollback()
# 执行insert,update,delete操作后,需commit()提交事务,否则连接关闭后更改失效
>>> conn.commit()
# 查询语句参数化,将所需参数按顺序放入元组中,再将该元组作为参数代入执行,注意单个元素的元组需要加逗号!
>>> cur.execute('select * from test1 where value=%s',('D',))
>>> rs=cur.fetchall()
>>> print(rs)
[(4, 'D ')]
# 无参数存储过程
>>> cursor.callproc('SP_XXXXX')
# 有参数存储过程
>>> cursor.callproc('SP_XXXXX',('ABC',))
# 有output的存储过程,但没成功,返回值msg应当为代入存储过程的参数元组
>>> msg=cursor.callproc('SP_XXXXX',('ABC',pymssql.output(str)))
>>> output=msg[1]
# 有返回值的存储过程也没成功,网上最多的方法都是以sql语句进行操作
# SP_test1说明:input参数@arg,查询test1所有记录,output返回行数,returnvalue返回999
# 此sql共返回3个记录集,第一个是查询结果,第二个是output,第三个是returnvalue
>>> sql="DECLARE @return_value int,@rows int;EXEC @return_value = [dbo].[SP_test1] @arg = N'b',@rows = @rows OUTPUT;SELECT @rows as N'@rows';SELECT 'Return Value' = @return_value"
>>> rs=cur.fetchall()
>>> rs
[(1, 'A '), (2, 'B '), (3, 'C '), (4, 'D '), (5, 'E '), (6, 'F '), (7, 'G '), (8, 'H '), (9, 'I '), (10, 'J '), (11, 'k ')]
# 指向下个记录集
>>> cur.nextset()
>>> rs=cur.fetchall()
>>> rs
[(11,)]
# 指向下个记录集
>>> cur.nextset()
>>> rs=cur.fetchall()
>>> rs
[(999,)]
>>> cur.close()
>>> conn.close()
使用with语句简化操作,无需进行close()操作:
>>> with pymssql.connect(host='HOST',user='USER',password='PASSWORD',database='test',charset='utf8') as conn:
with conn.cursor() as cur:
cur.execute('select * from test1')
rs=cur.fetchall()
for row in rs:
print(row[0],row[1])
1 A
2 B
3 C
4 D
5 E
6 F
7 G
8 H
9 I
10 J
11 k