Python操作MySQL数据库的工具--sqlalchemy

---

一、pymysql和sqlalchemy的区别

pymysql和sqlalchemy都是Python用来操作数据库的工具，两者的区别如下：

• pymysql库在学校用的较多，也称学者库；sqlalchemy基本都是在企业应用，也称企业库；
• pymysql库使用sql语句操作数据库，所以非常繁琐，要求程序员要记忆大量sql语法；而sqlalchemy是采用操作对象的方式来操作数据库，对程序员很友好，不必记忆较多sql语句用法；

综上所述，推荐大家日后在实际开发中使用sqlalchemy库。

二、sqlalchemy的详细使用

1.安装库

安装命令：pip install sqlalchemy

2.核心思想

用操作对象的方式去操作数据库。

3.整体思路

借用sqlalchemy的基本框架
使用和丰富框架 ----- 满足日常需求

4.sqlalchemy需要连接数据库

连接数据库的方法模版如下：

数据库类型+数据库驱动://用户名:密码@电脑IP:mysql端口号/自定义数据库名?charset=utf8

示例如下：

mysql+pymysql://mysql账号:mysql密码@localhost:3306/数据库名?charset=utf8

5.使用步骤

1.手动提前创建数据库

打开Navicat工具，建立好连接之后，右键点击连接名称，在下拉列表中选择【新建数据库】。

进来新建数据库页面之后，数据库名字自己随便起，注意：字符集要选择utf8mb4，排序规则要选择utf8mb4_general_ci，千万不能有错，完了点击确定即可，数据库就创建好了。

2.使用代码创建数据表

使用代码创建数据表，建议单独建个Python文件，方便一会调用，该文件只用来创建数据表，我就取名叫create_table.py，这里先附上创建数据表全部代码如下：

from sqlalchemy import Column, Integer, String, Enum
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine

# 1、找到基本框架 -- 把自定义的类变成数据表  declarative_base--声明基类
Base = declarative_base()

# 2、连接数据库
engine = create_engine(
    'mysql+pymysql://root:66666666@localhost:3306/python1?charset=utf8'
)


# 3、自定义类 -- 创建表
class User(Base):
    # 3.1 表名
    __tablename__ = '员工表'
    # 3.2 字段信息
    id = Column(Integer, primary_key=True, autoincrement=True, doc='员工ID')
    name = Column(String(20), doc='员工名字')
    age = Column(Integer, doc='员工年龄')
    sex = Column(Enum('男', '女'), doc='员工性别')
    phone = Column(String(30), doc='员工电话')

    # 3.3 字段信息初始化
    def __init__(self, id, name, age, sex, phone):
        self.id = id
        self.name = name
        self.age = age
        self.sex = sex
        self.phone = phone


# 4、表的生成
Base.metadata.create_all(engine)

需要注意的是，代码中第三大部分创建表这里需要自己根据自己的实际需求去写，如下图：

其余的所有部分都是创建数据表的标准模板，可以不做改动。

3.用代码操作数据表

用代码操作数据表，也就是通过代码对数据库中数据表内容进行增删改查等等操作，这里我也再新建一个Python文件，取名叫operation_table.py，先附上固定模版代码部分：

from create_table import User
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

# 1、连接数据库
engine = create_engine(
    'mysql+pymysql://root:66666666@localhost:3306/python1?charset=utf8'
)

# 2、创建一个会话对象 -- 第三者 -- 执行语句
# 2.1 绑定引擎
dbsession = sessionmaker(bind=engine)
# 2.2 实例化
session = dbsession()

# 3、数据增删改查

# 4、事务提交和关闭
session.commit()
session.close()

代码中第三部分数据的增删改查是根据自身需求实际对数据进行操作，其余所有部分均为固定模版，可以不做改动。

接下来说说第三部分，如何用代码对数据表中的数据做增删改查。

3.1 增加数据

A、增加单个数据：使用add()方法，示例如下所示

user = User(0, '张三', 18, '男', '1001')
session.add(user)

B、增加多个数据：使用add_all(列表)方法，示例如下所示

user1 = User(0, '张三', 20, '男', '1003')
user2 = User(0, '李四', 21, '女', '1004')
user3 = User(0, '王五', 22, '男', '1005')
user4 = User(0, '赵六', 23, '女', '1006')
user5 = User(0, '麻七', 24, '男', '1007')
session.add_all([user1, user2, user3, user4, user5])

3.2 查询数据

A、查询所有员工的信息，示例如下所示

query_obj = session.query(User).all()
for obj in query_obj:
    print(obj.id, obj.name, obj.age, obj.sex, obj.phone)

B、查询年龄在19-24的员工信息，示例如下所示

query_obj = session.query(User).filter(User.age.between(19, 24)).all()
for obj in query_obj:
    print(obj.id, obj.name, obj.age, obj.sex, obj.phone, sep=' | ')

C、查询性别为男的员工信息，示例如下所示

query_obj = session.query(User).filter(User.sex == '男').all()
for obj in query_obj:
    print(obj.id, obj.name, obj.age, obj.sex, obj.phone, sep=' | ')

D、查询性别为男的员工信息，并按年龄大小排序【升序：asc()，降序：desc()】，示例如下所示

query_obj = session.query(User).filter(User.sex == '男').order_by(User.age.asc()).all()
for obj in query_obj:
    print(obj.id, obj.name, obj.age, obj.sex, obj.phone, sep=' | ')

3.3 删除数据

A、删除员工：姓名=张三，示例如下所示

session.query(User).filter(User.name == '张三').delete()

B、删除员工：电话=1005，示例如下所示

session.query(User).filter(User.phone == 1005).delete()

C、清空员工信息表，示例如下所示

session.query(User).delete()

3.4 修改数据

A、id=18的用户姓名改成小明，示例如下所示

session.query(User).filter(User.id == 18).update({'name': '小明'})

B、id=22的用户姓名改成小红，性别改成女，电话改成0000，示例如下所示

session.query(User).filter(User.id == 22).update({'name': '小红', 'sex': '女', 'phone': '0000'})