记录一下,方便以后需要是查找
# -*- coding:utf-8 -*-
#若使用pymssql中文会发生乱码,请使用以下代码:
#str.encode('latin1').decode('gbk')
#str表示乱码的中文字符串
#经测试 pymssql2.1.4+python3.5不会发生以上乱码问题
import pymssql,pymysql
class Mssql:
def __init__(self):
# 数据库连接参数
self.host = '127.0.0.1'
self.user = 'sa'
self.pwd = '123456'
self.db = 'DBName'
def getConnect(self):
if not self.db:
raise(NameError, "没有设置数据库信息")
# mysql连接字符串
# self.conn = pymysql.connect(host=self.host, port=3307, user=self.user, passwd=self.pwd, db=self.db, charset="utf8mb4")
self.conn = pymssql.connect(host=self.host, user=self.user, password=self.pwd, database=self.db, charset="utf8")
cursor = self.conn.cursor()
if not cursor:
raise(NameError, "连接数据库失败")
else:
return cursor
# 查
def Query(self, sql):
cursor = self.getConnect()
cursor.execute(sql)
resList = cursor.fetchall() # 获取查询的所有数据
# 查询完毕后必须关闭连接
self.conn.close()
return resList
# 增删改查
def Excute(self, sql):
cursor = self.getConnect()
cursor.execute(sql)
self.conn.commit()
self.conn.close()
# ms = Mssql()
# # 查
# reslist = ms.Query("select top 5 * from PubWarehouseCheckLog")
# for i in reslist:
# print(i)
# 改
# newsql = "update dbo.flow_original_batch set dealerscode='%s' where batchid='149-YLQY025960-20191009'" % u'测试'
# print(newsql)
# ms.Excute(newsql.encode('utf-8'))
查询出来的数据转字典
data = [dict(zip(resList.keys(), resList)) for resList in resList]
return data
游标转换字典方式
resList = [dict(zip([desc[0] for desc in cursor.description], result)) for result in resList]
return resList