关于第四节课，Python操作MYSQL的学习和应用实践作业。
内容大纲：
——主要为简单的数据增删改查。
1.定义类，创建数据库链接方法、operateOne/insertMore/selectOne/selectAll方法、数据库关闭方法。
2.定义表中插入单条/多条数据（实例化类，调用insertMore/operateOne）
3.定义表中删除单条/多条数据（实例化类，调用operateOne）
4.定义表中修改单条/多条数据（实例化类，调用operateOne）
5.定义表中查询单条/多条数据（实例化类，调用selectOne/selectAll）
加个课程地址方便自己回顾：
https://m.qlchat.com/live/channel/channelPage/840000174025863.htm
代码如下
python3.5环境，pycharm编辑器
- 定义类，创建数据库链接方法、operateOne/insertMore/selectOne/selectAll方法、数据库关闭方法。
#-*-coding:utf-8-*-
import pymysql, logging, os, time
class OperationDb_interface(object):
# 创建数据库链接、创建游标
def __init__(self, host, user, passwd, db, port, charset='utf8'):
# 创建数据库链接
self.conn = pymysql.connect(
host= host,
user= user,
passwd= passwd,
db='yui_test',
port=3306,
charset = charset)
self.cur = self.conn.cursor() # 创建一个游标
# 定义单条数据操作，增删改
def operateOne(self, sql):
try:
self.cur.execute(sql) # 在游标下执行sql语句
self.conn.commit() # 提交到数据库
return True
# print("Successfully operate one data!")
except pymysql.Error as e:
print("Mysql Error %d: %s" % (e.args[0], e.args[1]))
# print("error_info: %s" % e)
# 保存在当前.py文件所在目录下
filedate = time.strftime("%Y%m%d%H:%M:%S_", time.localtime()).replace(':','') # 获取当前日期和时间，文件名不能包含冒号"："，去除冒号
logging.basicConfig(filename= os.path.join(os.getcwd(), './' + filedate + 'log.txt'),
level = logging.DEBUG,
format='%(asctime)s %(filename)s[line:%(lineno)d] %(levelname)s %(message)s')
logger = logging.getLogger(__name__)
logger.exception(e)
return False
# 定义表中插入多条数据
def insertMore(self, condition, args):
try:
self.cur.executemany(condition, args) # 在游标下插入多条数据
self.conn.commit()
return True
# print("Successfully insert more data!")
except pymysql.Error as e:
results = "SQL10001" #数据库执行错误
print("Mysql Error %d: %s" % (e.args[0], e.args[1]))
# print("error_info: %s" %e)
# 保存在当前.py文件所在目录下
filedate = time.strftime("%Y%m%d%H:%M:%S_", time.localtime()).replace(':','') # 获取当前日期和时间，文件名不能包含冒号"："，去除冒号
logging.basicConfig(filename= os.path.join(os.getcwd(), './' + filedate + 'log.txt'),
level = logging.DEBUG,
format='%(asctime)s %(filename)s[line:%(lineno)d] %(levelname)s %(message)s')
logger = logging.getLogger(__name__)
logger.exception(e)
return False
# 查询表中单条数据
def selectOne(self, condition):
try:
self.cur.execute(condition) # 在游标下获取一条数据
results = self.cur.fetchone() # 获取一条结果
except pymysql.Error as e:
results = "SQL10001" # 数据库执行错误
print("Mysql Error %d: %s" % (e.args[0], e.args[1]))
# print("error_info: %s" %e)
# 保存在当前.py文件所在目录下
filedate = time.strftime("%Y%m%d%H:%M:%S_", time.localtime()).replace(':', '') # 获取当前日期和时间，文件名不能包含冒号"："，去除冒号
logging.basicConfig(filename=os.path.join(os.getcwd(), './' + filedate + 'log.txt'),
level=logging.DEBUG,
format='%(asctime)s %(filename)s[line:%(lineno)d] %(levelname)s %(message)s')
logger = logging.getLogger(__name__)
logger.exception(e)
finally:
return results
# 查询表中多条数据
def selectAll(self, condition):
try:
self.cur.execute(condition) # 在游标下执行语句
self.cur.scroll(0, mode='absolute') # 光标回到初始位置
results = self.cur.fetchall() # 返回游标中所有结果
except pymysql.Error as e:
results = "SQL10001" # 数据库执行错误
print("Mysql Error %d: %s" % (e.args[0], e.args[1]))
# print("error_info: %s" %e)
# 保存在当前.py文件所在目录下
filedate = time.strftime("%Y%m%d%H:%M:%S_", time.localtime()).replace(':', '') # 获取当前日期和时间，文件名不能包含冒号"："，去除冒号
logging.basicConfig(filename=os.path.join(os.getcwd(), './' + filedate + 'log.txt'),
level=logging.DEBUG,
format='%(asctime)s %(filename)s[line:%(lineno)d] %(levelname)s %(message)s')
logger = logging.getLogger(__name__)
logger.exception(e)
finally:
return results
# 数据库关闭
def __del__(self):
if self.cur != None:
self.cur.close() # 关闭游标链接
if self.conn != None:
self.conn.close() # 关闭数据库链接
# 会在下面的print执行完后自动执行
- 实例化类，调用方法
# SQL Connection Config
host = 'localhost'
user = 'root'
passwd = 'root'
db = 'yui_test'
port = 3306
if __name__ == "__main__":
# 实例化类
test = OperationDb_interface(host, user, passwd, db, port)
# 1.1定义表中插入单条数据
insertOne_sql = '''insert into yui_user (name, sex, dept, birth, age)
values('小明2',1,'人事部','1989-05-06',28);'''
insertOne_result = test.operateOne(insertOne_sql)
print(insertOne_result)
# 1.2定义表中插入多条数据
data = [
('Sherry', '0'),
('Mike', '1'),
('Sunday', '0')
]
insertMore_sql = '''insert into yui_user(name, sex)
values (%s, %s);'''
insertMore_result = test.insertMore(insertMore_sql, data)
print(insertMore_result)
# 2.1定义表中删除单条数据
delOne_sql = "delete from yui_user where name ='Mike';"
delOne_result = test.operateOne(delOne_sql)
# 2.2定义表中删除多条数据
data = [
('Sherry'),
('Saturday')
]
for names in data:
delMore_sql = "delete from yui_user where name = \'%s\';" % names
delMore_result = test.operateOne(delMore_sql)
print(delMore_result)
# 3.1定义表中更新单条数据
updateOne_sql = "update yui_user set dept = '财务部' where name = 'Mike';"
updateOne_result = test.operateOne(updateOne_sql)
print(updateOne_result)
# 3.2定义表中更新多条数据
data = {
'Sherry':'技术部',
'Mike':'财务部',
'Sunday':'技术部'
}
for key in data:
updateMore_sql = "update yui_user set dept = \'%s\' where name = \'%s\';" % (data[key], key)
updateMore_result = test.operateOne(updateMore_sql)
print(updateMore_result)
# 4.1查询表中单条数据
selectOne_sql = "select * from yui_user where dept = '财务部';"
selectOne_result = test.selectOne(selectOne_sql)
print(selectOne_result)
# 4.2查询表中多条数据
# selectAll_sql = "select * from yui_user where dept = '人事部';"
selectAll_sql = "select * from yui_user;"
selectAll_result = test.selectAll(selectAll_sql)
# print(selectAll_result)
for row in selectAll_result:
pdx = str(row[0])
name = str(row[1])
sex = str(row[2])
dept = str(row[3])
birth = str(row[4])
age = str(row[5])
print(("pdx = %s, name = %s, sex = %s, dept = %s, birth = %s, age = %s") % \
(pdx, name, sex, dept, birth, age))
在实践过程中其实碰到很多很多小问题，由于没有代码基础还会犯很多基础性编程错误和理解错误。之后希望能把遇到的坑也补充写起来。
