操作 MySQL

一、基本介绍

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

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

MySQLdb 是最早出现的一个操作 MySQL 数据库的模块，核心由C语言编写，接口精炼，性能最棒，缺点是环境依赖较多，安装稍复杂，近两年已停止更新，且只支持Python2.x，不支持Python3.x。

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

2020 已经离我们很近了，所以，我们这里只聊 pymysql

二、 安装包 pymysql

pymsql是Python中操作MySQL的模块(Linux 机器)

shell> pip3 install pymysql

三、 基本操作

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. 查询数据

a. 获取到的数据是元组类型

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

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

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

获取数据中的第一条 10元
one_data = cursor.fetchone()

获取数据中的指定数量的条目  20元
many_data = cursor.fetchmany(2)

获取数据中剩余的全部数据  70元
all_data = cursor.fetchall()

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

b. 获取到的数据是字典类型的

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

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

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

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

print(result)

四、应该加入异常处理

import pymysql

# 创建连接
conn = pymysql.connect(host='172.16.153.10',
                       port=3306,
                       user='root',
                       passwd='123', 
                       db='shark_db')

# 创建游标
cursor = conn.cursor()

try:
    cursor.executemany("INSERT INTO para5(name,age) VALUES(%s,%s);", [('次牛444', '12'), ("次牛2", '11'), ('次牛3', '10')])
    conn.commit()
except Exception as e:
    # 如果执行sql语句出现问题，则执行回滚操作
    conn.rollback()
    print(e)
finally:
    # 不论try中的代码是否抛出异常，
    # 这里都会执行关闭游标和数据库连接
    cursor.close()
    conn.close()

五、 获取新增数据的自增 ID

# 先连接
cursor = conn.cursor()
cursor.executemany("insert into student(name,age, phone)values(%s,%s, %s)",
                   [ ("superman5110",18, '13295686769')]   )
conn.commit()
cursor.close()
conn.close()

# 获取最新自增ID
new_id = cursor.lastrowid

6. 操作存储过程(扩展)

无参数存储过程

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()