pyodbc的简单使用

Seawind2012

1、连接数据库

1）直接连接数据库和创建一个游标（cursor)
1        cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=localhost;DATABASE=testdb;UID=meWD=pass')
- @8 n2 b& t, I+ Z1 s2        cursor = cnxn.cursor()

2）使用DSN连接。通常DSN连接并不需要密码，还是需要提供一个PSW的关键字。
1        cnxn = pyodbc.connect('DSN=testWD=password')
. X0 S0 p+ o5 o2        cursor = cnxn.cursor()

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

; @( _) A0 ~) e! D' D2、数据查询（SQL语句为 select ...from..where）
8 Y) o' G& p: F  i0 x
1）所有的SQL语句都用cursor.execute函数运行。如果语句返回行，比如一个查询语句返回的行，你可以通过游标的fetch函数来获取数据，这些函数有（fetchone,fetchall,fetchmany）.如果返回空行，fetchone函数将返回None,而fetchall和fetchmany将返回一个空列。
1        cursor.execute("select user_id, user_name from users")
, {# R& E" N% k% `: V  t2        row = cursor.fetchone()
7 K/ b$ y; M$ d: U. N( D& U" s0 R3        if row:
4            print row

2）Row这个类，类似于一个元组，但是他们也可以通过字段名进行访问。
% o1 U* v  S( X2 @% i" \1 Y1        cursor.execute("select user_id, user_name from users")
2        row = cursor.fetchone()
* w% n- b/ q/ M! d7 Z5 f, h3        print 'name:', row[1]          # access by column index
- z! B9 f; X/ |9 R7 ^, }4        print 'name:', row.user_name   # or access by name
/ }# c! }6 N; P$ a7 C; }( @
3）如果所有的行都被检索完，那么fetchone将返回None.
) x% l% O4 \" A1        while 1:
2            row = cursor.fetchone()
; `) s# Z! ^1 n# @3            if not row:
4                break
5 h; c5 g* `! S2 {  P0 P5            print 'id:', row.user_id
. r$ I" L6 W0 X" {% [5 j0 z& P- ^
# h0 Z* E- H5 ^4)使用fetchall函数时，将返回所有剩下的行，如果是空行，那么将返回一个空列。（如果有很多行，这样做的话将会占用很多内存。未读取的行将会被压缩存放在数据库引擎中，然后由数据库服务器分批发送。一次只读取你需要的行，将会大大节省内存空间）
$ y6 l, B8 Y% N$ u  C0 q9 {1        cursor.execute("select user_id, user_name from users")
2        rows = cursor.fetchall()
3        for row in rows:
4            print row.user_id, row.user_name
2 X. u$ A+ V! Y
5）如果你打算一次读完所有数据，那么你可以使用cursor本身。
8 [1 u& H$ ^3 i4 M1        cursor.execute("select user_id, user_name from users"):
2        for row in cursor:
3            print row.user_id, row.user_name

& @7 k" @) @# C6）由于cursor.execute返回一个cursor，所以你可以把上面的语句简化成：
, w4 G  j6 r' j" `" W! G. ~1        for row in cursor.execute("select user_id, user_name from users"):
: [: `) U# [, [9 p! B) f2            print row.user_id, row.user_name

7）有很多SQL语句用单行来写并不是很方便，所以你也可以使用三引号的字符串来写：
1        cursor.execute("""
4 b: W: D! i3 n' a2 l/ t2                       select user_id, user_name
3                         from users
4                        where last_logon < '2001-01-01'
2 F$ B& N& L  ~) B7 {$ Z' H5                          and bill_overdue = 'y'
6                       """)

3、参数

1）ODBC支持在SQL语句中使用一个问号来作为参数。你可以在SQL语句后面加上值，用来传递给SQL语句中的问号。
1        cursor.execute("""
6 s1 U2 v0 t$ i8 e2 \7 @* j- C  Q2                       select user_id, user_name
3                         from users
8 _' y+ ^/ ?+ M4                        where last_logon < ?
; o: b( m5 m' P2 G/ x5                          and bill_overdue = ?
. i7 Z" B# Q6 X8 a* H6                       """, '2001-01-01', 'y')
; Q  }" l# \7 z) v1 ^, ]
7 G( h& [; N3 W& H, }3 T这样做比直接把值写在SQL语句中更加安全，这是因为每个参数传递给数据库都是单独进行的。如果你使用不同的参数而运行同样的SQL语句，这样做也更加效率。

$ `$ x) o! m  n7 s3）python DB API明确说明多参数时可以使用一个序列来传递。pyodbc同样支持：
1 I! e7 `8 l9 V1        cursor.execute("""
& U8 G1 L. _' b2                       select user_id, user_name
3                         from users
4                        where last_logon < ?
; p# o2 d! O+ O7 x. s5                          and bill_overdue = ?
6                       """, ['2001-01-01', 'y'])
  n* _5 ?, u- F
1        cursor.execute("select count(*) as user_count from users where age > ?", 21)
! K) A% o& Y/ Q. t0 G5 F2        row = cursor.fetchone()
3        print '%d users' % row.user_count
) S; ?$ A1 h2 j. g
3 e$ V- c* G8 T; I' }

4、数据插入

1）数据插入，把SQL插入语句传递给cursor的execute函数，可以伴随任何需要的参数。
1        cursor.execute("insert into products(id, name) values ('pyodbc', 'awesome library')")
" c2 n$ k; D' H$ I9 s* L2        cnxn.commit()
3 U  j* {, S  Y* ~6 w
) D: Y8 R+ k4 ^: S  N1        cursor.execute("insert into products(id, name) values (?, ?)", 'pyodbc', 'awesome library')
$ r- Y6 Q3 B( X' y5 }! w2        cnxn.commit()

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

5、数据修改和删除
7 W+ v9 r) @1 N
1）数据修改和删除也是跟上面的操作一样，把SQL语句传递给execute函数。但是我们常常想知道数据修改和删除时，到底影响了多少条记录，这个时候你可以使用cursor.rowcount的返回值。
  A9 m# }; E. ?9 X1        cursor.execute("delete from products where id <> ?", 'pyodbc')
3 O, H* a* S$ _. d7 ?2        print cursor.rowcount, 'products deleted'
7 ~2 T8 w+ R: r! i3        cnxn.commit()

2）由于execute函数总是返回cursor，所以有时候你也可以看到像这样的语句：（注意rowcount放在最后面）
1        deleted = cursor.execute("delete from products where id <> 'pyodbc'").rowcount
# W7 {' j$ |% a2        cnxn.commit()

0 ]7 C; V7 l1 q/ o1 f同样要注意调用cnxn.commit()函数

/ A# J9 [' a% I/ Z! M3 \* N6、小窍门
' P: t5 s+ ~% S$ U% U8 B
1）由于使用单引号的SQL语句是有效的，那么双引号也同样是有效的：
1        deleted = cursor.execute("delete from products where id <> 'pyodbc'").rowcount

2）假如你使用的是三引号，那么你也可以这样使用：
1        deleted = cursor.execute("""
9 }+ Y3 o4 H4 z2                                 delete
- ~" r- O  ^, K/ ^/ o4 K3                                   from products
4                                  where id <> 'pyodbc'
" {( W' A& M( k" V8 p# n5                                 """).rowcount
" U* U3 |3 ~$ v/ b/ T
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

3 ^" y! f% G0 P. E* r( n2 o3 ?4）假如你只是需要一个值，那么你可以在同一个行局中使用fetch函数来获取行和第一个列的所有数据。
: E5 U; I7 p3 `/ N  N) d( O1 q1        count = cursor.execute("select count(*) from users").fetchone()[0]
2        print '%s users' % count
/ ~/ T$ a- s/ H3 h3 L2 w" M
如果列为空，将会导致该语句不能运行。fetchone()函数返回None，而你将会获取一个错误:NoneType不支持下标。如果有一个默认值，你能常常使用ISNULL,或者在SQL数据库直接合并NULLs来覆盖掉默认值。
$ g( E6 A* O. y! Z9 c0 Z9 }1        maxid = cursor.execute("select coalesce(max(id), 0) from users").fetchone()[0]
+ ]! b& _+ [! w* ]7 L; ?( @7 ~
" `$ `' c% W% [在这个例子里面，如果max(id)返回NULL，coalesce(max(id),0)将导致查询的值为0。