数学建模社区-数学中国

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

---

作者: Seawind2012    时间: 2012-7-4 14:30
标题: pyodbc的简单使用
1、连接数据库
$ Y) q7 P, B- b! D$ j( p
1）直接连接数据库和创建一个游标（cursor)
1        cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=localhost;DATABASE=testdb;UID=meWD=pass')
, p5 h7 M1 Q$ k2        cursor = cnxn.cursor()

2）使用DSN连接。通常DSN连接并不需要密码，还是需要提供一个PSW的关键字。
1        cnxn = pyodbc.connect('DSN=testWD=password')
8 |: t4 p8 i: S8 A, j: i0 n2        cursor = cnxn.cursor()

关于连接函数还有更多的选项，可以在pyodbc文档中的 connect funtion 和 ConnectionStrings查看更多的细节
, r* P4 U& ?* ~' L  W
2、数据查询（SQL语句为 select ...from..where）
" J0 w- v( [) z
1 P7 H  O# j4 Q- \3 x  N; M1）所有的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()
* L& {* y6 ^" A) B7 h1 @5 }3        if row:
4            print row

2）Row这个类，类似于一个元组，但是他们也可以通过字段名进行访问。
1        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
% G' t3 e- J" Q, G
3）如果所有的行都被检索完，那么fetchone将返回None.
/ a3 _4 J- W; W7 K7 A' y1        while 1:
2            row = cursor.fetchone()
3            if not row:
4                break
5 ^! b/ K. `8 \) ~9 m2 a5            print 'id:', row.user_id
6 I6 y8 a# ~7 }7 j3 c0 u
2 |5 \& U$ z' w3 N. _4)使用fetchall函数时，将返回所有剩下的行，如果是空行，那么将返回一个空列。（如果有很多行，这样做的话将会占用很多内存。未读取的行将会被压缩存放在数据库引擎中，然后由数据库服务器分批发送。一次只读取你需要的行，将会大大节省内存空间）
3 Y! G" k( U, X1        cursor.execute("select user_id, user_name from users")
& r( ~" G, b$ Y: _! \2        rows = cursor.fetchall()
3        for row in rows:
/ ^7 r" A" [8 I$ I% |  z4            print row.user_id, row.user_name

5）如果你打算一次读完所有数据，那么你可以使用cursor本身。
1        cursor.execute("select user_id, user_name from users"):
# {# C( Q4 @! e+ Q  g& x$ @4 J2        for row in cursor:
5 m/ `, u. \! t4 N7 J( V3            print row.user_id, row.user_name

6）由于cursor.execute返回一个cursor，所以你可以把上面的语句简化成：
1        for row in cursor.execute("select user_id, user_name from users"):
2            print row.user_id, row.user_name

7）有很多SQL语句用单行来写并不是很方便，所以你也可以使用三引号的字符串来写：
' z0 O4 |  Z9 s: o: H- _5 J8 t! ?7 u1        cursor.execute("""
0 F, K6 r& B: r/ c* Q2                       select user_id, user_name
% ^8 `* G1 E+ ^. ^3                         from users
4                        where last_logon < '2001-01-01'
8 {. r. z% f0 q+ J+ N; X9 I& F+ [5                          and bill_overdue = 'y'
6                       """)
" X, X# A, F) y4 D: H
3、参数

1）ODBC支持在SQL语句中使用一个问号来作为参数。你可以在SQL语句后面加上值，用来传递给SQL语句中的问号。
1        cursor.execute("""
8 U" G" R/ \/ L; f' ^6 O2                       select user_id, user_name
3                         from users
4                        where last_logon < ?
5                          and bill_overdue = ?
2 h6 p; ], T; J3 n) p( A0 p6                       """, '2001-01-01', 'y')
4 y, z% k: l2 {; R
这样做比直接把值写在SQL语句中更加安全，这是因为每个参数传递给数据库都是单独进行的。如果你使用不同的参数而运行同样的SQL语句，这样做也更加效率。
6 s+ F+ _; N* H! G
2 t4 G- V7 E8 I. \) u3）python DB API明确说明多参数时可以使用一个序列来传递。pyodbc同样支持：
0 l& {# x! j* }0 K! d, ^- i1        cursor.execute("""
9 V9 N2 t  F6 H  ]2                       select user_id, user_name
; r. U' D9 N, H4 h8 V! P, w3                         from users
  m+ \+ D/ K% k% W, e0 j: q4                        where last_logon < ?
5                          and bill_overdue = ?
& N3 B& T: p+ Z2 \2 B" D$ B+ |6                       """, ['2001-01-01', 'y'])

1        cursor.execute("select count(*) as user_count from users where age > ?", 21)
% j- D' Y% p, e2 l# F2        row = cursor.fetchone()
# }, a3 [+ }; o4 t; X3        print '%d users' % row.user_count
$ i8 [# S+ u) E/ M. p


4、数据插入

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

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

5、数据修改和删除
: @' j' l  Q4 o- R- x$ _" S+ T
- a# P: B+ @, K% O  \1）数据修改和删除也是跟上面的操作一样，把SQL语句传递给execute函数。但是我们常常想知道数据修改和删除时，到底影响了多少条记录，这个时候你可以使用cursor.rowcount的返回值。
& R& s- z* y: B. o9 T2 [3 v1        cursor.execute("delete from products where id <> ?", 'pyodbc')
) p' P9 y8 u1 `8 `- i" h) {3 {' R% k2        print cursor.rowcount, 'products deleted'
# b$ ~# D" D6 E! x6 a3 \0 p3        cnxn.commit()

! p8 E9 J1 W- z4 ?' y1 O$ U' Y2）由于execute函数总是返回cursor，所以有时候你也可以看到像这样的语句：（注意rowcount放在最后面）
1        deleted = cursor.execute("delete from products where id <> 'pyodbc'").rowcount
2        cnxn.commit()
3 R) p' E. P" }) @7 D9 ~# J" L' S
同样要注意调用cnxn.commit()函数
# i# @% g0 }" l; H% G$ m, ~
6、小窍门

* c# k$ m% q! i6 T1）由于使用单引号的SQL语句是有效的，那么双引号也同样是有效的：
1        deleted = cursor.execute("delete from products where id <> 'pyodbc'").rowcount
) P: Y8 R& P& q# @+ m, {7 P
+ `8 O! J3 ]& k" v2）假如你使用的是三引号，那么你也可以这样使用：
1 O1 ^# o- S' H. F$ B3 |1        deleted = cursor.execute("""
2                                 delete
3                                   from products
/ y6 i; P1 u  U* k+ r, ?, y4                                  where id <> 'pyodbc'
$ G' ]) p; y6 I, R) U5                                 """).rowcount

3）有些数据库（比如SQL Server）在计数时并没有产生列名，这种情况下，你想访问数据就必须使用下标。当然你也可以使用“as”关键字来取个列名（下面SQL语句的“as name-count”）
1        row = cursor.execute("select count(*) as user_count from users").fetchone()
+ O% t! o$ R" M2        print '%s users' % row.user_count
& r+ F0 P0 x7 Z  ~( x7 z
4）假如你只是需要一个值，那么你可以在同一个行局中使用fetch函数来获取行和第一个列的所有数据。
3 ]1 o3 @* J0 P2 J1        count = cursor.execute("select count(*) from users").fetchone()[0]
1 O! [, c) R9 ]" S  Q! b5 L2        print '%s users' % count
. \8 e, B! i. d2 @' g" T5 b$ U  V
如果列为空，将会导致该语句不能运行。fetchone()函数返回None，而你将会获取一个错误:NoneType不支持下标。如果有一个默认值，你能常常使用ISNULL,或者在SQL数据库直接合并NULLs来覆盖掉默认值。
1        maxid = cursor.execute("select coalesce(max(id), 0) from users").fetchone()[0]

8 v0 w7 G) g7 J3 x* c+ n在这个例子里面，如果max(id)返回NULL，coalesce(max(id),0)将导致查询的值为0。

---

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

Powered by Discuz! X2.5