關(guān)于第四節(jié)課，Python操作MYSQL的學(xué)習(xí)和應(yīng)用實(shí)踐作業(yè)打毛。

內(nèi)容大綱：
——主要為簡單的數(shù)據(jù)增刪改查柿赊。
1.定義類俩功，創(chuàng)建數(shù)據(jù)庫鏈接方法、operateOne/insertMore/selectOne/selectAll方法碰声、數(shù)據(jù)庫關(guān)閉方法诡蜓。
2.定義表中插入單條/多條數(shù)據(jù)（實(shí)例化類，調(diào)用insertMore/operateOne）
3.定義表中刪除單條/多條數(shù)據(jù)（實(shí)例化類胰挑，調(diào)用operateOne）
4.定義表中修改單條/多條數(shù)據(jù)（實(shí)例化類蔓罚，調(diào)用operateOne）
5.定義表中查詢單條/多條數(shù)據(jù)（實(shí)例化類，調(diào)用selectOne/selectAll）

加個課程地址方便自己回顧：
https://m.qlchat.com/live/channel/channelPage/840000174025863.htm

代碼如下
python3.5環(huán)境瞻颂，pycharm編輯器

• 定義類脚粟，創(chuàng)建數(shù)據(jù)庫鏈接方法、operateOne/insertMore/selectOne/selectAll方法蘸朋、數(shù)據(jù)庫關(guān)閉方法核无。

#-*-coding:utf-8-*-

import pymysql, logging, os, time

class OperationDb_interface(object):
    # 創(chuàng)建數(shù)據(jù)庫鏈接、創(chuàng)建游標(biāo)
    def __init__(self, host, user, passwd, db, port, charset='utf8'):
        # 創(chuàng)建數(shù)據(jù)庫鏈接
        self.conn = pymysql.connect(
                                    host= host,
                                    user= user,
                                    passwd= passwd,
                                    db='yui_test',
                                    port=3306,
                                    charset = charset)
        self.cur = self.conn.cursor() # 創(chuàng)建一個游標(biāo)

    # 定義單條數(shù)據(jù)操作藕坯，增刪改
    def operateOne(self, sql):
        try:
            self.cur.execute(sql) # 在游標(biāo)下執(zhí)行sql語句
            self.conn.commit() # 提交到數(shù)據(jù)庫
            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)
            # 保存在當(dāng)前.py文件所在目錄下
            filedate = time.strftime("%Y%m%d%H:%M:%S_", time.localtime()).replace(':','')  # 獲取當(dāng)前日期和時間团南，文件名不能包含冒號"："，去除冒號
            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

    # 定義表中插入多條數(shù)據(jù)
    def insertMore(self, condition, args):
        try:
            self.cur.executemany(condition, args) # 在游標(biāo)下插入多條數(shù)據(jù)
            self.conn.commit()
            return True
            # print("Successfully insert more data!")
        except pymysql.Error as e:
            results = "SQL10001" #數(shù)據(jù)庫執(zhí)行錯誤
            print("Mysql Error %d: %s" % (e.args[0], e.args[1]))
            # print("error_info: %s" %e)
            # 保存在當(dāng)前.py文件所在目錄下
            filedate = time.strftime("%Y%m%d%H:%M:%S_", time.localtime()).replace(':','')  # 獲取當(dāng)前日期和時間炼彪，文件名不能包含冒號"："吐根，去除冒號
            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

    # 查詢表中單條數(shù)據(jù)
    def selectOne(self, condition):
        try:
            self.cur.execute(condition) # 在游標(biāo)下獲取一條數(shù)據(jù)
            results = self.cur.fetchone() # 獲取一條結(jié)果
        except pymysql.Error as e:
            results = "SQL10001"  # 數(shù)據(jù)庫執(zhí)行錯誤
            print("Mysql Error %d: %s" % (e.args[0], e.args[1]))
            # print("error_info: %s" %e)
            # 保存在當(dāng)前.py文件所在目錄下
            filedate = time.strftime("%Y%m%d%H:%M:%S_", time.localtime()).replace(':', '')  # 獲取當(dāng)前日期和時間，文件名不能包含冒號"："辐马，去除冒號
            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

    # 查詢表中多條數(shù)據(jù)
    def selectAll(self, condition):
        try:
            self.cur.execute(condition) # 在游標(biāo)下執(zhí)行語句
            self.cur.scroll(0, mode='absolute') # 光標(biāo)回到初始位置
            results = self.cur.fetchall() # 返回游標(biāo)中所有結(jié)果
        except pymysql.Error as e:
            results = "SQL10001"  # 數(shù)據(jù)庫執(zhí)行錯誤
            print("Mysql Error %d: %s" % (e.args[0], e.args[1]))
            # print("error_info: %s" %e)
            # 保存在當(dāng)前.py文件所在目錄下
            filedate = time.strftime("%Y%m%d%H:%M:%S_", time.localtime()).replace(':', '')  # 獲取當(dāng)前日期和時間拷橘，文件名不能包含冒號"："，去除冒號
            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

    # 數(shù)據(jù)庫關(guān)閉
    def __del__(self):
        if self.cur != None:
            self.cur.close() # 關(guān)閉游標(biāo)鏈接
        if self.conn != None:
            self.conn.close() # 關(guān)閉數(shù)據(jù)庫鏈接
        # 會在下面的print執(zhí)行完后自動執(zhí)行

• 實(shí)例化類喜爷，調(diào)用方法

# SQL Connection Config
host = 'localhost'
user = 'root'
passwd = 'root'
db = 'yui_test'
port = 3306

if __name__ == "__main__":

    # 實(shí)例化類
    test = OperationDb_interface(host, user, passwd, db, port)

    # 1.1定義表中插入單條數(shù)據(jù)
    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定義表中插入多條數(shù)據(jù)
    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定義表中刪除單條數(shù)據(jù)
    delOne_sql = "delete from yui_user where name ='Mike';"
    delOne_result = test.operateOne(delOne_sql)


    # 2.2定義表中刪除多條數(shù)據(jù)
    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定義表中更新單條數(shù)據(jù)
    updateOne_sql = "update yui_user set dept = '財務(wù)部' where name = 'Mike';"
    updateOne_result = test.operateOne(updateOne_sql)
    print(updateOne_result)


    # 3.2定義表中更新多條數(shù)據(jù)
    data = {
        'Sherry':'技術(shù)部',
        'Mike':'財務(wù)部',
        'Sunday':'技術(shù)部'
    }
    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查詢表中單條數(shù)據(jù)
    selectOne_sql = "select * from yui_user where dept = '財務(wù)部';"
    selectOne_result = test.selectOne(selectOne_sql)
    print(selectOne_result)

    # 4.2查詢表中多條數(shù)據(jù)
    # 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))

在實(shí)踐過程中其實(shí)碰到很多很多小問題冗疮，由于沒有代碼基礎(chǔ)還會犯很多基礎(chǔ)性編程錯誤和理解錯誤。之后希望能把遇到的坑也補(bǔ)充寫起來檩帐。