Some more testing...
- Note, I'm just trying to get the joins working before using the queries with
session etc
>>> m = db.devmap_manufacturer
>>> d = db.devmap_device
>>> d.join(m).first()
MappedDevmap_device(id=1,device_name='otc701',manufacturer_id=1)
>>> m = db.with_labels(db.devmap_manufacturer)
>>> d = db.with_labels(db.devmap_device)
>>> d.join(m)
<sqlalchemy.orm.query.Query object at 0x00F949D0
>>> d.join(m).first()
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
File
"c:\python25\lib\site-packages\SQLAlchemy-0.5.2-py2.5.egg\sqlalchemy\orm\query.py",
line 1219, in first
ret = list(self[0:1])
File
"c:\python25\lib\site-packages\SQLAlchemy-0.5.2-py2.5.egg\sqlalchemy\orm\query.py",
line 1140, in __getitem__
return list(res)
File
"c:\python25\lib\site-packages\SQLAlchemy-0.5.2-py2.5.egg\sqlalchemy\orm\query.py",
line 1280, in __iter__
return self._execute_and_instances(context)
File
"c:\python25\lib\site-packages\SQLAlchemy-0.5.2-py2.5.egg\sqlalchemy\orm\query.py",
line 1283, in _execute_and_in
stances
result = self.session.execute(querycontext.statement, params=self._params,
mapper=self._mapper_zero_or_none())
File
"c:\python25\lib\site-packages\SQLAlchemy-0.5.2-py2.5.egg\sqlalchemy\orm\session.py",
line 755, in execute
clause, params or {})
File
"c:\python25\lib\site-packages\SQLAlchemy-0.5.2-py2.5.egg\sqlalchemy\engine\base.py",
line 824, in execute
return Connection.executors[c](self, object, multiparams, params)
File
"c:\python25\lib\site-packages\SQLAlchemy-0.5.2-py2.5.egg\sqlalchemy\engine\base.py",
line 874, in _execute_claus
eelement
return self.__execute_context(context)
File
"c:\python25\lib\site-packages\SQLAlchemy-0.5.2-py2.5.egg\sqlalchemy\engine\base.py",
line 896, in __execute_cont
ext
self._cursor_execute(context.cursor, context.statement,
context.parameters[0], context=context)
File
"c:\python25\lib\site-packages\SQLAlchemy-0.5.2-py2.5.egg\sqlalchemy\engine\base.py",
line 950, in _cursor_execut
e
self._handle_dbapi_exception(e, statement, parameters, cursor, context)
File
"c:\python25\lib\site-packages\SQLAlchemy-0.5.2-py2.5.egg\sqlalchemy\engine\base.py",
line 931, in _handle_dbapi_
exception
raise exc.DBAPIError.instance(statement, parameters, e,
connection_invalidated=is_disconnect)
sqlalchemy.exc.ProgrammingError: (ProgrammingError) table name "foo" specified
more than once
'SELECT foo.devmap_device_id AS foo_devmap_device_id,
foo.devmap_device_device_name AS foo_devmap_device_device_name, f
oo.devmap_device_manufacturer_id AS foo_devmap_device_manufacturer_id \nFROM
(SELECT devmap_device.id AS devmap_device_i
d, devmap_device.device_name AS devmap_device_device_name,
devmap_device.manufacturer_id AS devmap_device_manufacturer_i
d \nFROM devmap_device) AS foo JOIN (SELECT devmap_manufacturer.id AS
devmap_manufacturer_id, devmap_manufacturer.manufa
cturer_name AS devmap_manufacturer_manufacturer_name \nFROM
devmap_manufacturer) AS foo ON foo.devmap_manufacturer_id =
foo.devmap_device_manufacturer_id \n LIMIT 1 OFFSET 0' {}
>>>
Tried pasting the SQL into psql and it basically says the same thing...
ERROR: table name "foo" specified more than once
********** Error **********
ERROR: table name "foo" specified more than once
SQL state: 42712
I'm trying to do the equivilent of this query, but it seems insanely difficult
to get it working:
select
manufacturer_name, device_name
from
devmap_device
join
devmap_manufacturer
on
manufacturer_id = devmap_manufacturer.id
where
manufacturer_name = 'chockia'
and
device_name = '8210'
----- Original Message ----
From: Stuart Axon <[email protected]>
To: [email protected]
Sent: Thursday, February 12, 2009 3:41:39 PM
Subject: [sqlalchemy] sqlsoup + with_labels not working on one table?
I've got a fairly simple database made with django and accessed via sqlsoup.
(finally got some time to take more of a look at the tutorial, which helped a
little :)
The two tables are
devmap_device
id
device_name
manufacturer_id
devmap_manufacturer
id
manufacturer_name
I tried to make a join to retrieve a device/manufacturer...
>>> from db import devmap_device, devmap_manufacturer
>>>
>>> e = create_engine('postgres://user:[email protected]/xxxx')
>>> db = SqlSoup(MetaData(e))
>>>
>>> db.devmap_device.join(db.devmap_manufacturer,
>>> db.devmap_device.manufacturer_id == db.devmap_manufacturer.id).first()
>>>
### At this point it says:
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
File "<string>", line 1, in <lambda>
File
"c:\python25\lib\site-packages\SQLAlchemy-0.5.2-py2.5.egg\sqlalchemy\util.py",
line 212, in go
return fn(*args, **kw)
File
"c:\python25\lib\site-packages\SQLAlchemy-0.5.2-py2.5.egg\sqlalchemy\orm\query.py",
line 873, in join
return self.__join(props, outerjoin=False, create_aliases=aliased,
from_joinpoint=from_joinpoint)
File "<string>", line 1, in <lambda>
File
"c:\python25\lib\site-packages\SQLAlchemy-0.5.2-py2.5.egg\sqlalchemy\orm\query.py",
line 52, in generate
fn(self, *args[1:], **kw)
File
"c:\python25\lib\site-packages\SQLAlchemy-0.5.2-py2.5.egg\sqlalchemy\orm\query.py",
line 1071, in __join
clause = orm_join(clause, right_entity, onclause, isouter=outerjoin,
join_to_left=join_to_left)
File
"c:\python25\lib\site-packages\SQLAlchemy-0.5.2-py2.5.egg\sqlalchemy\orm\util.py",
line 432, in join
return _ORMJoin(left, right, onclause, isouter, join_to_left)
File
"c:\python25\lib\site-packages\SQLAlchemy-0.5.2-py2.5.egg\sqlalchemy\orm\util.py",
line 409, in __init__
expression.Join.__init__(self, left, right, onclause, isouter)
File
"c:\python25\lib\site-packages\SQLAlchemy-0.5.2-py2.5.egg\sqlalchemy\sql\expression.py",
line 2479, in __init__
self.right = _selectable(right).self_group()
File
"c:\python25\lib\site-packages\SQLAlchemy-0.5.2-py2.5.egg\sqlalchemy\sql\expression.py",
line 968, in _selectable
raise exc.ArgumentError("Object %r is not a Selectable and does not
implement `__selectable__()`" % element)
sqlalchemy.exc.ArgumentError: Object
<sqlalchemy.sql.expression._BinaryExpression object at 0x00FAB4B0> is not a
Selecta
ble and does not implement `__selectable__()`
###
Also, if I try
>>> db.with_labels(db.devmap_manufacturer)
It says:
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
File
"c:\python25\lib\site-packages\SQLAlchemy-0.5.2-py2.5.egg\sqlalchemy\ext\sqlsoup.py",
line 534, in with_labels
return
self.map(expression._selectable(item).select(use_labels=True).alias('foo'))
File
"c:\python25\lib\site-packages\SQLAlchemy-0.5.2-py2.5.egg\sqlalchemy\ext\sqlsoup.py",
line 528, in map
t = class_for_table(selectable, **kwargs)
File
"c:\python25\lib\site-packages\SQLAlchemy-0.5.2-py2.5.egg\sqlalchemy\ext\sqlsoup.py",
line 448, in class_for_tabl
e
engine_encoding = selectable.metadata.bind.dialect.encoding
AttributeError: 'Alias' object has no attribute 'metadata'
...which seems strange as
>>> db.with_labels(db.devmap_device)
works fine !
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected]
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
-~----------~----~----~----~------~----~------~--~---
