SQLAlchemy定义数据表关系

一对多

采用作者和文章，一个作者可以有多篇文章，一篇文章只能有一个作者（暂不考虑合著的情况）。

一般在“一”的一方定义关系：

articles = relationship('Article',backref='author')

查询时articles属性会以列表的形式返回该作者的所有文章。

一般在“多”的一方定义外键：

author_id=Column(Integer,ForeignKey('tb_authors.id'))

创建数据时如果是从一的一方建立，在外键列有SQLAlchemy维护，不用手动添加：

author = Author(name='Jack')
author2 = Author(name='Susan')
article1 = Article(title='hello', content='world')
article2 = Article(title='hello', content='python')
article3 = Article(title='hello', content='java')

author.articles.append(article1)
author.articles.append(article2)
author2.articles.append(article3)
session.add(author)
session.add(author2)
session.commit()

此时作者数据表中的内容为：

文章数据表中的内容为：

 

完整代码如下：

rom sqlalchemy import DateTime, Table, Column, String, create_engine, Integer, MetaData, ForeignKey
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy.ext.declarative import declarative_base
from datetime import datetime

base = declarative_base()
engine = create_engine('sqlite:///demo.sqlite3')


class Author(base):
    __tablename__ = 'tb_authors'
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String, nullable=False)
    created_on = Column(DateTime, default=datetime.now)
    updated_on = Column(DateTime, default=datetime.now, onupdate=datetime.now)
    articles = relationship('Article', backref='author')

    def __repr__(self):
        return f'<Author: {self.name}>'


class Article(base):
    __tablename__ = 'tb_articles'
    id = Column(Integer, primary_key=True, autoincrement=True)
    title = Column(String, nullable=False)
    content = Column(String, nullable=False)
    author_id = Column(Integer, ForeignKey('tb_authors.id'))

    def __repr__(self):
        return f'<Article: {self.title}: {len(self.content)} letters>'


base.metadata.create_all(engine)

maker = sessionmaker(bind=engine)
session = maker()

author = Author(name='Jack')
author2 = Author(name='Susan')
article1 = Article(title='hello', content='world')
article2 = Article(title='hello', content='python')
article3 = Article(title='hello', content='java')

author.articles.append(article1)
author.articles.append(article2)
author2.articles.append(article3)
session.add(author)
session.add(author2)
session.commit()

a = session.query(Author).first()
print(a.articles)

一对一 

采用国家和首都，一个国家只能有一个首都，一个首都也只能属于一个国家。

一对一可以视为特殊的一对多。只不过在定义关系的一方（国家或首都均可，一般从国家方定义）应该明确的声明不使用集合（列表）而使用标量。

capital = relationship('Capital',backref='country',userlist=False)

 另一方依然定义外键：

country_id = Column(Integer,ForeignKey('tb_country.id'))

同样如果从国家一方建立数据，首都表中的外键列内容由SQLAlchemy维护：

co1 = Country(name='China')
city1 = Capital(name='Beijing')
co1.capital = city1
co2 = Country(name='Japan')
city2 = Capital(name='Tokyo')
co2.capital = city2
co3 = Country(name='Korea')
city3 = Capital(name='Seoul')
co3.capital = city3
session.add(co1)
session.add(co2)
session.add(co3)
session.commit()

此时国家表中的数据为：

首都表中的数据为：

 

完整代码如下：

from sqlalchemy import DateTime, Table, Column, String, create_engine, Integer, MetaData, ForeignKey
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy.ext.declarative import declarative_base
from datetime import datetime

base = declarative_base()
engine = create_engine('sqlite:///demo.sqlite3')


class Country(base):
    __tablename__ = 'tb_country'
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String, nullable=False)
    created_on = Column(DateTime, default=datetime.now)
    updated_on = Column(DateTime, default=datetime.now, onupdate=datetime.now)
    capital = relationship('Capital', backref='country', uselist=False)

    def __repr__(self):
        return f'<Country: {self.name}>'


class Capital(base):
    __tablename__ = 'tb_capital'
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String, nullable=False)
    country_id = Column(Integer, ForeignKey('tb_country.id'))
    created_on = Column(DateTime, default=datetime.now)
    updated_on = Column(DateTime, default=datetime.now, onupdate=datetime.now)

    def __repr__(self):
        return f'<Capital: {self.name}>'


base.metadata.create_all(engine)
maker = sessionmaker(bind=engine)
session = maker()

co1 = Country(name='China')
city1 = Capital(name='Beijing')
co1.capital = city1
co2 = Country(name='Japan')
city2 = Capital(name='Tokyo')
co2.capital = city2
co3 = Country(name='Korea')
city3 = Capital(name='Seoul')
co3.capital = city3
session.add(co1)
session.add(co2)
session.add(co3)
session.commit()

cs = session.query(Country).all()
for c in cs:
    print(f'{c.name}:{c.capital}')

 多对多

采用博客和标签，一篇博客可以有多个标签，一个标签也可以被多个博客所拥有。

关系型数据库描述多对多关系时必须有一张关系表。关系表一般就三列：id列，博客的id，标签的id。关系表利用SQLAlchemy提供的Table类直接创建，表中数据由SQLAlchemy自动维护：

blog_tag = Table('tb_blog_tag', base.metadata,
                 Column('id', Integer, primary_key=True, autoincrement=True),
                 Column('blog_id', Integer, ForeignKey('tb_blogs.id')),
                 Column('tag_id', Integer, ForeignKey('tb_tags.id')))

blog_tag变量引用通过Table类创建出来的名为tb_blog_tag的数据表。

在任意一方创建关系说明（博客或标签均可，一般从博客方），创建关系说明时通过secondary属性明确指定关系表的名称：

tags =relationship('Tag',backref='blogs',secondary=blog_tag)

从博客一方创建数据：

b1 = Blog(title='hello', content='world')
b2 = Blog(title='football', content='world cup')
b3 = Blog(title='cat', content='meow meow')
tag1 = Tag(name='Tech')
tag2 = Tag(name='Funny')
tag3 = Tag(name='News')
tag4 = Tag(name='Daily')
tag5 = Tag(name='Pets')
b1.tags.append(tag1)
b1.tags.append(tag2)
b1.tags.append(tag3)
b2.tags.append(tag3)
b2.tags.append(tag4)
b3.tags.append(tag2)
b3.tags.append(tag5)
session.add(b1)
session.add(b2)
session.add(b3)
session.commit()

此时关系表数据为：

数据由SQLAlchemy维护。

博客表数据为：

标签表数据为：

 

完整代码如下：

from sqlalchemy import DateTime, Table, Column, String, create_engine, Integer, MetaData, ForeignKey
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy.ext.declarative import declarative_base
from datetime import datetime

base = declarative_base()
engine = create_engine('sqlite:///demo.sqlite3')

blog_tag = Table('tb_blog_tag', base.metadata,
                 Column('id', Integer, primary_key=True, autoincrement=True),
                 Column('blog_id', Integer, ForeignKey('tb_blogs.id')),
                 Column('tag_id', Integer, ForeignKey('tb_tags.id')))


class Blog(base):
    __tablename__ = 'tb_blogs'
    id = Column(Integer, primary_key=True, autoincrement=True)
    title = Column(String, nullable=False)
    content = Column(String, nullable=False)
    created_on = Column(DateTime, default=datetime.now)
    updated_on = Column(DateTime, default=datetime.now, onupdate=datetime.now)
    tags = relationship('Tag', backref='blogs', secondary=blog_tag)

    def __repr__(self):
        return f'<Blog: {self.title} {len(self.content)} letters>'


class Tag(base):
    __tablename__ = 'tb_tags'
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String, nullable=False)

    def __repr__(self):
        return f'<Tag: {self.name}>'


base.metadata.create_all(engine)
maker = sessionmaker(bind=engine)
session = maker()

b1 = Blog(title='hello', content='world')
b2 = Blog(title='football', content='world cup')
b3 = Blog(title='cat', content='meow meow')
tag1 = Tag(name='Tech')
tag2 = Tag(name='Funny')
tag3 = Tag(name='News')
tag4 = Tag(name='Daily')
tag5 = Tag(name='Pets')
b1.tags.append(tag1)
b1.tags.append(tag2)
b1.tags.append(tag3)
b2.tags.append(tag3)
b2.tags.append(tag4)
b3.tags.append(tag2)
b3.tags.append(tag5)
session.add(b1)
session.add(b2)
session.add(b3)
session.commit()

bs = session.query(Blog).all()
for b in bs:
    print(b, end=' ')
    print(b.tags)