Python - 操作 MySQL 数据库

Python DB-API 规范

• Python 所有的数据库接口程序都在一定程度上遵守 Python DB-API 规范
• Python DB-API 是一个规范，它定义了一系列必须的对象和数据库存取方式，以便为各种各样的底层数据库系统和多种多样的数据库接口程序提供一致的访问接口
• 在没有 Python DB-API 之前，各数据库之间的应用接口非常混乱，实现各不相同
• 如果项目需要更换数据库时，则需要做大量的修改，非常不便
• Python DB-API 的出现就是为了解决这样的问题
• 由于 Python DB-API 为不同的数据库提供了一致的访问接口， 在不同的数据库之间移植代码成为一件轻松的事

什么是 PyMySQL？

PyMySQL 是在 Python3.x 版本中用于连接 MySQL 服务器的一个库，Python2 中则使用 mysqldb

安装

pip3 install PyMySQL

完整的简单小栗子

#!usr/bin/env python
# -*- coding:utf-8 _*-
"""
# author: 小菠萝测试笔记
# blog:  https://www.cnblogs.com/poloyy/
# time: 2021/8/12 3:06 下午
# file: 连接mysql.py
"""
import pymysql

db = pymysql.connect(
    host='localhost',
    port=3306,
    user='root',
    password='1234567890',
    db='MockServer',
    charset='utf8'
)

# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()

# 使用 execute()  方法执行 SQL 查询
cursor.execute("select * from api")

# 使用 fetchone() 方法获取单条数据.
data = cursor.fetchone()

print("data is  : %s " % data)

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

访问数据库

pymysql.connect(
    host = 'localhost',
    port = 3306,
    user = 'root',
    password = '123456',
    db ='MockServer',
    charset = 'utf8'
)

connect 方法生成一个 connect 对象, 通过这个对象来访问数据库

connect 方法的参数

参数	功能
user	访问数据库的用户
password	访问数据库的密码
host	Mysql 数据库服务所在的主机
port	Mysql 数据库服务的端口号，默认值为 3306
db	数据库名
charset	字符编码

connect 对象

• 使用 connect() 方法与数据库连接成功后，connect() 方法返回一个 connect() 对象
• 与数据库进行通信时, 向 connect 对象发送 SQL 查询命令, 并 connect 对象接收 SQL 查询结果

常用方法

方法	功能
close()	关闭数据库连接
commit()	提交当前事务
rollback()	取消当前事务
cursor()	创建一个游标对象用于执行 SQL 查询命令

cursor 对象

cursor 对象用于执行 SQL 命令和得到 SQL 查询结果 

常用方法

方法	功能
close()	关闭游标对象
execute()	执行一个数据库查询或命令
fetchone()	返回结果集的下一行
fetchall()	返回结果集中所有行

创建数据库

db = pymysql.connect(
    host='localhost',
    port=3306,
    user='root',
    password='1234567890',
    db='MockServer',
    charset='utf8'
)

# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()

sql = """SET character_set_database=utf8;
SET character_set_server=utf8;
DROP DATABASE IF EXISTS school;
CREATE DATABASE school;
USE school;"""
lists = sql.split("\n")
for i in lists:
    cursor.execute(i)

create_sql = """
CREATE TABLE students(
    sno VARCHAR(32),
    name VARCHAR(32),
    age INT
);
"""
cursor.execute(create_sql)
insert_sql = """
INSERT INTO students(sno, name, age) VALUES ('1', '张三', '20');
"""
cursor.execute(insert_sql)
db.commit()
db.close()

查询数据

db = pymysql.connect(
    host='localhost',
    port=3306,
    user='root',
    password='1234567890',
    db='school',
    charset='utf8'
)

# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()

fetchall

sql = "select * from students;"

rows = cursor.execute(sql)

# 记录数
print("there are %d students" % rows)

# 可迭代对象
students = cursor.fetchall()

# 循环输出
for i in students:
    print(i)


# 输出结果
there are 1 students
('1', '张三', 20)

fetchone

# 查询数据 - fetchone
sql = "select * from students;"

rows = cursor.execute(sql)

# 根据记录数循环
for i in range(rows):
    student = cursor.fetchone()
    print(student)


# 输出结果
('1', '张三', 20)

fetchmany

可以自定义返回多少条记录数

 

# 查询数据 - fetchmany
sql = "select * from students;"

rows = cursor.execute(sql)

# 可迭代对象
students = cursor.fetchmany(3)

# 循环结果集
for i in students:
    print(i)


# 输出结果
('100', '小菠萝', 24)

增加数据

db = pymysql.connect(
    host='localhost',
    port=3306,
    user='root',
    password='1234567890',
    db='school',
    charset='utf8'
)

# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()

# 增加数据
sno = 100
name = "小菠萝"
age = 24

sql = 'insert into students(sno,name,age) VALUES("%s", "%s", %d)' % (sno, name, age)

# 执行 insert sql
rows = cursor.execute(sql)

# 查看 insert 语句返回结果，其实就是执行成功了多少条数据
print('Insert %d students' % rows)

# 只有调用了 commit 方法才能将数据落盘，即提交 insert 操作
db.commit()


# 输出结果
Insert 1 students

修改数据

db = pymysql.connect(
    host='localhost',
    port=3306,
    user='root',
    password='1234567890',
    db='school',
    charset='utf8'
)

# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()

# 更新数据
sno = 10
name = "小菠萝"
age = 44

sql = 'UPDATE students SET name="%s", age=%d WHERE sno="%s"' % (name, age, sno)

# 执行 update sql
rows = cursor.execute(sql)

# 返回成功修改记录的条数
print('update %d students' % rows)

# 调用 commit，才会将 update 操作提交
db.commit()


# 输出结果
update 1 students

删除数据

db = pymysql.connect(
    host='localhost',
    port=3306,
    user='root',
    password='1234567890',
    db='school',
    charset='utf8'
)

# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()

# 更新数据
sno = 10
name = "小菠萝"
age = 44

sql = 'DELETE FROM students'

# 执行 delete sql
rows = cursor.execute(sql)

# 返回成功修改记录的条数
print('delete %d students' % rows)

# 调用 commit，才会将 delete 操作提交
db.commit()


# 输出结果
delete 2 students