python对于sqlserver的基本操作

基本步骤：

1. 导入pymssql库
2. 建立连接conn
   常用参数：
   host：主机
   user：用户名
   password：密码
   database：数据库
   charset：字符集，‘utf8’
   as_dict：查询结果list列表中的元素是否以字典返回（默认False，列表中元素为元组）
   autocommit：自动提交事务（默认False，需要使用commit()提交事务）
3. 通过conn打开游标cursor（返回None打开失败），执行sql语句
4. 使用获取查询结果，cursor.fetchall()获取所有未获取的记录，cursor.fetchone()获取第一条记录，cursor.fetchmany(i)获取前i条记录
5. insert，update，delete操作后，需要conn.commit()提交事务，保存操作
6. 操作结束，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