import sqlalchemy

from sqlalchemy import create_engine

from sqlalchemy.ext.declarative import declarative_base

from sqlalchemy import Column, Integer, String

from sqlalchemy.orm import sessionmaker

from sqlalchemy import func

# 连接方式 mysql+pymysql 用户名 root 密码 123456 主机 localhost 库 testdb encoding 字符集

engine = create_engine("mysql+pymysql://root:123456@localhost/testdb",encoding='utf-8', echo=True)

Base = declarative_base()  # 生成orm基类

class User(Base):

    __tablename__ = 'user'  # 表名

    id = Column(Integer, primary_key=True)

    name = Column(String(32))

    password = Column(String(64))

    def __repr__(self):

        return "<%s name:%s>" % (self.id,self.name)

class Student(Base):

    __tablename__ = 'student'  # 表名

    id = Column(Integer,primary_key=True)

    name = Column(String(32),nullable=False)

    register_data = Column(DATE,nullable=False)

    gender=Column(String(32),nullable=False)

    def __repr__(self):

        return "<%s name:%s>" % (self.id,self.name)

Base.metadata.create_all(engine)  # 创建表结构

Session_class = sessionmaker(bind=engine)  # 创建与数据库的会话session class ,注意,这里返回给session的是个class,不是实例

Session = Session_class()  # 生成session实例 cursor

print(Session.query(User,Student).filter(User.id==Student.id).all())

Session.query(User).join(Student).all()

Session.query(User).join(Student,isouter=True).all()

import sqlalchemy

from sqlalchemy import create_engine

from sqlalchemy.ext.declarative import declarative_base

from sqlalchemy import Column, Integer, String,ForeignKey,DATE

from sqlalchemy.orm import sessionmaker

from sqlalchemy import func

# 连接方式 mysql+pymysql 用户名 root 密码 123456 主机 localhost 库 testdb encoding 字符集

engine = create_engine("mysql+pymysql://root:@localhost/test",encoding='utf-8', echo=True)

Base = declarative_base()  # 生成orm基类

class Student(Base):

    __tablename__='student'

    id = Column(Integer,primary_key=True)

    name=Column(String(32),nullable=False)

    register_date=Column(DATE,nullable=False)

    def __repr__(self):

        return "<%s name:%s>" % (self.id,self.name)

class StudyRecord(Base):

    __tablename__='study_record'

    id = Column(Integer,primary_key=True)

    day = Column(Integer,nullable=False)

    status = Column(String(32),nullable=False)

    stu_id = Column(Integer,ForeignKey('student.id'))

    def __repr__(self):

        return "<%s name:%s>" % (self.id,self.day)

Base.metadata.create_all(engine)  # 创建表结构

Session_class = sessionmaker(bind=engine)

Session = Session_class()  # 生成session实例 cursor

s1 = Student(name='Alex',register_date='2017-10-24')

s2 = Student(name='jack',register_date='2017-10-24')

s3 = Student(name='rain',register_date='2017-10-24')

s4 = Student(name='Lily',register_date='2017-10-24')

s5 = Student(name='Lucy',register_date='2017-10-24')

study_obj1 = StudyRecord(day=1,status='yes',stu_id=1)

study_obj2 = StudyRecord(day=2,status='yes',stu_id=1)

study_obj3 = StudyRecord(day=3,status='yes',stu_id=1)

study_obj4 = StudyRecord(day=1,status='yes',stu_id=2)

study_obj5 = StudyRecord(day=2,status='yes',stu_id=2)

Session.add_all([s1,s2,s3,s4,s5,study_obj1,study_obj2,study_obj3,study_obj4,study_obj5])

Session.commit()  # 现此才统一提交，创建数据

class StudyRecord(Base):

    __tablename__='study_record'

    id = Column(Integer,primary_key=True)

    day = Column(Integer,nullable=False)

    status = Column(String(32),nullable=False)

    stu_id = Column(Integer,ForeignKey('student.id'))

    student = relationship('Student',backref='my_classes') #指定关联的表 Student， backref

    def __repr__(self):

        return "<%s day:%s status:%s>" % (self.student.name,self.day,self.status)

Base.metadata.create_all(engine)  # 创建表结构

Session_class = sessionmaker(bind=engine)
Session = Session_class()  # 生成session实例 cursor

Stu_obj = Session.query(Student).filter(Student.name=='alex').first()
print(Stu_obj.my_classes)