数学建模社区-数学中国

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

---

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

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')
2        cursor = cnxn.cursor()
% ^; n; Y5 n3 Q7 |' f0 Z% h9 B) u0 b
; D3 j2 D# W4 Z# F" A2）使用DSN连接。通常DSN连接并不需要密码，还是需要提供一个PSW的关键字。
1        cnxn = pyodbc.connect('DSN=testWD=password')
( a" G! v/ q2 t+ ~. o2        cursor = cnxn.cursor()

关于连接函数还有更多的选项，可以在pyodbc文档中的 connect funtion 和 ConnectionStrings查看更多的细节

5 b/ F& B8 Y$ l% X2 \" b2、数据查询（SQL语句为 select ...from..where）

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")
2        row = cursor.fetchone()
3        if row:
/ p/ Q9 J) l) m1 u' x' h4            print row
- K! ?1 F, S, r7 L  O+ t
2）Row这个类，类似于一个元组，但是他们也可以通过字段名进行访问。
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
4        print 'name:', row.user_name   # or access by name
; W4 n1 J7 ?0 n+ |6 B. e' o
3）如果所有的行都被检索完，那么fetchone将返回None.
- h1 [5 m' v9 l& y- x1        while 1:
2            row = cursor.fetchone()
3            if not row:
4                break
5            print 'id:', row.user_id

% E3 _1 X" J7 K4)使用fetchall函数时，将返回所有剩下的行，如果是空行，那么将返回一个空列。（如果有很多行，这样做的话将会占用很多内存。未读取的行将会被压缩存放在数据库引擎中，然后由数据库服务器分批发送。一次只读取你需要的行，将会大大节省内存空间）
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"):
2            print row.user_id, row.user_name

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
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'
6                       """)
3 D; m8 `8 W6 g0 B( J/ w
3、参数

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% t
这样做比直接把值写在SQL语句中更加安全，这是因为每个参数传递给数据库都是单独进行的。如果你使用不同的参数而运行同样的SQL语句，这样做也更加效率。
6 b, F7 V! b8 t7 i
3）python DB API明确说明多参数时可以使用一个序列来传递。pyodbc同样支持：
1        cursor.execute("""
2                       select user_id, user_name
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 = ?
6                       """, ['2001-01-01', 'y'])

1        cursor.execute("select count(*) as user_count from users where age > ?", 21)
2        row = cursor.fetchone()
. R! f& r- e" J% h! _3        print '%d users' % row.user_count


# l) H4 ]. c  [5 ]4 E
4、数据插入
' H. K  ~6 Z. O4 Y
1）数据插入，把SQL插入语句传递给cursor的execute函数，可以伴随任何需要的参数。
1 v/ l( y! K8 Q- g) _1        cursor.execute("insert into products(id, name) values ('pyodbc', 'awesome library')")
2        cnxn.commit()
$ D9 @. u$ D8 G0 _+ V
1        cursor.execute("insert into products(id, name) values (?, ?)", 'pyodbc', 'awesome library')
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、数据修改和删除

1）数据修改和删除也是跟上面的操作一样，把SQL语句传递给execute函数。但是我们常常想知道数据修改和删除时，到底影响了多少条记录，这个时候你可以使用cursor.rowcount的返回值。
  I& }; \2 q# n6 T* `) F  j1        cursor.execute("delete from products where id <> ?", 'pyodbc')
2        print cursor.rowcount, 'products deleted'
7 ?% W% S1 i4 Y0 r1 P" |2 ]: h3        cnxn.commit()

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()

同样要注意调用cnxn.commit()函数

6、小窍门

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

! z3 M4 L2 ^3 l; i# i2）假如你使用的是三引号，那么你也可以这样使用：
1        deleted = cursor.execute("""
2                                 delete
3                                   from products
4                                  where id <> 'pyodbc'
/ A- [1 J3 c7 }$ z5 }' }5                                 """).rowcount

! 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_count

* _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' % count

* W5 q; i1 C3 b4 B  d如果列为空，将会导致该语句不能运行。fetchone()函数返回None，而你将会获取一个错误:NoneType不支持下标。如果有一个默认值，你能常常使用ISNULL,或者在SQL数据库直接合并NULLs来覆盖掉默认值。
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