数学建模社区-数学中国

标题: pyodbc的简单使用 [打印本页]

---

作者: Seawind2012    时间: 2012-7-4 14:30
标题: pyodbc的简单使用
1、连接数据库

: \7 {; S% `+ I9 R2 d" I1）直接连接数据库和创建一个游标（cursor)
. z; Y" N2 l; U1        cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=localhost;DATABASE=testdb;UID=meWD=pass')
* g6 d; m% D% Z; w2        cursor = cnxn.cursor()

2）使用DSN连接。通常DSN连接并不需要密码，还是需要提供一个PSW的关键字。
1        cnxn = pyodbc.connect('DSN=testWD=password')
2 t+ O, o# U* q, ]6 @- P2        cursor = cnxn.cursor()

6 B; Q8 C4 h' {" f( g, B5 S/ E% ^0 u关于连接函数还有更多的选项，可以在pyodbc文档中的 connect funtion 和 ConnectionStrings查看更多的细节

+ @; a7 h7 b9 d$ V* D! [2、数据查询（SQL语句为 select ...from..where）

( {: Z! s5 J, ^9 `1）所有的SQL语句都用cursor.execute函数运行。如果语句返回行，比如一个查询语句返回的行，你可以通过游标的fetch函数来获取数据，这些函数有（fetchone,fetchall,fetchmany）.如果返回空行，fetchone函数将返回None,而fetchall和fetchmany将返回一个空列。
; ]8 R8 f0 k; `1 f9 L2 w* N4 }! Q1        cursor.execute("select user_id, user_name from users")
/ F8 h9 _3 M1 g  u/ x2        row = cursor.fetchone()
; j$ d* ?- t- G. x+ I4 H. ~3        if row:
, u* M* y6 o) ?! e' g2 D! V4            print row

2）Row这个类，类似于一个元组，但是他们也可以通过字段名进行访问。
5 w  E& U( T5 ?2 O2 j3 {6 L1        cursor.execute("select user_id, user_name from users")
2        row = cursor.fetchone()
3        print 'name:', row[1]          # access by column index
9 x) q- r' B6 t# u( W4        print 'name:', row.user_name   # or access by name
& y" M7 |+ c$ j. x8 f
. o' q7 U$ X! l/ F+ f* U3）如果所有的行都被检索完，那么fetchone将返回None.
1        while 1:
) T4 r, _, U# M; E1 c# S) i2            row = cursor.fetchone()
3            if not row:
4                break
0 e* N' _: l. z0 g, e& W! x5            print 'id:', row.user_id
+ _) P6 `) Z/ v% }
5 q2 L/ P) B& L: D8 g* Q1 ?4)使用fetchall函数时，将返回所有剩下的行，如果是空行，那么将返回一个空列。（如果有很多行，这样做的话将会占用很多内存。未读取的行将会被压缩存放在数据库引擎中，然后由数据库服务器分批发送。一次只读取你需要的行，将会大大节省内存空间）
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

. c2 e0 h# {( T+ ?. X5）如果你打算一次读完所有数据，那么你可以使用cursor本身。
1        cursor.execute("select user_id, user_name from users"):
2        for row in cursor:
3            print row.user_id, row.user_name
" j/ p  {# X$ R8 L; R  ?
6 W4 d6 v4 q! ~  t7 g5 ~6）由于cursor.execute返回一个cursor，所以你可以把上面的语句简化成：
1        for row in cursor.execute("select user_id, user_name from users"):
2 {! {, Z% d, S& \, S9 N1 Y2            print row.user_id, row.user_name
/ k+ H# _* {8 s+ U
$ C( D; O( A% U- Q& U: ^: ]7）有很多SQL语句用单行来写并不是很方便，所以你也可以使用三引号的字符串来写：
1        cursor.execute("""
2                       select user_id, user_name
6 O- C4 @1 l  Z. }2 Y4 f3                         from users
- @2 h9 `, w- I$ j  m- \7 F4                        where last_logon < '2001-01-01'
, z# |9 q- K. l* _  ~5 D5                          and bill_overdue = 'y'
6                       """)
, f  j% m; U; F1 ~2 m+ B
3、参数

( u; C( \2 Q! H" z1 u, e9 e1）ODBC支持在SQL语句中使用一个问号来作为参数。你可以在SQL语句后面加上值，用来传递给SQL语句中的问号。
1        cursor.execute("""
2                       select user_id, user_name
3                         from users
6 e& ]6 j) T. J1 [1 {* g4                        where last_logon < ?
5                          and bill_overdue = ?
6                       """, '2001-01-01', 'y')

8 V+ q) d. c  q0 G$ O这样做比直接把值写在SQL语句中更加安全，这是因为每个参数传递给数据库都是单独进行的。如果你使用不同的参数而运行同样的SQL语句，这样做也更加效率。

5 V0 f) t& e- ]% f" l: ]3）python DB API明确说明多参数时可以使用一个序列来传递。pyodbc同样支持：
1        cursor.execute("""
2                       select user_id, user_name
3                         from users
4                        where last_logon < ?
5                          and bill_overdue = ?
; J/ H) Q, F: ~6 W# y: L$ ?; O6                       """, ['2001-01-01', 'y'])

1        cursor.execute("select count(*) as user_count from users where age > ?", 21)
0 V! [/ l( B1 ~( F1 t2        row = cursor.fetchone()
0 P) Z9 n5 g5 Z% N2 T4 p3        print '%d users' % row.user_count


# P" Y) ^  H+ q! o+ I, V
8 ^" L0 ^0 _: J# I4、数据插入

1）数据插入，把SQL插入语句传递给cursor的execute函数，可以伴随任何需要的参数。
1        cursor.execute("insert into products(id, name) values ('pyodbc', 'awesome library')")
2        cnxn.commit()

1        cursor.execute("insert into products(id, name) values (?, ?)", 'pyodbc', 'awesome library')
2        cnxn.commit()

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

7 n! ]7 x; j, G) s+ `5、数据修改和删除
; F; c4 B0 K; }3 O
1）数据修改和删除也是跟上面的操作一样，把SQL语句传递给execute函数。但是我们常常想知道数据修改和删除时，到底影响了多少条记录，这个时候你可以使用cursor.rowcount的返回值。
1        cursor.execute("delete from products where id <> ?", 'pyodbc')
2        print cursor.rowcount, 'products deleted'
2 m) P, r$ r# c, ~1 S( A8 E% t3        cnxn.commit()
- l5 v% ]$ @, Y4 u7 P# a' z1 W
2）由于execute函数总是返回cursor，所以有时候你也可以看到像这样的语句：（注意rowcount放在最后面）
1        deleted = cursor.execute("delete from products where id <> 'pyodbc'").rowcount
2        cnxn.commit()
3 L  L% M$ r8 y* H3 C
同样要注意调用cnxn.commit()函数

7 @4 V/ Z5 B+ K6、小窍门

, i3 ?, z% H( C1 T1）由于使用单引号的SQL语句是有效的，那么双引号也同样是有效的：
1        deleted = cursor.execute("delete from products where id <> 'pyodbc'").rowcount

2）假如你使用的是三引号，那么你也可以这样使用：
1        deleted = cursor.execute("""
8 s, Q; x& ^- m8 j. k5 N2 x2                                 delete
; C& ]3 o/ i. {! `' d0 }3 n3                                   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()
, P; U, K) Y+ R1 v( d( C2        print '%s users' % row.user_count

9 ~" S( P  x1 M. i4）假如你只是需要一个值，那么你可以在同一个行局中使用fetch函数来获取行和第一个列的所有数据。
5 V( k3 r' Z6 f1        count = cursor.execute("select count(*) from users").fetchone()[0]
) ]- T! u0 h! Y2        print '%s users' % count
$ u4 h/ k8 |( w. }  Y; w; o: e( E
如果列为空，将会导致该语句不能运行。fetchone()函数返回None，而你将会获取一个错误:NoneType不支持下标。如果有一个默认值，你能常常使用ISNULL,或者在SQL数据库直接合并NULLs来覆盖掉默认值。
1        maxid = cursor.execute("select coalesce(max(id), 0) from users").fetchone()[0]
: P+ K5 C: X( ^6 ^5 ?: b: C
. x9 p; t; u% a" S0 B8 s9 @. P在这个例子里面，如果max(id)返回NULL，coalesce(max(id),0)将导致查询的值为0。

---

欢迎光临 数学建模社区-数学中国 (http://www.madio.net/)

Powered by Discuz! X2.5