ex5_1.py内容如下：

"""
两个外键关联到同一张表
"""
import sqlalchemy
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, String, Integer, ForeignKey
from sqlalchemy.orm import relationship

engine = create_engine("mysql+pymysql://root:123456@localhost/test", encoding='utf-8')

Base = declarative_base()

class Addresses(Base):
    __tablename__ = 'addresses'
    id = Column(Integer, primary_key=True, nullable=False)
    province = Column(String(10), nullable=True)
    city = Column(String(10), nullable=True)
    
class Users(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True, nullable=False)
    name = Column(String(10), nullable=True)
    first_addr_id = Column(Integer, ForeignKey('addresses.id'))
    second_addr_id = Column(Integer, ForeignKey('addresses.id'))
    first_addr = relationship("Addresses", foreign_keys=[first_addr_id])
    second_addr = relationship("Addresses", foreign_keys=[second_addr_id])
    
    def __repr__(self):
        return "<name %s address %s %s>" % (self.name, self.first_addr.province, self.second_addr.province)
    
Base.metadata.create_all(engine)

View Code

ex5_2.py：

"""
往表中写入数据
"""
import ex5_1
from sqlalchemy.orm import sessionmaker

Session_class = sessionmaker(bind=ex5_1.engine)
session = Session_class()

add1 = ex5_1.Addresses(province='HeNan', city='NanYang')
add2 = ex5_1.Addresses(province='HeBei', city='HanDan')
add3 = ex5_1.Addresses(province='HuNan', city='YueYang')

session.add_all([add1, add2, add3])

user1 = ex5_1.Users(name='Eric', first_addr_id=add1, second_addr_id=add2)
user2 = ex5_1.Users(name='Alice', first_addr_id=add2, second_addr_id=add3)
user3 = ex5_1.Users(name='Peter', first_addr_id=add3, second_addr_id=add1)

session.add_all([user1, user2, user3])
session.commit()

View Code

在cmd里执行ex5_2.py，报错如下：

Traceback (most recent call last):
File "ex5_2.py", line 21, in <module>
session.commit()
File "C:\Users\Eric\AppData\Local\Programs\Python\Python36\lib\site-packages\sqlalchemy\orm\session.py", line 943, in commit
self.transaction.commit()
File "C:\Users\Eric\AppData\Local\Programs\Python\Python36\lib\site-packages\sqlalchemy\orm\session.py", line 467, in commit
self._prepare_impl()
File "C:\Users\Eric\AppData\Local\Programs\Python\Python36\lib\site-packages\sqlalchemy\orm\session.py", line 447, in _prepare_impl
self.session.flush()
File "C:\Users\Eric\AppData\Local\Programs\Python\Python36\lib\site-packages\sqlalchemy\orm\session.py", line 2254, in flush
self._flush(objects)
File "C:\Users\Eric\AppData\Local\Programs\Python\Python36\lib\site-packages\sqlalchemy\orm\session.py", line 2380, in _flush
transaction.rollback(_capture_exception=True)
File "C:\Users\Eric\AppData\Local\Programs\Python\Python36\lib\site-packages\sqlalchemy\util\langhelpers.py", line 66, in __exit__
compat.reraise(exc_type, exc_value, exc_tb)
File "C:\Users\Eric\AppData\Local\Programs\Python\Python36\lib\site-packages\sqlalchemy\util\compat.py", line 187, in reraise
raise value
File "C:\Users\Eric\AppData\Local\Programs\Python\Python36\lib\site-packages\sqlalchemy\orm\session.py", line 2344, in _flush
flush_context.execute()
File "C:\Users\Eric\AppData\Local\Programs\Python\Python36\lib\site-packages\sqlalchemy\orm\unitofwork.py", line 391, in execute
rec.execute(self)
File "C:\Users\Eric\AppData\Local\Programs\Python\Python36\lib\site-packages\sqlalchemy\orm\unitofwork.py", line 556, in execute
uow
File "C:\Users\Eric\AppData\Local\Programs\Python\Python36\lib\site-packages\sqlalchemy\orm\persistence.py", line 181, in save_obj
mapper, table, insert)
File "C:\Users\Eric\AppData\Local\Programs\Python\Python36\lib\site-packages\sqlalchemy\orm\persistence.py", line 866, in _emit_insert_statements
execute(statement, params)
File "C:\Users\Eric\AppData\Local\Programs\Python\Python36\lib\site-packages\sqlalchemy\engine\base.py", line 948, in execute
return meth(self, multiparams, params)
File "C:\Users\Eric\AppData\Local\Programs\Python\Python36\lib\site-packages\sqlalchemy\sql\elements.py", line 269, in _execute_on_connection
return connection._execute_clauseelement(self, multiparams, params)
File "C:\Users\Eric\AppData\Local\Programs\Python\Python36\lib\site-packages\sqlalchemy\engine\base.py", line 1060, in _execute_clauseelement
compiled_sql, distilled_params
File "C:\Users\Eric\AppData\Local\Programs\Python\Python36\lib\site-packages\sqlalchemy\engine\base.py", line 1200, in _execute_context
context)
File "C:\Users\Eric\AppData\Local\Programs\Python\Python36\lib\site-packages\sqlalchemy\engine\base.py", line 1416, in _handle_dbapi_exception
util.reraise(*exc_info)
File "C:\Users\Eric\AppData\Local\Programs\Python\Python36\lib\site-packages\sqlalchemy\util\compat.py", line 187, in reraise
raise value
File "C:\Users\Eric\AppData\Local\Programs\Python\Python36\lib\site-packages\sqlalchemy\engine\base.py", line 1193, in _execute_context
context)
File "C:\Users\Eric\AppData\Local\Programs\Python\Python36\lib\site-packages\sqlalchemy\engine\default.py", line 508, in do_execute
cursor.execute(statement, parameters)
File "C:\Users\Eric\AppData\Local\Programs\Python\Python36\lib\site-packages\pymysql\cursors.py", line 168, in execute
query = self.mogrify(query, args)
File "C:\Users\Eric\AppData\Local\Programs\Python\Python36\lib\site-packages\pymysql\cursors.py", line 147, in mogrify
query = query % self._escape_args(args, conn)
File "C:\Users\Eric\AppData\Local\Programs\Python\Python36\lib\site-packages\pymysql\cursors.py", line 127, in _escape_args
return dict((key, conn.literal(val)) for (key, val) in args.items())
File "C:\Users\Eric\AppData\Local\Programs\Python\Python36\lib\site-packages\pymysql\cursors.py", line 127, in <genexpr>
return dict((key, conn.literal(val)) for (key, val) in args.items())
File "C:\Users\Eric\AppData\Local\Programs\Python\Python36\lib\site-packages\pymysql\connections.py", line 846, in literal
return self.escape(obj, self.encoders)
File "C:\Users\Eric\AppData\Local\Programs\Python\Python36\lib\site-packages\pymysql\connections.py", line 839, in escape
return converters.escape_item(obj, self.charset, mapping=mapping)
File "C:\Users\Eric\AppData\Local\Programs\Python\Python36\lib\site-packages\pymysql\converters.py", line 27, in escape_item
val = encoder(val, mapping)
File "C:\Users\Eric\AppData\Local\Programs\Python\Python36\lib\site-packages\pymysql\converters.py", line 118, in escape_unicode
return u"'%s'" % _escape_unicode(value)
File "C:\Users\Eric\AppData\Local\Programs\Python\Python36\lib\site-packages\pymysql\converters.py", line 73, in _escape_unicode
return value.translate(_escape_table)
AttributeError: 'Addresses' object has no attribute 'translate'