数学建模社区-数学中国

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

作者: Seawind2012    时间: 2012-7-4 14:30
标题: pyodbc的简单使用
1、连接数据库% Z3 Y, k9 {4 l7 E9 {# E
0 w' V2 L* ?9 y
1)直接连接数据库和创建一个游标(cursor)
; P# P' b0 n3 F4 ^8 \0 I7 R/ p  r; w1        cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=localhost;DATABASE=testdb;UID=meWD=pass')4 e7 @3 c8 I% `0 q
2        cursor = cnxn.cursor()
% ^; n; Y5 n3 Q7 |' f0 Z% h9 B) u0 b
; D3 j2 D# W4 Z# F" A2)使用DSN连接。通常DSN连接并不需要密码,还是需要提供一个PSW的关键字。) R+ J' F7 b! k2 O# H
1        cnxn = pyodbc.connect('DSN=testWD=password')
( a" G! v/ q2 t+ ~. o2        cursor = cnxn.cursor()5 S  N' k1 T- C" w; v8 N+ [4 H
  s* ]4 ^- k: d7 F7 K1 S
关于连接函数还有更多的选项,可以在pyodbc文档中的 connect funtion 和 ConnectionStrings查看更多的细节% U' a2 z, E4 l8 S9 i. ?

5 b/ F& B8 Y$ l% X2 \" b2、数据查询(SQL语句为 select ...from..where)1 s2 p8 T: x' Z( a% ]3 a
" Y$ Y5 a3 t  Y  g: k  n+ U
1)所有的SQL语句都用cursor.execute函数运行。如果语句返回行,比如一个查询语句返回的行,你可以通过游标的fetch函数来获取数据,这些函数有(fetchone,fetchall,fetchmany).如果返回空行,fetchone函数将返回None,而fetchall和fetchmany将返回一个空列。
! L) x: f0 a) v, X* @9 X1        cursor.execute("select user_id, user_name from users")7 G1 e/ J. D. x) |3 e+ M
2        row = cursor.fetchone()1 [% c4 `) h4 I% E: R+ R
3        if row:
/ p/ Q9 J) l) m1 u' x' h4            print row
- K! ?1 F, S, r7 L  O+ t. ?, q+ }- ^* V( a7 T* e- h
2)Row这个类,类似于一个元组,但是他们也可以通过字段名进行访问。3 ]  K% G: N9 f
1        cursor.execute("select user_id, user_name from users")
+ y2 g1 I- G' D3 U! T% u2        row = cursor.fetchone()
& f! X; s+ s3 X" q# m6 y3        print 'name:', row[1]          # access by column index$ M, g" ^6 f" F* T' B5 _
4        print 'name:', row.user_name   # or access by name
; W4 n1 J7 ?0 n+ |6 B. e' o+ N  d% Q  _; t, _" |
3)如果所有的行都被检索完,那么fetchone将返回None.
- h1 [5 m' v9 l& y- x1        while 1:. |* l& f' l5 A
2            row = cursor.fetchone()# c' q1 E3 J9 g: J! ~  q, W
3            if not row:" l4 c  K# l7 O6 }+ G
4                break" N+ h- U6 f. v$ I) k4 C% k
5            print 'id:', row.user_id5 Q  H* z3 X# ]- t

% E3 _1 X" J7 K4)使用fetchall函数时,将返回所有剩下的行,如果是空行,那么将返回一个空列。(如果有很多行,这样做的话将会占用很多内存。未读取的行将会被压缩存放在数据库引擎中,然后由数据库服务器分批发送。一次只读取你需要的行,将会大大节省内存空间)3 \5 q/ i) a) y
1        cursor.execute("select user_id, user_name from users")
+ W* e) `! W4 D& a: z2        rows = cursor.fetchall()
2 n6 }/ C9 n8 D" M, a3        for row in rows:
, Q5 b+ @3 L; U, S2 W4            print row.user_id, row.user_name
9 }3 w# `, }9 k3 Q7 n
" J( y, T& e( d* K: W5 m5)如果你打算一次读完所有数据,那么你可以使用cursor本身。
4 A9 O7 k. b; }1        cursor.execute("select user_id, user_name from users"):
+ G+ t6 @5 U- O7 E2        for row in cursor:
# _! c6 c! {6 q' y) ]& c) j1 E3            print row.user_id, row.user_name
/ ?! M4 D" V+ H/ e
4 g& G; O/ \! j+ d% W6 H6)由于cursor.execute返回一个cursor,所以你可以把上面的语句简化成:
' x- z. X8 N" T6 X1 p6 ?- {' D+ P+ }1        for row in cursor.execute("select user_id, user_name from users"):; {# C5 i: `( X  _
2            print row.user_id, row.user_name' ^$ X" C' t- l+ }( P# Q8 k7 Y

6 ]2 K; e& v% v) _7)有很多SQL语句用单行来写并不是很方便,所以你也可以使用三引号的字符串来写:
, S% ^! e# x2 B$ J! ^( l+ D. G. ?$ L1        cursor.execute("""
# G; |1 ?1 ?* u! X5 y6 H2 _; Q; B* t1 W2                       select user_id, user_name: ]4 Z8 D( \) ~" }. }2 g
3                         from users
8 W! J' n! i8 j! o* l! d+ i4                        where last_logon < '2001-01-01'
: _+ O  e2 m' ~: h5                          and bill_overdue = 'y'' `" k& t" c5 w  j
6                       """)
3 D; m8 `8 W6 g0 B( J/ w1 X* d8 [! X$ Q
3、参数8 @$ |0 j! _, z5 A) y- H) w! p" @

9 ~. M" C6 f/ p3 c1)ODBC支持在SQL语句中使用一个问号来作为参数。你可以在SQL语句后面加上值,用来传递给SQL语句中的问号。
2 `9 U' O" J. Y# V- S1        cursor.execute("""
+ ?; v1 B+ `& a8 ]9 @( c, u2                       select user_id, user_name
: S6 Z$ {5 h) e% T, ]3                         from users
. f+ u! a% W: W* G4                        where last_logon < ?
. |* C# D/ N' ^) R0 `$ a. i4 o5                          and bill_overdue = ?
6 H5 j4 N6 z( X3 A* d9 ]; Z% _6                       """, '2001-01-01', 'y')
, ~; Q& `. r% t1 G1 r; Q/ `2 _# `* O9 D
这样做比直接把值写在SQL语句中更加安全,这是因为每个参数传递给数据库都是单独进行的。如果你使用不同的参数而运行同样的SQL语句,这样做也更加效率。
6 b, F7 V! b8 t7 i3 y4 N  E8 T5 I0 U. I; o' i$ h
3)python DB API明确说明多参数时可以使用一个序列来传递。pyodbc同样支持:, b* G4 R4 j) u+ V9 }" ~; Q' V
1        cursor.execute("""" b: n2 d; Q1 _5 N
2                       select user_id, user_name7 A# Q2 a2 L$ z. |6 o' h  l
3                         from users
7 O0 R7 A  r+ w( A; w" {4                        where last_logon < ?
( ^3 ^& t" l0 u) M" E2 G7 y$ B& Y5                          and bill_overdue = ?0 c5 p9 [; C# w
6                       """, ['2001-01-01', 'y'])5 g. B. Q7 M5 y; V- B
) i- _% [- K2 f3 J1 `. V
1        cursor.execute("select count(*) as user_count from users where age > ?", 21)9 L5 J9 ^" @! i" U
2        row = cursor.fetchone()
. R! f& r- e" J% h! _3        print '%d users' % row.user_count, J1 k9 N% Y- c+ j: Z
' [6 }$ F1 G' @: B5 V8 v, q  ]" H

# l) H4 ]. c  [5 ]4 E5 A* U3 N7 F% R0 `5 ~; Q; S: x! C
4、数据插入
' H. K  ~6 Z. O4 Y& \( Z2 R) `# Y1 d& s
1)数据插入,把SQL插入语句传递给cursor的execute函数,可以伴随任何需要的参数。
1 v/ l( y! K8 Q- g) _1        cursor.execute("insert into products(id, name) values ('pyodbc', 'awesome library')")* m$ V, Z  s1 ?& o4 n+ Z
2        cnxn.commit()
$ D9 @. u$ D8 G0 _+ V! n& x, O3 F! g2 G
1        cursor.execute("insert into products(id, name) values (?, ?)", 'pyodbc', 'awesome library'). ], I2 N; I, g- w5 H
2        cnxn.commit()
0 b7 x) I' w) E* Z
8 Y5 w9 g% p- f注意调用cnxn.commit()函数:你必须调用commit函数,否者你对数据库的所有操作将会失效!当断开连接时,所有悬挂的修改将会被重置。这很容易导致出错,所以你必须记得调用commit函数。
7 {6 V2 ~$ [- W  U7 ~
) b& @5 e8 R/ ?+ q. l5 p2 Q5、数据修改和删除; s$ P! Z1 O3 Y& M3 |  v
/ L4 p! N" {6 J5 R' n5 o. b# h
1)数据修改和删除也是跟上面的操作一样,把SQL语句传递给execute函数。但是我们常常想知道数据修改和删除时,到底影响了多少条记录,这个时候你可以使用cursor.rowcount的返回值。
  I& }; \2 q# n6 T* `) F  j1        cursor.execute("delete from products where id <> ?", 'pyodbc')9 l7 j2 `, S  M$ f
2        print cursor.rowcount, 'products deleted'
7 ?% W% S1 i4 Y0 r1 P" |2 ]: h3        cnxn.commit()6 [# h$ X- Q" m' [
3 {2 ?! r- Z$ N* \  Z8 G; w; W  ?
2)由于execute函数总是返回cursor,所以有时候你也可以看到像这样的语句:(注意rowcount放在最后面)
0 `6 r* B2 j) {5 C3 i. C7 U1        deleted = cursor.execute("delete from products where id <> 'pyodbc'").rowcount
$ E4 `; s9 T: @7 x, v3 k2        cnxn.commit()' D  e2 a! T9 ]+ h
8 M3 Z  I% W# G  k
同样要注意调用cnxn.commit()函数* o) P4 a0 n7 {4 V2 d+ m
* X$ ^4 x# n1 ?. E& Q$ n. w
6、小窍门& b$ P- l' m3 ]. `

6 X: \6 x' @7 s4 ]$ \3 A1)由于使用单引号的SQL语句是有效的,那么双引号也同样是有效的:
% \" X0 u$ K: w! U, k( N5 M" g1        deleted = cursor.execute("delete from products where id <> 'pyodbc'").rowcount' P& a5 @1 m' s! Q; @' s

! z3 M4 L2 ^3 l; i# i2)假如你使用的是三引号,那么你也可以这样使用:9 T1 ^4 G5 R' j- x3 r# y2 G& t
1        deleted = cursor.execute("""7 h$ N7 M* f& x. g4 n0 q! J
2                                 delete8 g1 p5 P" V- \. H2 |" j2 j
3                                   from products1 Y$ |5 d# p7 U$ K: f9 R+ t% C3 c
4                                  where id <> 'pyodbc'
/ A- [1 J3 c7 }$ z5 }' }5                                 """).rowcount; {* i- p/ e8 Z) F, X9 w# A% \3 g* v

! g& E+ V( G) i9 N* F2 [5 i3)有些数据库(比如SQL Server)在计数时并没有产生列名,这种情况下,你想访问数据就必须使用下标。当然你也可以使用“as”关键字来取个列名(下面SQL语句的“as name-count”)
: h4 c0 e5 D- s5 Y& V! f; h4 v" z1        row = cursor.execute("select count(*) as user_count from users").fetchone()
% b9 t* m% \6 H) ^6 v' s" J2        print '%s users' % row.user_count8 w  ?/ T- g) E" j+ w  t

* _0 d5 P; y3 y, i/ i+ Q3 d* V7 G& Q  \4)假如你只是需要一个值,那么你可以在同一个行局中使用fetch函数来获取行和第一个列的所有数据。
' ~7 [9 g8 A9 n* v, p5 A1        count = cursor.execute("select count(*) from users").fetchone()[0]
: C. S2 r- b" ]7 F* v- T2        print '%s users' % count6 w* d4 D$ t! R7 g8 Q5 \

* W5 q; i1 C3 b4 B  d如果列为空,将会导致该语句不能运行。fetchone()函数返回None,而你将会获取一个错误:NoneType不支持下标。如果有一个默认值,你能常常使用ISNULL,或者在SQL数据库直接合并NULLs来覆盖掉默认值。; B' Y) n+ T7 b' i, ^; v; p& P9 B9 Z
1        maxid = cursor.execute("select coalesce(max(id), 0) from users").fetchone()[0]
' o. }& Y6 H  N6 n" E
* {/ w. W+ v9 [6 P% o+ X' i在这个例子里面,如果max(id)返回NULL,coalesce(max(id),0)将导致查询的值为0。




欢迎光临 数学建模社区-数学中国 (http://www.madio.net/) Powered by Discuz! X2.5