mysql_util.py
# -*- coding:utf-8 -*-
import os
import sys
import pymysql
class MySQLConnect(object):
""" mysqldb 操作类"""
def __init__(self, params):
""" 数据库初始化 """
self.host = str(params.get("host", ""))
self.username = str(params.get("username", ""))
self.password = str(params.get("password", ""))
self.dbname = str(params.get("dbname", ""))
self.port = str(params.get("port", ""))
def connect(self):
""" 链接数据库 """
try:
self.conn = pymysql.connect(host=self.host, user=self.username, passwd=self.password, db=self.dbname, port=int(self.port), charset='utf8', connect_timeout=1000)
except Exception as e:
print("conn mysql error: %s" % e)
self.cursor = self.conn.cursor() # 使用cursor方法获取操作游标
def close(self):
""" 关闭数据库 """
self.cursor.close()
self.conn.close()
def fetchall(self, sql):
""" 用于查询返回所有结果 """
results = []
try:
self.connect()
self.cursor.execute(sql)
results = self.cursor.fetchall()
except Exception as e:
print("mysql selct all error: %s" % e)
return results
def fetchone(self, sql):
""" 查询一条结果 """
try:
self.connect()
self.cursor.execute(sql)
result = self.cursor.fetchone()
except Exception as e:
self.rollback()
print("mysql select one error: %s" % e)
return result
def execute(self, sql):
""" 进行修改,插入,更新基本操作 """
try:
self.connect()
self.cursor.execute(sql)
effectRow = self.cursor.rowcount
return effectRow
except Exception as e:
self.rollback()
return ("mysql insert error: %s" % e)
def commit(self):
""" 事务提交操作 """
try:
self.conn.commit()
return ''
except Exception as e:
self.rollback()
return ("mysql commit error: %s" % e)
def rollback(self):
""" 事务回滚操作 """
self.conn.rollback()
#插入 更新
def test_execute():
sql = """CREATE TABLE `non_weekday_info` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`year` varchar(256) DEFAULT NULL,
`month` varchar(256) DEFAULT NULL,
`holiday` date DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=469 DEFAULT CHARSET=utf8
"""
params = {"host": "xxxx", "username": "root", "password": "xxxx", "dbname": "external_system", "port": "3306"}
myconn=MySQLConnect(params)
myconn.execute(sql)
print(myconn.commit())
myconn.close()
def test_execute1():
sql = """INSERT INTO `external_system`.`non_weekday_info`(`id`, `year`, `month`, `holiday`) VALUES (3, NULL, NULL, NULL)
"""
update_sql = '''
UPDATE `external_system`.`non_weekday_info` SET `year` = NULL, `month` = 1321141, `holiday` = NULL WHERE `id` = 1
'''
params = {"host": "xxxx", "username": "root", "password": "xxxx", "dbname": "external_system", "port": "3306"}
myconn=MySQLConnect(params)
print(myconn.execute(update_sql))
print(myconn.commit())
myconn.close()
def test_fetchone():
sql = """select * from non_weekday_info
"""
params = {"host": "xxxx", "username": "root", "password": "xxxx", "dbname": "external_system", "port": "3306"}
myconn=MySQLConnect(params)
result=myconn.fetchone(sql)
print(result,type(result))
def test_fetchall():
sql = """select * from non_weekday_info
"""
myconn=MySQLConnect(params)
result=myconn.fetchall(sql)
print(result,type(result))
if __name__ == '__main__':
params = {"host": "xxxx", "username": "root", "password": "xxxx", "dbname": "external_system", "port": "3306"}
# test_execute() 创建表
# test_execute1() 插入更新
# test_fetchone() 单条查询的方法
# test_fetchall()
# 使用变量向SQL语句中传递参数