Python SQLAlchemy - 外键错误

Question

我正在尝试使用 SQLAlchemy 学习 Python ORM。

我有这些 MySQL 表:

CREATE TABLE address (
  id INT NOT NULL AUTO_INCREMENT,
  street VARCHAR(45) NOT NULL,
  city VARCHAR(45) NOT NULL,
  PRIMARY KEY (id))
ENGINE = InnoDB;

CREATE TABLE user (
  id INT NOT NULL AUTO_INCREMENT,
  name VARCHAR(45) NOT NULL,
  email VARCHAR(45) NOT NULL,
  nick VARCHAR(45) NULL,
  address_id INT NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY email_UNIQUE (email),
  INDEX fk_user_address_idx (address_id ASC),
  CONSTRAINT fk_user_address
    FOREIGN KEY (address_id)
    REFERENCES address(id)
  )
ENGINE = InnoDB;

我使用这个教程http://docs.sqlalchemy.org/en/latest/orm/backref.html

这是我的 Python 代码:

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import *
from sqlalchemy.orm import *

Base = declarative_base()

class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)
    name = Column(String, nullable=False)
    email = Column(String, nullable=False, unique=True)
    nick = Column(String)

    address = relationship("Address", back_populates="user")

class Address(Base):
    __tablename__ = 'address'
    id = Column(Integer, primary_key=True)
    street = Column(String, nullable=False)
    city = Column(String, nullable=False)
    user = relationship('User', back_populates="address")

engine = create_engine('mysql://test:test@localhost:3306/test', echo=True)
Session = sessionmaker(bind=engine)
session = Session()

u1 = User()
u1.name = "Test user"
u1.email = "test@example.net"

a1 = Address()
a1.street = "Str 123"
a1.city = "City WTF"

u1.address = a1

session.add(a1)
session.add(u1)
session.commit()

运行应用程序后，它崩溃了:

/usr/bin/python2.7 /tmp/test/Test.py
Traceback (most recent call last):
  File "/tmp/test/Test.py", line 29, in <module>
    u1 = User()
  File "<string>", line 2, in __init__
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/orm/instrumentation.py", line 317, in _new_state_if_none
    state = self._state_constructor(instance, self)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/util/langhelpers.py", line 612, in __get__
    obj.__dict__[self.__name__] = result = self.fget(obj)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/orm/instrumentation.py", line 152, in _state_constructor
    self.dispatch.first_init(self, self.class_)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/event.py", line 409, in __call__
    fn(*args, **kw)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/orm/mapper.py", line 2260, in _event_on_first_init
    configure_mappers()
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/orm/mapper.py", line 2169, in configure_mappers
    mapper._post_configure_properties()
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/orm/mapper.py", line 1281, in _post_configure_properties
    prop.init()
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/orm/interfaces.py", line 231, in init
    self.do_init()
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/orm/properties.py", line 1030, in do_init
    self._setup_join_conditions()
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/orm/properties.py", line 1104, in _setup_join_conditions
    can_be_synced_fn=self._columns_are_mapped
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/orm/relationships.py", line 114, in __init__
    self._determine_joins()
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/orm/relationships.py", line 218, in _determine_joins
    % self.prop)
sqlalchemy.exc.NoForeignKeysError: Could not determine join condition between parent/child tables on relationship User.address - there are no foreign keys linking these tables.  Ensure that referencing columns are associated with a ForeignKey or ForeignKeyConstraint, or specify a 'primaryjoin' expression.

Process finished with exit code 1

我做错了什么？

Answer 1

您需要定义ForeignKey，它没有在模型上定义(您只在MySQL端定义了它):

class User(Base):
    __tablename__ = 'user'
    # other columns ...
    address_id = Column(Integer, ForeignKey('address.id'))

    address = relationship("Address", back_populates="user")

并且您的代码应该可以工作。

与您引用的示例相反，您在 User 表上定义了一个外键，而该示例是在 Address 表上定义的(这是更常见的情况) ).