Python_操作mysql，实现基础增删改查

关于第四节课，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))

在实践过程中其实碰到很多很多小问题，由于没有代码基础还会犯很多基础性编程错误和理解错误。之后希望能把遇到的坑也补充写起来。