自学Python第二十八天 - python 操作MYSQL数据库

pymysql

PyMySql 是 python 操作 MYSQL 数据库最常用的库。对于 MYSQL 的安装使用不在这里记录，只学习 python 部分。

一、安装

直接使用 pip 进行安装

pip install pymysql

二、连接到数据库

导入 pymysql 包后，使用 pymsql.connect() 函数创建数据库对象。pymsql.connect() 的常用参数有：

参数	说明
host=None	数据库连接地址
user=None	数据库用户名
password=‘’	数据库用户密码
database=None	要连接的数据库名称
db=None	等同于 database
port=3306	端口号，默认为3306
charset=‘’	要连接的数据库的字符编码（可以在终端登陆mysql后使用 \s 查看）
connect_timeout=10	连接数据库的超时时间，默认为10
autocommit=True	自动提交事务
cursorclass=pymysql.cursors.DictCursor	设置返回值为字典，默认返回元组，多个数据则存放在列表中

数据库对象创建示例：

import pymysql

db = pymysql.connect(
    host="localhost", 
    port=3306,
    user='root',    #在这里输入用户名
    password='888888',     #在这里输入密码
    charset='utf8mb4' 
) #连接数据库

三、数据库对象的操作

一般使用数据库对象的相应方法进行游标操作、提交事务、回滚事务和关闭数据库连接。

方法名	说明
cursor()	获取游标对象，操作数据库，如执行DML操作，调用存储过程等
commit()	提交事务
rollback()	回滚事务
close()	关闭数据库的连接

cursor = db.cursor()	# 创建游标对象
db.commit()		# 手动提交事务
db.rollback()	# 进行回滚操作
db.close()		# 关闭数据库连接

pymysql通过游标对象进行数据库操作，需注意的是pymysql默认autocommit=False，即不自动提交事务。
在通常的查询中不提交事务也可以获取结果，但是可能会出现返回旧数据的查询结果，所以需要设置为自动提交事务或手动提交。

四、游标操作

pymysql 通过游标来进行数据库操作，游标对象的常用方法有：

方法名	说明
callproc(procname,[,parameters])	调用存储过程，需要数据库支持
close()	关闭当前游标
execute(operation,[,parameters])	执行数据库操作，sql语句或者数据库命令
executemany(operation, seq_of_params)	用于批量操作
fetchone()	获取查询结果集合中的下一条记录
fetchmany(size)	获取指定数量的记录
fetchall()	获取查询结果集合所有记录
nextset()	跳至下一个可用的数据集
arraysize	指定使用fetchmany()获取的行数，默认为1
setinputsizes(size)	设置调用execute*()方法时分配的内存区域大小
setoutputsizes(size)	设置列缓冲区大小，对大数据列尤其有用

需注意的是，获取查询结果时是对游标进行操作，例如使用 fetchone() 方法后获取一条结果记录，再次使用则获取下一条，如果更换为 fetchall() 方法，已经获取的记录也不再重复获取。

cursor.execute('show databases;')		# 执行sql语句

one = cursor.fetchone()  #获取一条数据,元组类型
print('one:',one)

many = cursor.fetchmany(3) #获取指定条数的数据，不写默认为1
print('many:',many)

all = cursor.fetchall() #获取全部数据
print('all:',all)

execute 方法也支持传入 *args 参数

sql = 'insert into test values (%s, %s, %s, %s, %s);'

cursor.exectue(sql, ('数据1', '数据2', '数据3', '数据4', '数据5'))

五、完整实例

import pymysql

db = pymysql.connect(
    host="localhost", 
    port=3306,
    user='root',    #在这里输入用户名
    password='888888',     #在这里输入密码
    charset='utf8mb4',
    database='justtest'     #指定操作的数据库
    )

cursor = db.cursor() #创建游标对象

try:

    tableName = 'user'
    sql = 'create table %s (id varchar(20) not null, name varchar(20) not null, primary key(id));'%(tableName) 
    cursor.execute(sql)     #执行sql语句，创建表

    sql = 'show tables;'
    cursor.execute(sql)
    print('显示创建的表:',cursor.fetchall())  #显示创建的表


    sql = 'desc %s;'%(tableName) 
    cursor.execute(sql)
    print('显示表结构:',cursor.fetchall())  #显示表结构

except Exception as e:
    print(e)
    db.rollback()  #回滚事务

finally:
    cursor.close() 
    db.close()  #关闭数据库连接

aiomysql

aiomysql 是基于协程的异步操作 mysql 数据库的包。

pip install aiomysql

简单异步操作

使用 aiomysql 和使用 pymysql 及其类似，区别在于所有的操作都是异步，所以需要添加 await 关键字。另外创建连接对象时，需要传入创建的事件循环对象。

import asyncio
import aiomysql

loop = asyncio.get_event_loop()

async def test_example():
    conn = await aiomysql.connect(host='127.0.0.1', port=3306,
                                  user='root', password='root', db='py_spider',
                                  loop=loop)		# 将事件循环对象传入

    cursor = await conn.cursor()
    await cursor.execute("SELECT * from ali_work")

    # 打印输出当前表中的字段信息
    print(cursor.description)
    result = await cursor.fetchall()
    print(result)
    await cursor.close()
    conn.close()

loop.run_until_complete(test_example())

连接池操作

有时候需要限制访问并发数量，使用连接池连接。

import aiomysql

# 创建数据库连接池并获取游标对象
async with aiomysql.create_pool(user='root', password='root', db='py_spider', maxsize=10) as pool:		
    async with pool.acquire() as conn:		# 连接对象
        async with conn.cursor() as cursor:		# 游标对象
            # 创建表
            create_table_sql = """
                create table car_info(
                    id int primary key auto_increment,
                    name varchar(100),
                    price varchar(100),
                    brand varchar(100),
                    altitude varchar(100),
                    breadth varchar(100),
                    length varchar(100)
                );
            """

            # 在异步代码中必须先要检查表是否存在, 直接使用if not语句无效
            check_table_query = "show tables like 'car_info'"
            result = await cursor.execute(check_table_query)  # 如果表存在返回1 不存在返回0
            if not result:
                await cursor.execute(create_table_sql)

需要注意的是，异步 mysql 中的 sql 语句 if not 是不生效的，所以判断不能使用 sql 来进行判断，而是使用 like 先查找表是否存在。

DBUtils

DBUtils 是基于多线程异步操作数据库的包，支持 mysql 等常用主流数据库。使用前需要安装

pip install DBUtils

需要注意的是，DBUtils 只是个多线程的库，并不能够直接操作数据库。例如使用 mysql 数据库，必须保证正确的安装了 pymysql 库。

使用连接池

from dbutils.pooled_db import PooledDB

pool = PooledDB(
	creator=pymysql,		# 使用连接数据库的模块
	maxconnections=6,		# 允许最大连接数，0或None表示无限制
	mincached=2,			# 初始化时，连接池中至少创建的空闲连接，0表示不创建
	maxcached=5,			# 连接池中最多闲置的连接，超出设置会销毁。0和None表示不限制
	maxshared=3,			# 连接池中最多共享的连接数量，0和None表示全部共享。注：pymysql和mysqldb等模块并不支持共享连接
	blocking=True,			# 连接池中如果没有可用的连接时，是否阻塞等待。如不等待则报错。
	maxusage=None,			# 一个连接最多被重复使用的次数，None表示无限制
	host='127.0.0.1',
	port=3306,
	user='<username>',
	password='<password>',
	database='<database>',
	charset='utf8'
)

db_cursor = pool.connection().cursor()		# 从池中获取连接和游标
# 游标的使用和使用的数据库连接模块一致。

pool.close()		# 关闭连接池