sqlchemy的外键及其约束条件

外键创建

使用sqlalchemy创建外键非常简单。在表中增加一个字段，制定这个字段外键的是哪个表的哪个字段就可以了。
从表中外键定义的字段必须和主键字段类型保持一致。
实例代码：

import models
from sqlalchemy import Column, Integer, String, ForeignKey, SmallInteger


class User(models.Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(20))


class Details(models.Base):
    __tablename__ = 'user_details'
    id = Column(Integer, primary_key=True, autoincrement=True)
    age = Column(SmallInteger)
    user = Column(Integer, ForeignKey('user.id'))

外键的约束：

1.RESTRICT:父表数据被删除，会阻止删除.默认项
2.NO ACTION: mysql中，同RESTRICT.
3.CASCADE: 级联删除.
4.SET NULL: 父表数据被删除, 子表数据会设置为NULL，但是这个外键是可以为空nullable=True

orm关系以及一对多：

import models
from sqlalchemy import Column, Integer, String, ForeignKey, SmallInteger
from sqlalchemy.orm import relationship


class User(models.Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(20))


class Details(models.Base):
    __tablename__ = 'user_details'
    id = Column(Integer, primary_key=True, autoincrement=True)
    age = Column(SmallInteger)
    user = Column(Integer, ForeignKey('user.id'))

    _user = relationship('User')


first = models.session.query(Details).first()
print(first._user.name)

可以通过relationship来绑定关联表,后面可以直接通过这个绑定的字段来查询绑定的表的属性

import models
from sqlalchemy import Column, Integer, String, ForeignKey, SmallInteger
from sqlalchemy.orm import relationship


class User(models.Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(20))

    _details = relationship('Details')


class Details(models.Base):
    __tablename__ = 'user_details'
    id = Column(Integer, primary_key=True, autoincrement=True)
    age = Column(SmallInteger)
    user = Column(Integer, ForeignKey('user.id'))

    _user = relationship('User')


first = models.session.query(User).first()
print(first._details)

在主表中一样可以定义一个关联字段来关联从表,之后通过这个字段来查询从表的数据，返回的是一个所有关联这条主表数据的列表对象

import models
from sqlalchemy import Column, Integer, String, ForeignKey, SmallInteger
from sqlalchemy.orm import relationship


class User(models.Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(20))

    # _details = relationship('Details')


class Details(models.Base):
    __tablename__ = 'user_details'
    id = Column(Integer, primary_key=True, autoincrement=True)
    age = Column(SmallInteger)
    user = Column(Integer, ForeignKey('user.id'))

    _user = relationship('User', backref='_details')


first = models.session.query(User).first()
print(first._details)

在从表的关联字段中增加一个backref参数后一样，主表中就不用在定义关联字段，一样可以使用这个新增的字段来查询从表的数据

关联的relationship的字段的backref制定的参数时一个InstrumentedList对象,InstrumentedList又是list的子类.所以主表添加多条数据时候可以采用append的方法

方法1

a1 = Details(age=6)
a2 = Details(age=7)

user._details.append(a1)
user._details.append(a2)
models.session.add(user)
models.session.commit()

方法2

user = models.session.query(User).first()
a1 = Details(age=6)
a1._user = user
models.session.add(a1)
models.session.commit()

可以直接通过relationship的字段来制定主表

一对一

import models
from sqlalchemy import Column, Integer, String, ForeignKey, SmallInteger
from sqlalchemy.orm import relationship, backref


class User(models.Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(20))

    # _details = relationship('Details')


class Details(models.Base):
    __tablename__ = 'user_details'
    id = Column(Integer, primary_key=True, autoincrement=True)
    age = Column(SmallInteger)
    user = Column(Integer, ForeignKey('user.id'))

    _user = relationship('User', backref=backref('_details', uselist=False))

一对一关系的实现只要在relationship里面将uselist=False即可

多对多

import models
from sqlalchemy import Column, Integer, String, ForeignKey, SmallInteger, Table
from sqlalchemy.orm import relationship

article_tags = Table(
    'article_tags',
    models.Base.metadata,
    Column('article', Integer, ForeignKey('article.id')),
    Column('tags', Integer, ForeignKey('tags.id')),
)


class Article(models.Base):
    __tablename__ = 'article'
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(20))


class Tags(models.Base):
    __tablename__ = 'tags'
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(20))

    article = relationship('Article', backref='tags', secondary=article_tags)


models.Base.metadata.create_all()

a1 = Article(name='文章1')
t1 = Tags(name='标签1')

t1.article.append(a1)

models.session.add(t1)
models.session.commit()

# 查询绑定Aritcle的所有Tags
article = models.session.query(Article).first()
print(article.tags)
# 查询绑定Tags的所有Aritcle
tag = models.session.query(Tags).first()
print(tag.article)

多对多的中间表用Table来创建,在多对多的两张表里的任意一张表加上relationship关联, 另外relationship新加一个参数secondary等于创建的第三张表的对象