pymysql操作数据库

参考：https://www.cnblogs.com/xfxing/p/9322199.html

安装pymysql

pip install pymysql -i http://pypi.douban.com/simple/ --trusted-host pypi.douban.com

db.py

# coding=utf-8

import pymysql
from apscheduler.schedulers.background import BackgroundScheduler


class CDb():
    def __init__(self):
        self.mHost = "localhost"
        self.mUser = "pch"
        self.mPwd = "pch2015"
        self.mDB = "fishdb"
        self.mTimer = BackgroundScheduler()
        self.mTableUserInfo = "user_info"
        self.mTableItemInfo = "item_info"

    def start(self):
        self.mConn = pymysql.connect(host=self.mHost,
                                     port=3306,
                                     user=self.mUser,
                                     passwd=self.mPwd,
                                     db=self.mDB)
        self.mCursor = self.mConn.cursor()

    def dropTables(self):
        self.mCursor.execute(
            "drop table if exists {};".format(self.mTableItemInfo))
        self.mCursor.execute(
            "drop table if exists {};".format(self.mTableUserInfo))

    def creatTableUserInfo(self):
        sql = '''create table if not exists {}(
        user_id int auto_increment comment '编号id',
        user_name varchar(40) default '空' comment '名字',
        age int default 10 comment '玩家年纪',
        money double default 2.1 comment '存款',
        avatar mediumblob not null,
        primary key (user_id)
        )engine=innodb  default charset=utf8mb4  collate=utf8mb4_unicode_ci  
        row_format=compact auto_increment=190 comment="玩家信息表";'''.format(
            self.mTableUserInfo)

        self.mCursor.execute(sql)
        # self.commit()
        print("createTableUserInfo: {}".format(self.mTableUserInfo))

    def createTableItemInfo(self):
        sql = '''create table if not exists {}(
        item_id  int auto_increment comment '道具编号',
        item_name varchar(40)  default '空' comment '道具名称',
        price     int default 0 comment  '价格',
        user_id  int default 0 comment '玩家编号id',
        primary key (item_id),
        constraint fk_user_id foreign key(user_id) references user_info(user_id)
        )engine=innodb  default charset=utf8mb4  collate=utf8mb4_unicode_ci  
        row_format=compact comment="玩家信息表";'''.format(self.mTableItemInfo)
        self.mCursor.execute(sql)
        print("createTableItemInfo: {}".format(self.mTableItemInfo))
        # self.commit()

    def insert(self, uid, age):
        sql = '''insert into {} (user_name,age,avatar) values ("ys{}",{},"");
        '''.format(self.mTableUserInfo, uid, age)
        self.mCursor.execute(sql)
        # print("insert: {},  {}, {},  {}".format(uid, age, result, sql))

    def insertMany(self):
        users = [("用户1", 8),
                 ("用户2", 83),
                 ("用户3", 32),
                 (83838, 9)]

        sql = '''insert into {} (user_name,age,avatar) values (%s,%s,"");'''.format(
            self.mTableUserInfo)

        self.mCursor.executemany(sql, users)

        items = [
            ("武器1", 192),
            ("武器2", 192),
            ("武器3", 193),
        ]

        sql = '''insert into {} (item_name,user_id) values (%s,%s);'''.format(
            (self.mTableItemInfo))
        self.mCursor.executemany(sql, items)

    def updateName(self):
        sql = '''update {} set user_name="update更改名字" where user_name="用户3";'''.format(
            self.mTableUserInfo)
        self.mCursor.execute(sql)

    def keepHeartBeat(self):  # NOTE: 每2小时向mysql数据库发一次'select 1',因为mysql超过8小时没操作，会断开
        self.mCursor.execute("select 1")
        allRecords = self.mCursor.fetchall()
        self.mConn.commit()
        print("keepHeartBeat:allRecords = {}".format(allRecords))

        one = self.mCursor.fetchone()
        print("one = {}".format(one))
        self.mConn.commit()

    def selectSome(self):
        sql = '''select * from {} where user_id >= 192 and user_id <198 order by user_id desc;'''.format(
            self.mTableUserInfo)
        self.mCursor.execute(sql)
        some = self.mCursor.fetchall()
        print("selectSome:some = {}".format(some))
        for i in range(0, len(some)):
            print("selectSome:[{}]={}".format(i, some[i]))

    def selectOne(self):
        sql = '''select user_name from {} where user_id=197;'''.format(
            self.mTableUserInfo)
        self.mCursor.execute(sql)
        # some = self.mCursor.fetchall()
        # print("selectOne:some {}".format(some))
        one = self.mCursor.fetchone()
        print("selectOne:one {}".format(one))

    def deleteOne(self):
        sql = '''delete from {} where user_id=195;'''.format(
            self.mTableUserInfo)
        self.mCursor.execute(sql)
        self.commit()

    def commit(self):
        self.mConn.commit()

    def close(self):
        self.mCursor.close()
        self.mConn.close()

test.db.py：

'''
Description: 
Author: pdh
Date: 2020-11-05 19:50:14
LastEditors: pdh
LastEditTime: 2020-11-08 20:19:46
FilePath: \pysql\test_db.py
'''

# coding=utf-8

import utils.db
import threading


class CWorker(threading.Thread):

    def __init__(self):
        threading.Thread.__init__(self)

    def run(self):
        db = utils.db.CDb()
        db.start()
        db.dropTables()
        db.creatTableUserInfo()
        db.createTableItemInfo()
        # db.commit()

        # 一次插入多条记录
        db.insertMany()
        db.commit()

        # 插入记录
        # for id in range(73, 88):
        #     db.insert(id, random.randint(8, 98))
        #     # time.sleep(1)
        # db.commit()

        # 获取多条记录
        # db.selectSome()

        # 获取一条记录的名字
        # db.selectOne()

        # 删除一条记录
        # db.deleteOne()

        # 心跳
        # db.keepHeartBeat()


if __name__ == "__main__":
    worker = CWorker()
    worker.start()

    while True:
        line = input("输入：")
        if line == 'q':
            break

    print("main:end")