SqlAlchemy@2021-01-22

一对多

from sqlalchemy import create_engine,Column,Integer,String,Date,DateTime,func,ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship

class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(100))
    create_time = Column(DateTime, default = datetime.now)

class Book(Base):
    __tablename__ = 'book'
    id = Column(Integer, primary_key = True, autoincrement = True)
    price = Column(Integer)
    uid = Column(Integer, ForeignKey('user.id'))
    auther = relationship('User', backref = 'books')

user = User(name = 'wst001')
book1 = Book(price = '1', uid = 1)
book2 = Book(price = '2', uid = 1)

user.books = [book1, book2]

session.add(user)
session.commit()

特别注意的地方是 backref ,以及下面关系用 user.books = [], 只需要把user 对象添加到session .

一对一

from sqlalchemy import create_engine,Column,Integer,String,Date,DateTime,func,ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship, backref

class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(100))
    create_time = Column(DateTime, default = datetime.now)

class Book(Base):
    __tablename__ = 'book'
    id = Column(Integer, primary_key = True, autoincrement = True)
    price = Column(Integer)
    uid = Column(Integer, ForeignKey('user.id'))
    auther = relationship('User', backref = backref('books', uselist = False))

user = User(name = 'wst001')
book1 = Book(price = '1', uid = 1)

user.books = book1

session.add(user)
session.commit()

一对一需要注意的是 要在orm 里引入backref 模块，在relation参数里调用backref函数， 传入参数uselist = False

多对多的关系：

1. 多对多的关系需要通过一张中间表来绑定他们之间的关系。
2. 先把两个需要做多对多的模型定义出来
3. 使用Table定义一个中间表，中间表一般就是包含两个模型的外键字段就可以了，并且让他们两个来作为一个“复合主键”。
4. 在两个需要做多对多的模型中随便选择一个模型，定义一个relationship属性，来绑定三者之间的关系，在使用relationship的时候，需要传入一个secondary=中间表。

from sqlalchemy import create_engine,Column,Integer,String,Date,DateTime,func,ForeignKey, Table
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship, backref
from datetime import datetime
import time

article_tag = Table(
    "article_tag",
    Base.metadata,
    Column("article_id",Integer,ForeignKey("article.id"),primary_key=True),
    Column("tag_id",Integer,ForeignKey("tag.id"),primary_key=True)
)

class Article(Base):
    __tablename__ = 'article'
    id = Column(Integer,primary_key=True,autoincrement=True)
    title = Column(String(50),nullable=False)

    # tags = relationship("Tag",backref="articles",secondary=article_tag)

    def __repr__(self):
        return "<Article(title:%s)>" % self.title

class Tag(Base):
    __tablename__ = 'tag'
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(50), nullable=False)

    articles = relationship("Article",backref="tags",secondary=article_tag)

    def __repr__(self):
        return "<Tag(name:%s)>" % self.name

Base.metadata.drop_all()
Base.metadata.create_all()

article1 = Article(title = 'python')
article2 = Article(title = 'java')

tag1 = Tag(name = 'tag1')
tag2 = Tag(name = 'tag2')

article1.tags.append(tag1)
article1.tags.append(tag2)

article2.tags.append(tag1)
article2.tags.append(tag2)

session.add_all([article1, article2])
session.commit()