pyodbc的简单使用

Seawind2012

1、连接数据库

+ F8 c* R9 C* Q+ J- W+ G$ y1）直接连接数据库和创建一个游标（cursor)
1        cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=localhost;DATABASE=testdb;UID=meWD=pass')
2        cursor = cnxn.cursor()

2）使用DSN连接。通常DSN连接并不需要密码，还是需要提供一个PSW的关键字。
9 O$ a( t5 y8 C3 L! N1        cnxn = pyodbc.connect('DSN=testWD=password')
2        cursor = cnxn.cursor()
* e/ ~" {( C# y* F6 l
关于连接函数还有更多的选项，可以在pyodbc文档中的 connect funtion 和 ConnectionStrings查看更多的细节

2、数据查询（SQL语句为 select ...from..where）
* v/ {9 j! e1 S' S1 t* ]% i/ c! w8 D
2 ~# Z0 X, F! b1 }% A1）所有的SQL语句都用cursor.execute函数运行。如果语句返回行，比如一个查询语句返回的行，你可以通过游标的fetch函数来获取数据，这些函数有（fetchone,fetchall,fetchmany）.如果返回空行，fetchone函数将返回None,而fetchall和fetchmany将返回一个空列。
1        cursor.execute("select user_id, user_name from users")
2        row = cursor.fetchone()
1 w! T% j! A0 B, G8 \: W) Z3        if row:
+ g# J1 A7 K6 j4 F3 d4            print row
3 d" s' C( r9 o7 a7 ]7 l
5 t. E8 o7 o* X% D1 M5 M# p0 G2）Row这个类，类似于一个元组，但是他们也可以通过字段名进行访问。
3 y- J+ P# I2 a) Q2 }) e+ Z( U' W( l$ s1        cursor.execute("select user_id, user_name from users")
2        row = cursor.fetchone()
3        print 'name:', row[1]          # access by column index
4        print 'name:', row.user_name   # or access by name

3）如果所有的行都被检索完，那么fetchone将返回None.
1        while 1:
2            row = cursor.fetchone()
3            if not row:
4                break
5            print 'id:', row.user_id

9 A+ ~1 n0 T! l' B) ?2 h; k4)使用fetchall函数时，将返回所有剩下的行，如果是空行，那么将返回一个空列。（如果有很多行，这样做的话将会占用很多内存。未读取的行将会被压缩存放在数据库引擎中，然后由数据库服务器分批发送。一次只读取你需要的行，将会大大节省内存空间）
1        cursor.execute("select user_id, user_name from users")
* ~: s; L8 M. i: p' e+ i2 r+ ]- h2        rows = cursor.fetchall()
* h( q8 P3 `8 ]/ B- H2 |( ?3        for row in rows:
4            print row.user_id, row.user_name
7 B: B7 a( ]" @4 y) g
& V6 W% Q0 ~4 h& P* P4 y5）如果你打算一次读完所有数据，那么你可以使用cursor本身。
1        cursor.execute("select user_id, user_name from users"):
2        for row in cursor:
+ \; [/ k3 a: ^6 @' d. t$ ?3            print row.user_id, row.user_name
0 o6 y8 e! \, A2 E
0 L( r8 H% s) G) x& p) J6 ?6）由于cursor.execute返回一个cursor，所以你可以把上面的语句简化成：
1        for row in cursor.execute("select user_id, user_name from users"):
) J, ?9 z3 a  ^. U9 T/ h2            print row.user_id, row.user_name

7）有很多SQL语句用单行来写并不是很方便，所以你也可以使用三引号的字符串来写：
1        cursor.execute("""
2 W7 Q, J. i& X5 d# o4 u$ w& n" h2                       select user_id, user_name
3                         from users
4                        where last_logon < '2001-01-01'
5                          and bill_overdue = 'y'
: A# f% s+ |" `) U6 }4 y6                       """)
# b. _9 }* }& G9 c# x
3、参数
8 [) d% j; H+ ^: x
+ [& z7 O6 V2 o2 c3 I5 X1）ODBC支持在SQL语句中使用一个问号来作为参数。你可以在SQL语句后面加上值，用来传递给SQL语句中的问号。
# w5 E% W/ h; D  a0 p, \1        cursor.execute("""
& A$ B. ^4 c+ w5 C: I$ a4 e. x% Y0 n; D2                       select user_id, user_name
3                         from users
4                        where last_logon < ?
" B$ m! q% v7 I' A) O5                          and bill_overdue = ?
6                       """, '2001-01-01', 'y')

. O5 p8 ~, y' d& s6 E: H1 L这样做比直接把值写在SQL语句中更加安全，这是因为每个参数传递给数据库都是单独进行的。如果你使用不同的参数而运行同样的SQL语句，这样做也更加效率。
' v( Z4 l. T8 H, r9 a
" N0 u1 b" b2 f' r  w& A3）python DB API明确说明多参数时可以使用一个序列来传递。pyodbc同样支持：
8 a0 `( O% \8 e2 `8 @1        cursor.execute("""
2                       select user_id, user_name
3                         from users
4                        where last_logon < ?
5                          and bill_overdue = ?
: E: V) J8 T0 m1 M6                       """, ['2001-01-01', 'y'])
9 B( d* z# K) H$ Z% I
1        cursor.execute("select count(*) as user_count from users where age > ?", 21)
2        row = cursor.fetchone()
# k/ Z2 ^+ w+ J/ o! r% c& l3        print '%d users' % row.user_count
3 d+ Q% L9 C, }

0 ]) P/ E+ O+ k4 f! m) D
4、数据插入

9 ?# P5 m! ]; r. [0 B; h1）数据插入，把SQL插入语句传递给cursor的execute函数，可以伴随任何需要的参数。
1        cursor.execute("insert into products(id, name) values ('pyodbc', 'awesome library')")
0 Z: Y+ W! ^! q9 E# E. [2        cnxn.commit()
& a4 V8 ^5 P+ ^. o/ x
7 Z* h9 g* }2 @4 P) q* E1        cursor.execute("insert into products(id, name) values (?, ?)", 'pyodbc', 'awesome library')
2        cnxn.commit()
  [1 v, u9 `. u, Z5 C: T
0 j* s- d# y" T注意调用cnxn.commit()函数：你必须调用commit函数，否者你对数据库的所有操作将会失效！当断开连接时，所有悬挂的修改将会被重置。这很容易导致出错，所以你必须记得调用commit函数。
3 k4 K2 K3 `9 g+ l4 G3 [  j
5、数据修改和删除
5 G0 c) X8 ^9 z& U8 r1 ]# J
9 J2 e9 z+ W, X0 F  {1）数据修改和删除也是跟上面的操作一样，把SQL语句传递给execute函数。但是我们常常想知道数据修改和删除时，到底影响了多少条记录，这个时候你可以使用cursor.rowcount的返回值。
1        cursor.execute("delete from products where id <> ?", 'pyodbc')
2        print cursor.rowcount, 'products deleted'
8 z6 z" ^6 Q$ W8 F3        cnxn.commit()

* T( {: w* y5 o% ^( }" I2）由于execute函数总是返回cursor，所以有时候你也可以看到像这样的语句：（注意rowcount放在最后面）
1        deleted = cursor.execute("delete from products where id <> 'pyodbc'").rowcount
2        cnxn.commit()
; A; \" b$ ^% R$ O0 X8 y
同样要注意调用cnxn.commit()函数

6、小窍门

  G0 L8 d/ x' N$ M# W6 \( J1）由于使用单引号的SQL语句是有效的，那么双引号也同样是有效的：
" a( a! }1 l: k3 [$ Q1        deleted = cursor.execute("delete from products where id <> 'pyodbc'").rowcount

# o- v7 d& r# ]6 X: q0 L2）假如你使用的是三引号，那么你也可以这样使用：
! ]4 \% k9 W' P/ G* S; V1        deleted = cursor.execute("""
2                                 delete
3                                   from products
4                                  where id <> 'pyodbc'
5                                 """).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
. s4 s, i5 W' [, X, f- G% }. Q9 _, o
4）假如你只是需要一个值，那么你可以在同一个行局中使用fetch函数来获取行和第一个列的所有数据。
9 }/ `; L) ?# M1        count = cursor.execute("select count(*) from users").fetchone()[0]
2        print '%s users' % count
9 f2 X/ Y7 K2 p5 w
8 q  x4 r4 g+ f) {, @1 F0 H* w如果列为空，将会导致该语句不能运行。fetchone()函数返回None，而你将会获取一个错误:NoneType不支持下标。如果有一个默认值，你能常常使用ISNULL,或者在SQL数据库直接合并NULLs来覆盖掉默认值。
4 D. s' }; |* U, T1        maxid = cursor.execute("select coalesce(max(id), 0) from users").fetchone()[0]
$ Y* z: m8 P/ U& l
在这个例子里面，如果max(id)返回NULL，coalesce(max(id),0)将导致查询的值为0。