pyodbc文档之 Getting started（开始使用）

2018-07-02 本文已影响0人枫叶落尽

Connect to a Database（连接到数据库）

传递一个 ODBC连接字符串给pyodbc connect()函数,函数将返回一个 Connection. 有了connection 之后，你就可以创建Cursor.（游标），就像下面这样：

import pyodbc

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

# Using a DSN, but providing a password as well
cnxn = pyodbc.connect('DSN=test;PWD=password')

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

当建立连接的时候，有许多的选项，详情参见 connect()函数以及 the Connecting to Databases 这一节。

确保你设置了正确的你所连接的数据库所要求的编码或解码设置，以及使用适合的Python版本：

# This is just an example that works for PostgreSQL and MySQL, with Python 2.7.
cnxn.setdecoding(pyodbc.SQL_CHAR, encoding='utf-8')
cnxn.setdecoding(pyodbc.SQL_WCHAR, encoding='utf-8')
cnxn.setencoding(encoding='utf-8')

Selecting Some Data（选择一些数据）

Select Basics

所有的SQl语句都是用Cursor（）函数（游标）执行。如果语句返回的是行的集合，比如select语句，你可以使用Cursor 的 fetch函数 - fetchone(), fetchall(), fetchmany()。如果返回的结果没有行, fetchone() 会返回 None,但是 fetchall() 和 fetchmany() 都会返回空的列表（list)。

cursor.execute("select user_id, user_name from users")
row = cursor.fetchone()
if row:
    print(row)

Row objects are similar to tuples, but they also allow access to columns by name:
行对象和元组很类似，但是它们允许通过列名获取：

cursor.execute("select user_id, user_name from users")
row = cursor.fetchone()
print('name:', row[1])          # 通过下标访问 access by column index (zero-based)
print('name:', row.user_name)   # 通过列名访问 access by name

当检索完所有的行后， fetchone() 函数返回None。

while True:
    row = cursor.fetchone()
    if not row:
        break
    print('id:', row.user_id)

fetchall()函数返回一个列表中的所有剩余行。记住，这些行将全部存储在内存中，所以如果有很多行，则可能内存不足。如果没有行，则返回空列表。

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

如果要一次处理一行，可以使用游标本身作为迭代器：

cursor.execute("select user_id, user_name from users"):
for row in cursor:
    print(row.user_id, row.user_name)

或者这样子：

for row in cursor.execute("select user_id, user_name from users"):
    print(row.user_id, row.user_name)

Parameters（参数）

ODBC支持在SQL中使用问号来为参数占位。并在紧随SQL语句之后将问号替换为具体的值：

cursor.execute("""
    select user_id, user_name
      from users
     where last_logon < ?
       and bill_overdue = ?
""", datetime.date(2001, 1, 1), 'y')

这比将值放入字符串更安全，因为参数分别传递到数据库，以防止SQL注入攻击。如果用不同的参数重复执行相同的SQL语句，则效率也会更高。SQL语句只会在数据库中编译一次。（pyodbc 只保留最后一次编译的SQL语句，所以如果你执行不同的SQL语句，那么每个语句会被多次重新编译（亦即每次执行都会重新编译）。）

Python DB API指定参数应该作为一个序列传递，在pyodbc中也同样支持：

cursor.execute("""
    select user_id, user_name
      from users
     where last_logon < ?
       and bill_overdue = ?
""", [datetime.date(2001, 1, 1), 'y'])

Inserting Data（插入数据）

若要插入数据，请将插入SQL语句传递给 Cursor execute()，以及需要的参数。

cursor.execute("insert into products(id, name) values ('pyodbc', 'awesome library')")
cnxn.commit()

或者，参数化查询：

cursor.execute("insert into products(id, name) values (?, ?)", 'pyodbc', 'awesome library')
cnxn.commit()

注意此处的调用 cnxn.commit()。你必须调用commit (或者在connection中设置 autocommit 为 True ) ，否则你的查询不会被执行（数据库不会有改变）。

Updating and Deleting（更新和删除）

更新和删除的方法都是一样的。然而，如果希望知道在更新和删除数据时有多少条数据（记录）受影响，在这种情况下，您可以使用Cursor（游标）的 rowcount（行计数）属性：

cursor.execute("delete from products where id <> ?", 'pyodbc')
print(cursor.rowcount, 'products deleted')
cnxn.commit()

因为 execute() 通常返回 cursor（游标），你有时会看到这样的代码 (游标执行 .rowcount（末尾）).

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

注意调用commit() （示例代码中的 cnxn.commit() ）。你必须调用commit (或者在connection中设置 autocommit（自动提交）为 True ) ，否则你的语句不会生效（被数据库执行）!

Tips and Tricks（技巧和提示）

Quotes（引号的使用）

因为在SQL中，单引号是合法的，使用双引号把SQl语句包围起来：

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

使用“原始”字符串来避免SQL无意中的转义（除非你真的想要指定转义字符）：

cursor.execute("delete from products where name like '%bad\name%'")   #Python会将 \n 转义为 换行 
cursor.execute(r"delete from products where name like '%bad\name%'")  # 不会转换，保留完整的原始字符串

Naming Columns（列命名）

一些数据库（例如：SQL Server）不会为聚合函数所生成的字段（列）命名，例如：COUNT(*)，在这种情况下，您可以通过它的索引访问列，或者在列上使用别名（即使用“AS”关键字进行命名）。

row = cursor.execute("select count(*) as user_count from users").fetchone()
print('%s users' % row.user_count)

Formatting Long SQL Statements（格式化长SQL语句）

有许多方法去格式化一个包含长SQl语句的Python字符串。使用triple-quote string format（直译：三重引用字符串格式）是显而易见的方法（译注：可以直接搜索格式化SQL 就有许多在线格式化网站可以选择）。
这样做会在左侧创建大量空白的字符串（空格），但是数据库SQL引擎会忽略空白（包括tabs和换行）。如果你一定要移除这些空白，你可以使用内置的 textwrap 模块中的 dedent() 函数。例如：

import textwrap
. . .
sql = textwrap.dedent("""
    select p.date_of_birth,
           p.email,
           a.city
    from person as p
    left outer join address as a on a.address_id = p.address_id
    where p.status = 'active'
      and p.name = ?
""")
rows = cursor.execute(sql, 'John Smith').fetchall()

fetchval（fetchval函数）

如果你查询的是一个值（单个值，而非一条记录），你可以使用一个非常方便的方法（函数）fetchval。
如果SQL语句得到的结果是行（记录的形式），返回第一行的第一列的值。如果没有行（记录），返回None：

maxid = cursor.execute("select max(id) from users").fetchval()

大多数数据库支持 COALESCE 或 ISNULL ，能够把NULL转换为hardcoded value（硬编码值），但是，如果执行结果没有返回任何行的话，这个函数也不会返回任何行。这也就是说，COALESCE是非常有用的聚合函数，如max或count，但是当试图从特定行检索值时，fetchval更好：

cursor.execute("select coalesce(max(id), 0) from users").fetchone()[0]
cursor.execute("select coalesce(count(*), 0) from users").fetchone()[0]

在语句能够返回一个空集的情况下，fetchval则是绝佳的选择：

# Careful!
cursor.execute(
    """
    select create_timestamp
    from photos
    where user_id = 1
    order by create_timestamp desc
    limit 1
    """).fetchone()[0]

# Preferred
cursor.execute(
    """
    select max(updatetime), 0)
    from photos
    where user = 1
    order by create_timestamp desc
    limit 1
    """).fetchval()

第一个示例中，如果没有任何行有user_id 1，将引发异常。调用 fetchone() 返回 None.然后，Python会尝试对无效的结果进行[0]为索引的访问（None[0]) .即引发异常。
fetchval 是为这种情况而创建的，它检测到没有符合条件的行，然后返回None。