MySQL 是目前使用最广泛的数据库之一，它有着良好的性能，能够跨平台，支持分布式，能够承受高并发。如果还没有安装 MySQL，下载地址：https://dev.mysql.com/downloads/mysql/5.7.html，安装参考：https://jingyan.baidu.com/article/fc07f989b298ca12ffe519b6.html。
Python 大致有如下 5 种方式操作 MySQL。
MySQL-python
MySQL-python 也称 MySQLdb，基于 C 库开发，曾经是一个十分流行的 MySQL 驱动，具有出色的性能，但其早已停更，仅支持 Python2，不支持 Python3，现在基本不推荐使用了，取而代之的是它的衍生版。
mysqlclient
MySQLdb 的 Fork 版本，完全兼容 MySQLdb，支持 Python3，它是 Django ORM 的依赖工具，如果你喜欢用原生 SQL 操作数据库，那么推荐使用它。
PyMySQL
PyMySQL 采用纯 Python 开发，兼容 MySQLdb，性能不如 MySQLdb，安装方便，支持 Python3。
peewee
peewee 是一个流行的 ORM 框架，实现了对象与数据库表的映射，兼容多种数据库，我们无需知道原生 SQL，只要了解面向对象的思想就可以简单、快速的操作相应数据库，支持 Python3。
SQLAlchemy
SQLAlchemy 是一个 ORM 框架，同时也支持原生 SQL，支持 Python3，它类似于 Java 的 Hibernate 框架。
因为 MySQLdb 不支持 Python3，这里我们只介绍其中后 4 中方式的使用，先使用如下建表语句创建一张简单的数据库表。
CREATE TABLE student (
id int(10) AUTO_INCREMENT PRIMARY KEY,
name varchar(255) NOT NULL,
age int(10) NOT NULL
);执行 pip install mysqlclient 进行安装，看一下具体操作。
新增
import MySQLdb
connect = MySQLdb.connect(
# 主机
host='localhost',
# 端口号
port=3306,
# 用户名
user='root',
# 密码
passwd='root',
# 数据库名称
db='test',
# 指定字符的编、解码格式
use_unicode=True,
charset='utf8')
# 先获取游标，再进行相应 SQL 操作
cursor = connect .cursor()
# 执行新增 SQL
sql = 'insert into student (name, age) values(%s,%s);'
data = [
('只因', '22'),
('你太美', '23')
]
cursor.executemany(sql, data)
# 提交
connect.commit()
# 关闭
cursor.close()
connect.close()查询
import MySQLdb
connect = MySQLdb.connect(
host='localhost',
port=3306,
user='root',
passwd='root',
db='test',
use_unicode=True,
charset='utf8')
cursor = connect.cursor()
cursor.execute('SELECT * FROM student')
print(cursor.fetchall())
cursor.close()
connect.close()cursor 查看方法
fetchone() 获取结果集的下一行
fetchmany(size) 获取结果集的下几行务
fetchall() 获取结果集中剩下的所有行
修改
import MySQLdb
connect = MySQLdb.connect(
host='localhost',
port=3306,
user='root',
passwd='root',
db='test',
use_unicode=True,
charset='utf8')
cursor = connect.cursor()
cursor.execute("UPDATE student SET name='张四' WHERE id = 1")
connect.commit()
cursor.close()
connect.close()删除
import MySQLdb
connect = MySQLdb.connect(
host='localhost',
port=3306,
user='root',
passwd='root',
db='test',
use_unicode=True,
charset='utf8')
cursor = connect.cursor()
cursor.execute("DELETE FROM student WHERE id = 1")
connect.commit()
cursor.close()
connect.close()执行 pip install pymysql 进行安装，使用方式与 mysqlclient 基本类似。
import pymysql
connect = pymysql.connect(
host='localhost',
port=3306,
user='root',
password='root',
database='test',
charset='utf8'
)
cursor = connect.cursor()
sql = 'insert into student (name, age) values(%s,%s);'
data = [
('张三', '22'),
('李四', '23')
]
cursor.executemany(sql, data)
connect.commit()
cursor.execute('SELECT * FROM student')
print(cursor.fetchall())
cursor.close()
connect.close()执行 pip install peewee 进行安装，看一下具体操作。
定义映射类
from peewee import *
# 连接数据库
db = MySQLDatabase('test',
host='localhost',
port=3306,
user='root',
passwd='root',
charset='utf8')
# 映射类
class Teacher(Model):
id = AutoField(primary_key=True)
name = CharField()
age = IntegerField()
class Meta:
database = db
# 创建表
db.create_tables([Teacher])新增
from peewee import *
db = MySQLDatabase('test',
host='localhost',
port=3306,
user='root',
passwd='root',
charset='utf8')
class Teacher(Model):
id = AutoField(primary_key=True)
name = CharField()
age = IntegerField()
class Meta:
database = db
t1 = Teacher(name='张三', age=22)
t2 = Teacher(name='李四', age=33)
t3 = Teacher(name='王五', age=33)
t1.save()
t2.save()
t3.save()查询
from peewee import *
db = MySQLDatabase('test',
host='localhost',
port=3306,
user='root',
passwd='root',
charset='utf8')
class Teacher(Model):
id = AutoField(primary_key=True)
name = CharField()
age = IntegerField()
class Meta:
database = db
# 查询单条数据
t = Teacher.get(Teacher.id == 1)
print('name：', t.name)
# 查询多条
ts = Teacher.select().where(Teacher.age == 33)
for t in ts:
print('name：', t.name)修改
from peewee import *
db = MySQLDatabase('test',
host='localhost',
port=3306,
user='root',
passwd='root',
charset='utf8')
class Teacher(Model):
id = AutoField(primary_key=True)
name = CharField()
age = IntegerField()
class Meta:
database = db
t = Teacher.update({Teacher.name: '张四'}).where(Teacher.id == 1)
t.execute()删除
from peewee import *
db = MySQLDatabase('test',
host='localhost',
port=3306,
user='root',
passwd='root',
charset='utf8')
class Teacher(Model):
id = AutoField(primary_key=True)
name = CharField()
age = IntegerField()
class Meta:
database = db
Teacher.delete().where(Teacher.id == 2).execute()执行 pip install sqlalchemy 进行安装，看一下具体操作。
定义映射类
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String
engine = create_engine('mysql+mysqldb://root:root@localhost:3306/test?charset=utf8',
# 打印执行语句
echo=True,
# 连接池大小
pool_size=10,
# 指定时间内回收连接
pool_recycle=3600)
# 映射基类
Base = declarative_base()
# 具体映射类
class Teacher(Base):
# 指定映射表名
__tablename__ = 'teacher'
# 映射字段
id = Column(Integer, primary_key=True)
name = Column(String(30))
age = Column(Integer)
# 创建表
Base.metadata.create_all(engine)新增
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String
from sqlalchemy.orm import sessionmaker
engine = create_engine('mysql+mysqldb://root:root@localhost:3306/test?charset=utf8',
# 打印执行语句
echo=True,
# 连接池大小
pool_size=10,
# 指定时间内回收连接
pool_recycle=3600)
# 映射基类
Base = declarative_base()
# 具体映射类
class Teacher(Base):
# 指定映射表名
__tablename__ = 'teacher'
# 映射字段
id = Column(Integer, primary_key=True)
name = Column(String(30))
age = Column(Integer)
Session = sessionmaker(bind=engine)
# 创建 Session 类实例
session = Session()
ls = []
t1 = Teacher(name='张三', age=22)
t2 = Teacher(name='李四', age=33)
t3 = Teacher(name='王五', age=33)
ls.append(t1)
ls.append(t2)
ls.append(t3)
session.add_all(ls)
session.commit()
session.close()查询
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String
from sqlalchemy.orm import sessionmaker
engine = create_engine('mysql+mysqldb://root:root@localhost:3306/test?charset=utf8',
# 打印执行语句
echo=True,
# 连接池大小
pool_size=10,
# 指定时间内回收连接
pool_recycle=3600)
# 映射基类
Base = declarative_base()
# 具体映射类
class Teacher(Base):
# 指定映射表名
__tablename__ = 'teacher'
# 映射字段
id = Column(Integer, primary_key=True)
name = Column(String(30))
age = Column(Integer)
Session = sessionmaker(bind=engine)
# 创建 Session 类实例
session = Session()
# 查询一条数据，filter 相当于 where 条件
t = session.query(Teacher).filter(Teacher.id==1).one()
print('---', t.name)
# 查询所有数据
ts = session.query(Teacher).filter(Teacher.age==33).all()
for t in ts:
print('===', t.name)修改
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String
from sqlalchemy.orm import sessionmaker
engine = create_engine('mysql+mysqldb://root:root@localhost:3306/test?charset=utf8',
# 打印执行语句
echo=True,
# 连接池大小
pool_size=10,
# 指定时间内回收连接
pool_recycle=3600)
# 映射基类
Base = declarative_base()
# 具体映射类
class Teacher(Base):
# 指定映射表名
__tablename__ = 'teacher'
# 映射字段
id = Column(Integer, primary_key=True)
name = Column(String(30))
age = Column(Integer)
Session = sessionmaker(bind=engine)
# 创建 Session 类实例
session = Session()
t = session.query(Teacher).filter(Teacher.id==1).one()
print('修改前名字-->', t.name)
t.name = '张四'
session.commit()
print('修改后名字-->', t.name)删除
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String
from sqlalchemy.orm import sessionmaker
engine = create_engine('mysql+mysqldb://root:root@localhost:3306/test?charset=utf8',
# 打印执行语句
echo=True,
# 连接池大小
pool_size=10,
# 指定时间内回收连接
pool_recycle=3600)
# 映射基类
Base = declarative_base()
# 具体映射类
class Teacher(Base):
# 指定映射表名
__tablename__ = 'teacher'
# 映射字段
id = Column(Integer, primary_key=True)
name = Column(String(30))
age = Column(Integer)
Session = sessionmaker(bind=engine)
# 创建 Session 类实例
session = Session()
t = session.query(Teacher).filter(Teacher.id==1).one()
session.delete(t)
session.commit()
敲字不易，如果此文章对你有帮助的话，点个赞收个藏来个关注，给作者一个鼓励。也方便你下次能够快速查找。
