Python操作SQL Server类封装

2024-03-20 08:03| 来源: 网络整理| 查看: 265

最近用到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)

结果：

以上就是操作类的内容，第一次写欢迎指正。

【本文地址】

公司简介

联系我们