这里分享的python连接sql server 2005的代码,在google上有个开源的项目叫pymssql
项目地址:https://code.google.com/p/pymssql/
演示代码:
复制代码 代码示例:
#!/bin/python
#
#site: www.jb200.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.jb200.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.jb200.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()