pyodbc的简单使用

Seawind2012

1、连接数据库
4 U7 z; J5 ]2 ^5 F. l) S, [1 B, ~
  u" A) d2 K0 |2 O# v' \1）直接连接数据库和创建一个游标（cursor)
1        cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=localhost;DATABASE=testdb;UID=meWD=pass')
5 ?8 I7 |, T2 U% ]0 I/ E$ S2        cursor = cnxn.cursor()
; W# V% ?- P1 {4 Y& g5 D
5 m/ G- ?  X2 x$ q2）使用DSN连接。通常DSN连接并不需要密码，还是需要提供一个PSW的关键字。
+ T5 L5 Q- E0 h" z1        cnxn = pyodbc.connect('DSN=testWD=password')
- |% \8 E% ~0 u  V: j7 D2        cursor = cnxn.cursor()
5 `, x' W7 ^$ y
+ w5 B/ j- T5 }; E% c- [关于连接函数还有更多的选项，可以在pyodbc文档中的 connect funtion 和 ConnectionStrings查看更多的细节
. u# H& u) k- T2 F; U' K
$ k3 X  E% {& C2、数据查询（SQL语句为 select ...from..where）

- |! {6 B/ }: g( ^6 ?; M& L1）所有的SQL语句都用cursor.execute函数运行。如果语句返回行，比如一个查询语句返回的行，你可以通过游标的fetch函数来获取数据，这些函数有（fetchone,fetchall,fetchmany）.如果返回空行，fetchone函数将返回None,而fetchall和fetchmany将返回一个空列。
1        cursor.execute("select user_id, user_name from users")
. h+ @/ K! [, \6 S: r  Z2        row = cursor.fetchone()
3        if row:
4            print row
# i, @( P$ i6 \( c
2 e- A  d" h% T2）Row这个类，类似于一个元组，但是他们也可以通过字段名进行访问。
1        cursor.execute("select user_id, user_name from users")
0 A4 x8 h1 y1 O2        row = cursor.fetchone()
& n" O8 m5 D" }6 `3        print 'name:', row[1]          # access by column index
+ L- p6 z5 i  r  C3 Z0 D8 K4        print 'name:', row.user_name   # or access by name
# ^  m; ^6 G+ q( T" Q0 x
3）如果所有的行都被检索完，那么fetchone将返回None.
1        while 1:
/ W, Q. e7 r$ I( N2            row = cursor.fetchone()
, O) i; v: _" P6 n3 ?' J/ H3            if not row:
4                break
5            print 'id:', row.user_id

; }% K" V  r# t4)使用fetchall函数时，将返回所有剩下的行，如果是空行，那么将返回一个空列。（如果有很多行，这样做的话将会占用很多内存。未读取的行将会被压缩存放在数据库引擎中，然后由数据库服务器分批发送。一次只读取你需要的行，将会大大节省内存空间）
' F. H, }3 e, J+ b1        cursor.execute("select user_id, user_name from users")
2        rows = cursor.fetchall()
3        for row in rows:
8 X+ ~5 {2 W# m4            print row.user_id, row.user_name
* Y2 L# L7 i& L' L
! n4 k2 T$ {/ `' Y' Y$ A! Q4 R5）如果你打算一次读完所有数据，那么你可以使用cursor本身。
$ n+ G6 p/ P6 I/ z5 {9 v1        cursor.execute("select user_id, user_name from users"):
2        for row in cursor:
3            print row.user_id, row.user_name

6）由于cursor.execute返回一个cursor，所以你可以把上面的语句简化成：
1        for row in cursor.execute("select user_id, user_name from users"):
/ `& g$ v; N* E2 B1 J& c8 N2            print row.user_id, row.user_name

7）有很多SQL语句用单行来写并不是很方便，所以你也可以使用三引号的字符串来写：
1 R  ^7 n, o* P( D! p% R1        cursor.execute("""
2                       select user_id, user_name
3 D) _7 ~( ]1 w" c! z3                         from users
4                        where last_logon < '2001-01-01'
4 h! a3 |+ u; V9 k2 V) D8 W- t! \9 w5                          and bill_overdue = 'y'
# a7 ^6 q. V# k" w2 e6                       """)

8 ?4 o% H" p& ?& V2 b9 s3、参数
) w1 X# ?3 u" L, R6 T
1）ODBC支持在SQL语句中使用一个问号来作为参数。你可以在SQL语句后面加上值，用来传递给SQL语句中的问号。
  k. S5 S% r" m2 M" i! c' x8 j8 X1        cursor.execute("""
2                       select user_id, user_name
3                         from users
4                        where last_logon < ?
5                          and bill_overdue = ?
# [# }) x) U+ r8 T/ l% \6                       """, '2001-01-01', 'y')
$ K6 a) t, w, n0 f" l7 S" Y
/ L2 j* o! E1 l. F6 K$ |; f/ Y+ h这样做比直接把值写在SQL语句中更加安全，这是因为每个参数传递给数据库都是单独进行的。如果你使用不同的参数而运行同样的SQL语句，这样做也更加效率。
' p6 r! X$ {0 I7 r$ P2 H
3）python DB API明确说明多参数时可以使用一个序列来传递。pyodbc同样支持：
1        cursor.execute("""
2                       select user_id, user_name
) x) U1 a$ N1 {- t' F% u3                         from users
4                        where last_logon < ?
5                          and bill_overdue = ?
6                       """, ['2001-01-01', 'y'])
. X! t* X0 f, r/ C  i
4 r4 v0 t. G; G& N3 q1        cursor.execute("select count(*) as user_count from users where age > ?", 21)
" x) f3 F& Q, O+ A2        row = cursor.fetchone()
1 j- v+ \1 {7 L5 v; V3        print '%d users' % row.user_count

1 h- p3 l  Y+ R3 p

4、数据插入
9 P. k" ]  w* G+ M
1）数据插入，把SQL插入语句传递给cursor的execute函数，可以伴随任何需要的参数。
( T- w( ^/ j' J1        cursor.execute("insert into products(id, name) values ('pyodbc', 'awesome library')")
* Z9 b& U7 b& u; @& }2        cnxn.commit()
7 e9 w, o+ z$ ^7 }
" `) h# m3 F% K5 c* `8 L1        cursor.execute("insert into products(id, name) values (?, ?)", 'pyodbc', 'awesome library')
; p( `% q+ G0 F! `! i9 ]( n2        cnxn.commit()

注意调用cnxn.commit()函数：你必须调用commit函数，否者你对数据库的所有操作将会失效！当断开连接时，所有悬挂的修改将会被重置。这很容易导致出错，所以你必须记得调用commit函数。

5、数据修改和删除

# E* Y: ?; T7 U1 N1）数据修改和删除也是跟上面的操作一样，把SQL语句传递给execute函数。但是我们常常想知道数据修改和删除时，到底影响了多少条记录，这个时候你可以使用cursor.rowcount的返回值。
. M! f# ^  Y5 f9 i; f5 P1        cursor.execute("delete from products where id <> ?", 'pyodbc')
2        print cursor.rowcount, 'products deleted'
3        cnxn.commit()
4 H# J* {" Y0 g6 X2 G# {( ]' g& _! h
" V. d" g  z0 i2）由于execute函数总是返回cursor，所以有时候你也可以看到像这样的语句：（注意rowcount放在最后面）
1        deleted = cursor.execute("delete from products where id <> 'pyodbc'").rowcount
3 Y( P0 r/ l6 m2        cnxn.commit()
9 {, A- P0 @2 T
同样要注意调用cnxn.commit()函数

, u) M* M* ]0 E( c6、小窍门
4 h" E4 B  v( h1 C$ L
1）由于使用单引号的SQL语句是有效的，那么双引号也同样是有效的：
1        deleted = cursor.execute("delete from products where id <> 'pyodbc'").rowcount
5 j! ]8 o! {& e; p2 F
1 Q4 [' R" d, S/ G1 E2 q) V' `2）假如你使用的是三引号，那么你也可以这样使用：
7 s4 h: N. [$ g1        deleted = cursor.execute("""
2                                 delete
0 |/ x1 T' P- w* X3                                   from products
" S. g& ?5 N) q* x! ?6 K3 f4                                  where id <> 'pyodbc'
* x4 F/ |8 D+ w7 j) y4 T5                                 """).rowcount

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

" ^$ M" w/ B2 Z; d+ f1 s4）假如你只是需要一个值，那么你可以在同一个行局中使用fetch函数来获取行和第一个列的所有数据。
/ N3 t+ y0 O# i4 N- g/ \1        count = cursor.execute("select count(*) from users").fetchone()[0]
2        print '%s users' % count
$ y. V& T( T* `- K- |( a; z
如果列为空，将会导致该语句不能运行。fetchone()函数返回None，而你将会获取一个错误:NoneType不支持下标。如果有一个默认值，你能常常使用ISNULL,或者在SQL数据库直接合并NULLs来覆盖掉默认值。
1        maxid = cursor.execute("select coalesce(max(id), 0) from users").fetchone()[0]

8 D; L) F' P. p& I6 A3 c+ j; ^在这个例子里面，如果max(id)返回NULL，coalesce(max(id),0)将导致查询的值为0。