#!/usr/bin/env python
#-*- coding:utf-8 -*-
'''
@Time : 2020/4/27
@Author : Frank Su
@File : sql_sqlserver.py
'''
# -*- coding:utf-8 -*-
import pymssql
class MSSQL:
def __init__(self,host,user,pwd,db):
self.host = host
self.user = user
self.pwd = pwd
self.db = db
def __GetConnect(self):
if not self.db:
raise(NameError,"没有设置数据库信息")
self.conn = pymssql.connect(host=self.host,user=self.user,password=self.pwd,database=self.db,charset="utf8")
cur = self.conn.cursor()
if not cur:
raise(NameError,"连接数据库失败")
else:
return cur
# 执行查询语句
def ExecQuery(self,sql):
cur = self.__GetConnect()
cur.execute(sql)
resList = cur.fetchall()
#查询完毕后必须关闭连接
self.conn.close()
return resList
# 执行非查询语句
def ExecNonQuery(self,sql):
cur = self.__GetConnect()
cur.execute(sql)
self.conn.commit()
self.conn.close()
# 进行数据插入
def insert(self, tablename, params):
"""创建数据库表
args:
tablename :表名字
key :属性键
value :属性值
"""
key = []
value = []
for tmpkey, tmpvalue in params.items():
key.append(tmpkey)
if isinstance(tmpvalue, str):
value.append("\'" + tmpvalue + "\'")
else:
value.append(tmpvalue)
try:
attrs_sql = '(' + ','.join(key) + ')'
values_sql = ' values(' + ','.join(value) + ')'
except Exception as e:
None
# print(e)
#print("****************************")
sql = 'insert into %s'%tablename
sql = sql + attrs_sql + values_sql
print('_insert:'+sql)
try:
self.ExecNonQuery(sql)
except Exception as e:
None
# print(e)
if __name__ == "__main__":
ms = MSSQL(host="127.0.0.1",user="sa",pwd="suming",db="sqlTest")
reslist = ms.ExecQuery("select * from student")
for i in reslist:
print(i)
newsql="update student set name='%s' where id=1"%u'苏铭'
print(newsql)
ms.ExecNonQuery(newsql.encode('utf-8'))
python操作sqlserver工具类
最新推荐文章于 2024-07-25 11:34:22 发布