第十八章 Python3 操作 MySQL

本文链接:https://www.jianshu.com/p/50ba66cd335a
作者:西瓜甜

一、基本介绍

Python3 操作 MySQL 数据库 可以使用的模块是 pymysql 和 MySQLdb。

这个两个模块都是通过自己的 API 执行原生的 SQL 语句实现的。

MySQLdb 是最早出现的一个操作 MySQL 数据库的模块，核心由C语言编写，接口精炼，性能最棒，缺点是环境依赖较多，安装稍复杂，特别是 windows 中不好安装。更新较慢。

pymysql 为替代 MySQLdb 而生，纯 Python 实现，API 的接口与 MySQLdb 完全兼容，安装方便。

二、 安装包 pymysql

1 方案一 pymsql

pymsql是Python中操作MySQL的模块

shell> pip3 install pymysql

基本使用

import pymysql

# 获取连接到 DB 的对象
db = pymysql.connect(host='172.16.153.10',
                       port=3306,
                       user='root',
                       passwd='123', 
                       db='shark_db',
# 获取游标，之后使用游标操作表
cursor = conn.cursor()

2 方案2 mysqlclient

安装 mysqlclient

适用于 Centos7

安装系统依赖库和编译器

yum   install   mariadb-devel   gcc   

• 如果是系统自带的 python2环境，安装 Python2 依赖库

python-devel

• 如果是 python3环境，编译安装 python3 后就不用再操作了

安装 mysqlclient

pip   install   mysqlclient

基本使用

import MySQLdb

# 获取连接到 DB 的对象
db=MySQLdb.connect(host='172.16.153.10',passwd="moonpie",db="thangs")

# 获取游标，之后使用游标操作表
c=db.cursor()

三、 基本操作

1 创建表

import pymysql
  
# 创建连接
conn = pymysql.connect(host='172.16.153.10',
                       port=3306,
                       user='root',
                       passwd='123', 
                       db='shark_db',
                       charset='utf8mb4')
# 获取游标对象
cursor = conn.cursor()

# 定义 sql 语句
create_table_sql = """create table t1
 (id int auto_increment primary key, 
  name varchar(10) not null, 
  age int not null)"""

# 执行 sql 语句
cursor.execute(create_table_sql)
  
# 提交更改
conn.commit()

# 关闭游标对象
cursor.close()

# 关闭连接对象
conn.close()

2 插入数据

一次插入一条数据

一次插入一条数据, 并且使用变量占位符
insert_data_sql = "insert into t1(name, age) values(%s, %s);"
row = cursor.execute(insert_data_sql, ('shark', 18))

conn.commit()
cursor.close()
conn.close()

一次插入多条数据

定义插入数据的语句
many_sql = "insert into t1 (name, age) values(%s, %s)"

一次插入多条数据
row = cursor.executemany(many_sql, [('shark1', 18),('xiguatian', 20),('qf', 8)])

conn.commit()
cursor.close()
conn.close()

3 查询数据

3.1 获取到的数据是元组类型

定义一个查询语句
query_sql = "select id,name,age from t1 where name=%s;"

执行查询语句，并且返回得到结果的行数
row_nums = cursor.execute(query_sql, ('shark1'))

"""
获取到数据结果集具有迭代器的特性:
1. 可以通过索引取值，可以切片
2. 结果集中的数据每次取出一条就少一条
"""

获取结果集中的第一条数据， 注意不是整个表的第一条数据
one_data = cursor.fetchone()

获取结果集中接下来的第 2 条和 第 3 条 数据
many_data = cursor.fetchmany(2)

获取结果集中剩余的全部数据 
all_data = cursor.fetchall()

cursor.close()
conn.close()
print(row_nums)
print(one_data)
print(many_data)
print(all_data)

3.2 获取到的数据是字典类型的

游标设置为字典类型
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

query_sql = "select id,name,age from t1 where name=%s;"

row_nums = cursor.execute(query_sql, ('shark1'))

获取结果的操作和之前的一样
result = cursor.fetchone()
conn.commit()
cursor.close()
conn.close()

print(result)

4. 操作存储过程(扩展自修)

无参数存储过程

cursor.callproc('p1')  #等价于cursor.execute("call p1()")

有参存储过程

cursor.callproc('p2', args=(1, 22, 3, 4))

#获取执行完存储的参数,参数@开头
cursor.execute("select @p2,@_p2_1,@_p2_2,@_p2_3")  #{'@_p2_1': 22, '@p2': None, '@_p2_2': 103, '@_p2_3': 24}


row_1 = cursor.fetchone()