pyodbc的简单使用

Seawind2012

1、连接数据库
8 f& ~$ M; v; l9 E
1）直接连接数据库和创建一个游标（cursor)
1        cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=localhost;DATABASE=testdb;UID=meWD=pass')
2        cursor = cnxn.cursor()
% ~& Z, R7 D* i. K" u% D% |
( v6 `* l* g: c$ A3 T2 D8 T) T2）使用DSN连接。通常DSN连接并不需要密码，还是需要提供一个PSW的关键字。
% {8 ~0 ]% r% X) |1        cnxn = pyodbc.connect('DSN=testWD=password')
2        cursor = cnxn.cursor()
, H, ?# D, y. l
关于连接函数还有更多的选项，可以在pyodbc文档中的 connect funtion 和 ConnectionStrings查看更多的细节

8 q2 I% }' `# K- H; }, F8 u0 c2、数据查询（SQL语句为 select ...from..where）
/ @! `: X7 Z; d/ j* l
1）所有的SQL语句都用cursor.execute函数运行。如果语句返回行，比如一个查询语句返回的行，你可以通过游标的fetch函数来获取数据，这些函数有（fetchone,fetchall,fetchmany）.如果返回空行，fetchone函数将返回None,而fetchall和fetchmany将返回一个空列。
# h# z; G% @5 ?# g7 ]+ b" b- v1        cursor.execute("select user_id, user_name from users")
2        row = cursor.fetchone()
  d; F; l& F% \3        if row:
4            print row
2 y2 x, E$ x+ ]+ Z$ {
2）Row这个类，类似于一个元组，但是他们也可以通过字段名进行访问。
' d7 k7 R% x7 g9 C0 n& J1        cursor.execute("select user_id, user_name from users")
2        row = cursor.fetchone()
. G) t3 i- V3 i! Z3        print 'name:', row[1]          # access by column index
4        print 'name:', row.user_name   # or access by name

3）如果所有的行都被检索完，那么fetchone将返回None.
1        while 1:
1 r7 I+ O0 T! h2            row = cursor.fetchone()
3            if not row:
4                break
5            print 'id:', row.user_id
6 f: t4 o  u& i* S/ d
4)使用fetchall函数时，将返回所有剩下的行，如果是空行，那么将返回一个空列。（如果有很多行，这样做的话将会占用很多内存。未读取的行将会被压缩存放在数据库引擎中，然后由数据库服务器分批发送。一次只读取你需要的行，将会大大节省内存空间）
9 L2 q- u; J4 ?4 p  v: |, B; H( ^1        cursor.execute("select user_id, user_name from users")
, @9 ~$ T  r- c/ M' V' S, j# `2        rows = cursor.fetchall()
! m% Z2 P, z" G3        for row in rows:
4            print row.user_id, row.user_name
9 ?, Q" H$ p) m* Z, J) W0 A" k
5）如果你打算一次读完所有数据，那么你可以使用cursor本身。
1        cursor.execute("select user_id, user_name from users"):
! m- v0 \( s% \; t% I2        for row in cursor:
3            print row.user_id, row.user_name
7 ]8 x9 s4 Z5 l
6）由于cursor.execute返回一个cursor，所以你可以把上面的语句简化成：
1        for row in cursor.execute("select user_id, user_name from users"):
2            print row.user_id, row.user_name

7 t9 A2 d8 v) }0 z6 V7）有很多SQL语句用单行来写并不是很方便，所以你也可以使用三引号的字符串来写：
6 b0 K) Y) ^. T9 h4 A5 h9 G8 }. ?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、参数
: @/ p' p# N3 y& W: }
" {0 o4 t) r+ _* w8 p: U1）ODBC支持在SQL语句中使用一个问号来作为参数。你可以在SQL语句后面加上值，用来传递给SQL语句中的问号。
) r- N; @& u2 v- R$ ?- w1        cursor.execute("""
2                       select user_id, user_name
3                         from users
4                        where last_logon < ?
5                          and bill_overdue = ?
6                       """, '2001-01-01', 'y')

这样做比直接把值写在SQL语句中更加安全，这是因为每个参数传递给数据库都是单独进行的。如果你使用不同的参数而运行同样的SQL语句，这样做也更加效率。
" R- S3 Z8 _0 ?6 D
# N2 G/ ?& w2 r+ b3）python DB API明确说明多参数时可以使用一个序列来传递。pyodbc同样支持：
1        cursor.execute("""
; O# Y1 r* p& C& I' w2                       select user_id, user_name
3                         from users
4                        where last_logon < ?
5                          and bill_overdue = ?
: Z: t; q1 @1 e& P2 [0 n4 O6 B6                       """, ['2001-01-01', 'y'])
$ y# C8 R% a8 k0 d
5 Q9 ~+ e% g. `1        cursor.execute("select count(*) as user_count from users where age > ?", 21)
: V( J& S1 p# Q' F, i, s4 {2        row = cursor.fetchone()
3        print '%d users' % row.user_count
3 Q+ G& t/ w; m- h: Z

. u$ O# e- ~0 i
% d& P0 G+ V- B' r3 \4、数据插入
7 c" M  i; x2 }- j
" M2 K( I# B* l1）数据插入，把SQL插入语句传递给cursor的execute函数，可以伴随任何需要的参数。
. u: E- U, @2 ^: a/ W6 F, t1        cursor.execute("insert into products(id, name) values ('pyodbc', 'awesome library')")
3 n5 A% X2 s, k2        cnxn.commit()

7 m; K* d2 X9 n* m$ c, W- K, S1        cursor.execute("insert into products(id, name) values (?, ?)", 'pyodbc', 'awesome library')
% m4 q+ Y& C5 N6 f2        cnxn.commit()
+ R/ ^& K3 p4 _
注意调用cnxn.commit()函数：你必须调用commit函数，否者你对数据库的所有操作将会失效！当断开连接时，所有悬挂的修改将会被重置。这很容易导致出错，所以你必须记得调用commit函数。

. Y' K% f- \- z0 E$ f7 z5、数据修改和删除
; C9 m! Y; a  C, _
5 X5 z9 E# a( n) ?# z4 ?1）数据修改和删除也是跟上面的操作一样，把SQL语句传递给execute函数。但是我们常常想知道数据修改和删除时，到底影响了多少条记录，这个时候你可以使用cursor.rowcount的返回值。
1        cursor.execute("delete from products where id <> ?", 'pyodbc')
2        print cursor.rowcount, 'products deleted'
# `( q$ K% y5 Z( X/ p4 C/ t3        cnxn.commit()

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

! _) E; D' @# t7 \/ y/ G5 M同样要注意调用cnxn.commit()函数

4 A; Y* T( A+ E  H* ~6、小窍门

3 y: w% A6 |, q1）由于使用单引号的SQL语句是有效的，那么双引号也同样是有效的：
1        deleted = cursor.execute("delete from products where id <> 'pyodbc'").rowcount

2）假如你使用的是三引号，那么你也可以这样使用：
$ k. t" S( B. u( m  G5 f1        deleted = cursor.execute("""
2                                 delete
3                                   from products
0 _# u1 B0 N/ V/ C4                                  where id <> 'pyodbc'
5                                 """).rowcount

3）有些数据库（比如SQL Server）在计数时并没有产生列名，这种情况下，你想访问数据就必须使用下标。当然你也可以使用“as”关键字来取个列名（下面SQL语句的“as name-count”）
- Y$ X/ h& `; S5 i1        row = cursor.execute("select count(*) as user_count from users").fetchone()
2        print '%s users' % row.user_count
, S* }5 @) h9 @7 \& m4 p' f6 Y  [  C
4）假如你只是需要一个值，那么你可以在同一个行局中使用fetch函数来获取行和第一个列的所有数据。
1 ?' I! q" ^' C6 ^) s& _! f1        count = cursor.execute("select count(*) from users").fetchone()[0]
9 H! b1 }  A* e6 d: Q2        print '%s users' % count

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