pyodbc访问数据库(python ODBC访问数据库)

入门

连接到数据库

调用connect方法并传入ODBC连接字符串,其会返回一个connect对象。通过connect对象,调用cursor()方法,可以获取一个游标cursor。如下代码示例:

import pyodbc

#连接示例: Windows系统, DSN方式, 使用微软 SQL Server 数据库驱动

cnxn =pyodbc.connect('DRIVER={SQL Server};SERVER=localhost;PORT=1433;DATABASE=testdb;UID=me;PWD=pass')

#连接示例: Linux系统, DSN方式, 使用FreeTDS驱动

cnxn =pyodbc.connect('DRIVER={FreeTDS};SERVER=localhost;PORT=1433;DATABASE=testdb;UID=me;PWD=pass;TDS_Version=7.0')

#连接示例:使用DSN方式

cnxn = pyodbc.connect('DSN=test;PWD=password')

# 打开游标

cursor =cnxn.cursor()

以上示例只是标准示例,具体的ODBC连接字符串以你自己使用的驱动为准。

查询一些数据

所有SQL语句都使用Cursor.execute()方法执行。比如select语句会返回一些结果行,你可以使用游标(Cursor)相关的函数功能(fetchonefetchallfetchmany)对结果进行检索。

Cursor.fetchone 用于返回一个单行( Row)对象:

cursor.execute("selectuser_id, user_name from users")

row =cursor.fetchone()

if row:

    print(row)

Row 对象是类似一个python元组(tuples),不过也可以通过列名称来访问,例如:

cursor.execute("selectuser_id, user_name from users")

row =cursor.fetchone()

print('name:',row[1])         # 使用列索引号来访问数据

print('name:',row.user_name)  # 或者直接使用列名来访问数据

当所有行都已被检索,则fetchone返回None.

while 1:

    row = cursor.fetchone()

    if not row:

        break

    print('id:', row.user_id)

Cursor.fetchall方法返回所有剩余行并存储于一个列表中。如果没有行,则返回一个空列表。(注意:如果有很多行,会造成大量内存占用。Fetchall会一次性将所有数据查询到本地,然后再遍历)

cursor.execute("selectuser_id, user_name from users")

rows = cursor.fetchall()

for row in rows:

    print(row.user_id, row.user_name)

如果并不在意数据处理时间,可以使用光标本身作为一个迭代器,逐行迭代。这样可以节省大量的内存开销,但是由于和数据来回进行通信,速度会相对较慢:

cursor.execute("selectuser_id, user_name from users"):

for row in cursor:

    print(row.user_id, row.user_name)

由于Cursor.execute总是返回游标(cursor, 所以也可以简写成:

for row incursor.execute("select user_id, user_name from users"):

    print(row.user_id, row.user_name)

我们可以在execut中使用”””三重引号,来应用多行SQL字符串。这样sql的可读性大大增强。这是python特有的特性:

cursor.execute(

    """

    select user_id, user_name

      from users

     where last_logon < '2001-01-01'

           and bill_overdue = 1

    """)

SQL参数

ODBC支持使用问号作为SQL的查询参数占位符。可以在execute方法的SQL参数之后,提供SQL参数占位符的值:

cursor.execute(

    """

    select user_id, user_name

      from users

     where last_logon < ?

           and bill_overdue = ?

    """, '2001-01-01', 1)

这样做可以防止SQL注入攻击,提高安全性。如果使用不同的参数反复执行相同的SQL它效率会更高,这种情况下该SQL将只预装(prepared )一次。pyodbc只保留最后一条编写的语句,所以如果程序在语句之间进行切换,每次都会预装,造成多次预装。)

PythonDB API指定参数应以序列(sequence)对象传递,所以pyodbc也支持这种方式:

cursor.execute(

    """

    select user_id, user_name

      from users

     where last_logon < ?

           and bill_overdue = ?

    """, ['2001-01-01', 1])

插入数据

插入数据使用相同的函数 - 通过传入insert SQL和相关占位参数执行插入数据:

cursor.execute("insertinto products(id, name) values ('pyodbc', 'awesome library')")

cnxn.commit()

cursor.execute("insertinto products(id, name) values (?, ?)", 'pyodbc', 'awesome library')

cnxn.commit()

注意:调用cnxn.commit()。发成错误可以回滚。具体需要看数据库特性支持情况。如果数据发生改变,最好进行commit。如果不提交,则在连接中断时,所有数据会发生回滚。

更新和删除数据

更新和删除工作以同样的方式:通过特定的SQL来执行。通常我们都想知道更新和删除的时候有多少条记录受到影响,可以使用Cursor.rowcount来获取值:

cursor.execute("deletefrom products where id <> ?", 'pyodbc')

print('Deleted {}inferior products'.format(cursor.rowcount))

cnxn.commit()

由于execute 总是返回游标(允许你调用链或迭代器使用),有时我们直接这样简写:

deleted =cursor.execute("delete from products where id <> 'pyodbc'").rowcount

cnxn.commit()

注意一定要调用commit。否则连接中断时会造成改动回滚。

技巧和窍门

引号

于单引号SQL是有效的,当值需要使用单引号时,使用用双引号包围的SQL:

cursor.execute("deletefrom products where id <> 'pyodbc'")

如果使用三重引号, 我们可以这样使用单引号:

cursor.execute(

    """

    delete

      from products

     where id <> 'pyodbc'

    """)

列名称

Microsoft SQLServer之类的一些数据库不会产生计算列的列名,在这种情况下,需要通过索引来访问列。我们也可以使用sql列别名的方式,为计算列指定引用名称:

row =cursor.execute("select count(*) as user_count fromusers").fetchone()

print('{}users'.format(row.user_count)

当然也可以直接使用列索引来访问列值:

count =cursor.execute("select count(*) from users").fetchone()[0]

print('{}users'.format(count)

注意,上例的首列不能是Null。否则fetchone方法将返回None并且会报NoneType不支持索引的错误。如果有一个默认值,经常可以是ISNULL或合并:

maxid =cursor.execute("select coalesce(max(id), 0) fromusers").fetchone()[0]

自动清理

连接(默认)在一个事务中。如果一个连接关闭前没有提交,则会进行当前事务回滚。很少需要finallyexcept 语句来执行人为的清理操作,程序会自动清理。

例如,如果下列执行过程中任何一条SQL语句出现异常,都将引发导致这两个游标执行失效。从而保证原子性,要么所有数据都插入发生,要么所有数据都不插入。不需要人为编写清理代码。

cnxn =pyodbc.connect(...)

cursor = cnxn.cursor()

cursor.execute("insertinto t(col) values (1)")

cursor.execute("insertinto t(col) values (2)")

cnxn.commit()

 

阅读更多

没有更多推荐了,返回首页