[sqlalchemy] Re: multiple foreign keys to the same table
On Aug 23, 2008, at 1:56 PM, Rob wrote:
Hi,
I'm using sqlalchemy 0.5 beta 3 and I am trying to have a Call object
that contains two relations to a Contact object. One is the callee
and the other is the caller. The code is as follows:
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Table, Column, Integer, String, MetaData,
ForeignKey
from sqlalchemy.orm import relation, backref, mapper
Base = declarative_base()
metadata = Base.metadata
contact_table = Table('contact', metadata,
Column('id', Integer, primary_key=True),
Column('first_name', String(20)),
Column('last_name', String(30)))
call_table = Table('call', metadata,
Column('id', Integer, primary_key=True),
Column('subject', String(255)),
Column('callee_id', Integer, ForeignKey('contact.id')),
Column('caller_id', Integer, ForeignKey('contact.id')))
class Contact(object):
def __init__(self, first_name, last_name):
self.first_name = first_name
self.last_name = last_name
def __repr__(self):
return self.first_name + ' ' + self.last_name
mapper(Contact, contact_table)
class Call(object):
def __init__(self, subject, callee, caller):
self.subject = subject
self.callee = callee
self.caller = caller
def __repr__(self):
return self.subject
mapper(Call, call_table, properties={
'callee':relation(Call,
primaryjoin=call_table.c.callee_id==contact_table.c.id,
backref='callee_calls'),
'caller':relation(Call,
primaryjoin=call_table.c.caller_id==contact_table.c.id,
backref='caller_calls')
})
c = Contact('my_first_name', 'my_last_name')
I get a long error:
sqlalchemy.exc.ArgumentError: Could not locate any equated, locally
mapped column pairs for primaryjoin condition 'call.caller_id =
contact.id' on relation Call.caller. For more relaxed rules on join
conditions, the relation may be marked as viewonly=True.
callee and caller relate Call to another Call. The join condition
given does not connect call_table to itself and instead connects to
contact_table which is not involved in the relation(). Based on the
table it seems like callee and caller should relate to a Contact,
not a Call.
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: multiple foreign keys to the same table
Hi,
You're right:
mapper(Call, call_table, properties={
'callee':relation(Contact,
primaryjoin=call_table.c.callee_id==contact_table.c.id,
backref='callee_calls'),
'caller':relation(Contact,
primaryjoin=call_table.c.caller_id==contact_table.c.id,
backref='caller_calls')
})
did the trick.
Thanks!
On Aug 23, 8:58 pm, Michael Bayer [EMAIL PROTECTED] wrote:
On Aug 23, 2008, at 1:56 PM, Rob wrote:
Hi,
I'm using sqlalchemy 0.5 beta 3 and I am trying to have a Call object
that contains two relations to a Contact object. One is the callee
and the other is the caller. The code is as follows:
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Table, Column, Integer, String, MetaData,
ForeignKey
from sqlalchemy.orm import relation, backref, mapper
Base = declarative_base()
metadata = Base.metadata
contact_table = Table('contact', metadata,
Column('id', Integer, primary_key=True),
Column('first_name', String(20)),
Column('last_name', String(30)))
call_table = Table('call', metadata,
Column('id', Integer, primary_key=True),
Column('subject', String(255)),
Column('callee_id', Integer, ForeignKey('contact.id')),
Column('caller_id', Integer, ForeignKey('contact.id')))
class Contact(object):
def __init__(self, first_name, last_name):
self.first_name = first_name
self.last_name = last_name
def __repr__(self):
return self.first_name + ' ' + self.last_name
mapper(Contact, contact_table)
class Call(object):
def __init__(self, subject, callee, caller):
self.subject = subject
self.callee = callee
self.caller = caller
def __repr__(self):
return self.subject
mapper(Call, call_table, properties={
'callee':relation(Call,
primaryjoin=call_table.c.callee_id==contact_table.c.id,
backref='callee_calls'),
'caller':relation(Call,
primaryjoin=call_table.c.caller_id==contact_table.c.id,
backref='caller_calls')
})
c = Contact('my_first_name', 'my_last_name')
I get a long error:
sqlalchemy.exc.ArgumentError: Could not locate any equated, locally
mapped column pairs for primaryjoin condition 'call.caller_id =
contact.id' on relation Call.caller. For more relaxed rules on join
conditions, the relation may be marked as viewonly=True.
callee and caller relate Call to another Call. The join condition
given does not connect call_table to itself and instead connects to
contact_table which is not involved in the relation(). Based on the
table it seems like callee and caller should relate to a Contact,
not a Call.
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Multiple Foreign Keys
Yep, that was exactly what I needed! I've just ordered the book so
hopefully I won't end up struggling on the little things like this
again! Thanks for your help, much appreciated!
Ally
On Aug 18, 11:30 pm, Michael Bayer [EMAIL PROTECTED] wrote:
On Aug 18, 12:30 pm, Ally [EMAIL PROTECTED] wrote:
Hi all,
I’m fairly new to DBs and SQA and I’m having a few issues with
multiple foreign keys. Essentially, I have a “Character” table with
Character IDs and their associated name, and a Stats table, with
containing data about various events, with two separate columns both
with FKs to the Character ID table.
These tables are stored in on my HDD relected at runtime, using the
Table('Character', meta, autoload=True) format. My problems arise
whenever I try and join these tables, I keep getting an error similar
to this:
“Can't determine join between Stats and 'Character'; tables have more
than one foreign key constraint relationship between them. Please
specify the 'onclause' of this join explicitly.”
Fair enough, but when I try to do this, along the lines of:
s =
join(Stats,Character,DeathKill.c.OBj1_uid==Character.c.character_uid)
I get:
“sqlalchemy.exc.ArgumentError: Not an executable clause: [DeathKill]
JOIN [Character] ON [DeathKill].killer_uid =
[Character].character_uid”
Any suggestions or pointers would be greatly appreciated! Sorry I
can’t post more code just now as I not near my work PC!
There's some context missing here that would help with an answer. If
you are just taking s and saying something along the lines of
s.execute(), you'd need to first convert s into a select()
construct using something like select([stats_table]).select_from(s).
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Multiple Foreign Keys
On Aug 18, 12:30 pm, Ally [EMAIL PROTECTED] wrote:
Hi all,
I’m fairly new to DBs and SQA and I’m having a few issues with
multiple foreign keys. Essentially, I have a “Character” table with
Character IDs and their associated name, and a Stats table, with
containing data about various events, with two separate columns both
with FKs to the Character ID table.
These tables are stored in on my HDD relected at runtime, using the
Table('Character', meta, autoload=True) format. My problems arise
whenever I try and join these tables, I keep getting an error similar
to this:
“Can't determine join between Stats and 'Character'; tables have more
than one foreign key constraint relationship between them. Please
specify the 'onclause' of this join explicitly.”
Fair enough, but when I try to do this, along the lines of:
s =
join(Stats,Character,DeathKill.c.OBj1_uid==Character.c.character_uid)
I get:
“sqlalchemy.exc.ArgumentError: Not an executable clause: [DeathKill]
JOIN [Character] ON [DeathKill].killer_uid =
[Character].character_uid”
Any suggestions or pointers would be greatly appreciated! Sorry I
can’t post more code just now as I not near my work PC!
There's some context missing here that would help with an answer. If
you are just taking s and saying something along the lines of
s.execute(), you'd need to first convert s into a select()
construct using something like select([stats_table]).select_from(s).
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Multiple foreign keys to the same table from another table
On Jul 20, 11:57 pm, Sean Davis [EMAIL PROTECTED] wrote:
I have a table that has two columns that reference the same table in a
foreign-key relationship. What do I need to specify and where to
avoid the error below?
class 'sqlalchemy.exceptions.ArgumentError': Error determining
primary and/or secondary join for relationship 'FeatureLoc.feature
(Feature)'. If the underlying error cannot be corrected, you should
specify the 'primaryjoin' (and 'secondaryjoin', if there is an
association table present) keyword arguments to the relation()
function (or for backrefs, by specifying the backref using the
backref() function with keyword arguments) to explicitly specify the
join conditions. Nested error is Can't determine join between
'featureloc' and 'feature'; tables have more than one foreign key
constraint relationship between them. Please specify the 'onclause' of
this join explicitly.
I am reflecting these tables from the database:
feature_t = Table('feature',metadata,autoload=True)
featureloc_t = Table('featureloc',metadata,autoload=True)
class Feature(object):
pass
class FeatureLoc(object):
pass
sqlalchemy.orm.mapper(Feature,feature_t,properties={
'elements' : relation(Element,backref='feature')
})
sqlalchemy.orm.mapper(FeatureLoc,featureloc_t,properties={
'srcfeature' : relation(Feature),
'feature': relation(Feature)
})
Looks like this will do it:
sqlalchemy.orm.mapper(FeatureLoc,featureloc_t,properties={
'srcfeature' :
relation(Feature,primaryjoin=featureloc_t.c.srcfeature_id==feature_t.c.id,backref='features'),
'feature':
relation(Feature,primaryjoin=featureloc_t.c.feature_id==feature_t.c.id)
})
Sorry for the noise.
Sean
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Multiple Foreign Keys to the same table, problems with second object.
Excellent, that (expire, refresh) works. Thank-you very much. Is there a reason why it sometimes works? On Feb 20, 11:47 pm, Michael Bayer [EMAIL PROTECTED] wrote: On Feb 20, 2007, at 12:01 PM, Ali wrote: u = User(name='Ali') u.flush() r = Receipt(user_id=u.id) r.flush() r.user is u True r.cancelled_user_id = u.id r.flush() r.cancelled_user is u True oh sorry, i missed this one. when you say r.cancelled_user_id = u.id, that will send the right SQL to the database but isnt going to change the r.cancelled_user property to anything unless you refresh ()/expire() the Receipt object. references: http://www.sqlalchemy.org/docs/ unitofwork.myt#unitofwork_api_flush_whatishttp://www.sqlalchemy.org/trac/wiki/ FAQ#Isetthefoo_idattributeonmyinstanceto7butthefooattributeisstillNonesh ouldntithaveloadedFoowithid7 --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Multiple Foreign Keys to the same table, problems with second object.
the cancelled_user may not have been lazy loaded before you flush() ed. On Feb 21, 2007, at 6:09 AM, Ali wrote: Excellent, that (expire, refresh) works. Thank-you very much. Is there a reason why it sometimes works? On Feb 20, 11:47 pm, Michael Bayer [EMAIL PROTECTED] wrote: On Feb 20, 2007, at 12:01 PM, Ali wrote: u = User(name='Ali') u.flush() r = Receipt(user_id=u.id) r.flush() r.user is u True r.cancelled_user_id = u.id r.flush() r.cancelled_user is u True oh sorry, i missed this one. when you say r.cancelled_user_id = u.id, that will send the right SQL to the database but isnt going to change the r.cancelled_user property to anything unless you refresh ()/expire() the Receipt object. references: http://www.sqlalchemy.org/docs/ unitofwork.myt#unitofwork_api_flush_whatishttp:// www.sqlalchemy.org/trac/wiki/ FAQ#Isetthefoo_idattributeonmyinstanceto7butthefooattributeisstillNon esh ouldntithaveloadedFoowithid7 --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Multiple Foreign Keys to the same table, problems with second object.
On Feb 20, 12:01 pm, Ali [EMAIL PROTECTED] wrote:
Here is my code (with other fields removed):
stockreceipt_table = Table('stockreceipt', meta,
Column('user_id', Integer, ForeignKey('user.id')),
# Cancellation
Column('cancelled_user_id', Integer, ForeignKey('user.id')),
)
class Receipt(object):
Stock Receipt
assign_mapper(ctx, Receipt, stockreceipt_table,
properties=dict(
user=relation(User, backref='receipts',
primaryjoin=stockreceipt_table.c.user_id ==
users_table.c.id),
cancelled_user=relation(User, backref='cancelled_receipts',
lazy=False,
primaryjoin=stockreceipt_table.c.cancelled_user_id ==
users_table.c.id),
)
that cant be right, since theres no primary key expressed either in
the stockreceipt_table or in your Receipt mapper.
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Multiple Foreign Keys to the same table, problems with second object.
I left out the fields for brevity of example. They both have an id
column defined as primary keys.
On 20 Feb, 17:11, Michael Bayer [EMAIL PROTECTED] wrote:
On Feb 20, 12:01 pm, Ali [EMAIL PROTECTED] wrote:
Here is my code (with other fields removed):
stockreceipt_table = Table('stockreceipt', meta,
Column('user_id', Integer, ForeignKey('user.id')),
# Cancellation
Column('cancelled_user_id', Integer, ForeignKey('user.id')),
)
class Receipt(object):
Stock Receipt
assign_mapper(ctx, Receipt, stockreceipt_table,
properties=dict(
user=relation(User, backref='receipts',
primaryjoin=stockreceipt_table.c.user_id ==
users_table.c.id),
cancelled_user=relation(User, backref='cancelled_receipts',
lazy=False,
primaryjoin=stockreceipt_table.c.cancelled_user_id ==
users_table.c.id),
)
that cant be right, since theres no primary key expressed either in
the stockreceipt_table or in your Receipt mapper.
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Multiple Foreign Keys to the same table, problems with second object.
By both, I mean user and receipt tables
On 20 Feb, 17:12, Ali [EMAIL PROTECTED] wrote:
I left out the fields for brevity of example. They both have an id
column defined as primary keys.
On 20 Feb, 17:11, Michael Bayer [EMAIL PROTECTED] wrote:
On Feb 20, 12:01 pm, Ali [EMAIL PROTECTED] wrote:
Here is my code (with other fields removed):
stockreceipt_table = Table('stockreceipt', meta,
Column('user_id', Integer, ForeignKey('user.id')),
# Cancellation
Column('cancelled_user_id', Integer, ForeignKey('user.id')),
)
class Receipt(object):
Stock Receipt
assign_mapper(ctx, Receipt, stockreceipt_table,
properties=dict(
user=relation(User, backref='receipts',
primaryjoin=stockreceipt_table.c.user_id ==
users_table.c.id),
cancelled_user=relation(User, backref='cancelled_receipts',
lazy=False,
primaryjoin=stockreceipt_table.c.cancelled_user_id ==
users_table.c.id),
)
that cant be right, since theres no primary key expressed either in
the stockreceipt_table or in your Receipt mapper.
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Multiple Foreign Keys to the same table, problems with second object.
Ok,
from sqlalchemy import *
from sqlalchemy.ext.assignmapper import assign_mapper
from sqlalchemy.ext.sessioncontext import SessionContext
meta = DynamicMetaData()
ctx = SessionContext(create_session)
def make_engine(uri=None):
if uri is None:
uri = 'sqlite:///:memory:'
engine = create_engine(uri, echo=True)
return engine
def connect(uri=None):
meta.connect(make_engine(uri=uri))
users_table = Table('user', meta,
Column('id', Integer, autoincrement=True, primary_key=True),
Column('name', String),
Column('password', String),
Column('registration', String),
)
class User(object):
User
def get_is_admin(self):
return self.id == 1
is_admin = property(get_is_admin)
def __str__(self):
s = self.name
if self.registration:
s = '%s (%s)' % (s, self.registration)
return s
assign_mapper(ctx, User, users_table)
stockreceipt_table = Table('stockreceipt', meta,
Column('id', Integer, autoincrement=True, primary_key=True),
Column('drug_id', Integer, ForeignKey('drug.id')),
Column('date', Date),
Column('quantity', Integer),
Column('supplier_id', Integer, ForeignKey('supplier.id')),
Column('user_id', Integer, ForeignKey('user.id')),
Column('cancelled', Boolean),
Column('cancelled_reason', String),
Column('cancelled_user_id', Integer, ForeignKey('user.id')),
Column('internal_transfer', Boolean),
Column('invoice_number', String),
Column('internal_from', String),
)
class Receipt(object):
Stock Receipt
def get_cancelled_user_hack(self):
return User.get_by(id=self.cancelled_user_id)
cancelled_user_hack = property(get_cancelled_user_hack)
assign_mapper(ctx, Receipt, stockreceipt_table,
properties=dict(
user=relation(User, backref='receipts',
primaryjoin=stockreceipt_table.c.user_id ==
users_table.c.id),
cancelled_user=relation(User, backref='cancelled_receipts',
lazy=False,
primaryjoin=stockreceipt_table.c.cancelled_user_id ==
users_table.c.id),
supplier=relation(Supplier, backref='receipts'),
drug=relation(Drug, backref='receipts'),
)
)
meta.create_all()
connect()
u = User(name='Ali')
u.flush()
r = Receipt(user_id=u.id)
r.flush()
r.user is u
True
r.cancelled_user_id = u.id
r.flush()
r.cancelled_user is u
True
Now, as I mentioned, r.cancelled_user is set correctly in this
example, but not (always) in my application, although it is used
similarly. (I am happy to paste 2000 lines of PyGTK if you like). When
the attribute is accessed, there is no SQL output at all, although in
this example there is.
Thanks for any assistance,
Ali
On Feb 20, 6:04 pm, Michael Bayer [EMAIL PROTECTED] wrote:
why dont you attach a script that runs completely ? use a sqlite://
database.
On Feb 20, 12:14 pm, Ali [EMAIL PROTECTED] wrote:
By both, I mean user and receipt tables
On 20 Feb, 17:12, Ali [EMAIL PROTECTED] wrote:
I left out the fields for brevity of example. They both have an id
column defined as primary keys.
On 20 Feb, 17:11, Michael Bayer [EMAIL PROTECTED] wrote:
On Feb 20, 12:01 pm, Ali [EMAIL PROTECTED] wrote:
Here is my code (with other fields removed):
stockreceipt_table = Table('stockreceipt', meta,
Column('user_id', Integer, ForeignKey('user.id')),
# Cancellation
Column('cancelled_user_id', Integer, ForeignKey('user.id')),
)
class Receipt(object):
Stock Receipt
assign_mapper(ctx, Receipt, stockreceipt_table,
properties=dict(
user=relation(User, backref='receipts',
primaryjoin=stockreceipt_table.c.user_id ==
users_table.c.id),
cancelled_user=relation(User, backref='cancelled_receipts',
lazy=False,
primaryjoin=stockreceipt_table.c.cancelled_user_id ==
users_table.c.id),
)
that cant be right, since theres no primary key expressed either in
the stockreceipt_table or in your Receipt mapper.
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Multiple Foreign Keys to the same table, problems with second object.
well I cant run the program since its not complete (no Drug, Supplier
class/tables defined, etc), but if id hazard a guess id say you
shouldnt be calling flush() on your class instances, since it will
not flush any changes on any dependencies. call ctx.current.flush()
instead.
On Feb 20, 2007, at 1:40 PM, Ali wrote:
Ok,
from sqlalchemy import *
from sqlalchemy.ext.assignmapper import assign_mapper
from sqlalchemy.ext.sessioncontext import SessionContext
meta = DynamicMetaData()
ctx = SessionContext(create_session)
def make_engine(uri=None):
if uri is None:
uri = 'sqlite:///:memory:'
engine = create_engine(uri, echo=True)
return engine
def connect(uri=None):
meta.connect(make_engine(uri=uri))
users_table = Table('user', meta,
Column('id', Integer, autoincrement=True, primary_key=True),
Column('name', String),
Column('password', String),
Column('registration', String),
)
class User(object):
User
def get_is_admin(self):
return self.id == 1
is_admin = property(get_is_admin)
def __str__(self):
s = self.name
if self.registration:
s = '%s (%s)' % (s, self.registration)
return s
assign_mapper(ctx, User, users_table)
stockreceipt_table = Table('stockreceipt', meta,
Column('id', Integer, autoincrement=True, primary_key=True),
Column('drug_id', Integer, ForeignKey('drug.id')),
Column('date', Date),
Column('quantity', Integer),
Column('supplier_id', Integer, ForeignKey('supplier.id')),
Column('user_id', Integer, ForeignKey('user.id')),
Column('cancelled', Boolean),
Column('cancelled_reason', String),
Column('cancelled_user_id', Integer, ForeignKey('user.id')),
Column('internal_transfer', Boolean),
Column('invoice_number', String),
Column('internal_from', String),
)
class Receipt(object):
Stock Receipt
def get_cancelled_user_hack(self):
return User.get_by(id=self.cancelled_user_id)
cancelled_user_hack = property(get_cancelled_user_hack)
assign_mapper(ctx, Receipt, stockreceipt_table,
properties=dict(
user=relation(User, backref='receipts',
primaryjoin=stockreceipt_table.c.user_id ==
users_table.c.id),
cancelled_user=relation(User, backref='cancelled_receipts',
lazy=False,
primaryjoin=stockreceipt_table.c.cancelled_user_id ==
users_table.c.id),
supplier=relation(Supplier, backref='receipts'),
drug=relation(Drug, backref='receipts'),
)
)
meta.create_all()
connect()
u = User(name='Ali')
u.flush()
r = Receipt(user_id=u.id)
r.flush()
r.user is u
True
r.cancelled_user_id = u.id
r.flush()
r.cancelled_user is u
True
Now, as I mentioned, r.cancelled_user is set correctly in this
example, but not (always) in my application, although it is used
similarly. (I am happy to paste 2000 lines of PyGTK if you like). When
the attribute is accessed, there is no SQL output at all, although in
this example there is.
Thanks for any assistance,
Ali
On Feb 20, 6:04 pm, Michael Bayer [EMAIL PROTECTED] wrote:
why dont you attach a script that runs completely ? use a
sqlite://
database.
On Feb 20, 12:14 pm, Ali [EMAIL PROTECTED] wrote:
By both, I mean user and receipt tables
On 20 Feb, 17:12, Ali [EMAIL PROTECTED] wrote:
I left out the fields for brevity of example. They both have an id
column defined as primary keys.
On 20 Feb, 17:11, Michael Bayer [EMAIL PROTECTED] wrote:
On Feb 20, 12:01 pm, Ali [EMAIL PROTECTED] wrote:
Here is my code (with other fields removed):
stockreceipt_table = Table('stockreceipt', meta,
Column('user_id', Integer, ForeignKey('user.id')),
# Cancellation
Column('cancelled_user_id', Integer, ForeignKey('user.id')),
)
class Receipt(object):
Stock Receipt
assign_mapper(ctx, Receipt, stockreceipt_table,
properties=dict(
user=relation(User, backref='receipts',
primaryjoin=stockreceipt_table.c.user_id ==
users_table.c.id),
cancelled_user=relation(User,
backref='cancelled_receipts',
lazy=False,
primaryjoin=stockreceipt_table.c.cancelled_user_id ==
users_table.c.id),
)
that cant be right, since theres no primary key expressed
either in
the stockreceipt_table or in your Receipt mapper.
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Multiple Foreign Keys to the same table, problems with second object.
Thanks for the advice, and sorry I didn't provide the correct
information, but ctx.current.flush() makes no difference.
r.cancelled_user is (nearly: 19/20) always None immediately after the
assign and flush with no database access being made. Interestingly,
the user_id/user relationship is always correct
I will just have to stick to using the hack on the Receipt object to
get the cancelled_user until I am more familiar with SA to work out
what is going on.
Ali
Missing Tables:
suppliers_table = Table('supplier', meta,
Column('id', Integer, autoincrement=True, primary_key=True),
Column('name', String),
Column('address', String),
Column('postcode', String),
Column('telephone', String),
)
class Supplier(NamedObject):
Supplier
def get_long_string(self):
return '%s (%s %s %s)' % (self.name, self.address,
self.postcode, self.telephone)
assign_mapper(ctx, Supplier, suppliers_table)
forms_table = Table('form', meta,
Column('id', Integer, autoincrement=True, primary_key=True),
Column('name', String),
)
class Form(NamedObject):
Drug Form
assign_mapper(ctx, Form, forms_table)
drugs_table = Table('drug', meta,
Column('id', Integer, autoincrement=True, primary_key=True),
Column('name', String),
Column('strength', String),
Column('stock', Integer),
Column('form_id', Integer, ForeignKey('form.id'))
)
class Drug(object):
Drug Definition
def __str__(self):
return '%s (%s %s)' % (self.name, self.form, self.strength)
str = property(__str__)
assign_mapper(ctx, Drug, drugs_table,
properties = dict(
form = relation(Form, backref='drugs')
)
)
On Feb 20, 9:04 pm, Michael Bayer [EMAIL PROTECTED] wrote:
well I cant run the program since its not complete (no Drug, Supplier
class/tables defined, etc), but if id hazard a guess id say you
shouldnt be calling flush() on your class instances, since it will
not flush any changes on any dependencies. call ctx.current.flush()
instead.
On Feb 20, 2007, at 1:40 PM, Ali wrote:
Ok,
from sqlalchemy import *
from sqlalchemy.ext.assignmapper import assign_mapper
from sqlalchemy.ext.sessioncontext import SessionContext
meta = DynamicMetaData()
ctx = SessionContext(create_session)
def make_engine(uri=None):
if uri is None:
uri = 'sqlite:///:memory:'
engine = create_engine(uri, echo=True)
return engine
def connect(uri=None):
meta.connect(make_engine(uri=uri))
users_table = Table('user', meta,
Column('id', Integer, autoincrement=True, primary_key=True),
Column('name', String),
Column('password', String),
Column('registration', String),
)
class User(object):
User
def get_is_admin(self):
return self.id == 1
is_admin = property(get_is_admin)
def __str__(self):
s = self.name
if self.registration:
s = '%s (%s)' % (s, self.registration)
return s
assign_mapper(ctx, User, users_table)
stockreceipt_table = Table('stockreceipt', meta,
Column('id', Integer, autoincrement=True, primary_key=True),
Column('drug_id', Integer, ForeignKey('drug.id')),
Column('date', Date),
Column('quantity', Integer),
Column('supplier_id', Integer, ForeignKey('supplier.id')),
Column('user_id', Integer, ForeignKey('user.id')),
Column('cancelled', Boolean),
Column('cancelled_reason', String),
Column('cancelled_user_id', Integer, ForeignKey('user.id')),
Column('internal_transfer', Boolean),
Column('invoice_number', String),
Column('internal_from', String),
)
class Receipt(object):
Stock Receipt
def get_cancelled_user_hack(self):
return User.get_by(id=self.cancelled_user_id)
cancelled_user_hack = property(get_cancelled_user_hack)
assign_mapper(ctx, Receipt, stockreceipt_table,
properties=dict(
user=relation(User, backref='receipts',
primaryjoin=stockreceipt_table.c.user_id ==
users_table.c.id),
cancelled_user=relation(User, backref='cancelled_receipts',
lazy=False,
primaryjoin=stockreceipt_table.c.cancelled_user_id ==
users_table.c.id),
supplier=relation(Supplier, backref='receipts'),
drug=relation(Drug, backref='receipts'),
)
)
meta.create_all()
connect()
u = User(name='Ali')
u.flush()
r = Receipt(user_id=u.id)
r.flush()
r.user is u
True
r.cancelled_user_id = u.id
r.flush()
r.cancelled_user is u
True
Now, as I mentioned, r.cancelled_user is set correctly in this
example, but not (always) in my application, although it is used
similarly. (I am happy to paste 2000 lines of PyGTK if you like). When
the attribute is accessed, there is no SQL output at all, although in
this example there is.
Thanks for any assistance,
Ali
On Feb 20, 6:04 pm, Michael Bayer [EMAIL PROTECTED] wrote:
why dont you attach a
