Hi all,
Excuse the long post: I hope the extra info (code, SA logging info, db
queries) helps illustrate my problem.
BTW I'm using SQLAlchemy 0.1.4). BTW2: I haven't examined the SA code
yet to see what I'm doing wrong: I'm hoping some-one can point out my
stupidity before I spend too much time poring over the SA code :-)
I have a table with two columns referencing different rows of another
table. However the same FK is being inserted into both columns. I've
tried using eager loading and adding 'use_alias' to the relation() but
it doesn't help.
So where am I being an idiot?
Thanks
Alan
The promised extra info...
*) SA logging output (table create):
[2006-03-28 17:34:03,641] [engine]:
CREATE TABLE arp_images(
image_id SERIAL NOT NULL PRIMARY KEY,
file_name TEXT
)
[2006-03-28 17:34:03,641] [engine]: None
[2006-03-28 17:34:03,816] [engine]:
CREATE TABLE arp_others(
other_id SERIAL NOT NULL PRIMARY KEY,
image1_id INTEGER REFERENCES arp_images(image_id),
image2_id INTEGER REFERENCES arp_images(image_id),
name TEXT
)
[2006-03-28 17:34:03,816] [engine]: None
*) SA logging (insert):
[2006-03-28 17:34:03,970] [engine]: select
nextval('arp_images_image_id_seq')
[2006-03-28 17:34:03,971] [engine]: None
[2006-03-28 17:34:03,972] [engine]: INSERT INTO arp_images (image_id,
file_name) VALUES (%(image_id)s, %(file_name)s)
[2006-03-28 17:34:03,972] [engine]: {'file_name': 'image-1',
'image_id': 1L}
[2006-03-28 17:34:03,974] [engine]: select
nextval('arp_images_image_id_seq')
[2006-03-28 17:34:03,975] [engine]: None
[2006-03-28 17:34:03,975] [engine]: INSERT INTO arp_images (image_id,
file_name) VALUES (%(image_id)s, %(file_name)s)
[2006-03-28 17:34:03,976] [engine]: {'file_name': 'image-2',
'image_id': 2L}
[2006-03-28 17:34:03,978] [engine]: select
nextval('arp_others_other_id_seq')
[2006-03-28 17:34:03,978] [engine]: None
[2006-03-28 17:34:03,979] [engine]: INSERT INTO arp_others (other_id,
image1_id, image2_id, name) VALUES (%(other_id)s, %(image1_id)s,
%(image2_id)s, %(name)s)
[2006-03-28 17:34:03,979] [engine]: {'image2_id': 1L, 'name': 'other',
'other_id': 1L, 'image1_id': 1L}
*) Db contents
psql -U test -d asmpipe_dev -c "select * from arp_images"
image_id | file_name
----------+-----------
1 | image-1
2 | image-2
(2 rows)
psql -U test -d asmpipe_dev -c "select * from arp_others"
other_id | image1_id | image2_id | name
----------+-----------+-----------+-------
1 | 1 | 1 | other
(1 row)
image2_id should be 2.
*) SA logging & output of test prog:
[2006-03-28 17:54:03,717] [engine]: SELECT arp_others.image2_id AS
arp_others_image2_id, arp_others.name AS arp_others_name,
arp_others.image1_id AS arp_others_image1_id, arp_others.other_id AS
arp_others_other_id
FROM arp_others
WHERE arp_others.name = %(arp_others_name)s
[2006-03-28 17:54:03,717] [engine]: {'arp_others_name': 'other'}
1 [2006-03-28 17:54:03,721] [engine]: SELECT arp_images.image_id AS
arp_images_image_id, arp_images.file_name AS arp_images_file_name
FROM arp_images
WHERE arp_images.image_id = %(arp_images_image_id)s AND
arp_images.image_id = %(arp_images_image_id_1)s
[2006-03-28 17:54:03,721] [engine]: {'arp_images_image_id': 1,
'arp_images_image_id_1': None}
[2006-03-28 17:54:03,723] [engine]: SELECT arp_images.image_id AS
arp_images_image_id, arp_images.file_name AS arp_images_file_name
FROM arp_images
WHERE arp_images.image_id = %(arp_images_image_id)s AND
arp_images.image_id = %(arp_images_image_id_1)s
[2006-03-28 17:54:03,723] [engine]: {'arp_images_image_id': 1,
'arp_images_image_id_1': None}
[Other(name="'other'", image1='None', image2='None')]
Other(name="'other'", image1='None', image2='None')
None
None
*) Offending test prog
from sqlalchemy import *
engine = create_engine("postgres",
{"database": "asmpipe_dev",
"host": "localhost",
"user": "test",
"password": "test"},
echo=True, echo_uow=True)
images = Table("arp_images", engine,
Column("image_id", Integer, primary_key=True),
Column("file_name", String))
others = Table("arp_others", engine,
Column("other_id", Integer, primary_key=True),
Column("image1_id", Integer,
ForeignKey("arp_images.image_id")),
Column("image2_id", Integer,
ForeignKey("arp_images.image_id")),
Column("name", String))
class Image(object):
def __init__(self, file_name=None):
self.file_name = file_name
def __repr__(self):
return "%s(file_name=%r)" % (self.__class__.__name__,
repr(self.file_name))
class Other(object):
def __init__(self, name=None, image1=None, image2=None):
self.name = name
self.image1 = image1
self.image2 = image2
def __repr__(self):
return "%s(name=%r, image1=%r, image2=%r)" %
(self.__class__.__name__, repr(self.name), repr(self.image1),
repr(self.image2))
Image.mapper = mapper(Image, images)
Other.mapper = mapper(Other, others,
properties={
"image1": relation(Image.mapper,uselist=False), #use_alias=True,
lazy=False),
"image2": relation(Image.mapper,uselist=False), #use_alias=True,
lazy=False),
})
if __name__ == "__main__":
# Drop tables
for t in (others, images):
try:
t.drop()
except SQLError, e:
pass
# Make tables
images.create()
others.create()
# Populate tables
i1 = Image(file_name="image-1")
i2 = Image(file_name="image-2")
o = Other(name="other", image1=i1, image2=i2)
objectstore.commit()
# Query tables
objectstore.clear()
other_rs = Other.mapper.select_by(name="other")
print len(other_rs), other_rs
other = other_rs[0]
print other
print other.image1
print other.image2
-------------------------------------------------------
This SF.Net email is sponsored by xPML, a groundbreaking scripting language
that extends applications into web and mobile media. Attend the live webcast
and join the prime developer group breaking into this new coding territory!
http://sel.as-us.falkag.net/sel?cmd=lnk&kid=110944&bid=241720&dat=121642
_______________________________________________
Sqlalchemy-users mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users
