sqlalchemy的foreignkey操作

创表操作
create_table_foreignkey.py

#一对多建表操作
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine
from sqlalchemy import Column, Integer, String, ForeignKey
from sqlalchemy.orm import relationship


Base = declarative_base()

class Student(Base):
    __tablename__ = 'student'
    id = Column(Integer, primary_key=True)
    name = Column(String(32))
    school_id = Column(Integer, ForeignKey('school.id'))
    stu2sch = relationship('School', backref='sch2stu')

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

engine = create_engine("mysql+pymysql://root:kkd*33251.@127.0.0.1:3306/db103?charset=utf8")

Base.metadata.create_all(engine)

添加数据

# 添加数据
from sqlalchemy.orm import sessionmaker
from create_table_foreignkey import engine, Student, School

Session = sessionmaker(engine)
db_session = Session()

#1、添加数据 ------原始方法
# sch_obj = School(name='ajimide')
# db_session.add(sch_obj)# 执行数据库操作必须写此步
# db_session.commit()
#
# sch = db_session.query(School).filter(School.name == 'ajimide').first()
# stu_obj = Student(name='赵日天', school_id=sch.id)
# db_session.add(stu_obj)
# db_session.commit()
# db_session.close()

#2、基于relationship 反向sch ---》 stu
# sch_obj = School(name="daersasi")
# sch_obj.sch2stu = [Student(name='谢天笑'), Student(name='李志')]
# db_session.add(sch_obj)
# db_session.commit()
# db_session.close()

#3、基于relationship 正向stu ---》 sch
stu_obj = Student(name='芳华', stu2sch=School(name='北京电影学院'))
db_session.add(stu_obj)
db_session.commit()
db_session.close()

查询操作

# 查询数据
from sqlalchemy.orm import sessionmaker
from create_table_foreignkey import engine, Student, School

Session = sessionmaker(engine)
db_session = Session()

# 1、查询数据 (正向查询，从有foreignkey表查到主表)
stu = db_session.query(Student).all()
for row in stu:
    print(row.id, row.name, row.school_id, row.stu2sch.name)

# 2、查询数据（反向查询，从主表查从表）
sch = db_session.query(School).all()
for school in sch:
    for student in school.sch2stu:
        print(school.id, school.name, student.name)**

修改和删除数据

# 查询数据
from sqlalchemy.orm import sessionmaker
from create_table_foreignkey import engine, Student, School

Session = sessionmaker(engine)
db_session = Session()

# 1、修改数据
sch = db_session.query(School).filter(School.name=='ajimide').first()
db_session.query(Student).filter(Student.name=='李志').update({ 
   'school_id': sch.id})
db_session.commit()
db_session.close()
# 2、删除数据
stu = db_session.query(School).filter(School.name=='ajimide').first()
db_session.query(Student).filter(Student.school_id==sch.id).delete()
db_session.commit()
db_session.close()

查询操作的进阶
and_ or_ 查询

from sqlalchemy.sql import and_, or_
#and操作
ret = db_session.query(Student).filter(and_(Student.id == 3, Student.name=='李志')).all()
或者
ret = db_session.query(Student).filter_by(id == 3, name=='李志').all()
#or操作
ret = db_session.query(Student).filter(or_(Student.id == 3, Student.name=='李志')).all()

给查询字段自定义名称

#查询数据 指定查询数据列 加入别名
ret = db_session.query(Student.id.label('uid'), Student.name.label('username')).first()

字符串匹配防暑筛选添加 并使用order_by进行排序

#字符串匹配防暑筛选添加 并使用order_by进行排序
from sqlalchemy.sql import text
db_session.query(Student).filter(text("id=:value and name=:name")).params(value=3, name='李志').first()

排序

#排序 ASC--正序 desc--倒序
res = db_session.query(Student).order_by(Student.id.desc()).all()
for row in res:
    print(row.id, row.name)