SQLAlchemy 数据库操作

参考：space.bilibili.com/34615745618…

SQLAlchemy 特点

• 使用 Python 编写的 ORM 框架
• 支持 MS SQL、MySQL、Oracle、PGSQL、Sybase

下载

pip install sqlalchemy

连接 MySQL & 查询

import sqlalchemy

# 创建引擎 echo=True，打印数据库执行日志
# engine = sqlalchemy.create_engine('mysql+pymysql://root:root@localhost:3306/python_test', echo=True)
engine = sqlalchemy.create_engine('mysql+pymysql://root:root@localhost:3306/python_test')
# 创建链接
conn = engine.connect()

# 脚本创建查询
query_01 = sqlalchemy.text("select * from user")
result_set = conn.execute(query_01)

for row in result_set:
    print(row)

conn.close()
engine.dispose()

查询记录

select * from student

from db_init import engine, tb_person

with engine.connect() as conn:
    query = tb_person.select()
    result_set = conn.execute(query)

    # 结果集操作 1
    for row in result_set:
        print(row[0])
        print(row.name)

    #  操作 2 ，返回 list
    result = result_set.fetchall()

    # 只提取一行 （一般操作只有一个结果的集合）
    row = result_set.fetchone()

select * from student where ... and ...

from db_init import engine, tb_student
from sqlalchemy.sql import and_, or_

with engine.connect() as conn:
    # 简单条件查询
    # query = tb_student.select().where(tb_student.columns.birthday >= "2000-1-2").where(tb_student.columns.id < 4)

    # 复杂条件查询
    query = tb_student.select().where(
        and_(
            tb_student.columns.birthday >= "2000-1-2",
            tb_student.columns.id < 4
        )
    )
    result_set = conn.execute(query)

    result = result_set.fetchall()
    print(result)

面向数据表操作

创建表和增加记录

使用 meta_data.create_all(engine) 创建表，不常用

import sqlalchemy

engine = sqlalchemy.create_engine("mysql+pymysql://root:root@localhost:3306/python_test", echo=True)
meta_data = sqlalchemy.MetaData()

tb_person = sqlalchemy.Table(
    'person', meta_data,
    sqlalchemy.Column("id", sqlalchemy.Integer, primary_key=True),
    sqlalchemy.Column("name", sqlalchemy.String(128), unique=False, nullable=False),
    sqlalchemy.Column("birthday", sqlalchemy.Date, nullable=False),
)

tb_student = sqlalchemy.Table(
    'student', meta_data,
    sqlalchemy.Column("id", sqlalchemy.Integer, primary_key=True),
    sqlalchemy.Column("name", sqlalchemy.String(128), unique=False, nullable=False),
    sqlalchemy.Column("birthday", sqlalchemy.Date, nullable=False),
    sqlalchemy.Column("address", sqlalchemy.String(256)),
)

meta_data.create_all(engine)

插入记录

from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String, Date

engine = create_engine("mysql+pymysql://root:root@localhost:3306/python_test", echo=True)
meta = MetaData()

# 编程式建表
student = Table(
    'student', meta,
    Column("id", Integer, primary_key=True),
    Column("name", String(128), unique=False, nullable=False),
    Column("birthday", Date, nullable=False),
)

meta.create_all(engine)

# 编程式插入
insert = student.insert()
print(insert)

# insert one record

# insert_tom = insert.values(name="Tom", birthday="2000-1-1")
# with engine.connect() as conn:
#     result = conn.execute(insert_tom)
#     print(result.inserted_primary_key)
#     conn.commit()

# insert multiple records
with engine.connect() as conn:
    conn.execute(insert, [
        {"name": "Jack", "birthday": "2000-1-2"},
        {"name": "Mary", "birthday": "2000-1-3"},
        {"name": "Smith", "birthday": "2000-1-4"},
    ])
    conn.commit()

更新与删除

update

from db_init import engine, tb_student

with engine.connect() as conn:

    # demo-1 update all
    # update_query = tb_student.update().values(address="wuhan")

    # demo-2 update by
    update_query = tb_student.update().values(address="shanghai").where(tb_student.columns.id == 3)

    conn.execute(update_query)
    conn.commit()

delete

from db_init import engine, tb_student

with engine.connect() as conn:

    # demo-1 delete all
    # delete_query = tb_student.delete()

    # demo-2 update by
    delete_query = tb_student.delete().where(tb_student.columns.id == 7)

    conn.execute(delete_query)
    conn.commit()

关联表定义

db_init.py

import sqlalchemy

engine = sqlalchemy.create_engine("mysql+pymysql://root:root@localhost:3306/python_test", echo=True)
meta_data = sqlalchemy.MetaData()

tb_department = sqlalchemy.Table(
    "department", meta_data,
    sqlalchemy.Column("id", sqlalchemy.Integer, primary_key=True),
    sqlalchemy.Column("name", sqlalchemy.String(128))
)

tb_employee = sqlalchemy.Table(
    "employee", meta_data,
    sqlalchemy.Column("id", sqlalchemy.Integer, primary_key=True),
    sqlalchemy.Column("department_id", sqlalchemy.Integer, sqlalchemy.ForeignKey("department.id"), nullable=False),
    sqlalchemy.Column("name", sqlalchemy.String(255), nullable=False),
    sqlalchemy.Column("birthday", sqlalchemy.Date, default="2000-01-01")
)

meta_data.create_all(engine)

insert_records.py

from db_init import engine, tb_department, tb_employee

with engine.connect() as conn:
    conn.execute(tb_department.insert(), [
        {"name": "hr"},
        {"name": "it"}
    ])

    conn.execute(tb_employee.insert(), [
        {"department_id": 1, "name": "Jack"},
        {"department_id": 1, "name": "Tom"},
        {"department_id": 1, "name": "Mary"},
        {"department_id": 2, "name": "Smith"},
        {"department_id": 2, "name": "Rose"},
        {"department_id": 2, "name": "Leon"},
    ])

    conn.commit()

关联表查询

query.py

import sqlalchemy

from db_init import engine, tb_employee, tb_department

with engine.connect() as conn:
    join = tb_employee.join(tb_department, tb_department.columns.id == tb_employee.columns.department_id)
    # out join
    # query = sqlalchemy.select(join).where(tb_department.columns.name == "hr")
    """ 打印 emp + dep
    (1, 1, 'Jack', datetime.date(2000, 1, 1), 1, 'hr')
    (2, 1, 'Tom', datetime.date(2000, 1, 1), 1, 'hr')
    (3, 1, 'Mary', datetime.date(2000, 1, 1), 1, 'hr')
    """

    # left join : emp left join dep
    # query = sqlalchemy.select(tb_employee).select_from(join).where(tb_department.columns.name == "hr")
    """ 只打印 emp 表信息
    (1, 1, 'Jack', datetime.date(2000, 1, 1))
    (2, 1, 'Tom', datetime.date(2000, 1, 1))
    (3, 1, 'Mary', datetime.date(2000, 1, 1))
    """

    # left join : emp left join dep
    query = sqlalchemy.select(tb_department).select_from(join).where(tb_employee.columns.name == "Rose")
    """ 只打印 dep 表信息
    (2, 'it')
    """

    result = conn.execute(query).fetchall()
    for raw in result:
        print(raw)

面向对象操作

对比两种实体映射方式

面向数据表

面向对象

映射类定义和记录添加

定义数据模型类

db_init.py

from sqlalchemy import create_engine, Column, Integer, String, Date
from sqlalchemy.orm import declarative_base, sessionmaker

engine = create_engine("mysql+pymysql://root:root@localhost:3306/python_test", echo=True)
Base = declarative_base()

class Person(Base):
    __tablename__ = "person"

    id = Column(Integer, primary_key=True)
    name = Column(String(128), nullable=False)
    birthday = Column(Date)
    address = Column(String(255), nullable=True)


Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)

insert.py

from db_init import Session, Person

session = Session()

# add a record
# p = Person(name="Amy", birthday="2000-01-01", address="wuhan")
# session.add(p)

# add a list of records
persons = [
    Person(name="Qiu", birthday="2000-01-02", address="wuhan"),
    Person(name="Liu", birthday="2000-01-03", address="wuhan"),
]
session.add_all(persons)

session.commit()

映射类查询与删除

query

from db_init import Session, Person

session = Session()

# first(): get first record
# person_first = session.query(Person).filter(Person.id == 1).first()
# person_first = session.query(Person).filter(Person.id == 100).first()
person_first = session.query(Person).filter(Person.id < 100).first()

# one(): get the only record
# person_one = session.query(Person).filter(Person.id < 100).one()  # except
person_one = session.query(Person).filter(Person.id == 1).one()

# scalar():
# person_scalar = session.query(Person).filter(Person.id < 100).scalar() # except
# person_scalar = session.query(Person).filter(Person.id == 1).scalar()
person_scalar = session.query(Person).filter(Person.id == 100).scalar()

if person_first:
    print(f"person_first    >>> name: {person_first.name}, birthday: {person_first.birthday}")

if person_one:
    print(f"person_one      >>> name: {person_one.name}, birthday: {person_one.birthday}")

if person_scalar:
    print(f"person_scalar   >>> name: {person_scalar.name}, birthday: {person_scalar.birthday}")

update

from db_init import Session, Person

session = Session()

# demo_1 single-update
person = session.query(Person).filter(Person.id == 1).one()
person.address = "chengdu"

# demo_2 single-update
update = session.query(Person).filter(Person.id == 1).update({
    Person.address: "shanghai"
})

# demo_3 multi-update
update = session.query(Person).filter(Person.id > 10).update({
    Person.address: "hangzhou"
})


session.commit()

新的映射方式

import datetime

from sqlalchemy import create_engine, String
from sqlalchemy.sql import func
from sqlalchemy.orm import declarative_base, sessionmaker, Mapped, mapped_column
from typing_extensions import Annotated

engine = create_engine("mysql+pymysql://root:root@localhost:3306/python_test", echo=True)
Base = declarative_base()

int_pk = Annotated[int, mapped_column(primary_key=True)]
unique = Annotated[str, mapped_column(String(128), nullable=False, unique=True)]
timestamp_default_now = Annotated[datetime.datetime, mapped_column(nullable=False, server_default=func.now())]


class Customer(Base):
    __tablename__ = "customer"

    """面向数据库操作
    id = Column(Integer, primary_key=True)
    name = Column(String(128), nullable=False)
    birthday = Column(Date)
    address = Column(String(255), nullable=True)
    """

    """面向类操作
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(128), nullable=False)
    birthday: Mapped[datetime.datetime]
    """

    """新的映射方式"""
    id: Mapped[int_pk]
    name: Mapped[unique]
    birthday: Mapped[datetime.datetime]
    city: Mapped[str] = mapped_column(String(128), nullable=True)
    create_time: Mapped[timestamp_default_now]

Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)

一对多 ORM

db_init.py

import datetime

from sqlalchemy import create_engine, String, ForeignKey
from sqlalchemy.orm import declarative_base, sessionmaker, Mapped, mapped_column, relationship
from typing_extensions import Annotated

engine = create_engine("mysql+pymysql://root:root@localhost:3306/python_test", echo=True)
Base = declarative_base()

int_pk = Annotated[int, mapped_column(primary_key=True)]
unique = Annotated[str, mapped_column(String(128), nullable=False, unique=True)]
timestamp_not_null = Annotated[datetime.datetime, mapped_column(nullable=False)]


class Department(Base):
    __tablename__ = "department"

    id: Mapped[int_pk]
    name: Mapped[unique]

    def __repr__(self):
        return f"id: {self.id}, name: {self.name}"

class Employee(Base):
    __tablename__ = "employee"

    id: Mapped[int_pk]
    dep_id: Mapped[int] = mapped_column(ForeignKey("department.id"))
    name: Mapped[unique]
    birthday: Mapped[timestamp_not_null]

    # 关联内存中属性
    # department: Mapped[Department] = relationship()
    department: Mapped[Department] = relationship(lazy=False)

    def __repr__(self):
        return f"id: {self.id}, dep_id: {self.name}, birthday: {self.birthday}"

Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)

query.py

from db_init import Session, Employee, Department

def insert_records(session):
    d1 = Department(name="hr")
    session.add(d1)
    # session.flush()

    # e1 = Employee(dep_id=d1.id, name="Jack", birthday="2000-01-01")
    e1 = Employee(department=d1, name="Jack", birthday="2000-01-01")
    session.add(e1)

    session.commit()

def select_records(session):
    emp = session.query(Employee).filter(Employee.id == 1).one()
    dep = emp.department
    print(emp)
    print(dep)


session = Session()

# insert_records(session)
if __name__ == '__main__':
    select_records(session)

不建议直接在定义中使用，但是可以了解的操作：

relationship 设置为 lazy=False

select 一次，使用了 join

relationship 默认为 lazy=True

emp 和 dep 分开select

多对多 ORM

db_init.py

import datetime

import sqlalchemy
from sqlalchemy import create_engine, String, ForeignKey
from sqlalchemy.orm import declarative_base, sessionmaker, Mapped, mapped_column, relationship
from typing_extensions import Annotated
from typing import List, Set

engine = create_engine("mysql+pymysql://root:root@localhost:3306/python_test", echo=True)
Base = declarative_base()

int_pk = Annotated[int, mapped_column(primary_key=True)]
unique = Annotated[str, mapped_column(String(128), nullable=False, unique=True)]
timestamp_not_null = Annotated[datetime.datetime, mapped_column(nullable=False)]
require_string = Annotated[str, mapped_column(String(128), nullable=False)]

# 定义关联关系
association_table = sqlalchemy.Table(
    "user_role", Base.metadata,
    sqlalchemy.Column("user_id", ForeignKey("user.id"), primary_key=True),
    sqlalchemy.Column("role_id", ForeignKey("role.id"), primary_key=True)
)


class User(Base):
    __tablename__ = "user"

    id: Mapped[int_pk]
    name: Mapped[unique]
    password: Mapped[require_string]

    roles: Mapped[Set["Role"]] = relationship(secondary=association_table, lazy=False)

    def __repr__(self):
        return f"id: {self.id}, dep_id: {self.name}"


class Role(Base):
    __tablename__ = "role"

    id: Mapped[int_pk]
    name: Mapped[unique]

    users: Mapped[Set["User"]] = relationship(secondary=association_table, lazy=True, back_populates="roles")

    def __repr__(self):
        return f"id: {self.id}, dep_id: {self.name}"


Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)

query.py

from db_init import Session, User, Role

def insert_records(s):
    role1 = Role(name="admin")
    role2 = Role(name="operator")

    user1 = User(name="Jack", password="111")
    user2 = User(name="Tom", password="222")
    user3 = User(name="Mary", password="333")

    user1.roles.add(role1)
    user1.roles.add(role2)

    user2.roles.add(role1)
    user3.roles.add(role2)

    s.add_all([user1, user2, user3])
    s.commit()

def select_user(s):
    u = s.query(User).filter(User.id == 1).one()
    print(u)
    print(u.roles)

session = Session()

if __name__ == '__main__':
    # insert_records(session)
    select_user(session)

一对一 ORM

双向一对一

插入

基于 ORM 的查询

query.py ...

from db_init import Session, User, Role
from sqlalchemy.sql import select, delete, update, insert

def execute_query(query):
    result = session.execute(query)
    for row in result:
        print(row)

def select_single_target():
    query = select(User).order_by(User.id)
    execute_query(query)

def select_multiple():
    query = select(User, Role).join(Role.id)
    execute_query(query)


session = Session()

if __name__ == '__main__':
    select_single_target()
    select_multiple()

基于 ORM 的批量增删改

增

删

改

Session 与事务控制

还没有深入了解... 后续补充