数学建模社区-数学中国
标题:
pyodbc的简单使用
[打印本页]
作者:
Seawind2012
时间:
2012-7-4 14:30
标题:
pyodbc的简单使用
1、连接数据库
- b8 j- V& `9 K6 S
. m1 \/ {0 A8 W3 ]% l( |* E
1）直接连接数据库和创建一个游标（cursor)
/ D4 y5 P+ K+ X2 J( z& N! s
1 cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=localhost;DATABASE=testdb;UID=me
WD=pass')
$ _5 D3 d9 d- o9 y. I
2 cursor = cnxn.cursor()
- W8 n3 |3 `( w# R' f
% u j7 s* B) ~! }7 x
2）使用DSN连接。通常DSN连接并不需要密码，还是需要提供一个PSW的关键字。
% C$ z8 n( J# ?9 r) d
1 cnxn = pyodbc.connect('DSN=test
WD=password')
) l& B. H0 J1 M
2 cursor = cnxn.cursor()
7 E2 r7 n _ I
) P3 w" b6 [9 l+ b
关于连接函数还有更多的选项，可以在pyodbc文档中的 connect funtion 和 ConnectionStrings查看更多的细节
- M$ K+ G4 d' ~1 v. }
( Y& U1 I8 B. Y) P
2、数据查询（SQL语句为 select ...from..where）
, `8 `* L5 A- o: ~: P
: }) Y" F% J, u! ]
1）所有的SQL语句都用cursor.execute函数运行。如果语句返回行，比如一个查询语句返回的行，你可以通过游标的fetch函数来获取数据，这些函数有（fetchone,fetchall,fetchmany）.如果返回空行，fetchone函数将返回None,而fetchall和fetchmany将返回一个空列。
5 Q* ?; I/ i; ]+ L( X t
1 cursor.execute("select user_id, user_name from users")
1 `) d, C7 V! o( u/ Z4 Y8 B w3 S
2 row = cursor.fetchone()
x d$ N9 G! X9 S2 u
3 if row:
8 Y* N& v7 Z8 D+ f( c+ S8 R
4 print row
: p7 V/ t' G3 _1 Z: G/ u7 J6 C
" Z" v4 ~& a: ~! ]7 }, `
2）Row这个类，类似于一个元组，但是他们也可以通过字段名进行访问。
" M* e- h; v$ D- |4 D
1 cursor.execute("select user_id, user_name from users")
# |1 C% ~! z m* @3 l" b: i
2 row = cursor.fetchone()
6 [# l. I) V, I g5 Y. b! ?- j
3 print 'name:', row[1] # access by column index
) P; c4 z* y+ [+ t% S# Y/ B8 {7 q
4 print 'name:', row.user_name # or access by name
" d# f2 Z0 K+ N& J0 }, F2 S, I
& _- b% ^1 ?" x' \: I" h* |
3）如果所有的行都被检索完，那么fetchone将返回None.
! _6 U4 s8 L2 V8 o$ E# q
1 while 1:
- x! n" R3 B$ s4 g2 z9 V
2 row = cursor.fetchone()
- s$ `+ _" ^( u' \' ~
3 if not row:
* B( Y( x7 H1 n
4 break
6 f) Z- r E, U
5 print 'id:', row.user_id
/ m, C. x; n( _; b' y2 H: Q
7 i7 ^3 Y2 S A$ d
4)使用fetchall函数时，将返回所有剩下的行，如果是空行，那么将返回一个空列。（如果有很多行，这样做的话将会占用很多内存。未读取的行将会被压缩存放在数据库引擎中，然后由数据库服务器分批发送。一次只读取你需要的行，将会大大节省内存空间）
. a% H5 T8 V/ v1 q; J) {0 Y
1 cursor.execute("select user_id, user_name from users")
) P& R* N4 }, M9 h% x3 o* u
2 rows = cursor.fetchall()
/ C; s9 s" N6 g% U8 N0 f; P
3 for row in rows:
+ w$ q3 x G4 n7 G
4 print row.user_id, row.user_name
1 p+ s: G8 r( O- y& U
( U, q+ ^! J+ z# I% J ?. w
5）如果你打算一次读完所有数据，那么你可以使用cursor本身。
- p5 R8 o/ a' f
1 cursor.execute("select user_id, user_name from users"):
( i/ V+ `7 G7 l
2 for row in cursor:
2 {, \ ]' L& [7 n9 \) D! y
3 print row.user_id, row.user_name
2 p' k% w/ ]; u2 E* w1 b
/ y' D6 n' |7 |. ~0 l
6）由于cursor.execute返回一个cursor，所以你可以把上面的语句简化成：
/ ]% ]1 G ?7 f0 @
1 for row in cursor.execute("select user_id, user_name from users"):
& W% X9 h% l" N% W) @8 f
2 print row.user_id, row.user_name
$ O5 i# l: H3 q
9 I4 V/ _. f( G. j- X4 P" Y) B+ v
7）有很多SQL语句用单行来写并不是很方便，所以你也可以使用三引号的字符串来写：
# S( w( J8 V8 i, A+ ]* V
1 cursor.execute("""
- J* @, h+ X7 \! ^
2 select user_id, user_name
; K" S1 j) @- N5 o% j
3 from users
3 V5 n9 D# J9 s: x5 P7 l- v
4 where last_logon < '2001-01-01'
( Q' }/ E, r2 _, r$ r. n; |
5 and bill_overdue = 'y'
+ a( h& D4 A- U
6 """)
- E* `' U9 Q6 `9 C
. V' [: i! U( z
3、参数
6 _1 L; T- C) Z2 ^! A
6 i* b/ G4 h' L8 r' r7 ?+ t
1）ODBC支持在SQL语句中使用一个问号来作为参数。你可以在SQL语句后面加上值，用来传递给SQL语句中的问号。
9 C' h, n/ L+ ?5 H! _' e. T
1 cursor.execute("""
# w$ E# N& m8 ~1 p
2 select user_id, user_name
* d* f0 p+ Q* W N y: h" H1 |
3 from users
- D8 ?' p; K- u! `3 S$ L }
4 where last_logon < ?
3 d+ ]1 ]) t, m* P
5 and bill_overdue = ?
! D J- z" p, h7 _+ V0 h
6 """, '2001-01-01', 'y')
1 `! T7 z; u8 `, w! m6 p) c! _
5 [: [( a5 F% ]8 O# a: R
这样做比直接把值写在SQL语句中更加安全，这是因为每个参数传递给数据库都是单独进行的。如果你使用不同的参数而运行同样的SQL语句，这样做也更加效率。
7 t6 j% `6 e/ l: f
3 E; i6 R+ L4 N
3）python DB API明确说明多参数时可以使用一个序列来传递。pyodbc同样支持：
1 B x; a" R1 @* a+ {" _! V0 H
1 cursor.execute("""
5 e5 T9 V% S( C
2 select user_id, user_name
" k1 K3 p5 M, C
3 from users
3 Q& s7 y; b' I* t
4 where last_logon < ?
" I' r( `& k8 P4 e5 k( [
5 and bill_overdue = ?
5 c: o" ]( J# c/ o8 Y8 d
6 """, ['2001-01-01', 'y'])
- s, y d, G$ o: P& @* u8 c' ~7 \2 y
5 [3 x# w% h# `! O( ^: T: j3 o
1 cursor.execute("select count(*) as user_count from users where age > ?", 21)
2 V" u7 \9 Z/ L
2 row = cursor.fetchone()
* D1 i2 k# z, }8 @
3 print '%d users' % row.user_count
) \ w0 c6 k; s" ^( g# R6 J1 C
4 G. g# y+ W: x
% e0 N$ v. V+ u0 L- v
- s4 N- H+ @! W, x
4、数据插入
/ H$ _3 o1 l# k6 N" T
9 L, y) H) H% F7 W1 @
1）数据插入，把SQL插入语句传递给cursor的execute函数，可以伴随任何需要的参数。
% H W6 k3 H. _ q4 G1 _2 [
1 cursor.execute("insert into products(id, name) values ('pyodbc', 'awesome library')")
1 `0 D( `. S* b% m2 O
2 cnxn.commit()
+ {* f9 C# Y( P* Q
, C: H' n& H0 ^2 ~; ^4 k, v
1 cursor.execute("insert into products(id, name) values (?, ?)", 'pyodbc', 'awesome library')
- H9 C: ^9 q( D0 L' }9 u. J
2 cnxn.commit()
5 t2 `* x$ y! {6 `& v+ Z
/ V+ _0 I' E, P1 Q
注意调用cnxn.commit()函数：你必须调用commit函数，否者你对数据库的所有操作将会失效！当断开连接时，所有悬挂的修改将会被重置。这很容易导致出错，所以你必须记得调用commit函数。
1 X; C+ Y* x1 |! r- {
3 M; g' D9 v3 h, A
5、数据修改和删除
0 y+ R) [: [0 ?6 C8 d" {
0 E6 A# n t8 X" e" Z! }
1）数据修改和删除也是跟上面的操作一样，把SQL语句传递给execute函数。但是我们常常想知道数据修改和删除时，到底影响了多少条记录，这个时候你可以使用cursor.rowcount的返回值。
4 h' s) {5 o# q1 |6 p
1 cursor.execute("delete from products where id <> ?", 'pyodbc')
C ]: v% [; a* n- r
2 print cursor.rowcount, 'products deleted'
8 e8 ~2 q7 x! z- g
3 cnxn.commit()
8 a+ `9 r% p( a; P
! ]$ o0 h. q# g0 m
2）由于execute函数总是返回cursor，所以有时候你也可以看到像这样的语句：（注意rowcount放在最后面）
9 r/ c9 G6 |, F: z, y
1 deleted = cursor.execute("delete from products where id <> 'pyodbc'").rowcount
# u1 m/ @3 d* F) h) \, V
2 cnxn.commit()
3 t9 S# l: e p$ e
2 h5 F b9 K( w* g$ q. o1 e
同样要注意调用cnxn.commit()函数
* G* [! i: c9 a! l6 U" g
5 u3 ~! o1 A1 e4 x' c- n
6、小窍门
- @* v$ G3 S; w9 Q/ S) H, l8 [! g, `
8 n D) i4 U7 o8 N+ c
1）由于使用单引号的SQL语句是有效的，那么双引号也同样是有效的：
# U: R; g8 E$ { |& K4 l
1 deleted = cursor.execute("delete from products where id <> 'pyodbc'").rowcount
. Q1 i' V, K. ~
/ O( D" c+ L. W* r# P
2）假如你使用的是三引号，那么你也可以这样使用：
+ I9 G v. {1 e
1 deleted = cursor.execute("""
, x; P, [5 j. ^ H9 z
2 delete
# |! I+ F: i# b+ L
3 from products
5 r" G. A; i% C: g q) [% ~0 i0 q
4 where id <> 'pyodbc'
' r4 J* M) [! F$ Q4 T- j
5 """).rowcount
6 r' B1 U: G2 p$ G9 k' G/ O% C/ A' T
# O: \1 E0 c# W2 p; _& r: I
3）有些数据库（比如SQL Server）在计数时并没有产生列名，这种情况下，你想访问数据就必须使用下标。当然你也可以使用“as”关键字来取个列名（下面SQL语句的“as name-count”）
; K# R: [5 w# J' k1 M0 m
1 row = cursor.execute("select count(*) as user_count from users").fetchone()
* o q, h# ^& J; h5 R6 Q! s
2 print '%s users' % row.user_count
: A% S; Y& |9 }1 P( b$ n9 S& i" g; v. I
: B$ l" D k' L( G Q
4）假如你只是需要一个值，那么你可以在同一个行局中使用fetch函数来获取行和第一个列的所有数据。
" ^8 z$ O# ~) [* z3 N m7 S5 e- [
1 count = cursor.execute("select count(*) from users").fetchone()[0]
. r6 G# b0 `& z
2 print '%s users' % count
" V5 y- u1 j7 p5 O
0 r* {5 ]" Z @4 P7 O
如果列为空，将会导致该语句不能运行。fetchone()函数返回None，而你将会获取一个错误:NoneType不支持下标。如果有一个默认值，你能常常使用ISNULL,或者在SQL数据库直接合并NULLs来覆盖掉默认值。
7 K$ F5 z' G* g" O1 y
1 maxid = cursor.execute("select coalesce(max(id), 0) from users").fetchone()[0]
k+ f) D# y$ J Q
5 N6 y* L1 W0 Q
在这个例子里面，如果max(id)返回NULL，coalesce(max(id),0)将导致查询的值为0。
欢迎光临 数学建模社区-数学中国 (http://www.madio.net/)
Powered by Discuz! X2.5
WD=pass')$ _5 D3 d9 d- o9 y. IWD=password')) l& B. H0 J1 M