python调用mysql数据库工具类

工具类

import MySQLdb
import json
import datetime

class CJsonEncoder(json.JSONEncoder):
	def default(self, obj):
		if isinstance(obj, datetime.datetime):
			return obj.strftime('%Y-%m-%d %H:%M:%S')
		elif isinstance(obj, datetime.date):
			return obj.strftime('%Y-%m-%d')
		else:
			return json.JSONEncoder.default(self, obj)

class SqlUtil(object):
	"""docstring for SQLUtils"""
	def __init__(self):
		conn=MySQLdb.connect(host='127.0.0.1',port=3306,user='root',passwd='root@123',db='jkTest',charset='utf8')
		self.connection = conn
		pass
	def getList(self,sql):
		connection = self.connection
		with connection.cursor() as cursor:
			cursor.execute(sql)
			self.outSql(sql)
			title = cursor.description
			result = []
			row = cursor.fetchall()
			for x in row:
				for j in x:
					item=self.putObj(x,title)
				result.append(item)
		return result

	def putObj(self,obj,title):
		row = {}
		for i in range(len(title)):
			row[title[i][0]] = obj[i]
		return row

	def exec(self,sql):
		connection =self.connection
		with connection.cursor() as cursor:
			cursor.execute(sql)

	def outSql(self,sql):
		print("================================================")
		print(sql)
		print("================================================")

调用方式

from SqlUtils import SqlUtil
sqlUtil=SqlUtil();

	time=datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
	sql='INSERT INTO t_test_msg(user_name, msg, c_time) VALUES (\'%s\', \'%s\', \'%s\')'%(user_name,msg,time)
	sqlUtil.exec(sql)

regList=sqlUtil.getList('select * from t_test_reg where 1=1 and isOpen=\'1\'')