python数据库函数的封装调用
来源:互联网 发布:淘宝买药品要提交需求 编辑:程序博客网 时间:2024/05/16 07:43
这里分享的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'))
0 0
- python数据库函数的封装调用
- 利用boost.python封装C++函数供python调用
- Python入门:函数封装之python调用sqlmap
- python调用java基于pyjnius的封装
- fortran中调用C封装的函数
- 封装函数与调用
- Delphi数据库操作函数的封装(转)
- php数据库常用函数的封装
- Python调用采用Boost Python封装的c++(2)
- Python调用采用Boost Python封装的c++(2)
- Python调用采用Boost Python封装的c++
- AsyncTask函数化的封装,AsyncTask函数式的调用
- 调用远程数据库的函数
- 调用远程数据库的函数
- Python调用PHP的函数
- C调用Python的函数
- Python调用 c 的函数
- C调用Python的函数
- java基础之反射
- ural 1018 Binary Apple Tree
- Hadoop 使用常见问题
- 黑马程序员——java多线程之死锁和等待唤醒机制
- exit 和 return
- python数据库函数的封装调用
- c++ 的(. ::)运算符
- CentOS 7 Linux系统管理员的命令行工具箱目录
- Linux下多线程查看工具(pstree、ps、pstack)
- 前端架构的技术储备需要想什么
- Windows快捷键
- 使用strace+pstack利器分析程序性能
- Unix路径“/”和“//”
- iOS复习笔记16:应用启动过程和工程结构