Re: [sqlalchemy] vacation...
Hi Mike, On 7/31/2014 0:33, Michael Bayer wrote: hey folks - I'm on vacation from thursday tomorrow through next thursday, so folks please hold down the fort! I won't be off the grid but I might not be able to get to my email as regularly. Have a good vacation, I hope you forgot to pack the laptop;-) . Werner -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] Foreign Keys and 'backref'
On page 23 of the 0.9.7 (Section 2.1.10, Building A Relationship) I see
the example:
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))
The last line is in the child table (with the foreign key) and the
backref() statement has the child table name and an order_by option) Why
is backref written twice, and why is the parent table (user/User) mentioned
twice? Or, is 'user' a column in the addresses table?
On page 89 (Section 2.3.3, Linking Relationships With Backref) I see a
different example:
class User(Base):
__tablename__ = 'user'
id = Column(Integer, primary_key=True)
name = Column(String)
addresses = relationship(Address, backref=user)
class Address(Base):
__tablename__ = 'address'
id = Column(Integer, primary_key=True)
email = Column(String)
user_id = Column(Integer, ForeignKey('user.id'))
Here, the relationship() statement is in the parent table and it specifies
the child class Address with backref mentioned once to the parent table
user.
These two examples appear different to me. If no one can clear up my
confusion I'll wait for Mike to return from vacation to be straightened out
on how to define referencial integrity.
Thanks,
Rich
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Re: Foreign Keys and 'backref'
On Thu, 31 Jul 2014, Jonathan Vanasco wrote:
backref indicates the string name of a property to be placed on the
related mapper’s class that will handle this relationship in the other
direction. The other property will be created automatically when the
mappers are configured. Can also be passed as a backref() object to
control the configuration of the new relationship.
Using your examples side-by-side:
user = relationship(User, backref=backref('addresses', order_by=id))
addresses = relationship(Address, backref=user)
Jonathan,
So I can specify the relationship from either class/table. I'll read the
docs again to make sure I fully understand which form to use when.
Thank you,
Rich
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Re: Foreign Keys and 'backref'
On Thu, 31 Jul 2014, Jonathan Vanasco wrote: The relevant docs say this ( http://docs.sqlalchemy.org/en/rel_0_9/orm/relationships.html#sqlalchemy.orm.relationship.params.backref ) Re-reading this section of the docs I'm seeing what I missed the first time through. Thanks again, Jonathan, Rich -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Re: Foreign Keys and 'backref'
yes. `backref` just lets you consolidate defining a relationship in a single place. -- `relationship` lets you specify the relationship from A to B `backref` is a kwarg to `relationship` that lets you specify the inverse ( B to A ) class TableA(): b = relationship(TableB, backref=a) class TableB(): pass is the same thing as saying: class TableA(): b = relationship(TableB) class TableB(): a = relationship(TableA) `backref` can either be a `String` OR a `backref()` constructor -- which lets you customized the join with the same conditions you can elect in the `relationship` constructor class Foo(): bar = relationship(Bar, lots_of_keywords_here=True, backref=backref(the_same_keywords_work_here=True) ) -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Re: Foreign Keys and 'backref'
On Thu, 31 Jul 2014, Jonathan Vanasco wrote: yes. `backref` just lets you consolidate defining a relationship in a single place. `relationship` lets you specify the relationship from A to B `backref` is a kwarg to `relationship` that lets you specify the inverse ( B to A ) class TableA(): b = relationship(TableB, backref=a) class TableB(): pass is the same thing as saying: class TableA(): b = relationship(TableB) class TableB(): a = relationship(TableA) `backref` can either be a `String` OR a `backref()` constructor -- which lets you customized the join with the same conditions you can elect in the `relationship` constructor class Foo(): bar = relationship(Bar, lots_of_keywords_here=True, backref=backref(the_same_keywords_work_here=True) ) Jonathan, That makes everything clear. My original confusion arose because I'm used to specifying many children to one parent with the postgres syntax REFERENCES in the children's table; that's the only relationships I've used (other than many-to-many in linking tables). The abundance of options in SQLAlchemy threw me. Much appreciated, Rich -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] one to many relationship
I'm using sqlalchemy 0.8.7 on python 2.7.8.
Here is my test case:
#!/usr/bin/env python
from sqlalchemy import Column, ForeignKey
from sqlalchemy.orm import relationship, backref
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.dialects.postgresql import *
import uuid
Base = declarative_base()
class parent(Base):
__tablename__ = 'parent'
parent_id= Column(parent_id, UUID(as_uuid=True),
primary_key=True)
description = Column(description, VARCHAR)
childRelationship = relationship('child', backref = parent)
#Base2 = declarative_base()
#class child(Base2):
class child(Base):
__tablename__ = 'child'
child_id = Column(child_id, UUID(as_uuid=True),
primary_key=True)
description = Column(description, VARCHAR)
parent_id = Column(parent_id, UUID(as_uuid=True),
ForeignKey(parent))
myP = parent()
From the documentation I thought I'd be able to instantiate myP at that
point.
Instead I get this:
Traceback (most recent call last):
File ./testcase.py, line 33, in module
myP = parent()
File string, line 2, in __init__
File build/bdist.linux-x86_64/egg/sqlalchemy/orm/instrumentation.py,
line 317, in _new_state_if_none
File build/bdist.linux-x86_64/egg/sqlalchemy/util/langhelpers.py, line
612, in __get__
File build/bdist.linux-x86_64/egg/sqlalchemy/orm/instrumentation.py,
line 152, in _state_constructor
File build/bdist.linux-x86_64/egg/sqlalchemy/event.py, line 420, in
__call__
File build/bdist.linux-x86_64/egg/sqlalchemy/orm/mapper.py, line 2262,
in _event_on_first_init
File build/bdist.linux-x86_64/egg/sqlalchemy/orm/mapper.py, line 2171,
in configure_mappers
File build/bdist.linux-x86_64/egg/sqlalchemy/orm/mapper.py, line 1281,
in _post_configure_properties
File build/bdist.linux-x86_64/egg/sqlalchemy/orm/interfaces.py, line
231, in init
File build/bdist.linux-x86_64/egg/sqlalchemy/orm/properties.py, line
1030, in do_init
File build/bdist.linux-x86_64/egg/sqlalchemy/orm/properties.py, line
1104, in _setup_join_conditions
File build/bdist.linux-x86_64/egg/sqlalchemy/orm/relationships.py,
line 114, in __init__
File build/bdist.linux-x86_64/egg/sqlalchemy/orm/relationships.py,
line 197, in _determine_joins
File build/bdist.linux-x86_64/egg/sqlalchemy/sql/util.py, line 355, in
join_condition
File build/bdist.linux-x86_64/egg/sqlalchemy/schema.py, line 1406, in
get_referent
File build/bdist.linux-x86_64/egg/sqlalchemy/sql/expression.py, line
3363, in corresponding_column
AttributeError: type object 'parent' has no attribute 'proxy_set'
If I change the child class to use Base2 instead of Base (commented out
above), it changes the error:
Traceback (most recent call last):
File ./testcase.py, line 33, in module
myP = parent()
File string, line 2, in __init__
File build/bdist.linux-x86_64/egg/sqlalchemy/orm/instrumentation.py,
line 317, in _new_state_if_none
File build/bdist.linux-x86_64/egg/sqlalchemy/util/langhelpers.py, line
612, in __get__
File build/bdist.linux-x86_64/egg/sqlalchemy/orm/instrumentation.py,
line 152, in _state_constructor
File build/bdist.linux-x86_64/egg/sqlalchemy/event.py, line 420, in
__call__
File build/bdist.linux-x86_64/egg/sqlalchemy/orm/mapper.py, line 2262,
in _event_on_first_init
File build/bdist.linux-x86_64/egg/sqlalchemy/orm/mapper.py, line 2171,
in configure_mappers
File build/bdist.linux-x86_64/egg/sqlalchemy/orm/mapper.py, line 1281,
in _post_configure_properties
File build/bdist.linux-x86_64/egg/sqlalchemy/orm/interfaces.py, line
231, in init
File build/bdist.linux-x86_64/egg/sqlalchemy/orm/properties.py, line
1029, in do_init
File build/bdist.linux-x86_64/egg/sqlalchemy/orm/properties.py, line
1084, in _process_dependent_arguments
File build/bdist.linux-x86_64/egg/sqlalchemy/util/langhelpers.py, line
612, in __get__
File build/bdist.linux-x86_64/egg/sqlalchemy/orm/properties.py, line
1003, in mapper
File
build/bdist.linux-x86_64/egg/sqlalchemy/ext/declarative/clsregistry.py,
line 266, in return_cls
sqlalchemy.exc.InvalidRequestError: When initializing mapper
Mapper|parent|parent, expression 'child' failed to locate a name (name
'child' is not defined). If this is a class name, consider adding this
relationship() to the class '__main__.parent' class after both dependent
classes have been defined.
I believe I'm following the documentation here --
http://docs.sqlalchemy.org/en/rel_0_8/orm/relationships.html -- pretty
closely in the many-to-one relationship example, but it isn't working. What
am I overlooking?
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to
Re: [sqlalchemy] one to many relationship
On 31 Jul 2014, at 21:34, Rick Otten rottenwindf...@gmail.com wrote:
I'm using sqlalchemy 0.8.7 on python 2.7.8.
Here is my test case:
#!/usr/bin/env python
from sqlalchemy import Column, ForeignKey
from sqlalchemy.orm import relationship, backref
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.dialects.postgresql import *
import uuid
Base = declarative_base()
class parent(Base):
__tablename__ = 'parent'
parent_id= Column(parent_id, UUID(as_uuid=True), primary_key=True)
description = Column(description, VARCHAR)
childRelationship = relationship('child', backref = parent)
#Base2 = declarative_base()
#class child(Base2):
class child(Base):
__tablename__ = 'child'
child_id = Column(child_id, UUID(as_uuid=True), primary_key=True)
description = Column(description, VARCHAR)
parent_id = Column(parent_id, UUID(as_uuid=True), ForeignKey(parent))
myP = parent()
That ForeignKey definition looks wrong - it should point at a column, not a
class. I think you want:
ForeignKey(parent.parent_id)
From the documentation I thought I'd be able to instantiate myP at that
point.
Instead I get this:
Traceback (most recent call last):
File ./testcase.py, line 33, in module
myP = parent()
File string, line 2, in __init__
File build/bdist.linux-x86_64/egg/sqlalchemy/orm/instrumentation.py, line
317, in _new_state_if_none
File build/bdist.linux-x86_64/egg/sqlalchemy/util/langhelpers.py, line
612, in __get__
File build/bdist.linux-x86_64/egg/sqlalchemy/orm/instrumentation.py, line
152, in _state_constructor
File build/bdist.linux-x86_64/egg/sqlalchemy/event.py, line 420, in
__call__
File build/bdist.linux-x86_64/egg/sqlalchemy/orm/mapper.py, line 2262, in
_event_on_first_init
File build/bdist.linux-x86_64/egg/sqlalchemy/orm/mapper.py, line 2171, in
configure_mappers
File build/bdist.linux-x86_64/egg/sqlalchemy/orm/mapper.py, line 1281, in
_post_configure_properties
File build/bdist.linux-x86_64/egg/sqlalchemy/orm/interfaces.py, line 231,
in init
File build/bdist.linux-x86_64/egg/sqlalchemy/orm/properties.py, line
1030, in do_init
File build/bdist.linux-x86_64/egg/sqlalchemy/orm/properties.py, line
1104, in _setup_join_conditions
File build/bdist.linux-x86_64/egg/sqlalchemy/orm/relationships.py, line
114, in __init__
File build/bdist.linux-x86_64/egg/sqlalchemy/orm/relationships.py, line
197, in _determine_joins
File build/bdist.linux-x86_64/egg/sqlalchemy/sql/util.py, line 355, in
join_condition
File build/bdist.linux-x86_64/egg/sqlalchemy/schema.py, line 1406, in
get_referent
File build/bdist.linux-x86_64/egg/sqlalchemy/sql/expression.py, line
3363, in corresponding_column
AttributeError: type object 'parent' has no attribute 'proxy_set'
If I change the child class to use Base2 instead of Base (commented out
above), it changes the error:
Traceback (most recent call last):
File ./testcase.py, line 33, in module
myP = parent()
File string, line 2, in __init__
File build/bdist.linux-x86_64/egg/sqlalchemy/orm/instrumentation.py, line
317, in _new_state_if_none
File build/bdist.linux-x86_64/egg/sqlalchemy/util/langhelpers.py, line
612, in __get__
File build/bdist.linux-x86_64/egg/sqlalchemy/orm/instrumentation.py, line
152, in _state_constructor
File build/bdist.linux-x86_64/egg/sqlalchemy/event.py, line 420, in
__call__
File build/bdist.linux-x86_64/egg/sqlalchemy/orm/mapper.py, line 2262, in
_event_on_first_init
File build/bdist.linux-x86_64/egg/sqlalchemy/orm/mapper.py, line 2171, in
configure_mappers
File build/bdist.linux-x86_64/egg/sqlalchemy/orm/mapper.py, line 1281, in
_post_configure_properties
File build/bdist.linux-x86_64/egg/sqlalchemy/orm/interfaces.py, line 231,
in init
File build/bdist.linux-x86_64/egg/sqlalchemy/orm/properties.py, line
1029, in do_init
File build/bdist.linux-x86_64/egg/sqlalchemy/orm/properties.py, line
1084, in _process_dependent_arguments
File build/bdist.linux-x86_64/egg/sqlalchemy/util/langhelpers.py, line
612, in __get__
File build/bdist.linux-x86_64/egg/sqlalchemy/orm/properties.py, line
1003, in mapper
File
build/bdist.linux-x86_64/egg/sqlalchemy/ext/declarative/clsregistry.py,
line 266, in return_cls
sqlalchemy.exc.InvalidRequestError: When initializing mapper
Mapper|parent|parent, expression 'child' failed to locate a name (name
'child' is not defined). If this is a class name, consider adding this
relationship() to the class '__main__.parent' class after both dependent
classes have been defined.
This is expected. The call to declarative_base() creates a registry for classes
that inherit from that Base. When you use strings instead of classes in your
relationships (as you do when creating childRelationship), those strings are
looked up in the registry to find the class that they
Re: [sqlalchemy] one to many relationship
That ForeignKey definition looks wrong - it should point at a column, not a class. I think you want: ForeignKey(parent.parent_id) Interesting, thanks, in the case where I use the same 'Base' for both classes, that solves it. In PostgreSQL the column definition isn't required if the column name is the same in both tables. I thought I'd tried explicitly setting it in sqlalchemy, but I was probably doing that in the case with Base2 and not the plain Base. This is expected. The call to declarative_base() creates a registry for classes that inherit from that Base. When you use strings instead of classes in your relationships (as you do when creating childRelationship), those strings are looked up in the registry to find the class that they refer to. Because ‘child’ and ‘parent’ exist in different registries, sqlalchemy can’t resolve the string. Interesting. So if I have a separate file for each table, I have to make sure they all use the same Base. I can do that. Thank you very much for the quick, helpful, and interesting reply! +1 :-) etc. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
