<启发式自动化办公>之Python操作Mysql数据库

·  阅读 99
<启发式自动化办公>之Python操作Mysql数据库

我们使用 Pymysql 作为 PythonMysql 的连接库。

安装 PyMysql

pip install PyMysql
复制代码

默认安装最新版本即可。

连接 Mysql 数据库

初始化连接

# -*- coding: utf-8 -*-
import pymysql

dbinfo = {
    "host": "localhost",  # mysql主机
    "user": "root",  # 用户名
    "password": "",  # 密码
    "db": "hello_life"  # 数据库
}


def init():
    db = pymysql.connect(**dbinfo)
    return db
复制代码

插入数据

Quotations 表添加一些数据

# -*- coding: utf-8 -*-
import pymysql
import time

dbinfo = {
    "host": "127.0.0.1",  # mysql主机
    "user": "root",  # 用户名
    "password": "",  # 密码
    "db": "hello_life"  # 数据库
}


def init_connect():
    db = pymysql.connect(**dbinfo)
    return db


def insert_data(db, itemList):
    cursor = db.cursor()  # 游标对象
    for item in itemList:
        sql = "insert into `Quotations` (`name`,`description`,`createDate`) values (%s,%s,%s)"
        cursor.execute(sql, (item["name"], item["description"], time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())))  # 执行sql语句
    db.commit()  # 提交事务


if __name__ == '__main__':
    db = init_connect()
    itemList = [
        {"name": "杨boss", "description": "小老弟你怎么回事儿"},
        {"name": "张老板", "description": "这谁顶得住啊"}
    ]
    insert_data(db, itemList)
复制代码

一次性插入多条数据

# -*- coding: utf-8 -*-
import pymysql
import time

dbinfo = {
    "host": "127.0.0.1",  # mysql主机
    "user": "root",  # 用户名
    "password": "",  # m密码
    "db": "hello_life"  # 数据库
}

def init_connect():
    db = pymysql.connect(**dbinfo)
    return db

def insert_many_data(db, itemList):
    cursor = db.cursor()  # 游标对象
    sql = "insert into `Quotations` (`name`,`description`,`createDate`) values (%s,%s,%s)"
    cursor.executemany(sql, itemList)  # 执行sql语句
    db.commit()  # 提交事务


if __name__ == '__main__':
    db = init_connect()
    itemList = [
        {"name": "杨boss", "description": "小老弟你怎么回事儿","createDate":time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())},
        {"name": "张老板", "description": "这谁顶得住啊","createDate":time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())}
    ]
    insert_many_data(db, [tuple(item.values()) for item in itemList])
    db.close()
复制代码

通过上面的实例我们可以知道大概的执行流程是

  • 初始化连接 Mysql
  • 生成游标对象用于执行事务操作
  • execut 方法传入的是 sql 语句
  • 提交事务并关闭连接

其中通用的做法是你只要写好 sql 语句就可以对 Mysql 起到同样的作用

诸如

insert_sql = 'INSERT INTO Quotations (name, description, createDate) VALUES ("小妮", "今天的饭还没白嫖,还不能下班", NOW());'
update_sql = "UPDATE Quotations SET description='我从来没有见过这么废物的人' WHERE name='杨老板';"
delete_sql = "DELETE FROM Quotations WHERE name='陈梦游';"
复制代码

查询数据

import pymysql

dbinfo = {
    "host": "127.0.0.1",  # mysql主机
    "user": "root",  # 用户名
    "password": "",  # m密码
    "db": "hello_life"  # 数据库
}

def init_connect():
    db = pymysql.connect(**dbinfo)
    return db

def query_data(cursor):
    sql = "select * from Quotations;"
    cursor.execute(sql)  # 执行sql语句
    datas = cursor.fetchall() # 获取全部数据
    for data in datas:
        print(data)

if __name__ == '__main__':
    db = init_connect()
    cursor = db.cursor(pymysql.cursors.DictCursor)  # 游标对象
    query_data(cursor)
    cursor.close()
    db.close()
复制代码

得到结果

(1, '小妮', '今天的饭还没白嫖,还不能下班', datetime.datetime(2021, 4, 16, 0, 0))
(3, '杨boss', '我从来没有见过这么废物的人', datetime.datetime(2021, 4, 16, 0, 0))
(5, '小妮', '现在接驳车人太多了,还不能下班', datetime.datetime(2021, 4, 16, 12, 0, 46))
(6, '杨老板', '废物', datetime.datetime(2021, 4, 16, 12, 0, 46))
(7, '陈梦游', '这都是潜在需求来的', datetime.datetime(2021, 4, 16, 12, 0, 46))
(8, '陈梦游', '我跟你们两个谈一下', datetime.datetime(2021, 4, 16, 12, 0, 46))
(9, '陈梦游', '你扯淡', datetime.datetime(2021, 4, 16, 12, 0, 46))
(10, '杨老板', '别吧', datetime.datetime(2021, 4, 16, 13, 29, 2))
(11, '陈梦游', '低标准', datetime.datetime(2021, 4, 16, 13, 29, 28))
(12, '杨boss', '小老弟你怎么回事儿', datetime.datetime(2021, 4, 16, 14, 26, 47))
(13, '张老板', '这谁顶得住啊', datetime.datetime(2021, 4, 16, 14, 26, 47))
复制代码

可以看到它直接返回了一个元组对象, 这和我们预想的结果有些不同,一般情况下我们需要由键值对组成的 json 对象,也就是 Python 中的字典了。

在游标中指定 pymysql.cursors.DictCursor ,把结果输出为 dict 对象

cursor = db.cursor(pymysql.cursors.DictCursor) 
复制代码

得到结果

{'id': 1, 'name': '小妮', 'description': '今天的饭还没白嫖,还不能下班', 'createDate': datetime.datetime(2021, 4, 16, 0, 0)}
{'id': 3, 'name': '杨boss', 'description': '我从来没有见过这么废物的人', 'createDate': datetime.datetime(2021, 4, 16, 0, 0)}
{'id': 5, 'name': '小妮', 'description': '现在接驳车人太多了,还不能下班', 'createDate': datetime.datetime(2021, 4, 16, 12, 0, 46)}
{'id': 6, 'name': '杨老板', 'description': '废物', 'createDate': datetime.datetime(2021, 4, 16, 12, 0, 46)}
{'id': 7, 'name': '陈梦游', 'description': '这都是潜在需求来的', 'createDate': datetime.datetime(2021, 4, 16, 12, 0, 46)}
{'id': 8, 'name': '陈梦游', 'description': '我跟你们两个谈一下', 'createDate': datetime.datetime(2021, 4, 16, 12, 0, 46)}
{'id': 9, 'name': '陈梦游', 'description': '你扯淡', 'createDate': datetime.datetime(2021, 4, 16, 12, 0, 46)}
{'id': 10, 'name': '杨老板', 'description': '别吧', 'createDate': datetime.datetime(2021, 4, 16, 13, 29, 2)}
{'id': 11, 'name': '陈梦游', 'description': '低标准', 'createDate': datetime.datetime(2021, 4, 16, 13, 29, 28)}
{'id': 12, 'name': '杨boss', 'description': '小老弟你怎么回事儿', 'createDate': datetime.datetime(2021, 4, 16, 14, 26, 47)}
{'id': 13, 'name': '张老板', 'description': '这谁顶得住啊', 'createDate': datetime.datetime(2021, 4, 16, 14, 26, 47)}
复制代码
  • cursor.fetchall() 获取全部结果
  • cursor.fetchone() 获取符合条件的第 1 条结果
  • cursor.fetchmany(2) 获取 2 条结果

关闭游标和数据库连接释放资源

  • cursor.close()
  • db.close()

数据回滚

Python 中,我们可以通过捕捉异常来进行事务的回滚操作

import pymysql

dbinfo = {
    "host": "127.0.0.1",  # mysql主机
    "user": "root",  # 用户名
    "password": "",  # m密码
    "db": "hello_life"  # 数据库
}

def init_connect():
    db = pymysql.connect(**dbinfo)
    return db

def query_data(cursor):
    sql = "select title from Quotations;"
    try:
        cursor.execute(sql)  # 执行sql语句
        for data in cursor.fetchall(): # 获取全部数据
            print(data)
    except Exception as e:
        print(str(e))
        db.rollback()

if __name__ == '__main__':
    db = init_connect()
    cursor = db.cursor(pymysql.cursors.DictCursor)  # 游标对象
    query_data(cursor)
    cursor.close()
    db.close()
复制代码

查询一个不存在的字段,得到回滚信息

(1054, "Unknown column 'title' in 'field list'")
复制代码

多线程插入数据

# -*- coding: utf-8 -*-
"""
@Time : 2021/4/22 11:53
@Auth : wutong
@File :mysql多线程插入.py
@IDE :PyCharm
"""
import time
import pymysql
import threading

"""尝试使用多线程插入 1000000 条数据到 Mysql"""


class Db(object):
    def __init__(self, host=None, username=None, pwd=None, dbname=None):
        self.pool = {}
        self.host = host
        self.username = username
        self.pwd = pwd
        self.dbname = dbname

    def get_instance(self, ):
        name = threading.current_thread().name
        if name not in self.pool:
            conn = pymysql.connect(self.host, self.username, self.pwd, self.dbname)
            self.pool[name] = conn
        return self.pool[name]


class Test(object):
    def __init__(self):
        self.max_id = 100000
        self.start_id = 0
        self.db = Db('127.0.0.1', 'root', '', 'TT')
        self.lock = threading.Lock()
        self.main()

    def main(self):
        threads = []
        for i in range(10):
            t = threading.Thread(target=self.insert_data)
            t.start()
            threads.append(t)
        for t in threads:
            t.join()

    def insert_data(self):
        db = self.db.get_instance()
        cursor = db.cursor()
        while True:
            if self.start_id >= self.max_id:
                break
            s = self.start_id
            with self.lock:
                # print("上锁 +1000")
                self.start_id += 10000
                if self.start_id > self.max_id:
                    self.start_id = self.max_id
            e = self.start_id

            print(f"正在插入 {s}{e} 条数据")
            sql = 'insert into archives(`title`,`desc`,`createDate`,`other`) values(%s,%s,%s,%s)'
            results = [
                (f'python {i}', f'desc {i}', time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()), f'other {i * 12}')
                for i in range(s, e)]
            try:
                cursor.executemany(sql, results)
                db.commit()
                # print(threading.current_thread().name, ': ', sql, ': success')
            except:
                db.rollback()
                # print(threading.current_thread().name, ': ', sql, ':failed')
                raise


if __name__ == '__main__':
    Test()
复制代码
分类:
代码人生
标签:
收藏成功!
已添加到「」, 点击更改