pyodbc的简单使用

Seawind2012

1、连接数据库
& J( V  p! V9 v1 X* n# T
- t  a" D! P& y% l1）直接连接数据库和创建一个游标（cursor)
1        cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=localhost;DATABASE=testdb;UID=meWD=pass')
' B% ?8 ~- m9 K+ }0 K2        cursor = cnxn.cursor()
: i! [7 X4 S3 [, Q5 K0 q; V
7 N4 @1 x; o2 v1 s! e6 n% s  }2）使用DSN连接。通常DSN连接并不需要密码，还是需要提供一个PSW的关键字。
: }9 N  v8 ^7 E; r% m% o1        cnxn = pyodbc.connect('DSN=testWD=password')
2        cursor = cnxn.cursor()

关于连接函数还有更多的选项，可以在pyodbc文档中的 connect funtion 和 ConnectionStrings查看更多的细节

2、数据查询（SQL语句为 select ...from..where）

/ W6 V: A$ P& E) f6 d1）所有的SQL语句都用cursor.execute函数运行。如果语句返回行，比如一个查询语句返回的行，你可以通过游标的fetch函数来获取数据，这些函数有（fetchone,fetchall,fetchmany）.如果返回空行，fetchone函数将返回None,而fetchall和fetchmany将返回一个空列。
1        cursor.execute("select user_id, user_name from users")
2        row = cursor.fetchone()
) T  M9 H0 ^6 @' [, C3 z3        if row:
4            print row
+ }4 h( `; ?; c! t
+ d/ c& T- H8 t8 Y7 a" w  i2）Row这个类，类似于一个元组，但是他们也可以通过字段名进行访问。
$ `& p3 T! \5 @, @. P1        cursor.execute("select user_id, user_name from users")
; ^8 W" s4 ~) C6 A* v* J8 x2        row = cursor.fetchone()
" }( x' R6 u& T3        print 'name:', row[1]          # access by column index
+ `) ?. q+ Z  ^, [2 T  Z6 b4        print 'name:', row.user_name   # or access by name

3）如果所有的行都被检索完，那么fetchone将返回None.
. w4 }! `: Q: Z4 u! D- A1        while 1:
2            row = cursor.fetchone()
: f( N2 ]' X; ^; R+ v3            if not row:
% `) a4 n2 c& `4                break
! K4 y, Q4 }+ W# t9 }% K5            print 'id:', row.user_id
# \0 U+ O, @' K- Y. u
' F. V& E9 Y2 j7 ~* y& b4)使用fetchall函数时，将返回所有剩下的行，如果是空行，那么将返回一个空列。（如果有很多行，这样做的话将会占用很多内存。未读取的行将会被压缩存放在数据库引擎中，然后由数据库服务器分批发送。一次只读取你需要的行，将会大大节省内存空间）
2 |# q, x, ]0 X9 X2 V  m1        cursor.execute("select user_id, user_name from users")
2        rows = cursor.fetchall()
' _3 Q$ _8 o. G+ }/ x4 y+ F! y3 b3        for row in rows:
4            print row.user_id, row.user_name
" ^, m3 B1 N" k2 R% h
/ f- M; [  b# B! `$ u. ~5）如果你打算一次读完所有数据，那么你可以使用cursor本身。
2 b9 Z$ w( B2 t( c% B1        cursor.execute("select user_id, user_name from users"):
2        for row in cursor:
2 w$ h+ i3 I# N6 ?8 f( j3            print row.user_id, row.user_name

6）由于cursor.execute返回一个cursor，所以你可以把上面的语句简化成：
9 f: {7 I( K8 {% @8 y( B1        for row in cursor.execute("select user_id, user_name from users"):
2            print row.user_id, row.user_name

7）有很多SQL语句用单行来写并不是很方便，所以你也可以使用三引号的字符串来写：
1        cursor.execute("""
2                       select user_id, user_name
3                         from users
4                        where last_logon < '2001-01-01'
5                          and bill_overdue = 'y'
6                       """)

3、参数
, `) b1 z; w2 M
/ f/ A  O; Y5 Z2 k1）ODBC支持在SQL语句中使用一个问号来作为参数。你可以在SQL语句后面加上值，用来传递给SQL语句中的问号。
) `3 W  j/ ]9 a6 R/ {( @! {4 w1        cursor.execute("""
: x/ E) T5 j! U' y8 g6 p2                       select user_id, user_name
3                         from users
  T+ g7 R9 b% L  h6 t! {4                        where last_logon < ?
) [7 s3 S  n+ A4 P) k3 e8 }5                          and bill_overdue = ?
6                       """, '2001-01-01', 'y')

这样做比直接把值写在SQL语句中更加安全，这是因为每个参数传递给数据库都是单独进行的。如果你使用不同的参数而运行同样的SQL语句，这样做也更加效率。

3）python DB API明确说明多参数时可以使用一个序列来传递。pyodbc同样支持：
1        cursor.execute("""
6 W- y2 A9 M% Z& g8 {7 t" A  ~/ G. H: m6 T, L2                       select user_id, user_name
3                         from users
4                        where last_logon < ?
5                          and bill_overdue = ?
% {6 {" S* A5 e& v' X1 w  k& s6                       """, ['2001-01-01', 'y'])
$ q" o9 B" S% N" g. Q
1        cursor.execute("select count(*) as user_count from users where age > ?", 21)
2        row = cursor.fetchone()
: y( _  [7 c7 b/ ?6 j; C9 f9 [3        print '%d users' % row.user_count
, c, `+ \9 @: _2 i9 H, c1 {" H( D7 Y1 q
2 E& U3 R2 {$ m1 t* x
/ P, ^; i- ?/ U8 k5 k
4、数据插入
) q' Z+ i8 I5 ]. ]3 Q1 ?9 ?
1）数据插入，把SQL插入语句传递给cursor的execute函数，可以伴随任何需要的参数。
8 |' K- U/ G; U5 m1        cursor.execute("insert into products(id, name) values ('pyodbc', 'awesome library')")
% {$ g, z8 ]4 \) \8 P5 @/ I2        cnxn.commit()

# U+ \8 A: W. K1        cursor.execute("insert into products(id, name) values (?, ?)", 'pyodbc', 'awesome library')
6 c6 C% U$ p$ J1 _3 B2        cnxn.commit()

8 f9 v' {. e+ P3 h% x6 @5 P& i注意调用cnxn.commit()函数：你必须调用commit函数，否者你对数据库的所有操作将会失效！当断开连接时，所有悬挂的修改将会被重置。这很容易导致出错，所以你必须记得调用commit函数。
# S+ u, w8 _* J: Y# J( ^2 {& s- x( r- K
5、数据修改和删除
$ O1 P, G8 z3 a
1）数据修改和删除也是跟上面的操作一样，把SQL语句传递给execute函数。但是我们常常想知道数据修改和删除时，到底影响了多少条记录，这个时候你可以使用cursor.rowcount的返回值。
1        cursor.execute("delete from products where id <> ?", 'pyodbc')
$ M1 \8 w9 v  Y& Z, c+ n2        print cursor.rowcount, 'products deleted'
. s! U2 J" L3 N/ C- b  b3        cnxn.commit()

2）由于execute函数总是返回cursor，所以有时候你也可以看到像这样的语句：（注意rowcount放在最后面）
1        deleted = cursor.execute("delete from products where id <> 'pyodbc'").rowcount
2        cnxn.commit()
" q/ z( l; D( N$ ^* o$ k. A& w
  f7 Y7 R. E0 j8 U* a同样要注意调用cnxn.commit()函数

% v. ]; f. s; d/ a6、小窍门

1）由于使用单引号的SQL语句是有效的，那么双引号也同样是有效的：
+ J+ f$ x% x9 J1        deleted = cursor.execute("delete from products where id <> 'pyodbc'").rowcount
5 C3 {1 g3 u' W/ J2 z) |
# B+ ~0 N0 S2 i, n; C2 i2）假如你使用的是三引号，那么你也可以这样使用：
  i; q8 [+ c* v$ d$ j1        deleted = cursor.execute("""
2 h; K% K; P6 R( x& e& x0 @2                                 delete
- z7 N1 w8 n, L9 Q. q6 j/ b3                                   from products
4                                  where id <> 'pyodbc'
7 t1 q: ?( Y& q% g2 L5                                 """).rowcount

3）有些数据库（比如SQL Server）在计数时并没有产生列名，这种情况下，你想访问数据就必须使用下标。当然你也可以使用“as”关键字来取个列名（下面SQL语句的“as name-count”）
+ S2 [! v8 E7 q: i1        row = cursor.execute("select count(*) as user_count from users").fetchone()
2        print '%s users' % row.user_count

4）假如你只是需要一个值，那么你可以在同一个行局中使用fetch函数来获取行和第一个列的所有数据。
1        count = cursor.execute("select count(*) from users").fetchone()[0]
2        print '%s users' % count

如果列为空，将会导致该语句不能运行。fetchone()函数返回None，而你将会获取一个错误:NoneType不支持下标。如果有一个默认值，你能常常使用ISNULL,或者在SQL数据库直接合并NULLs来覆盖掉默认值。
1        maxid = cursor.execute("select coalesce(max(id), 0) from users").fetchone()[0]
( ?, s/ H1 A6 L8 M
在这个例子里面，如果max(id)返回NULL，coalesce(max(id),0)将导致查询的值为0。