我们使用 Pymysql 作为 Python 和 Mysql 的连接库。
安装 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()
