Re: [sqlalchemy] defining foreign keys?
Hi James,
you cannot define two mapper properties that use the same name.
Your User model specifies:
addresses = relationship('Address', order_by='Address.id', backref='user')
Your Address model specifies:
user = relationship('User', backref=backref('addresses', order_by=id))
in the User model you define a backref='user', which will create a
property 'Address.user' in the mapped entity, but it already exist!
Tha same in Address model: you define a backref named 'address' which
already exists in User entity.
I don't know you use case but there are two scenario.
1) 'addresses' and 'user' are the end of the same relationship, in this
case you must remove one relationship definition and use backref:
class User(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True)
name = Column(String)
fullname = Column(String)
password = Column(String)
addresses = relationship('Address', order_by='Address.id',
backref='user')
class Address(Base):
__tablename__ = 'addresses'
id = Column(Integer, primary_key=True)
email_address = Column(String, nullable=False)
user_id = Column(Integer, ForeignKey('users.id http://users.id'))
2) addresses and user properties are two different relationships, then
you must renamed backrefs :)
Il 26/10/11 04.05, James Hartley ha scritto:
I suspect this is user error, but I am not ferreting out my mistake.
I'm porting some older code to SQLAlchemy 0.71 on top of Python
2.7.1. Code which had originally implemented foreign keys without
using REFERENCES clauses in CREATE TABLE statements previously ran
fine. Now, adding formal foreign keys isn't working. I have boiled
this down to the following variant on the example found in the
SQLAlchemy Documentation:
=8--
#!/usr/bin/env python
from sqlalchemy import create_engine, Column, ForeignKey, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship, backref
Base = declarative_base()
class User(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True)
name = Column(String)
fullname = Column(String)
password = Column(String)
addresses = relationship('Address', order_by='Address.id',
backref='user')
def __init__(self, name, fullname, password):
self.name http://self.name = name
self.fullname = fullname
self.password = password
def __repr__(self):
return User('%s', '%s', '%s', '%s') % (self.id
http://self.id, self.name http://self.name, self.fullname,
self.password)
class Address(Base):
__tablename__ = 'addresses'
id = Column(Integer, primary_key=True)
email_address = Column(String, nullable=False)
user_id = Column(Integer, ForeignKey('users.id http://users.id'))
user = relationship('User', backref=backref('addresses', order_by=id))
def __init__(self, email):
self.email_address = email
def __repr__(self):
return Address('%s', '%s', '%s') % (self.id
http://self.id, self.email_address, self.user_id)
if __name__ == '__main__':
engine = create_engine('sqlite:///:memory:', echo=True)
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()
user = User('jdoe', 'John Doe', 'password')
print user
session.add(user)
session.commit()
=8--
Execution yields the following traceback:
=8--
traceback (most recent call last):
File ./test.py, line 51, in module
user = User('jdoe', 'John Doe', 'password')
File string, line 2, in __init__
File
/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/instrumentation.py,
line 309, in _new_state_if_none
state = self._state_constructor(instance, self)
File
/usr/local/lib/python2.7/site-packages/sqlalchemy/util/langhelpers.py,
line 432, in __get__
obj.__dict__[self.__name__] = result = self.fget(obj)
File
/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/instrumentation.py,
line 157, in _state_constructor
self.dispatch.first_init(self, self.class_)
File /usr/local/lib/python2.7/site-packages/sqlalchemy/event.py,
line 274, in __call__
fn(*args, **kw)
File
/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/mapper.py,
line 2787, in _event_on_first_init
configure_mappers()
File
/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/mapper.py,
line 2719, in configure_mappers
mapper._post_configure_properties()
File
/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/mapper.py,
line 1035, in _post_configure_properties
prop.init()
File
/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/interfaces.py,
line 121, in init
self.do_init()
File
/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/properties.py,
line 905, in do_init
self._generate_backref()
File
Re: [sqlalchemy] defining foreign keys?
On Oct 26, 2011, at 1:04 PM, James Hartley wrote:
On Wed, Oct 26, 2011 at 2:22 AM, Stefano Fontanelli s.fontane...@asidev.com
wrote:
Hi James,
you cannot define two mapper properties that use the same name.
Thank you. I truly appreciate your response. I can now implement the table
interconnection while both classes are defined in the same file. My problem
is when the classes are separated. Given the following two files:
8--
#!/usr/bin/env python
user.py
from sqlalchemy import create_engine, Column, ForeignKey, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship, backref
Base = declarative_base()
class User(Base):
__tablename__ = 'users'
from user import User
Base = declarative_base()
class Address(Base):
__tablename__ = 'addresses'
id = Column(Integer, primary_key=True)
email_address = Column(String, nullable=False)
user_id = Column(Integer, ForeignKey('users.id'), nullable=False)
user = relationship('User', backref=backref('addresses', order_by=id))
If you wish to locate classes based on their string name as you are doing in
relationship('User') here, the calling class (Address) must share the same
registry of names that the desired class (User) does. This registry is part of
the Base. Therefore your entire application needs to have exactly one usage
of declarative_base(), where all descending classes use the same Base object,
and not one usage per file.Additionally, both user.py and address.py must
be imported via the Python import statement before the mappings can be used.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] defining foreign keys?
On Wed, Oct 26, 2011 at 10:15 AM, Michael Bayer mike...@zzzcomputing.comwrote:
On Oct 26, 2011, at 1:04 PM, James Hartley wrote:
On Wed, Oct 26, 2011 at 2:22 AM, Stefano Fontanelli
s.fontane...@asidev.com wrote:
Hi James,
you cannot define two mapper properties that use the same name.
If you wish to locate classes based on their string name as you are doing
in relationship('User') here, the calling class (Address) must share the
same registry of names that the desired class (User) does. This registry is
part of the Base. Therefore your entire application needs to have
exactly one usage of declarative_base(), where all descending classes use
the same Base object, and not one usage per file.
This is what I had missed. Moving the call to declarative_base() to its own
module importing it as needed has taken care of all remaining problems.
Thank you Michael Stefano for taking the time to clear this up. I
sincerely appreciate it.
Jim
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] defining foreign keys?
I suspect this is user error, but I am not ferreting out my mistake.
I'm porting some older code to SQLAlchemy 0.71 on top of Python 2.7.1. Code
which had originally implemented foreign keys without using REFERENCES
clauses in CREATE TABLE statements previously ran fine. Now, adding formal
foreign keys isn't working. I have boiled this down to the following
variant on the example found in the SQLAlchemy Documentation:
=8--
#!/usr/bin/env
python
from sqlalchemy import create_engine, Column, ForeignKey, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship, backref
Base = declarative_base()
class User(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True)
name = Column(String)
fullname = Column(String)
password = Column(String)
addresses = relationship('Address', order_by='Address.id',
backref='user')
def __init__(self, name, fullname, password):
self.name = name
self.fullname = fullname
self.password = password
def __repr__(self):
return User('%s', '%s', '%s', '%s') % (self.id, self.name,
self.fullname, self.password)
class Address(Base):
__tablename__ = 'addresses'
id = Column(Integer, primary_key=True)
email_address = Column(String, nullable=False)
user_id = Column(Integer, ForeignKey('users.id'))
user = relationship('User', backref=backref('addresses', order_by=id))
def __init__(self, email):
self.email_address = email
def __repr__(self):
return Address('%s', '%s', '%s') % (self.id, self.email_address,
self.user_id)
if __name__ == '__main__':
engine = create_engine('sqlite:///:memory:', echo=True)
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()
user = User('jdoe', 'John Doe', 'password')
print user
session.add(user)
session.commit()
=8--
Execution yields the following traceback:
=8--
traceback (most recent call last):
File ./test.py, line 51, in module
user = User('jdoe', 'John Doe', 'password')
File string, line 2, in __init__
File
/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/instrumentation.py,
line 309, in _new_state_if_none
state = self._state_constructor(instance, self)
File
/usr/local/lib/python2.7/site-packages/sqlalchemy/util/langhelpers.py,
line 432, in __get__
obj.__dict__[self.__name__] = result = self.fget(obj)
File
/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/instrumentation.py,
line 157, in _state_constructor
self.dispatch.first_init(self, self.class_)
File /usr/local/lib/python2.7/site-packages/sqlalchemy/event.py, line
274, in __call__
fn(*args, **kw)
File /usr/local/lib/python2.7/site-packages/sqlalchemy/orm/mapper.py,
line 2787, in _event_on_first_init
configure_mappers()
File /usr/local/lib/python2.7/site-packages/sqlalchemy/orm/mapper.py,
line 2719, in configure_mappers
mapper._post_configure_properties()
File /usr/local/lib/python2.7/site-packages/sqlalchemy/orm/mapper.py,
line 1035, in _post_configure_properties
prop.init()
File
/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/interfaces.py, line
121, in init
self.do_init()
File
/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/properties.py, line
905, in do_init
self._generate_backref()
File
/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/properties.py, line
1376, in _generate_backref
self, mapper))
sqlalchemy.exc.ArgumentError: Error creating backref 'user' on relationship
'User.addresses': property of that name exists on mapper
'Mapper|Address|addresses'
=8--
Any insight shared would be greatly appreciated. Thanks.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
