sqlalchemy 外键

建表

from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

from sqlalchemy import Column,INT,VARCHAR,ForeignKey

from sqlalchemy.orm import relationship

class student(Base):

    __tablename__='student'

    id=Column(INT,primary_key=True)

    name=Column(VARCHAR(64))

    school_id=Column(INT,ForeignKey('school.id'))

    #stu2sch=relationship('school',backref='sch2stu') #本条在采用relationship插入数据是写入

class school(Base):

    __tablename__='school'

    id=Column(INT,primary_key=True)

    name = Column(VARCHAR(64))

from sqlalchemy import  create_engine

engine=create_engine('mysql+pymysql://root:941015@192.168.50.67:3306/sqlarchm?charset=utf8')

Base.metadata.create_all(engine)

# Base.metadata.drop_all(engine)

插入数据：

from sqlalchemy.orm import  sessionmaker

from create_table_foreignkey import engine,school,student

Session=sessionmaker(engine)

db_session=Session()

#笨方法

# sch_obj=school(name='dongnanya')

# db_session.add(sch_obj)

# db_session.commit()

#

# sch_obj=db_session.query(school).filter(school.name=='dongnanya').first()

# student_obj=student(name='chaochao',school_id=sch_obj.id)

# db_session.add(student_obj)

# db_session.commit()

# db_session.close()

#利用relationship -正向

# stu_obj = student(name='LUCIFER',stu2sch=school(name='dongbeiya'))

#

# db_session.add(stu_obj)

# db_session.commit()

# db_session.close()

#利用relationship -反向

sch_obj=school(name='xxy')

sch_obj.sch2stu=[student(name='beibei'),student(name='huahua')]

db_session.add(sch_obj)

db_session.commit()

查：

from sqlalchemy.orm import sessionmaker

from create_table_foreignkey import engine,student,school

Session=sessionmaker(engine)

db_session=Session()

#查询 笨

# sch_obj=db_session.query(school).filter(school.name=='dongbeiya').first()

#

# stu_obj=db_session.query(student).filter(student.school_id==sch_obj.id).first()

#

# print(stu_obj.name,sch_obj.name)

#relationship 正向

# stu_obj=db_session.query(student).filter(student.name=='LUCIFER').first()

# print(stu_obj.stu2sch.name,stu_obj.name)

#relationship 反向

sch_obj=db_session.query(school).all()

for row in sch_obj:

    for stu in row.sch2stu:

        print(row.name,stu.name)

删除和更新没有简单操作

相关文章