1.pip安装模块
pip install mysqlclient
2.引入下面的类
# encoding: utf-8
# sql导出
# mysqldump -u root -p db_name > test_db.sql
import MySQLdb
class MySqlOp(object):
def __init__(self, login_msg):
self.host = login_msg[0]
self.port = login_msg[1]
self.db_name = login_msg[2]
self.user = login_msg[3]
self.passwd = login_msg[4]
self.charset = 'utf8'
# 更新
def operate(self, sql):
db = MySQLdb.connect(host=self.host, port=self.port, user=self.user, passwd=self.passwd, db=self.db_name, charset=self.charset)
cur = db.cursor()
try:
# 执行sql语句
cur.execute(sql)
cur.close()
# 提交到数据库执行
db.commit()
except Exception as e:
print(e)
cur.close()
# Rollback in case there is any error
db.rollback()
# 关闭数据库连接
db.close()
# 插入
def select(self, sql):
db = MySQLdb.connect(host=self.host, port=self.port, user=self.user, passwd=self.passwd, db=self.db_name, charset=self.charset)
cur = db.cursor()
results = None
try:
# 执行sql语句
cur.execute(sql)
# 获取所有记录列表
results = cur.fetchall()
# print(results)
except Exception as e:
print(e)
# 关闭数据库连接
db.close()
return results
# 调用存储过程
def callproc(self, sql, args=[]):
db = MySQLdb.connect(host=self.host, port=self.port, user=self.user, passwd=self.passwd, db=self.db_name, charset=self.charset)
cur = db.cursor()
results = None
try:
# 执行sql语句
cur.callproc(sql, args)
# 获取所有记录列表
results = cur.fetchall()
cur.close()
# 提交到数据库执行
db.commit()
# print(results)
except Exception as e:
print(e)
# 关闭数据库连接
cur.close()
# Rollback in case there is any error
db.rollback()
db.close()
return results
@classmethod
def int_c(cls, int_):
'''
整数处理
'''
return int_ if int_ else 'null'
@classmethod
def str_c(cls, str_):
'''
字符串处理
'''
return "'{}'".format(str_) if str_ else 'null'
3.使用
if __name__ == '__main__':
# 连接信息 [ip, 端口, 数据库名, 用户名, 密码]
login_msg = ['192.168.0.1', 3306, 'student_db', 'paisen', 'abcd1234']
msq = MySqlOp(login_msg)
# 查询
book = msq.select('''show full columns from data;''')
print(book)
# 更新
msq.operate('''insert into student (no, name) values (1, "刘华强");''')
# 调用存储过程
cover = msq.callproc('getDataCoverPath', [100,])
print(cover) 作者:什么都干的派森 https://www.bilibili.com/read/cv13130463?from=search&spm_id_from=333.337.0.0 出处:bilibili