pyodbc 操作SQL Server数据库

pyodbc是Python包,使用ODBC驱动器来连接SQL Server数据库,pyodbc的基本类型是Connection,Cursor和Row,其中,Connection表示客户端和数据库的连接,并用于提交事务;Cursor表示向数据库发送的查询请求,Row表示获取的结果集。

从微软官方文档来看,更推荐使用pyodbc来操作SQL Server数据库。

一,ODBC驱动程序

要使用pyodbc连接SQL Server数据库,必须安装SQL Server数据库的ODBC 驱动程序:

Install the Microsoft ODBC Driver for SQL Server on Windows

当前的版本是Microsoft ODBC Driver 17,支持从SQL Server 2008到当前最新的SQL Server 2019版本。

{ODBC Driver 17 for SQL Server} 

Microsoft ODBC Driver for SQL Server 是一个动态链接库(DLL),包含run-time支持库,使得程序可以使用native-code API连接到SQL Server数据库。

使用pyodbc连接数据库的基本操作,连接数据库,执行查询,并遍历查询结果:

import pyodbc

# Specifying the ODBC driver, server name, database, etc. directly
cn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER=localhost;DATABASE=testdb;UID=me;PWD=pass')

# Create a cursor from the connection
cursor = cn.cursor()

#Selecting Some Data
cursor.execute("select user_id, user_name from users")
rows = cursor.fetchall()
for row in rows:
    print(row.user_id, row.user_name)

cn.close()

二,连接对象

连接对象代表Python和数据库的连接,通过connect()函数来创建连接对象,连接对象通过ODBC驱动程序来访问SQL Server数据库:

cn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER=localhost;DATABASE=mine;UID=user;PWD=pwd')

1,连接对象的属性

cn.autocommit 是连接对象的一个属性,默认情况下,连接对象是不会自动提交的,该属性的值是False。在执行完成查询请求之后,需要显示提交。

如果设置autocommit为True,那么设置连接为自动提交模式:

cn.autocommit = True

cn.timeout, 用于设置超时时间,单位是秒,默认值是0,表示不限制时间。

2,连接对象可以创建Cursor对象

mycursor = cn.cursor()

3,连接对象提交或回滚事务

如果Cursor对象执行的是更新操作,比如执行UPDATE、DELETE或INSERT等更新操作,那么必须通过连接对象来显式提交事务;如果执行的更新操作失败,连接对象必须显式回滚事务。如果Cursor对象执行的SELECT操作,不需要提交事务或者回滚事务。

cn.commit()
cn.rollback()

4,关闭连接对象

当关闭连接时,任何未提交的查询语句都会回滚,更新操作的结果将会丢失。当连接对象被删除,特别是超出作用域时,连接会自动关闭,但是,推荐显式关闭连接。

cn.close()

三,Cursor对象

Cursor对象用于管理每个fetch操作的上下文,Cursor对象通过Connection对象来创建。

1,执行SQL语句

Cursor调用execute()函数来执行SQL语句:

execute(sql, *parameters)
executemany(sql, *params)

execute()函数只执行一次SQL语句,可以向SQL语句传递参数,传参的格式有以下两种:

# standard
cursor.execute("select a from tbl where b=? and c=?", (x, y))
# pyodbc extension
cursor.execute("select a from tbl where b=? and c=?", x, y)

executemany()可以对每组参数执行相同的SQL语句,

params = [ ('A', 1), ('B', 2) ]
cursor.executemany("insert into t(name, id) values (?, ?)", params)

该语句等价于执行execute()函数两次:

params = [ ('A', 1), ('B', 2) ]
for p in params:
    cursor.execute("insert into t(name, id) values (?, ?)", p)

如果设置cursor.fast_executemany = True,可以提高executemany()函数的性能,该属性默认是False。

2,fetch结果

从cursor对象中获取单行:

cursor.fetchone()

从cursor对象中获取多行:

cursor.fetchone(size=1)

从cursor对象中获取所有行:

cursor.fetchall()

3,跳到下一个结果集

此方法将使Cursor对象跳至下一个可用结果集,并丢弃当前结果集中的所有剩余行。 如果没有更多结果集,则该方法返回False。 否则,它将返回True,随后对fetch方法的调用将返回下一个结果集中的行。

如果执行返回多个结果的存储过程,那么主要使用此方法。

cursor.nextset()

4,关闭连接

在不再需要cursor时,请使用关闭Cursor对象。

cursor.close()

如果试图访问已经被关闭的cursor,程序将会出错。Cursor对象会在被删除或者超出作用域时自动关闭。

四,Row对象

Row对象调用fetch函数返回的一行数据,row对象的值可以被替换,从同一个select语句中返回的row对象共享内存。

row对象可以通过索引来访问数据,也可以通过列名来访问数据:

cursor.execute("select album_id, photo_id from photos where user_id=1")
row = cursor.fetchone()
print(row.album_id, row.photo_id)
print(row[0], row[1])  # same as above, but less readable

fetchone()函数返回的是单行Row对象,fetchmany()函数 或这fetchall()函数返回的rows对象的列表:

cursor.execute("select album_id, photo_id from photos where user_id=1")
rows = cursor.fetchall()
for row in rows:
    row.album_id=1
row.photo_id=1001

五,上下文管理器

Connection对象和Cursor对象都支持Python的上下文管理(Context manager),使用with语句来自动关闭Connection对象和Cursor对象,并调用Connection对象的commit()函数来提交查询。

1,Connection对象的上下文管理器

Connection对象的上下文管理器,不仅自动关闭连接,还会提交查询:

with pyodbc.connect('mydsn') as cnxn:
    do_stuff

#等价于
cnxn = pyodbc.connect('mydsn')
do_stuff
if not cnxn.autocommit:
    cnxn.commit()  

2,Cursor对象的上下文管理器

Cursor对象的上下文管理器,会自动关闭Cursor,还会提交查询:

with cnxn.cursor() as crsr:
    do_stuff

#等价于
crsr = cnxn.cursor()
do_stuff
if not cnxn.autocommit:
    cnxn.commit()  

3,使用上下文管理器来查询结果

import pyodbc

# Specifying the ODBC driver, server name, database, etc. directly
with pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER=localhost;DATABASE=testdb;UID=me;PWD=pass') as cn:

    # Create a cursor from the connection
    with cn.cursor() as cursor:
        
        #Selecting Some Data
        cursor.execute("select user_id, user_name from users")
        rows = cursor.fetchall()
        for row in rows:
            print(row.user_id, row.user_name)

4,使用上下文管理器来执行更新操作

在执行更新操作,需要显式提交事务

import pyodbc

with pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER=localhost;DATABASE=testdb;UID=me;PWD=pass') as cn:
    try:
        cn.autocommit = False
        with cn.cursor() as cursor:
            params = [ ('A', 1), ('B', 2) ]
            cursor.executemany("insert into t(name, id) values (?, ?)", params)
    except pyodbc.DatabaseError as err:
        cn.rollback()
    else:
        cn.commit()
    finally:
        cn.autocommit = True

六,调用存储过程

pyodbc 使用{call ...}来调用存储过程,调用存储过程的格式如下:

cursor.execute("{CALL usp_NoParameters}")
params = (14, "Dinsdale") cursor.execute("{CALL usp_HaveParammeters (?,?)}", params)

 

 

 

参考文档:

pyodbc wiki

作者悦光阴
本文版权归作者和博客园所有,欢迎转载,但未经作者同意,必须保留此段声明,且在文章页面醒目位置显示原文连接,否则保留追究法律责任的权利。
posted @ 2020-09-05 12:42  悦光阴  阅读(2820)  评论(0编辑  收藏  举报