最近用到Python,要操作数据库,没有现成的操作类,临时写了一个Python的SQLSERVER操作封装类,分享一下,需要安装pymssql包,安装方法:https://blog.csdn.net/sinat_28984567/article/details/105316092,下边是操作类:
# python sql server 操作类
import pymssql
class DBHelper:
def __init__(self, host="127.0.0.1", port="1433", user="sa", password="123456", database="test"): # 构造函数
try:
self.conn = pymssql.connect(host, user, password, database)
self.cursor = self.conn.cursor()
except Exception as e:
print(e)
# 返回执行execute()方法后影响的行数
def execute(self, sql):
self.cursor.execute(sql)
rowcount = self.cursor.rowcount
return rowcount
# 删除并返回影响行数
def delete(self, **kwargs):
table = kwargs['table']
where = kwargs['where']
sql = 'DELETE FROM %s where %s' % (table, where)
print(sql)
try:
# 执行SQL语句
self.cursor.execute(sql)
# 提交到数据库执行
self.conn.commit()
# 影响的行数
rowcount = self.cursor.rowcount
except:
# 发生错误时回滚
self.conn.rollback()
return rowcount
# 新增并返回新增ID
def insert(self, **kwargs):
table = kwargs['table']
del kwargs['table']
sql = 'insert into %s(' % table
fields = ""
values = ""
for k, v in kwargs.items():
fields += "%s," % k
values += "'%s'," % v
fields = fields.rstrip(',')
values = values.rstrip(',')
sql = sql + fields + ")values(" + values + ")"
print(sql)
try:
# 执行SQL语句
self.cursor.execute(sql)
# 提交到数据库执行
self.conn.commit()
# 获取自增id
res = self.cursor.lastrowid
except:
# 发生错误时回滚
self.conn.rollback()
return res
# 修改数据并返回影响的行数
def update(self, **kwargs):
table = kwargs['table']
# del kwargs['table']
kwargs.pop('table')
where = kwargs['where']
kwargs.pop('where')
sql = 'update %s set ' % table
for k, v in kwargs.items():
sql += "%s='%s'," % (k, v)
sql = sql.rstrip(',')
sql += ' where %s' % where
print(sql)
try:
# 执行SQL语句
self.cursor.execute(sql)
# 提交到数据库执行
self.conn.commit()
# 影响的行数
rowcount = self.cursor.rowcount
except:
# 发生错误时回滚
self.conn.rollback()
return rowcount
# 查-一条条数据
def selectTopone(self, **kwargs):
table = kwargs['table']
field = 'field' in kwargs and kwargs['field'] or '*'
where = 'where' in kwargs and 'where ' + kwargs['where'] or ''
order = 'order' in kwargs and 'order by ' + kwargs['order'] or ''
sql = 'select top 1 %s from %s %s %s ' % (field, table, where, order)
print(sql)
try:
# 执行SQL语句
self.cursor.execute(sql)
# 使用 fetchone() 方法获取单条数据.
data = self.cursor.fetchone()
except:
# 发生错误时回滚
self.conn.rollback()
return data
# 查所有数据
def selectAll(self, **kwargs):
table = kwargs['table']
field = 'field' in kwargs and kwargs['field'] or '*'
where = 'where' in kwargs and 'where ' + kwargs['where'] or ''
order = 'order' in kwargs and 'order by ' + kwargs['order'] or ''
sql = 'select %s from %s %s %s ' % (field, table, where, order)
print(sql)
try:
# 执行SQL语句
self.cursor.execute(sql)
# 使用 fetchone() 方法获取单条数据.
data = self.cursor.fetchall()
except:
# 发生错误时回滚
self.conn.rollback()
return data
测试:
import pymssql
import MSSQL
conn = MSSQL.DBHelper('127.0.0.1', '1433', 'sa', '123456', 'test')
# insert测试
# cs = conn.insert(table="T1", Name="Python测试2", Sex="男")
# print(cs)
# delete 测试
# cs = conn.delete(table="T1", where="Id = 2")
# print(cs)
# update 测试
# cs = conn.update(table="T1", Name="Python测试3", Sex="man", where="Id in(1,2)")
# print(cs)
# select 测试
cs = conn.selectAll(table="T1", where="Name = 'Python测试3'")
print(cs)
结果:
以上就是操作类的内容,第一次写欢迎指正。