pyodbc的简单使用

Seawind2012

1、连接数据库
1 ^( U. l1 R! {) J& a4 p) P  m
* h5 a, p. V! R* H8 O1）直接连接数据库和创建一个游标（cursor)
1        cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=localhost;DATABASE=testdb;UID=meWD=pass')
2        cursor = cnxn.cursor()

4 x0 U  f# ~/ a! b( V2）使用DSN连接。通常DSN连接并不需要密码，还是需要提供一个PSW的关键字。
1        cnxn = pyodbc.connect('DSN=testWD=password')
9 B* Y' d# D4 n' j% H( z) m2        cursor = cnxn.cursor()
/ ?( ^! [: W$ d
; v8 I' z/ D5 e8 Y8 S; @* q  h! Y关于连接函数还有更多的选项，可以在pyodbc文档中的 connect funtion 和 ConnectionStrings查看更多的细节

2、数据查询（SQL语句为 select ...from..where）
1 f( V* f- G- T5 f& D9 B# G* S
1）所有的SQL语句都用cursor.execute函数运行。如果语句返回行，比如一个查询语句返回的行，你可以通过游标的fetch函数来获取数据，这些函数有（fetchone,fetchall,fetchmany）.如果返回空行，fetchone函数将返回None,而fetchall和fetchmany将返回一个空列。
7 {; g" y5 N: T/ W- Y5 y9 g1        cursor.execute("select user_id, user_name from users")
2        row = cursor.fetchone()
3        if row:
* t: R; F. l$ [/ x$ _0 ~# K4            print row
; K, e- @; K9 a) N& `; ~) g
2）Row这个类，类似于一个元组，但是他们也可以通过字段名进行访问。
1        cursor.execute("select user_id, user_name from users")
2        row = cursor.fetchone()
  _: Z7 x) `5 Z, b  V+ E3        print 'name:', row[1]          # access by column index
4        print 'name:', row.user_name   # or access by name
# N7 r  _4 ?! G3 M" w$ n
/ [. \  H2 h) J3）如果所有的行都被检索完，那么fetchone将返回None.
1        while 1:
( Y- }4 V- V- `% Q/ K# B( T4 ~2            row = cursor.fetchone()
3            if not row:
4                break
3 F4 |  g& D' R. L$ W- v7 T5            print 'id:', row.user_id
7 L/ T1 w) M; N1 P; R( ^- R! h
4)使用fetchall函数时，将返回所有剩下的行，如果是空行，那么将返回一个空列。（如果有很多行，这样做的话将会占用很多内存。未读取的行将会被压缩存放在数据库引擎中，然后由数据库服务器分批发送。一次只读取你需要的行，将会大大节省内存空间）
! x: ~; g# Z' Y9 p$ L8 d1        cursor.execute("select user_id, user_name from users")
2        rows = cursor.fetchall()
) H4 `/ b5 @* E, |! m3        for row in rows:
2 t% p9 ~. F7 M- J3 g4            print row.user_id, row.user_name
* O: j  h- O6 a% s) c: y
5）如果你打算一次读完所有数据，那么你可以使用cursor本身。
1        cursor.execute("select user_id, user_name from users"):
+ z' ^/ f! _* |$ H8 r2        for row in cursor:
3            print row.user_id, row.user_name

. o4 v! |" s. j4 D6）由于cursor.execute返回一个cursor，所以你可以把上面的语句简化成：
8 i5 w. }$ \0 ~1        for row in cursor.execute("select user_id, user_name from users"):
2            print row.user_id, row.user_name

1 p5 L: }5 e5 |0 |7）有很多SQL语句用单行来写并不是很方便，所以你也可以使用三引号的字符串来写：
1        cursor.execute("""
! x4 N) ?% Q7 L4 v6 v2 K2                       select user_id, user_name
1 l" J; [  _* l$ x3                         from users
4                        where last_logon < '2001-01-01'
5                          and bill_overdue = 'y'
6                       """)
4 v7 z3 X1 b2 U# F/ {7 b9 N
3、参数

' r' ^5 P3 @5 X8 c  |! \1）ODBC支持在SQL语句中使用一个问号来作为参数。你可以在SQL语句后面加上值，用来传递给SQL语句中的问号。
3 A( ^! X6 _9 v; S6 N( P- z) h1        cursor.execute("""
( E( T4 c5 E" x0 q% u- l" x0 T2                       select user_id, user_name
. }3 j1 A9 x; n3                         from users
. Y" P: `8 x* K0 d' h4                        where last_logon < ?
5                          and bill_overdue = ?
6                       """, '2001-01-01', 'y')
$ Q( O& H" y& U( a  D
6 \5 v) _& A6 y8 Q. N1 O这样做比直接把值写在SQL语句中更加安全，这是因为每个参数传递给数据库都是单独进行的。如果你使用不同的参数而运行同样的SQL语句，这样做也更加效率。

3）python DB API明确说明多参数时可以使用一个序列来传递。pyodbc同样支持：
2 v0 i6 N( q4 g& _+ A( D5 ~1        cursor.execute("""
. E: V9 @, e5 R6 U; x( A( O5 z2                       select user_id, user_name
" s# X2 E9 _9 |3 S; Q1 @0 ~* e3                         from users
4                        where last_logon < ?
1 V9 x7 I) K+ I! x, j$ ]' n5                          and bill_overdue = ?
6                       """, ['2001-01-01', 'y'])

1        cursor.execute("select count(*) as user_count from users where age > ?", 21)
8 g2 {( ?, @# }( F' z7 y2        row = cursor.fetchone()
: K/ ?- Q5 o" z3        print '%d users' % row.user_count
4 \6 g+ @6 L9 z; }1 B

8 @" s+ B3 E3 O: x. V: Y$ t' o; B
9 N1 o! J6 ~+ U" m. T# v; I4、数据插入

1 Z" [1 Z& F1 ~5 F1 F* y8 t' t1）数据插入，把SQL插入语句传递给cursor的execute函数，可以伴随任何需要的参数。
( {: i* {# w5 {% R1        cursor.execute("insert into products(id, name) values ('pyodbc', 'awesome library')")
4 U+ T+ D* K4 n7 y7 V* X5 G7 [2        cnxn.commit()
  g9 x, Y" P: V$ v8 e
5 M4 f: C$ |. w2 }4 |) \6 l7 ?1        cursor.execute("insert into products(id, name) values (?, ?)", 'pyodbc', 'awesome library')
0 H$ _6 D( _" w" |; y2        cnxn.commit()

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

) s- i- C' D2 P5 U! f1 d5、数据修改和删除
+ m! W; a* m* S5 n3 N3 r! K0 Z
/ x$ o% A7 r- E& r4 G1）数据修改和删除也是跟上面的操作一样，把SQL语句传递给execute函数。但是我们常常想知道数据修改和删除时，到底影响了多少条记录，这个时候你可以使用cursor.rowcount的返回值。
5 v& K- k& b" J) r* P) }1        cursor.execute("delete from products where id <> ?", 'pyodbc')
2        print cursor.rowcount, 'products deleted'
3        cnxn.commit()

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

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

0 u, c" C$ u# |6、小窍门

1）由于使用单引号的SQL语句是有效的，那么双引号也同样是有效的：
1        deleted = cursor.execute("delete from products where id <> 'pyodbc'").rowcount
7 z/ j1 p% q$ q2 \* r
2）假如你使用的是三引号，那么你也可以这样使用：
1        deleted = cursor.execute("""
+ W* z! R* E% |* O8 q) {2                                 delete
- E1 b/ R' B8 k3 I* {/ h3                                   from products
" V2 F* e; a9 z9 N* L! ]0 p4                                  where id <> 'pyodbc'
5                                 """).rowcount

3）有些数据库（比如SQL Server）在计数时并没有产生列名，这种情况下，你想访问数据就必须使用下标。当然你也可以使用“as”关键字来取个列名（下面SQL语句的“as name-count”）
% I7 [( q3 P$ l, v! E1        row = cursor.execute("select count(*) as user_count from users").fetchone()
0 G3 F. D+ T7 r3 Z$ m4 T- N2        print '%s users' % row.user_count
$ A2 x! s" H( d. b# M. Z( U0 F0 a
; m, I: B/ w7 b% Z7 o& k4）假如你只是需要一个值，那么你可以在同一个行局中使用fetch函数来获取行和第一个列的所有数据。
1        count = cursor.execute("select count(*) from users").fetchone()[0]
  `6 U" `. ]9 K0 q8 I2        print '%s users' % count
) S- q- i/ y, N! I
" t1 I$ M4 @0 E% ]& ~/ p5 p; y如果列为空，将会导致该语句不能运行。fetchone()函数返回None，而你将会获取一个错误:NoneType不支持下标。如果有一个默认值，你能常常使用ISNULL,或者在SQL数据库直接合并NULLs来覆盖掉默认值。
1        maxid = cursor.execute("select coalesce(max(id), 0) from users").fetchone()[0]
5 W5 p  Z. B, h" A1 j& ~8 s9 K( o
) F0 h/ y. q* z9 V) q& i在这个例子里面，如果max(id)返回NULL，coalesce(max(id),0)将导致查询的值为0。