5. 外键

MySQL

mysql> select * from student;
+----+------+---------------+--------+
| id | name | register_date | gender |
+----+------+---------------+--------+
|  1 | s2   | 2015-03-01    | F      |
+----+------+---------------+--------+
1 row in set (0.00 sec)

mysql> select * from user;
+----+------+----------+
| id | name | password |
+----+------+----------+
|  1 | rain | 12345    |
|  2 | leco | 666666   |
+----+------+----------+
2 rows in set (0.00 sec)

code

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, DATE, Enum
from sqlalchemy.orm import sessionmaker
from sqlalchemy import func

# 创建连接
engine = create_engine("mysql+pymysql://root:root@127.0.0.1/cmz", encoding='utf-8', echo=False)
Base = declarative_base()  # 生成ORM基类


# 创建表结构
class User(Base):
    __tablename__ = 'user'  # 表名
    id = Column(Integer, primary_key=True)
    name = Column(String(32))
    password = Column(String(64))


class Student(Base):
    __tablename__ = 'student'  # 表名
    id = Column(Integer, primary_key=True)  # 字段，整形，主键 column是导入的
    name = Column(String(32), nullable=False)
    register_date = 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)

# 实例与socket绑定,创建与数据库的绘画session class，注意，这里返回
# 给session的是一个class，不是实例
Session_class = sessionmaker(bind=engine)
# 生成session实例，cursor
Session = Session_class()

# s1 = Student(name="s2", register_date="2015-03-01", gender="F")
# Session.add(s1)

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

执行结果

[(<__main__.User object at 0x000002717E36D8D0>, <1 name:s2>)]
(<__main__.User object at 0x000001CF04082320>, s2)