[sqlalchemy] relationship trouble
Hi!
I added a new self-referential relationship to one of my tables but since I
couldn't find how to create the new column through SA I added it to the db
manually after checking how it's been done before.
The code looks like this (using elixir):
class User(Entity):
username = Field(String(80), primary_key=True)
# New fields
parent = ManyToOne('User')
children = OneToMany('User')
So I added a field in the db with the name parent_name since I noticed
thats how SA names foreign keys and I also indexed it with the name
ix_user_parent_name
Do I need to do anything else to get this to work or have I done anything
wrong?
What I get is: 'str' object has no attribute '_state' for that object
in _get_committed_attr_by_column.
I read that this means that I add a string to a relationship object, but I
could access the parents fields when testing.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To view this discussion on the web visit
https://groups.google.com/d/msg/sqlalchemy/-/RJ-iAdcQWDoJ.
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] relationship trouble when using UsageRecipes/Views
I am working on my localization stuff and run into a problem when I want
to add an relationship to a class.
I based my code on
http://www.sqlalchemy.org/trac/wiki/UsageRecipes/Views , but maybe I
messed something up.
The following works:
r1 = session.query(db.Region_LV).get(175)
print type(result.country_lv)
print r1
print r1.country_lv.name
with this output:
Region_LV(centralkey=175, country_lv=Country_LV(centralkey=83,
created_at=datetime.datetime(2011, 9, 7, 9, 39, 10, 702000),
created_by=None, dialcode=30, fk_language_id=2, id=83, is2code=u'GR',
iswinecountry=1, name=u'Gr\xe8ce', shortname=None, un3code=u'GRC',
updated_at=datetime.datetime(2011, 9, 7, 9, 39, 10, 702000),
updated_by=None, website1=None, website2=None),
created_at=datetime.datetime(2011, 9, 7, 9, 39, 11, 452000),
created_by=None, fk_country_id=83, fk_language_id=2, id=175,
name=u'Sud-Ouest', shortname=u'Sud-Ouest',
updated_at=datetime.datetime(2011, 9, 7, 9, 39, 11, 452000),
updated_by=None)
Grèce
The model for all this is:
class Region(DeclarativeBase, mix.StandardColumnMixin):
__tablename__ = u'region'
centralkey = sa.Column(sa.BigInteger())
name = sa.Column(sa.Unicode(length=50), nullable=False)
shortname = sa.Column(sa.Unicode(length=10))
fk_country_id = sautils.reference_col('country')
__localize_columns__ = ['name', 'shortname']
Region_L, Region_LV = sautils.CreateLocalized(Region())
Region_LV.country_lv = sao.relationship('Country_LV')
CreateLocalized creates the Region_LV based on the usage recipe.
Now if I add:
Region_LV.language = sao.relationship('Language')
I get:
sqlalchemy.exc.ArgumentError: Could not determine join condition between
parent/child tables on relationship Region_LV.language. Specify a
'primaryjoin' expression. If 'secondary' is present, 'secondaryjoin' is
needed as well.
or:
Region_LV.language = sao.relationship('Language',
primaryjoin=Region_LV.fk_language_id==Language.id)
I get:
sqlalchemy.exc.ArgumentError: Could not determine relationship direction
for primaryjoin condition 'country_lv.fk_language_id = language.id', on
relationship Region_LV.language. Ensure that the referencing Column
objects have a ForeignKey present, or are otherwise part of a
ForeignKeyConstraint on their parent Table, or specify the foreign_keys
parameter to this relationship.
or:
Region_LV.language = sao.relationship('Language',
primaryjoin=Country_LV.fk_language_id==Language.id,
foreign_keys=[Country_LV.__table__.c.fk_language_id])
I get:
sqlalchemy.exc.ArgumentError: Column-based expression object expected
for argument 'foreign_keys'; got: 'Country_LV.fk_language_id', type
type 'str'
sqlalchemy.exc.ArgumentError: Could not locate any foreign-key-equated,
locally mapped column pairs for primaryjoin condition
'country_lv.fk_language_id = language.id' on relationship
Region_LV.language. For more relaxed rules on join conditions, the
relationship may be marked as viewonly=True.
Tried with viewonly but couldn't make that work either.
I guess/think my problem is that I don't define a ForeignKeyConstraint
for the fk_language_id column but I haven't found how this is done as
the Country_LV view is created using from sqlalchemy.sql import table.
I'd appreciate any tips on how to get this to work.
Werner
--
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] relationship trouble when using UsageRecipes/Views
On Sep 7, 2011, at 4:40 AM, werner wrote:
or:
Region_LV.language = sao.relationship('Language',
primaryjoin=Country_LV.fk_language_id==Language.id,
foreign_keys=[Country_LV.__table__.c.fk_language_id])
I get:
sqlalchemy.exc.ArgumentError: Column-based expression object expected for
argument 'foreign_keys'; got: 'Country_LV.fk_language_id', type type 'str'
this one above is the one that doesn't make any sense (also its probably how
the relationship should be set up). Clearly x.__table__.c.somename is a
Column object, not a string. Something is up with what you're sending it.
I've tried to reproduce with no luck. See attached. Also I don't even need
the primaryjoin/foreignkeys if the originating Table upon which the view is
based has foreign keys to the parent.
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.schema import DDLElement
from sqlalchemy.sql import table
from sqlalchemy.ext import compiler
Base= declarative_base()
# first the view stuff as it is on the wiki
class CreateView(DDLElement):
def __init__(self, name, selectable):
self.name = name
self.selectable = selectable
class DropView(DDLElement):
def __init__(self, name):
self.name = name
@compiler.compiles(CreateView)
def compile(element, compiler, **kw):
return CREATE VIEW %s AS %s % (element.name, compiler.sql_compiler.process(element.selectable))
@compiler.compiles(DropView)
def compile(element, compiler, **kw):
return DROP VIEW %s % (element.name)
def view(name, metadata, selectable):
t = table(name)
for c in selectable.c:
c._make_proxy(t)
CreateView(name, selectable).execute_at('after-create', metadata)
DropView(name).execute_at('before-drop', metadata)
return t
# now do an example using declartive
Base = declarative_base()
class MoreStuff(Base):
__tablename__ = 'morestuff'
id = Column(Integer, primary_key=True)
# if you use this one, then we don't even need the primaryjoin/foriegn_keys
# on the view, the FK propagates out
# stuff_id = Column(Integer, ForeignKey('stuff.id'))
# ... but lets say it wasn't there
stuff_id = Column(Integer)
data = Column(String(50))
class MSView(Base):
__table__ = view(msview, Base.metadata,
MoreStuff.__table__.select()
)
__mapper_args__ = {primary_key:__table__.c.id}
# cannot reproduce your error.
class Stuff(Base):
__tablename__ = 'stuff'
id = Column(Integer, primary_key=True)
data = Column(String(50))
# works
#msview = relationship(MSView, primaryjoin=Stuff.id==MSView.stuff_id, foreign_keys=[MSView.stuff_id])
# works
#msview = relationship(MSView, primaryjoin=Stuff.id==MSView.stuff_id, foreign_keys=MSView.stuff_id)
# doesn't work, tries to resolve __table__ as a column
#msview = relationship(MSView, primaryjoin=Stuff.id==MSView.stuff_id, foreign_keys=[MSView.__table__.c.stuff_id])
# doesn't work, tries to resolve __table__ as a column
#msview = relationship(MSView, primaryjoin=Stuff.id==MSView.stuff_id, foreign_keys=MSView.__table__.c.stuff_id)
# works
#msview = relationship(MSView, primaryjoin=Stuff.id==MSView.stuff_id, foreign_keys=[MSView.stuff_id])
# works
msview = relationship(MSView, primaryjoin=Stuff.id==MSView.stuff_id, foreign_keys=[MSView.__table__.c.stuff_id])
# all is well
e = create_engine('sqlite://', echo=True)
Base.metadata.create_all(e)
e.execute(Stuff.__table__.insert(),
{'data':'apples'},
{'data':'pears'},
{'data':'oranges'},
{'data':'orange julius'},
{'data':'apple jacks'},
)
e.execute(MoreStuff.__table__.insert(),
{'stuff_id':3, 'data':'foobar'},
{'stuff_id':4, 'data':'foobar'}
)
s = Session(e)
print s.query(Stuff).options(eagerload(msview)).all()--
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] relationship trouble when using UsageRecipes/Views
Michael,
On 09/07/2011 05:10 PM, Michael Bayer wrote:
On Sep 7, 2011, at 4:40 AM, werner wrote:
or:
Region_LV.language = sao.relationship('Language',
primaryjoin=Country_LV.fk_language_id==Language.id,
foreign_keys=[Country_LV.__table__.c.fk_language_id])
I get:
sqlalchemy.exc.ArgumentError: Column-based expression object expected for argument
'foreign_keys'; got: 'Country_LV.fk_language_id', typetype 'str'
this one above is the one that doesn't make any sense (also its probably how
the relationship should be set up).
Sorry that was a copy/paste error on my part.
Clearly x.__table__.c.somename is a Column object, not a string.
Something is up with what you're sending it. I've tried to reproduce with
no luck. See attached. Also I don't even need the primaryjoin/foreignkeys if
the originating Table upon which the view is based has foreign keys to the
parent.
The additional column is in my case added in to the view as the base
table doesn't have it and it is filled by the stored procedure (which
combines the base table plus the localized table ...) , i.e.:
def view(name, metadata, selectable):
t = table(name)
for c in selectable.c:
c._make_proxy(t)
lc = sasql.column(stuff_id, sa.Integer)
t.append_column(lc)
But what I had overlooked/forgotten to include was the __mapper_args__
= {primary_key:__table__.c.id} line.
After adding this the relationship as shown at the beginning it works.
Thanks a lot for your answer and your patience.
Werner
--
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] relationship trouble when using UsageRecipes/Views
On Sep 7, 2011, at 11:53 AM, werner wrote:
Michael,
On 09/07/2011 05:10 PM, Michael Bayer wrote:
On Sep 7, 2011, at 4:40 AM, werner wrote:
or:
Region_LV.language = sao.relationship('Language',
primaryjoin=Country_LV.fk_language_id==Language.id,
foreign_keys=[Country_LV.__table__.c.fk_language_id])
I get:
sqlalchemy.exc.ArgumentError: Column-based expression object expected for
argument 'foreign_keys'; got: 'Country_LV.fk_language_id', typetype 'str'
this one above is the one that doesn't make any sense (also its probably how
the relationship should be set up).
Sorry that was a copy/paste error on my part.
Clearly x.__table__.c.somename is a Column object, not a string.
Something is up with what you're sending it. I've tried to reproduce
with no luck. See attached. Also I don't even need the
primaryjoin/foreignkeys if the originating Table upon which the view is
based has foreign keys to the parent.
The additional column is in my case added in to the view as the base table
doesn't have it and it is filled by the stored procedure (which combines the
base table plus the localized table ...) , i.e.:
def view(name, metadata, selectable):
t = table(name)
for c in selectable.c:
c._make_proxy(t)
lc = sasql.column(stuff_id, sa.Integer)
t.append_column(lc)
But what I had overlooked/forgotten to include was the __mapper_args__ =
{primary_key:__table__.c.id} line.
After adding this the relationship as shown at the beginning it works.
well that's also strange. If the mapper is not able to find a primary key,
the error is very clear:
sqlalchemy.exc.ArgumentError: Mapper Mapper|MSView|msview could not assemble
any primary key columns for mapped table 'msview'
not sure why that isn't what you were seeing.
--
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] relationship trouble when using UsageRecipes/Views
On 09/07/2011 05:58 PM, Michael Bayer wrote:
On Sep 7, 2011, at 11:53 AM, werner wrote:
Michael,
On 09/07/2011 05:10 PM, Michael Bayer wrote:
On Sep 7, 2011, at 4:40 AM, werner wrote:
or:
Region_LV.language = sao.relationship('Language',
primaryjoin=Country_LV.fk_language_id==Language.id,
foreign_keys=[Country_LV.__table__.c.fk_language_id])
I get:
sqlalchemy.exc.ArgumentError: Column-based expression object expected for argument
'foreign_keys'; got: 'Country_LV.fk_language_id', typetype 'str'
this one above is the one that doesn't make any sense (also its probably how
the relationship should be set up).
Sorry that was a copy/paste error on my part.
Clearly x.__table__.c.somename is a Column object, not a string.
Something is up with what you're sending it. I've tried to reproduce with
no luck. See attached. Also I don't even need the primaryjoin/foreignkeys if
the originating Table upon which the view is based has foreign keys to the
parent.
The additional column is in my case added in to the view as the base table
doesn't have it and it is filled by the stored procedure (which combines the
base table plus the localized table ...) , i.e.:
def view(name, metadata, selectable):
t = table(name)
for c in selectable.c:
c._make_proxy(t)
lc = sasql.column(stuff_id, sa.Integer)
t.append_column(lc)
But what I had overlooked/forgotten to include was the __mapper_args__ =
{primary_key:__table__.c.id} line.
After adding this the relationship as shown at the beginning it works.
well that's also strange. If the mapper is not able to find a primary key,
the error is very clear:
sqlalchemy.exc.ArgumentError: Mapper Mapper|MSView|msview could not assemble
any primary key columns for mapped table 'msview'
not sure why that isn't what you were seeing.
Just tried to step back and see what other change I have done which
would explain my problem, but I missed to commit to source control
(didn't bother as it didn't work - should have known better).
Werner
Werner
--
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.
