python 封装sql_python sqlserver的封装

from do_config import cfg #将数据库的信息写到配置文件读取

import pymssql

class HandleSqlSever:

def __init__(self):

self.conn = pymssql.connect(host = cfg.get_values('sql_server','host'),

user = cfg.get_values('sql_server','user'),

password = cfg.get_values('sql_server','password'),

database = cfg.get_values('sql_server','database'),

charset = cfg.get_values('sql_server','charset'),

autocommit=False

)

self.cur = self.conn.cursor()

def query_sql(self,sql,args=None,is_all=False):

"""

查询sql

:param sql:

:param args:

:param is_all:

:return:

"""

self.cur.execute(sql,args)

if is_all:

return self.cur.fetchall()

else:

return self.cur.fetchone()

def insert_update_delete(self,sql,val):

"""

增删改

:param sql:

:return:

"""

try:

self.cur.execute(sql,val)

except Exception as e:

print("执行增删改{}有错，错误是{}，需要回滚".format(val,e))

self.conn.rollback()

# 增删改操作有误时回滚操作

else:

self.conn.commit()

print("事务提交成功")

def close_mysql(self):

self.cur.close()

self.conn.close()

if __name__ == '__main__':

data =HandleSqlSever()

#sql ="""select testId from T_Test where sourceName='江苏省人民医院'"""

#result = data.query_sql(sql,is_all=True)

ts = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime())

sql = "INSERT INTO T_PA_UserGroupMap (userId, groupId,spId,creatorId,createdAt) VALUES (%s, %s,21,100664773,%s)"

val = ("100668149", 355, ts)

data.insert_update_delete(sql,val)

data.close_mysql()