SQLAlchemy

Python 的 ORM 框架 SQLAlchemy

Object Relation Mapping
Object - Table 通过 Object 去操纵数据表

 

新建单表 create_table.py

from sqlalchemy.ext.declarative import declarative_base  # orm 官宣基类
from sqlalchemy import Column, Integer, String  # orm 数据类型字段
from sqlalchemy import create_engine  # 创建数据库连接

# 常见ORM 模型基类
Base = declarative_base()


class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(20))


engine = create_engine('mysql+pymysql://root:@127.0.0.1:3306/day127sqlalchemy?charset=utf8')

# 去engine 数据库中创建所有继承Base 的orm 对象
Base.metadata.create_all(engine)

 

单表的增删改查 curd_one_table.py

from sqlalchemy.orm import sessionmaker

from create_table import engine, User

# crud 增删改查 : create/ retrieve/ update/ delete


# 创建会话, 打开数据库连接(通过engine)
Session = sessionmaker(engine)
# 打开会话窗口
db_session = Session()


# 增 insert into 表名(字段1, 字段2) values(值1, 值2)
user_1 = User(name='May')
db_session.add(user_1)
# 增 删 改 都要 commit
db_session.commit()
db_session.close()
# 批量增加
db_session.add_all([User(name='Sandy'), User(name='Sheldon')])
db_session.commit()
db_session.close()


# 查 select * from 表名 where ....
res1 = db_session.query(User).all()
print(res1)  # 列表套对象
# 带条件的查询 filter
res2 = db_session.query(User).filter(User.id > 2).first()
print(res2.name)
res3 = db_session.query(User).filter_by(id = 3).first()
print(res3.name)


# 改 update user set name=Ryan where id=1
res4 = db_session.query(User).filter(User.id == 1).update({'name': 'ryan'})
db_session.commit()
db_session.close()


# 删 delete from user where ...
res5 = db_session.query(User).filter(User.id == 5).delete()
db_session.commit()
db_session.close()

 

ForeignKey的创建 create_ForeignKey_table.py

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, String, Integer, ForeignKey
from sqlalchemy.orm import relationship
from sqlalchemy import create_engine

Base = declarative_base()


class School(Base):
    __tablename__ = 'school'
    id = Column(Integer, primary_key=True)
    name = Column(String(20))


class Student(Base):
    __tablename__ = 'student'
    id = Column(Integer, primary_key=True)
    name = Column(String(20))
    # 关联字段, 让school_id 和school的id 字段关联, 注意一定是foreignkey一定是表名
    school_id = Column(Integer, ForeignKey('school.id'))
    # 将school和student 创建关系, backref 是反向关联字段
    stu2sch = relationship('School', backref='sch2stu')


engine = create_engine('mysql+pymysql://root:@127.0.0.1:3306/day127sqlalchemy?charset=utf8')
Base.metadata.create_all(engine)

 

ForeignKey的增删改查 curd_foreign_key_table.py

from sqlalchemy.orm import sessionmaker

from create_ForeignKey_table import engine, School, Student

Session = sessionmaker(engine)
db_session = Session()


# 增
# 1.笨方法
school_obj = School(name='Beijing_college')
db_session.add(school_obj)
db_session.commit()
school_id = db_session.query(School).filter(School.name == 'Beijing_college').first().id
student_obj = Student(name='001', school_id=school_obj.id)
db_session.add(student_obj)
db_session.commit()
db_session.close()
# 2. 正向 relationship
student2 = Student(name='002', stu2sch=School(name='Shanghai_college'))
db_session.add(student2)
db_session.commit()
db_session.close()
# 3. 反向 relationship
school_3 = School(name='Guangzhou_college')
school_3.sch2stu = [Student(name='003'), Student(name='004')]
db_session.add(school_3)
db_session.commit()
db_session.close()


# 查
# 正向查询
# res1 = db_session.query(Student).filter(Student.stu2sch.id > 2).all()
# 报错. 类 没有 stu2sch 这个属性, 但是 实例化出来的对象可以
student_list = db_session.query(Student).all()
for student in student_list:
    print(student.id, student.name, student.stu2sch.name)
# 反向查询
school_list = db_session.query(School).all()
for school in school_list:
    print(school.sch2stu)  # [<create_ForeignKey_table.Student object at 0x0000009742531B70>]
    for student in school.sch2stu:
        print(school.name, student.name)


# 改
school_obj = db_session.query(School).filter(School.id == 3).first()
res = db_session.query(Student).filter(Student.name == '001').update({Student.school_id: school_obj.id})
db_session.commit()
student_obj = db_session.query(Student).filter(Student.name == '001').first()
print(student_obj.school_id, student_obj.stu2sch.name)  # 3 Shanghai_college
db_session.close()


# 删
sch = db_session.query(School).filter(School.id == 3).first()
db_session.query(Student).filter(Student.school_id == sch.id).delete()
db_session.commit()
db_session.close()

 

Many2Many的创建 create_M2M_table.py

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, ForeignKey
from sqlalchemy import create_engine
from sqlalchemy.orm import relationship

Base = declarative_base()


class Boy(Base):
    __tablename__ = 'boy'
    id = Column(Integer, primary_key=True)
    name = Column(String(20))
    # 创建关联
    boy2girl = relationship('Girl', secondary='hotel', backref='girl2boy')


class Girl(Base):
    __tablename__ = 'girl'
    id = Column(Integer, primary_key=True)
    name = Column(String(20))


class Hotel(Base):
    __tablename__ = 'hotel'
    id = Column(Integer, primary_key=True)
    name = Column(String(20))
    boy_id = Column(Integer, ForeignKey('boy.id'))
    girl_id = Column(Integer, ForeignKey('girl.id'))


engine = create_engine('mysql+pymysql://root:@127.0.0.1:3306/day127sqlalchemy?charset=utf8')
Base.metadata.create_all(engine)

 

Many2Many的增删改查 curd_m2m_tables.py

from sqlalchemy.orm import sessionmaker

from create_M2M_table import engine, Boy, Girl

Session = sessionmaker(engine)
db_session = Session()


# 增
# relationship 正向
boy_1 = Boy(name='Sheldon', boy2girl=[Girl(name='Amy')])  # 注意此处为列表
db_session.add(boy_1)
db_session.commit()
db_session.close()
# relationship 反向
girl_1 = Girl(name='Penny')
girl_1.girl2boy = [Boy(name='Leonard')]
db_session.add(girl_1)
db_session.commit()
db_session.close()


# 查
boy_list = db_session.query(Boy).all()
for boy in boy_list:
    for girl in boy.boy2girl:
        print(boy.name, girl.name)

 

 

更多查询操作, 待补充

from sqlalchemy.orm import sessionmaker
from sqlalchemy import and_, or_, text
from sqlalchemy.sql import func

from create_table import User, engine

Session = sessionmaker(engine)
db_session = Session()

# 1. and_, or_
res = db_session.query(User).filter(or_(User.id > 2, User.name == 'May')).all()
res2 = db_session.query(User).filter(and_(User.id>= 1, User.name == 'May')).all()
for row in res2:
    print(row.name)

# 2. 别名
res3 = db_session.query(User.name.label('username'), User.id).all()
print(res3)  # [('ryan', 1), ('May', 2), ('Sandy', 3), ('Sheldon', 4)]
for row in res3:
    print(type(row))  # <class 'sqlalchemy.util._collections.result'>
    print(row.username, row.id)

# 3. 字符串匹配方式筛选条件 并使用 order_by进行 降序, 排序
res4 = db_session.query(User).filter(text('id<:value and name=:name')).params(value=3, name='May').\
    order_by(User.id.desc()).all()

# # 4. 原生SQL查询
res5 = db_session.query(User).from_statement(text("SELECT * FROM User where name=:name")).\
    params(name='May').all()

# 5. between
res6 = db_session.query(User).filter(User.id.between(2,3)).all()

# 6. in_, ~in_取反,只能和in组合
res7 = db_session.query(User).filter(User.id.in_([3, 5, 7])).all()
res8 = db_session.query(User).filter(~User.id.in_([3, 5, 7])).all()

# 7. 子查询
res9 = db_session.query(User).filter(User.id.in_(db_session.query(User.id).filter(User.id > 2))).all()

# 8. like 通配符查询
res10 = db_session.query(User).filter(User.name.like('M%')).all()

# 9. 限制
res11 = db_session.query(User)[1:3]

# 10. 分组
# 待整理

db_session.close()