pyodbc的简单使用

Seawind2012

1、连接数据库

0 F$ G+ _  e2 b+ B- m/ K1）直接连接数据库和创建一个游标（cursor)
  H+ `5 g1 G/ s$ u' n$ C7 s1        cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=localhost;DATABASE=testdb;UID=meWD=pass')
1 k+ t( x" g8 u2        cursor = cnxn.cursor()
$ f$ K- i6 k7 L' r6 b, g
2）使用DSN连接。通常DSN连接并不需要密码，还是需要提供一个PSW的关键字。
1        cnxn = pyodbc.connect('DSN=testWD=password')
2        cursor = cnxn.cursor()
9 D8 ^7 _: c5 Q. U) Q8 L8 V
关于连接函数还有更多的选项，可以在pyodbc文档中的 connect funtion 和 ConnectionStrings查看更多的细节
3 s: Y$ q: v( A5 L4 A7 I
2、数据查询（SQL语句为 select ...from..where）
2 ~4 g( d2 u. W( s; I
1）所有的SQL语句都用cursor.execute函数运行。如果语句返回行，比如一个查询语句返回的行，你可以通过游标的fetch函数来获取数据，这些函数有（fetchone,fetchall,fetchmany）.如果返回空行，fetchone函数将返回None,而fetchall和fetchmany将返回一个空列。
4 w  ~& k. y7 ?! q5 p! _' c1        cursor.execute("select user_id, user_name from users")
0 H; m8 v  o3 R: A! p2        row = cursor.fetchone()
2 y' P3 U  S. g3        if row:
4            print row

2）Row这个类，类似于一个元组，但是他们也可以通过字段名进行访问。
1        cursor.execute("select user_id, user_name from users")
! H" T; s& W2 G2        row = cursor.fetchone()
/ A* E8 I3 M1 i) a, g2 S3        print 'name:', row[1]          # access by column index
4        print 'name:', row.user_name   # or access by name
+ M7 j4 ~% {/ _9 ~' s3 W+ S2 ]
5 W3 O, ?+ {# X. v: D+ n) e3）如果所有的行都被检索完，那么fetchone将返回None.
% m; G+ Z/ @2 M' S: r4 w1        while 1:
$ E; t: |# k; t2            row = cursor.fetchone()
3            if not row:
+ A- U) t0 v) i3 I5 a4                break
5            print 'id:', row.user_id

3 l$ s9 K( X* Z5 u4)使用fetchall函数时，将返回所有剩下的行，如果是空行，那么将返回一个空列。（如果有很多行，这样做的话将会占用很多内存。未读取的行将会被压缩存放在数据库引擎中，然后由数据库服务器分批发送。一次只读取你需要的行，将会大大节省内存空间）
1        cursor.execute("select user_id, user_name from users")
2        rows = cursor.fetchall()
0 Y5 O# z) B6 b8 W! K; m4 J. t3        for row in rows:
: m, ]3 V5 Q; J; A! C4            print row.user_id, row.user_name

- [3 m* H6 y6 j5）如果你打算一次读完所有数据，那么你可以使用cursor本身。
1        cursor.execute("select user_id, user_name from users"):
2        for row in cursor:
: l3 d; |$ m, |, m2 m6 u3            print row.user_id, row.user_name
3 ~- e$ O0 Y) ^( l: H/ H: v
2 B9 R, C+ K3 a( J# `4 z# c+ ?' i6）由于cursor.execute返回一个cursor，所以你可以把上面的语句简化成：
1        for row in cursor.execute("select user_id, user_name from users"):
2            print row.user_id, row.user_name
, p$ L! {0 U2 I% Y7 {, B; n3 C
; _# K- C# n' k4 L. ?/ ~1 c) c7）有很多SQL语句用单行来写并不是很方便，所以你也可以使用三引号的字符串来写：
1        cursor.execute("""
2                       select user_id, user_name
4 \  v1 ]$ H3 ~1 y# g0 b3                         from users
4                        where last_logon < '2001-01-01'
5 _* a- g) e! i5                          and bill_overdue = 'y'
1 T" |. j, ^* w8 N/ I6                       """)
" ]. u5 N/ x9 J0 n+ _) H# ]+ ?" ^# q
3、参数
' G& Y( H- o) Y& m
3 W, e& F9 a$ B2 M1）ODBC支持在SQL语句中使用一个问号来作为参数。你可以在SQL语句后面加上值，用来传递给SQL语句中的问号。
6 q3 V" ]* d, o$ Z6 F- K1        cursor.execute("""
# K& k/ P& w0 f  m5 D/ x% }2                       select user_id, user_name
3                         from users
4 f9 ^' z1 N* L: r; Q8 l/ ]0 h( g4                        where last_logon < ?
5                          and bill_overdue = ?
6                       """, '2001-01-01', 'y')

这样做比直接把值写在SQL语句中更加安全，这是因为每个参数传递给数据库都是单独进行的。如果你使用不同的参数而运行同样的SQL语句，这样做也更加效率。
. l7 g9 P. [, n, \( z
9 R/ c* j7 s* H! r3）python DB API明确说明多参数时可以使用一个序列来传递。pyodbc同样支持：
1        cursor.execute("""
2                       select user_id, user_name
3                         from users
4                        where last_logon < ?
5                          and bill_overdue = ?
7 q7 F0 J! ?: w: F$ Q6                       """, ['2001-01-01', 'y'])
  n% A1 b5 l5 t" s5 B. a: T. [
1        cursor.execute("select count(*) as user_count from users where age > ?", 21)
6 [) `. e! t/ c" t' k/ C5 t7 m2        row = cursor.fetchone()
1 p( H/ y, a- G" y, N: v3        print '%d users' % row.user_count



; L+ m* W" N+ N9 r$ L4、数据插入

0 O! s" |7 k; X  ~; D' h1）数据插入，把SQL插入语句传递给cursor的execute函数，可以伴随任何需要的参数。
3 v* I/ C& y+ q, r$ X8 ^6 x" \1        cursor.execute("insert into products(id, name) values ('pyodbc', 'awesome library')")
2        cnxn.commit()
( B8 c3 w: [4 U! {
8 y" I- a4 F" Y1        cursor.execute("insert into products(id, name) values (?, ?)", 'pyodbc', 'awesome library')
) U1 k& v5 S+ k- u& \9 y* \+ I2        cnxn.commit()
, @/ |7 p7 c% L  `% j
注意调用cnxn.commit()函数：你必须调用commit函数，否者你对数据库的所有操作将会失效！当断开连接时，所有悬挂的修改将会被重置。这很容易导致出错，所以你必须记得调用commit函数。
4 p- n: _0 t7 C1 _( C
5、数据修改和删除
4 t" w9 K2 ?' k. Q. x
! Z7 L7 v# p9 q, A3 f& B' b; v6 K1）数据修改和删除也是跟上面的操作一样，把SQL语句传递给execute函数。但是我们常常想知道数据修改和删除时，到底影响了多少条记录，这个时候你可以使用cursor.rowcount的返回值。
- y  I7 z3 Q% B1        cursor.execute("delete from products where id <> ?", 'pyodbc')
$ j; M9 ^5 B+ a  p$ O% p2        print cursor.rowcount, 'products deleted'
3        cnxn.commit()

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

同样要注意调用cnxn.commit()函数

6、小窍门
# ?$ f$ V; E5 j( H
1）由于使用单引号的SQL语句是有效的，那么双引号也同样是有效的：
0 o5 R! f1 b1 N# z* |  O6 e1        deleted = cursor.execute("delete from products where id <> 'pyodbc'").rowcount

2）假如你使用的是三引号，那么你也可以这样使用：
: K7 i8 L& y5 g+ j1        deleted = cursor.execute("""
2                                 delete
2 O, m9 F. x% b; l# r3                                   from products
4                                  where id <> 'pyodbc'
6 y6 Q) ^7 H+ L. }; \5 \5                                 """).rowcount
+ F) L( ?* C, Y0 p
; p" y! v/ n2 y2 N+ J+ a3）有些数据库（比如SQL Server）在计数时并没有产生列名，这种情况下，你想访问数据就必须使用下标。当然你也可以使用“as”关键字来取个列名（下面SQL语句的“as name-count”）
, @) U5 ]/ X* U1        row = cursor.execute("select count(*) as user_count from users").fetchone()
2        print '%s users' % row.user_count
4 D& b% f& G- w* Q3 `2 x  P
: O' j+ P! [0 |3 h. C4）假如你只是需要一个值，那么你可以在同一个行局中使用fetch函数来获取行和第一个列的所有数据。
* w; y) A. f& f& m3 b1        count = cursor.execute("select count(*) from users").fetchone()[0]
/ Y+ x$ [: G7 U2        print '%s users' % count
- S7 V+ \' q+ D- U3 Y& A
! |" A* t3 K+ V0 y* y* K3 K如果列为空，将会导致该语句不能运行。fetchone()函数返回None，而你将会获取一个错误:NoneType不支持下标。如果有一个默认值，你能常常使用ISNULL,或者在SQL数据库直接合并NULLs来覆盖掉默认值。
1        maxid = cursor.execute("select coalesce(max(id), 0) from users").fetchone()[0]

在这个例子里面，如果max(id)返回NULL，coalesce(max(id),0)将导致查询的值为0。