家里养了三只兔子，刚好想捯饬一下python3+mysql，于是有了以下的兔子管理系统。
目录
一、新建数据库和表
数据库名：rabbits
CREATE DATABASE `rabbits` CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';
表名:user
二、 python连接数据库
# -*- coding:utf-8 -*-
import pymysql
host = 'localhost'
prot = 3306
dbName = 'rabbits'
user = 'root'
password = '10086'
def get_connection():
conn = pymysql.connect(host=host, port=prot, db=dbName, user=user, password=password)
return conn
def check():
conn = get_connection()
# 使用 cursor() 方法创建一个 dict 格式的游标对象 cursor
cursor = conn.cursor(pymysql.cursors.DictCursor)
# 使用 execute() 方法执行 SQL 查询
cursor.execute("select count(id) as total from user")
# 使用 fetchone() 方法获取单条数据.
data = cursor.fetchone()
print(data['total'])
print("=====")
# 关闭数据库连接
cursor.close()
conn.close()
if __name__ == '__main__':
check()
运行结果，因为数据库还没有信息
如果上面的mysql账号密码是错的会提示认证错误
三、使用with 优化操作代码
从以上代码可以看到, 如果每次都要打开连接, 关闭连接 .... 代码难看且容易出错.
最好的办法是用 python with 的方式来增加一个上下文管理器. 修改如下:
# -*- coding:utf-8 -*-
import pymysql
from timeit import default_timer
host = 'localhost'
prot = 3306
dbName = 'rabbits'
user = 'root'
password = '10086'
def get_connection():
conn = pymysql.connect(host=host, port=prot, db=dbName, user=user, password=password)
return conn
class UsingMysql(object):
def __init__(self, commit=True, log_time=True, log_label='总用时'):
"""
:param commit:是否在最后提交事务(设置为false是用来单元测试的)
:param log_time:打印程序运行总时间
:param log_label:自定义log的文字
"""
self._log_time = log_time
self._commit = commit
self._log_label = log_label
def __enter__(self):
# 如果需要记录时间
if self._log_time is True:
self._start = default_timer()
# 在进入的时候自动获取连接和cursor
conn = get_connection()
cursor = conn.cursor(pymysql.cursors.DictCursor)
conn.autocommit = False
self._conn = conn
self._cursor = cursor
return self
def __exit__(self, *exc_info):
# 提交事务
if self._commit:
self._conn.commit()
# 在退出的时候自动关闭连接和cursor
self._cursor.close()
self._conn.close()
if self._log_time is True:
diff = default_timer() - self._start
print('-- %s:%.6f 秒' % (self._log_label, diff))
@property
def cursor(self):
return self._cursor
def check_it():
with UsingMysql(log_time=True) as um:
um.cursor.execute("select count(id) as total from user")
data = um.cursor.fetchone()
print("-- 当前数量为: %d " % data['total'])
def check():
conn = get_connection()
# 使用 cursor() 方法创建一个 dict 格式的游标对象 cursor
cursor = conn.cursor(pymysql.cursors.DictCursor)
# 使用 execute() 方法执行 SQL 查询
cursor.execute("select count(id) as total from user")
# 使用 fetchone() 方法获取单条数据.
data = cursor.fetchone()
print(data['total'])
print("=====")
# 关闭数据库连接
cursor.close()
conn.close()
if __name__ == '__main__':
# check()
check_it()
四、封装公用代码
现在新增一个pymysql_comm.py 类, 将连接代码和写好的UsingMysql 放进去, 代码如下:
新建一个source目录
把
都拖进去
然后pymysql_comm.py代码
# -*- conding:utf-8 -*-
import pymysql
from timeit import default_timer
host = 'localhost'
prot = 3306
dbName = 'rabbits'
user = 'root'
password = '10086'
def get_connection():
conn = pymysql.connect(host=host, port=prot, db=dbName, user=user, password=password)
return conn
class UsingMysql(object):
def __init__(self, commit=True, log_time=True, log_label='总用时'):
"""
:param commit:是否在最后提交事务(设置为false是用来单元测试的)
:param log_time:打印程序运行总时间
:param log_label:自定义log的文字
"""
self._log_time = log_time
self._commit = commit
self._log_label = log_label
def __enter__(self):
# 如果需要记录时间
if self._log_time is True:
self._start = default_timer()
# 在进入的时候自动获取连接和cursor
conn = get_connection()
cursor = conn.cursor(pymysql.cursors.DictCursor)
conn.autocommit = False
self._conn = conn
self._cursor = cursor
return self
def __exit__(self, *exc_info):
# 提交事务
if self._commit:
self._conn.commit()
# 在退出的时候自动关闭连接和cursor
self._cursor.close()
self._conn.close()
if self._log_time is True:
diff = default_timer() - self._start
print('-- %s:%.6f 秒' % (self._log_label, diff))
@property
def cursor(self):
return self._cursor
test.py代码
# -*- coding: UTF-8 -*-
from pymysql_comm import UsingMysql
def check_it():
with UsingMysql(log_time=True) as um:
um.cursor.execute("select count(id) as total from user")
data = um.cursor.fetchone()
print("-- 当前数量为: %d " % data['total'])
if __name__ == '__main__':
check_it()
后续的学习和开发都可以使用这个封装类,
用类似test.py的方式来写自己的业务代码, 更方便精简了.
五、增删改查api
5.1 新增单条记录
def select_one(cursor):
cursor.execute("select * from user")
data = cursor.fetchone()
print("--单条记录:{0}".format(data))
def create_one():
with UsingMysql(log_time=True) as um:
sql = "insert into user(name, month, color, weight) values (%s, %s, %s, %s)"
params = ('旦旦', '16', '灰白', 3.54)
um.cursor.execute(sql, params)
select_one(um.cursor)5.2 新增多条记录
def get_count(cursor):
cursor.execute("select count(id) as total from user")
# 使用 fetchone() 方法获取单条数据.
data = cursor.fetchone()
print("--当前总数量为: %d" % data['total'])
def delete_all(cursor):
cursor.execute("delete from user")
def create_many():
with UsingMysql(log_time=True) as um:
# 清空之前的测试记录
delete_all(um.cursor)
for i in range(0, 10):
sql = "insert into user(name, month, color, weight) values (%s, %s, %s, %s)"
params = ('旦旦', '16', '灰白', i)
um.cursor.execute(sql, params)
# 查看结果
get_count(um.cursor)
--当前总数量为: 10
-- 总用时:0.013897 秒
5.3 删除某条记录
def select_one_by_id(cursor, id):
sql = 'select * from user where id = %s'
params = id
cursor.execute(sql, params)
data = cursor.fetchone()
if data:
print("有id为%s的兔子" % data['id'])
return id
else:
print("没有id为%s的兔子了" % id)
def delete_one(cursor, id):
sql = 'delete from user where id = %s'
params = id
cursor.execute(sql, params)
print("--已删除id为%s的兔子信息..." % id)
def check_delete_one(id):
with UsingMysql(log_time=True) as um:
if select_one_by_id(um.cursor, id):
delete_one(um.cursor, id)
select_one_by_id(um.cursor, id)
5.4 修改记录
def update_by_id(cursor, name, id):
sql = "update user set name= '%s' where id =%d " %(name, id)
cursor.execute(sql)
print('cursor.fetchone() 是', cursor.fetchone())
return cursor.fetchone()
def check_update(new_name, id):
with UsingMysql(log_time=True) as um:
data = select_all(um.cursor)
update_by_id(um.cursor, new_name, id)
# 查看
select_one_by_id(um.cursor, id) 
5.5 查找
查找主要涉及pymysql 的fetchone(返回单条数据), fetchall(返回所有数据) . fetchone 上面已经写过了, 现在来看看fetchall 方法:
这些都比较简单, 可以自己扩展
5.6 分页查询
分页查询主要是用了mysql 的limit 特性, 和pymysql 没太大关系, 代码如下:
def fetch_page_data(cursor, weight, page_size, skip):
sql = 'select * from user where weight > %d limit %d , %d ' %(weight, skip, page_size)
# skip是第一个返回记录行的偏移量,page_size是指 指定返回记录行的最大数目
cursor.execute(sql)
data_list = cursor.fetchall()
print('--总数 : %d ' %len(data_list))
print('数据 : {0}' .format(data_list))
return data_list
def check_page():
with UsingMysql(log_time=True) as um:
page_size = 3
weight = 3
for page_no in range(1, 6):
print('==第%d页数据' % page_no)
skip = (page_no - 1) * page_size
print('skip = ', skip) # skip是页码乘以 page_sie
fetch_page_data(um.cursor, weight, page_size, skip)
六、中级篇: 使用连接池和封装方法
经过一系列示例, 现在你应该会用pymysql 做最基本的增删改查分页了. 现在来看点高级点的功能: 更好的封装代码和使用数据库连接池.
6.1 封装代码
我们发觉调用pymysql的代码都差不多, 其实可以挪到公用方法里去, 新增一个 pymysql_lib_1.py 文件, 实现UsingMysql 如下:
其实就是在pymysql_common的基础上, 封装了一系列增删改查业务的方法,把那些方法稍微改一下放进来这里就可以了
这里加自己想要增删改查
在 test.py写这个,
确实是9个
6.2 使用连接池
在上面的使用过程中, 每个请求都会开启一个数据库连接. 如果连接数太多, 数据库很快就会报错. 如何调整数据库的连接数增加并发性能算是个比较有技术含量的话题, 我打算放到高级篇里再介绍. 现在这里要让你知道的是: 数据库这么返回连接是不行的, 必须要使用连接池.
连接池代码当然不用自己动手, python的世界那么大~ 先安装DBUtils, 如下:
查看是否安装成功
#! /usr/bin/python
# -*- coding: UTF-8 -*-
"""
作者: 小肥巴巴
简书: https://www.jianshu.com/u/db796a501972
邮箱: imyunshi@163.com
github: https://github.com/xiaofeipapa/python_example
您可以任意转载, 恳请保留我作为原作者, 谢谢.
"""
import pymysql
from timeit import default_timer
from DBUtils.PooledDB import PooledDB
class DMysqlConfig:
"""
:param mincached:连接池中空闲连接的初始数量
:param maxcached:连接池中空闲连接的最大数量
:param maxshared:共享连接的最大数量
:param maxconnections:创建连接池的最大数量
:param blocking:超过最大连接数量时候的表现，为True等待连接数量下降，为false直接报错处理
:param maxusage:单个连接的最大重复使用次数
:param setsession:optional list of SQL commands that may serve to prepare
the session, e.g. ["set datestyle to ...", "set time zone ..."]
:param reset:how connections should be reset when returned to the pool
(False or None to rollback transcations started with begin(),
True to always issue a rollback for safety's sake)
:param host:数据库ip地址
:param port:数据库端口
:param db:库名
:param user:用户名
:param passwd:密码
:param charset:字符编码
"""
def __init__(self, host, db, user, password, port=3306):
self.host = host
self.port = port
self.db = db
self.user = user
self.password = password
self.charset = 'UTF8' # 不能是 utf-8
self.minCached = 10
self.maxCached = 20
self.maxShared = 10
self.maxConnection = 100
self.blocking = True
self.maxUsage = 100
self.setSession = None
self.reset = True
# ---- 用连接池来返回数据库连接
class DMysqlPoolConn:
__pool = None
def __init__(self, config):
if not self.__pool:
self.__class__.__pool = PooledDB(creator=pymysql,
maxconnections=config.maxConnection,
mincached=config.minCached,
maxcached=config.maxCached,
maxshared=config.maxShared,
blocking=config.blocking,
maxusage=config.maxUsage,
setsession=config.setSession,
charset=config.charset,
host=config.host,
port=config.port,
database=config.db,
user=config.user,
password=config.password,
)
def get_conn(self):
return self.__pool.connection()
# ========== 在程序的开始初始化一个连接池
host = 'localhost'
port = 3306
db = 'mysql_test'
user = 'mysql_test'
password = 'mysql_test'
db_config = DMysqlConfig(host, db, user, password, port)
g_pool_connection = DMysqlPoolConn(db_config)
# ---- 使用 with 的方式来优化代码
class UsingMysql(object):
def __init__(self, commit=True, log_time=True, log_label='总用时'):
"""
:param commit: 是否在最后提交事务(设置为False的时候方便单元测试)
:param log_time: 是否打印程序运行总时间
:param log_label: 自定义log的文字
"""
self._log_time = log_time
self._commit = commit
self._log_label = log_label
def __enter__(self):
# 如果需要记录时间
if self._log_time is True:
self._start = default_timer()
# 从连接池获取数据库连接
conn = g_pool_connection.get_conn()
cursor = conn.cursor(pymysql.cursors.DictCursor)
conn.autocommit = False
self._conn = conn
self._cursor = cursor
return self
def __exit__(self, *exc_info):
# 提交事务
if self._commit:
self._conn.commit()
# 在退出的时候自动关闭连接和cursor
self._cursor.close()
self._conn.close()
if self._log_time is True:
diff = default_timer() - self._start
print('-- %s: %.6f 秒' % (self._log_label, diff))
# ========= 一系列封装的业务方法
# 返回 count
def get_count(self, sql, params=None, count_key='count(id)'):
self.cursor.execute(sql, params)
data = self.cursor.fetchone()
if not data:
return 0
return data[count_key]
def fetch_one(self, sql, params=None):
self.cursor.execute(sql, params)
return self.cursor.fetchone()
def fetch_all(self, sql, params=None):
self.cursor.execute(sql, params)
return self.cursor.fetchall()
def fetch_by_pk(self, sql, pk):
self.cursor.execute(sql, (pk,))
return self.cursor.fetchall()
def update_by_pk(self, sql, params=None):
self.cursor.execute(sql, params)
@property
def cursor(self):
return self._cursor
七、最后的严肃问题: raw sql ? 使用或放弃?
从UsingMysql 可以看出代码优化到这个层面已经到头了. 可是那些什么insert 语句, update 语句还是要拼一大堆sql 字段, 怎么办? 这里有两个办法: 一个是思考一些代码生成技术, 根据各种参数自动组装sql, 这样下去这代码就会变成自己独有的orm了(年轻时我就这么干) . 另一个选择(也就是我现在的选择), 不用pymysql, 而是使用sqlalchemy .... :-D :-D :-D
我现在工作中很少用Mysql , 通常用到的时候都是接手别人的代码. 所以我一般这么做: 简单无性能瓶颈的业务代码, 我用sqlalchemy 不用动脑子. 有性能瓶颈的地方, 我用pymysql原生sql进行操作. 因为pymysql 网上很少成型的好文章, 所以我才写了这么一大坨进行总结.
7.1 sqlchemy 入门
新增一个 sqlal_comm.py 类, 代码如下:
#! /usr/bin/python
# -*- coding: UTF-8 -*-
"""
作者: 小肥巴巴
简书: https://www.jianshu.com/u/db796a501972
邮箱: imyunshi@163.com
github: https://github.com/xiaofeipapa/python_example
您可以任意转载, 恳请保留我作为原作者, 谢谢.
"""
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index, Text
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy import create_engine
from timeit import default_timer
host = 'localhost'
port = 3306
db = 'mysql_test'
user = 'mysql_test'
password = 'mysql_test'
g_mysql_url = 'mysql+pymysql://%s:%s@%s:%d/%s' % (user, password, host, port, db)
engine = create_engine(g_mysql_url)
Base = declarative_base()
class Product(Base):
__tablename__ = 'Product'
id = Column(Integer, primary_key=True, autoincrement=True)
name = Column(String(40))
remark = Column(String(1000), nullable=True)
isBuy = Column(Integer, default=1)
Base.metadata.create_all(engine) #创建表
Session = sessionmaker(bind=engine)
# =============== 以上为初始化数据库和表
# ---- 使用 with 的方式来优化代码
class UsingAlchemy(object):
def __init__(self, commit=True, log_time=True, log_label='总用时'):
"""
:param commit: 是否在最后提交事务(设置为False的时候方便单元测试)
:param log_time: 是否打印程序运行总时间
:param log_label: 自定义log的文字
"""
self._log_time = log_time
self._commit = commit
self._log_label = log_label
self._session = Session()
def __enter__(self):
# 如果需要记录时间
if self._log_time is True:
self._start = default_timer()
return self
def __exit__(self, *exc_info):
# 提交事务
if self._commit:
self._session.commit()
if self._log_time is True:
diff = default_timer() - self._start
print('-- %s: %.6f 秒' % (self._log_label, diff))
@property
def session(self):
return self._session
这个文件分为两大部分: 上部分是 sqlalchemy 的固定套路: 拼接连接字符串, 进行连接初始化, 然后初始化数据库的表. 下部分是继续之前的上下文管理套路, 让代码编写更轻松点.
新增一个test4.py 进行测试, 如下:
#! /usr/bin/python
# -*- coding: UTF-8 -*-
"""
作者: 小肥巴巴
简书: https://www.jianshu.com/u/db796a501972
邮箱: imyunshi@163.com
github: https://github.com/xiaofeipapa/python_example
您可以任意转载, 恳请保留我作为原作者, 谢谢.
"""
from sqlal_comm import Session, Product, UsingAlchemy
# 测试获取一条记录
def check_it():
session = Session()
result = session.query(Product).first()
if result is None:
session.commit()
return None
session.commit()
session.close()
print('-- 得到记录: {0}'.format(result))
# 测试获取一条记录
def check_it_2():
with UsingAlchemy() as ua:
result = ua.session.query(Product).first()
print('-- 得到记录: {0}'.format(result))
if __name__ == '__main__':
check_it()
check_it_2()这个文件用两种方式来进行调用, 显然用了上下文管理的方式会更轻松点.
7.2 sqlalchemy 条件查询和分页
有一篇博客极好, 把增删改查总结得明明白白. 所以我也偷懒了, 在最后直接放出他的文章. 我这里来补充两个他没有写的: 条件查询和分页查询.
7.3 条件查询
主要的业务场景就是: 用户传入多个参数, 要根据参数的不同构造不同的查询条件. 新增一个python文件, 如下:
#! /usr/bin/python
# -*- coding: UTF-8 -*-
"""
作者: 小肥巴巴
简书: https://www.jianshu.com/u/db796a501972
邮箱: imyunshi@163.com
github: https://github.com/xiaofeipapa/python_example
您可以任意转载, 恳请保留我作为原作者, 谢谢.
"""
from sqlal_comm import Session, Product, UsingAlchemy
is_available = 1
# 重新插入数据
def re_insert_data():
with UsingAlchemy() as ua:
# 删除所有数据
ua.session.query(Product).filter(Product.id > 0).delete()
data_list = []
for i in range(0, 10):
data = Product()
data.name = '双肩包%d' % i
data.remark = '双肩包%d' % i
data.isBuy = is_available
data_list.append(data)
# 批量增加数据
ua.session.add_all(data_list)
# 测试条件查询
def check_search(keyword):
re_insert_data()
with UsingAlchemy() as ua:
# 多条件的列表组合
query_filter = []
if keyword:
like_value = '%{}%'.format(keyword)
# 查询 name 和 remark 字段里 包含查询关键词的记录
query_filter.append(Product.name.like(like_value) | Product.remark.like(like_value))
# 增加另一个查询条件作为测试
query_filter.append(Product.isBuy == is_available)
# 查找结果
data_list = ua.session.query(Product).filter(*query_filter).all()
print('-- 记录条数: {}'.format(len(data_list)))
print('-- 该记录是: %s' % data_list[0].name)
if __name__ == '__main__':
check_search(3)
7.4 分页查找
新增一个py文件
#! /usr/bin/python
# -*- coding: UTF-8 -*-
"""
作者: 小肥巴巴
简书: https://www.jianshu.com/u/db796a501972
邮箱: imyunshi@163.com
github: https://github.com/xiaofeipapa/python_example
您可以任意转载, 恳请保留我作为原作者, 谢谢.
"""
from sqlal_comm import Session, Product, UsingAlchemy
is_available = 1
# 重新插入数据
def re_insert_data():
with UsingAlchemy() as ua:
# 删除所有数据
ua.session.query(Product).filter(Product.id > 0).delete()
data_list = []
for i in range(0, 10):
data = Product()
data.name = '双肩包%d' % i
data.remark = '双肩包%d' % i
data.isBuy = is_available
data_list.append(data)
# 批量增加数据
ua.session.add_all(data_list)
# 测试分页查找
def check_search(page_no, page_size):
re_insert_data()
with UsingAlchemy() as ua:
# 多条件的列表组合
query_filter = list()
# 增加另一个查询条件作为测试
query_filter.append(Product.isBuy == is_available)
offset = (page_no - 1) * page_size
# 查找结果
data_list = ua.session.query(Product).filter(*query_filter).limit(page_size).offset(offset).all()
print('=== 记录条数: {}'.format(len(data_list)))
for data in data_list:
print('-- 记录: ' + data.name)
if __name__ == '__main__':
page_size = 5
for page_no in range(1, 3):
check_search(page_no, page_size)
可以看到分页查找就是在获取列表之前调用limit 和 offset 方法, 也就是这句:
data_list = ua.session.query(Product).filter(*query_filter).limit(page_size).offset(offset).all()7.5 sqlalchemy 增删改查总结
这篇文章写得很好了, 看这里吧: https://www.cnblogs.com/pycode/p/mysql-orm.html
八、关于事务锁和优化并发性能的高级篇.
https://www.jianshu.com/p/f66e375bc9a4
九、兔子管理系统的初搭建
功能
1、新增兔子，需要输入兔子的名字，出生日期（动态计算当前年龄），颜色，以及体重g
2、删除兔子，根据兔子的名字 搜索出兔子的id， 再进行删除。（怕有重名的情况，以id为关键词进行删除）
3、update信息，除了年龄是自动计算的，那么需要update去更新当前显示的体重，新建一个表去记录体重的变化过程，表的内容为id 索引到 兔子id，体重为user表的每一次的weight操作, 日期为更新时间。
插入体重表，体重表再update到user表。
4、查询功能，分为兔子名字 以及查询所有的兔子（如果数据量大就做成分页查询）
步骤一：设计表
体重表：
以上步骤参考这个文章自己入门过了一遍，后面的连接池偷懒了直接复制的：
264
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
