这里分享的python连接sql server 2005的代码,在google上有个开源的项目叫pymssql
项目地址:https://code.google.com/p/pymssql/
演示代码:
复制代码代码示例:
#!/bin/python
#
#site: www.jbxue.com
import pymssql
conn = pymssql.connect(host='192.168.1.1',port='1433', user='user', password='password', database='mydatabase', as_dict=True)
cur = conn.cursor()
cur.execute('SELECT * FROM persons WHERE salesrep=%s', 'John Doe')
for row in cur:
print "ID=%d, Name=%s" % (row['id'], row['name'])
conn.close()
#
#site: www.jbxue.com
import pymssql
conn = pymssql.connect(host='192.168.1.1',port='1433', user='user', password='password', database='mydatabase', as_dict=True)
cur = conn.cursor()
cur.execute('SELECT * FROM persons WHERE salesrep=%s', 'John Doe')
for row in cur:
print "ID=%d, Name=%s" % (row['id'], row['name'])
conn.close()
说明:
第一行导入
第二个连接ip为192.168.1.1端口为1433的mydatabase数据库
第四行就是查询表
python遍历表中的数据,调用存储过程:
复制代码代码示例:
#!/bin/python
#
#site: www.jbxue.com
import pymssql
conn = pymssql.connect(host='192.168.1.1','1433', user='user', password='password', database='mydatabase', as_dict=True)
cur = conn.cursor()
cur.callproc('findPerson', ('John Doe',))
for row in cur:
print "ID=%d, Name=%s" % (row['id'], row['name'])
conn.close()
#
#site: www.jbxue.com
import pymssql
conn = pymssql.connect(host='192.168.1.1','1433', user='user', password='password', database='mydatabase', as_dict=True)
cur = conn.cursor()
cur.callproc('findPerson', ('John Doe',))
for row in cur:
print "ID=%d, Name=%s" % (row['id'], row['name'])
conn.close()
创建表:
复制代码代码示例:
#!/bin/python
#
#site: www.jbxue.com
import pymssql
conn = pymssql.connect(host='SQL01', user='user', password='password', database='mydatabase')
cur = conn.cursor()
cur.execute('CREATE TABLE persons(id INT, name VARCHAR(100))')
cur.executemany("INSERT INTO persons VALUES(%d, %s)", \
[ (1, 'John Doe'), (2, 'Jane Doe') ])
conn.commit() # you must call commit() to persist your data if you don't set autocommit to True
cur.execute('SELECT * FROM persons WHERE salesrep=%s', 'John Doe')
row = cur.fetchone()
while row:
print "ID=%d, Name=%s" % (row[0], row[1])
row = cur.fetchone()
# if you call execute() with one argument, you can use % sign as usual
# (it loses its special meaning).
cur.execute("SELECT * FROM persons WHERE salesrep LIKE 'J%'")
conn.close()
#
#site: www.jbxue.com
import pymssql
conn = pymssql.connect(host='SQL01', user='user', password='password', database='mydatabase')
cur = conn.cursor()
cur.execute('CREATE TABLE persons(id INT, name VARCHAR(100))')
cur.executemany("INSERT INTO persons VALUES(%d, %s)", \
[ (1, 'John Doe'), (2, 'Jane Doe') ])
conn.commit() # you must call commit() to persist your data if you don't set autocommit to True
cur.execute('SELECT * FROM persons WHERE salesrep=%s', 'John Doe')
row = cur.fetchone()
while row:
print "ID=%d, Name=%s" % (row[0], row[1])
row = cur.fetchone()
# if you call execute() with one argument, you can use % sign as usual
# (it loses its special meaning).
cur.execute("SELECT * FROM persons WHERE salesrep LIKE 'J%'")
conn.close()
本文主要是Python操作SQLServer示例,包括执行查询及更新操作(写入中文)。
需要注意的是:读取数据的时候需要decode('utf-8'),写数据的时候需要encode('utf-8'),这样就可以避免烦人的中文乱码或报错问题。
Python操作SQLServer需要使用pymssql模块,使用pip install pymssql安装即可。
此外代码中使用的封装MSSQL类是从网上搜索到的,直接用即可。
1 | # -*- coding:utf-8 -*- |
2 |
3 | import pymssql |
4 |
5 | class MSSQL: |
6 | def __init__(self,host,user,pwd,db): |
7 | self.host = host |
8 | self.user = user |
9 | self.pwd = pwd |
10 | self.db = db |
11 |
12 | def __GetConnect(self): |
13 | if not self.db: |
14 | raise(NameError,"没有设置数据库信息") |
15 | self.conn = pymssql.connect(host=self.host,user=self.user,password=self.pwd,database=self.db,charset="utf8") |
16 | cur = self.conn.cursor() |
17 | if not cur: |
18 | raise(NameError,"连接数据库失败") |
19 | else: |
20 | return cur |
21 |
22 | def ExecQuery(self,sql): |
23 | cur = self.__GetConnect() |
24 | cur.execute(sql) |
25 | resList = cur.fetchall() |
26 |
27 | #查询完毕后必须关闭连接 |
28 | self.conn.close() |
29 | return resList |
30 |
31 | def ExecNonQuery(self,sql): |
32 | cur = self.__GetConnect() |
33 | cur.execute(sql) |
34 | self.conn.commit() |
35 | self.conn.close() |
36 |
37 | ms = MSSQL(host="192.168.1.1",user="sa",pwd="sa",db="testdb") |
38 | reslist = ms.ExecQuery("select * from webuser") |
39 | for i in reslist: |
40 | print i |
41 |
42 | newsql="update webuser set name='%s' where id=1"%u'测试' |
43 | print newsql |
44 | ms.ExecNonQuery(newsql.encode('utf-8')) |