python sqlalchemy（ORM）- 01 简单使用

简介

• sqlalchemy 是python操作sql数据库的工具，是一个对象关系映射的框架；
• 在python中提供高效、高性能的数据库访问，企业级的数据持久化模式；
• pypi 地址
• sqlalchemy官网
   
• sqlalchemy架构
  
  • sql操作是独立于ORM操作的，实现对一个数据的增删改查；基于命令的；
  • ORM 基于Core 实现对象模型映射到数据库；基于状态的；
  • sqlalchemy底层封装 pymysql / psycopg2等，通过创建engine，连接数据库；

安装

• 主要版本 2.0 、1.4、1.3；
• 官方文档
• pip install sqlalchemy==1.4.49
• 查看版本 可以使用pip list/ pip freeze ; 或者ipython中sqlalchemy._version_
   

ORM简单使用

1. 声明模型类，在python项目中，需要先声明对应的模型类；
   模型类，对应数据库中的表；
   类属性，对应表字段；
   实例对象， 对应表中的一行记录；

# sqlalchemy 没有Double类型， 10种类型
from sqlalchemy import Column, Integer, Float, String, Boolean, DECIMAL, ForeignKey, Enum, Date, Time, DateTime, Text # 
from sqlalchemy.orm import declarative_base, relationship, Session, sessionmaker
# sessionmaker(engine) 返回一个Session类
from sqlalchemy import create_engine, select


# 基类
Base = declarative_base()


# 用户类
class User(Base):
    __tablename__ = "user_account"
    # id 主键
    id = Column(Integer, primary_key=True)
    # 姓名 varchar(30)
    name = Column(String(30), name="n1") # name参数 可以指定对应到表中的字段名
    # 全名 
    fullname = Column(String)
    # 枚举字段
    sex = Column(Enum("male", "female", name='sex')) # 必须给name
    # 获取传入一个枚举类 Enum(SexEnum)
    # class SexEnum(Enum):  # from enum import Enum
    
    # 模型类 间使用的关系（并非表字段）
    addresses = relationship( # 必须用引号表示类
        "Address", back_populates="user", cascade="all, delete-orphan"
    ) 
    # back_populates （双向）模型类之间反向引用的属性
    # back_ref 单向的反向引用
    # delete-orphan 子表取消关联时，删除记录
	# 打印对象时的输出 
    def __repr__(self):
        return f"User(id={self.id!r}, name={self.name!r}, fullname={self.fullname!r})"


class Address(Base):
    __tablename__ = "address"
    id = Column(Integer, primary_key=True)
    email_address = Column(String, nullable=False)
    # 外键字段
    user_id = Column(Integer, ForeignKey("user_account.id"), nullable=False)
    
    # 关系，仅供模型类使用  必用引号表示类
    user = relationship("User", back_populates="addresses")
    def __repr__(self):
        return f"Address(id={self.id!r}, email_address={self.email_address!r})"
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48

2. 创建表结构
   使用基类的metadata创建所有的表

sqlalchemy_database_uri = "postgresql://user:pw@ip:5432/xxdb"
engine = create_engine(sqlalchemy_database_uri, echo=True) # sqlalchemy 日志输出到标准输出
# 使用基类 元数据 创建表
# Base.metadata.drop_all(engine) # 删除所有已存在的表
Base.metadata.create_all(engine)
1
2
3
4
5
6

3. 添加数据

# 上下文管理器   创建session
with Session(engine) as session:
	# User 对象
    user1 = User(
        name="spongebob",
        fullname="Spongebob Squarepants",
        sex="male",
        # 一对多关系， 所以给列表，插入用户时，连带插入地址
        addresses=[Address(email_address="spongebob@sqlalchemy.org")],
    )
    user2 = User(
        name="sandy",
        fullname="Sandy Cheeks",
        sex="female", # 枚举
        addresses=[ # 模型类之间的关系
            Address(email_address="sandy@sqlalchemy.org"),
            Address(email_address="sandy@squirrelpower.org"),
        ],
    )
    # 仅仅赋值字段，不处理关系
    user3 = User(name="patrick", fullname="Patrick Star", sex="male")
    # 添加用户，同时会添加地址
    session.add_all([user1, user2, user3]) # 添加一个用add
    session.commit()
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

4. 简单查询

from sqlalchemy import select

# 创建会话，建议使用上下文管理器 操作
session = Session(engine)

# 查询声明
# select * from user_t where name in ("spongebob", "sandy");
stmt = select(User).where(User.name.in_(["spongebob", "sandy"]))
# 查询某些字段
# stmt = select(User.name, User.sex).where(User.id==1)  # > 1
# 执行声明
scalar_result = session.scalars(stmt)
# 
scalar_result.one()  # 获取一条数据（仅有一条时）
scalar_result.one_or_none() # 获取一条（仅有一条时）
scalar_result.all()
scalar_result.fetchall() # 生成器，只能获取一次
# 也可以遍历获取数据
for user in scalar_result:
    print(user)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

也可会话查询
filter() 复杂条件；
filter_by(field=xx) 等值条件过滤；
limit() 限制行数
order_by() 根据指定条件进行排序
group_by() 根据指定条件进行分组

# 获取一个对象
user = session.query(User).filter_by(name="jack").first() # 根据name字段查询
# first() 返回一个对象
# all() 返回对象列表

# 查询某些字段
user = session.query(User.name, User.sex).filter_by(id=1).all()
# 返回 具体字段值元组 列表 [('jack', 'male')] 
1
2
3
4
5
6
7
8

5. 更新

# 查询对象
user = session.query(User).filter_by(id=1).one_or_none()
# 更新对象的sex
user.sex = "female"

session.commit()
1
2
3
4
5
6

6. 删除记录

# 查询对象
# user = session.get(User, ident=2) 根据id查询
user = session.query(User).filter_by(id=1).one_or_none()
# 会话删除对象
session.delete(user)
# 提交
session.commit()
1
2
3
4
5
6
7

7. 聚合查询

from sqlalchemy import func, and_, or_  # 与条件  或条件

# 查询 id > 1 的所有记录的 id列，并计数
count_num = session.query(func.count(User.id)).filter(User.id > 1).first()
# (5,)
1
2
3
4
5

8. 多条件查询

from sqlalchemy import or_, and_
# 查询 id>3 且name 以2结尾的用户    % 多个字符  _ 一个字符
user = session.query(User).filter(User.id > 3, User.name.like("%2")).all()
# [lucy2]

# 查询id>4 或者name 以1结尾的用户
user = session.query(User).filter(or_(User.id > 4, User.name.like("%1"))).all()
# [jack1, lucy1, jack3, lucy3]

# 分页
user = session.query(User).filter(User.id > 1).offset(3).limit(3).all()

# 排序
from sqlalchemy import text
user = session.query(User).filter(User.id > 1).order_by(text("id desc")).all() # age asc 升序

# 分组 聚合
user = session.query(func.count(User.sex)).filter(User.id >= 1).group_by(text("sex")).all()
# [(3,), (3,)]
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

 

基于sql的CRUD

from sqlalchemy import create_engine
import psycopg2  # 底层基于psycopg2

sqlalchemy_database_uri = "postgresql://jack:jack@192.168.0.112:5432/jack_db"

sql = "create table if not exists stu(id int primary key , name varchar(50), age int)"

engine = create_engine(sqlalchemy_database_uri)
conn = engine.connect()
cursor = conn.exec_driver_sql(sql)


# insert_one = "insert into stu values (1, 'jack', 23), (2, 'lucy', 34)"
# conn.exec_driver_sql(insert_one)

# delete_one = "delete from stu where id = 2"
# conn.exec_driver_sql(delete_one)

update_one = "update stu  set age=35 where id=1;"
conn.exec_driver_sql(update_one)

select_one = "select * from stu"
cursor = conn.exec_driver_sql(select_one)
print(cursor.fetchall())

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

 
 
下一篇：python sqlalchemy（ORM）- 02 表关系