使用pymssql模块操作SQL Server数据库

一，连接数据库

使用pymssql连接SQL Server数据库，首先创建连接和游标：


import pymssql

conn = pymssql.connect(host='host',user='user',password='pwd',database='db_name')
cursor = conn.cursor()



1，行的格式

当执行select语句获取数据时，返回的数据行有两种格式：元组和字典，行的默认格式是元组。pymssql返回的数据集的格式是在创建游标时设置的，当参数 as_dict为True时，返回的行是字典格式，该参数的默认值是False，因此，默认的行格式是元组。



cursor = conn.cursor(as_dict=True)



2，执行查询

使用游标执行SQL语句



cursor.execute("sql statement")



3，提交事务

当执行更新操作时，需要显式调用 commit()函数来提交事务：



conn.commit()



如果事务执行失败，可以回滚事务：



conn.rollback()



4，关闭连接，释放资源



conn.close()



二，更新数据

对数据库有查询，更新和新增操作，在更新和插入数据时，需要显式提交事务。当需要更新数据时，调用游标的execute()函数执行SQL命令来实现：



Cursor.execute(operation)
Cursor.execute(operation, params)



如果要在一个事务中执行多条SQL命令，可以调用游标的executemany()函数：



Cursor.executemany(operation, params_seq)



1，执行数据更新和删除

通过游标的execute()函数来执行TSQL语句，调用 commit() 来提交事务



cursor.execute("""
sql statement
""")　　

conn.commit()



2，执行数据的多行插入

如果需要插入多条记录，可以使用游标的executemany()函数，该函数包含模板SQL 命令和一个格式化的参数列表，用于在一条事务中插入多条记录：



args=[(1, 'John Smith', 'John Doe'),
     (2, 'Jane Doe', 'Joe Dog'),
     (3, 'Mike T.', 'Sarah H.')]

cursor.executemany("INSERT INTO persons VALUES (%d, %s, %s)", args )
conn.commit()



三，遍历数据

当从SQL Server数据库中获取数据时，可以使用fetchone()从结果集中提取一条记录，使用fetchmany()从结果集中提取多条记录，或使用fetchall()提取全部结果集：



cursor.fetchone()           # return a row (a tuple or a dict) if as_dict was passed to pymssql.connect()
cursor.fetchmany(size=None) # return a list of tuples or dicts if as_dict was passed to pymssql.connect() 
cursor.fetchall()           # return a list of tuples or dicts if as_dict was passed to pymssql.connect()



由于游标是一个迭代器，因此，可以使用for语句以迭代方式逐行处理查询的结果集。



for row in cursor:



1，以元组方式遍历数据

返回的结果集中，每一行都是一个元组：



cursor=connect.cursor()
cursor.execute('SELECT * FROM persons WHERE salesrep=%s', 'John Doe')
for row in cursor:
    print('row = %r' % (row,))



2，以字典方式遍历数据

返回的结果集，每一行是一个字典结构：



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来自动关闭连接：



import pymssql 
　
with pymssql.connect(server, user, password, "db_name") 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 reference

Python中从SQL型数据库读写dataframe型数据