sqlalchemy DB表关系研究

大家知道OpenStack项目的DB表是定义在db\sqlalchemy\models.py文件中。

如果看过该文件中定义的这些DB表，会发现表之间的关系主要有两种：

ForeignKey：外键

relationship：关系

其中外键就是数据库表外键的概念，其作用大家可以查阅相关资料。

这里主要关注relationship的作用。

下面通过一段测试代码来验证。代码如下：

import sqlalchemyfrom sqlalchemy import create_enginefrom sqlalchemy.ext import declarativefrom sqlalchemy.orm import sessionmakerfrom sqlalchemy.orm import relationshipengine = create_engine('mysql://root:openstack@localhost:3306/mydata?charset=utf8', encoding='utf-8', echo=True)Session = sessionmaker(bind=engine)session = Session()Base = declarative.declarative_base()class Phone(Base):    __tablename__ = 'phone'    id = sqlalchemy.Column('id', sqlalchemy.Integer, primary_key=True)    number = sqlalchemy.Column('number', sqlalchemy.String(18))    user = sqlalchemy.Column('user', sqlalchemy.Integer, sqlalchemy.ForeignKey('user.id'), nullable=False)class User(Base):    __tablename__ = 'user'    id = sqlalchemy.Column('id', sqlalchemy.Integer, primary_key=True)    name = sqlalchemy.Column('name', sqlalchemy.String(32))    #phone = relationship(Phone, cascade="delete")Base.metadata.create_all(engine)user = User(id=1, name='Steve')session.add(user)session.commit()phone = Phone(number='123', user=1)session.add(phone)session.commit()user = session.query(User).filter_by(id=1).first()session.delete(user)session.commit()Base.metadata.drop_all(engine)

这段代码主要是定义了两个表：user和phone。

这两个表的主键都是id；而phone表包含一个外键user，引用的user表的id。

执行此段代码，会得到如下异常错误：

sqlalchemy.exc.IntegrityError: (_mysql_exceptions.IntegrityError) (1451, 'Cannot delete or update a parent row: a foreign key constraint fails (`mydata`.`phone`, CONSTRAINT `phone_ibfk_1` FOREIGN KEY (`user`) REFERENCES `user` (`id`))') [SQL: u'DELETE FROM user WHERE user.id = %s'] [parameters: (1L,)]

此错误说的是，由于phone表中外键依赖的限制，删除user记录的操作失败了，

放开前面代码中注释掉的一行，User类定义变成如下形式：

class User(Base):    __tablename__ = 'user'    id = sqlalchemy.Column('id', sqlalchemy.Integer, primary_key=True)    name = sqlalchemy.Column('name', sqlalchemy.String(32))    phone = relationship(Phone, cascade="delete")

然后，手动清除mydata数据库中的phone和user表，重新执行代码。

这一次执行成功了，关键执行流程如下：

2015-10-31 15:41:21,650 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)2015-10-31 15:41:21,651 INFO sqlalchemy.engine.base.Engine INSERT INTO user (id, name) VALUES (%s, %s)2015-10-31 15:41:21,652 INFO sqlalchemy.engine.base.Engine (1, 'Steve')2015-10-31 15:41:21,652 INFO sqlalchemy.engine.base.Engine COMMIT2015-10-31 15:41:21,654 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)2015-10-31 15:41:21,655 INFO sqlalchemy.engine.base.Engine INSERT INTO phone (number, user) VALUES (%s, %s)2015-10-31 15:41:21,655 INFO sqlalchemy.engine.base.Engine ('123', 1)2015-10-31 15:41:21,656 INFO sqlalchemy.engine.base.Engine COMMIT2015-10-31 15:41:21,658 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)2015-10-31 15:41:21,658 INFO sqlalchemy.engine.base.Engine SELECT user.id AS user_id, user.name AS user_name FROM user WHERE user.id = %s  LIMIT %s2015-10-31 15:41:21,658 INFO sqlalchemy.engine.base.Engine (1, 1)2015-10-31 15:41:21,660 INFO sqlalchemy.engine.base.Engine SELECT phone.id AS phone_id, phone.number AS phone_number, phone.user AS phone_user FROM phone WHERE %s = phone.user2015-10-31 15:41:21,660 INFO sqlalchemy.engine.base.Engine (1L,)2015-10-31 15:41:21,662 INFO sqlalchemy.engine.base.Engine DELETE FROM phone WHERE phone.id = %s2015-10-31 15:41:21,662 INFO sqlalchemy.engine.base.Engine (1L,)2015-10-31 15:41:21,663 INFO sqlalchemy.engine.base.Engine DELETE FROM user WHERE user.id = %s2015-10-31 15:41:21,663 INFO sqlalchemy.engine.base.Engine (1L,)2015-10-31 15:41:21,663 INFO sqlalchemy.engine.base.Engine COMMIT

可以看到，先插入了一行user记录，然后再插入了phone记录。
接着查询了刚插入的user记录，然后先删除了phone记录，再删除了user记录。

正式因为先删除了phone记录，这样对user记录的外键依赖不再存在，所以后面的删除user记录成功了。

但是，前面的代码中并没有显示删除phone记录，那么这条删除操作是从哪里来的呢？

这就是前面phone = relationship(Phone, cascade="delete")一行的作用。

增加这一行之后，user和phone形成了一对多的父子关系。

关系模式的相关概念，可参考http://docs.sqlalchemy.org/en/rel_1_0/orm/basic_relationships.html。

而cascade="delete"参数的作用，是定义操作应该如何在父子之间传递。

这有点类似于mysql增加外键时，ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}参数的作用。

这里的delete即表示删除父记录要相应删除相关的子记录。

这就是前面先删除了phone记录的原因。

relationship还有其他一些比较有用的参数，有关这些参数作用，以及参数的可选项，

可以参考sqlalchemy\orm\relationships.py中RelationshipProperty类的相关描述。