Given the same schema, mappers etc, I am trying to join the node table with itself. If I use the 'console' relation defined in mapper, sqlalchemy produces invalid sql.
query.join('consoleNodes').select_by(name='konsole23.xx.xxxx.com') INFO:sqlalchemy.engine.base.Engine.0x..74:SELECT ...... FROM node JOIN node ON node.console_id = node.node_id WHERE node.name = ? ORDER BY node.oid INFO:sqlalchemy.engine.base.Engine.0x..74:['konsole23.xx.xxxx.com'] This produces an ambigious column name. How do I get sqlalchemy to use an alias for the right hand side join table ? /kk On 3/27/07, Karthik Krishnamurthy <[EMAIL PROTECTED]> wrote: > > Another issue with the same setup: > > node = query.select_by(name='konsole19.xx.xxxx.com')[0] > print query.select_by(console=node) > > The debugging output shows this: > > INFO:sqlalchemy.engine.base.Engine.0x..74:SELECT node.cport AS node_cport, > node.type_id AS node_type_id, node.locshelf AS node_locshelf, node.s_timeAS > node_s_time, > node.site_id AS node_site_id, node.console_id AS node_console_id, > node.locside AS node_locside, node.locarea AS node_locarea, > node.netswitch_id AS node_netswitch_id, node.bmodule2 AS node_bmodule2, > node.sport AS node_sport, node.node_id AS node_node_id, node.bplug AS > node_bplug, node.bmodule AS node_bmodule, node.parent_id AS > node_parent_id, node.os_id AS node_os_id, node.bport2 AS node_bport2, > node.backplane AS node_backplane, node.prop_id AS node_prop_id, > node.status AS node_status, node.model_id AS node_model_id, node.locrow AS > node_locrow, node.serialno AS node_serialno, node.bootbox2_id AS > node_bootbox2_id, node.m_time AS node_m_time, node.ytag AS node_ytag, > node.bplug2 AS node_bplug2, node.loccage AS node_loccage, node.ponumber AS > node_ponumber, node.name AS node_name, node.racksize AS node_racksize, > node.bootbox_id AS node_bootbox_id, node.locroom AS node_locroom, > node.notes AS node_notes, node.bport AS node_bport, node.locrack AS > node_locrack, node.c_time AS node_c_time, node.pdu AS node_pdu > FROM node > WHERE (node.node_id = ?) AND node.console_id = node.node_id ORDER BY > node.oid > > It works when I say instead > print query.select_by (console_id=node.id) > > Shouldn't sqlalchemy be able to construct the right SQL from the > primaryjoin condition specified in the mapper > for 'console' ? > > /kk > > > This > > On 3/27/07, Karthik Krishnamurthy <[EMAIL PROTECTED]> wrote: > > > > It works after I specify primary_key in my mapper or allow_null_pks, but > > not if I specify > > the column as primary_key in the Table() constructor. > > > > Thanks > > /kk > > > > On 3/26/07, Michael Bayer <[EMAIL PROTECTED]> wrote: > > > > > > > > > i notice that neither your table DDL nor your mappers have any notion > > > of a primary key, so thats not the complete application...whats below > > > will throw an error immediately. > > > > > > but the most likely cause for what youre seeing is that if any > > > element of the primary key in a result row is "None", no row will be > > > loaded. this behavior can be changed using the "allow_null_pks" > > > option on your mapper. > > > > > > > > > On Mar 26, 2007, at 7:17 AM, Karthik Krishnamurthy wrote: > > > > > > > > > > > Hi, > > > > I find that sqlalchemy isn't creating objects for certain rows. > > > > > > > > For example the following code gives me back an OpsDB.Node instance > > > > query.select_by(name=' konsole12.xx.xx.com')[0] > > > > whereas > > > > query.select_by(name=' konsole23.xx.xxx.com')[0] > > > > > > > > I have provided below the debugging output gotten by setting > > > > sqlalchemy.engine and sqlalchemy.orm at DEBUG, the table schema and > > > > the code that defines the table and the mapper for the said table. > > > > > > > > /kk > > > > > > > > DEBUG info > > > > ======================================================= > > > > INFO:sqlalchemy.engine.base.Engine.0x..74:SELECT node.cport AS > > > > node_cport, node.type_id AS node_type_id, node.locshelf AS > > > > node_locshelf, node.s_time AS node_s_time, node.site_id AS > > > > node_site_id, node.console_id AS node_console_id, node.locside AS > > > > node_locside, node.locarea AS node_locarea, node.netswitch_id AS > > > > node_netswitch_id, node.bmodule2 AS node_bmodule2, node.sport AS > > > > node_sport, node.node_id AS node_node_id, node.bplug AS node_bplug, > > > > node.bmodule AS node_bmodule, node.parent_id AS node_parent_id, > > > > node.os_id AS node_os_id, node.bport2 AS node_bport2, node.backplane > > > > AS node_backplane, node.prop_id AS node_prop_id, node.status AS > > > > node_status, node.model_id AS node_model_id, node.locrow AS > > > > node_locrow, node.serialno AS node_serialno, node.bootbox2_id AS > > > > node_bootbox2_id, node.m_time AS node_m_time, node.ytag AS > > > node_ytag, > > > > node.bplug2 AS node_bplug2, node.loccage AS node_loccage, > > > > node.ponumber AS node_ponumber, node.name AS node_name, > > > node.racksize > > > > AS node_racksize, node.bootbox_id AS node_bootbox_id, node.locroomAS > > > > node_locroom, node.notes AS node_notes, node.bport AS node_bport, > > > > node.locrack AS node_locrack, node.c_time AS node_c_time, node.pduAS > > > > node_pdu > > > > FROM node > > > > WHERE node.name = ? ORDER BY node.oid > > > > INFO:sqlalchemy.engine.base.Engine.0x..74:[' konsole19.xx.xxxx.com'] > > > > DEBUG:sqlalchemy.orm.query.Query:instances() > > > > DEBUG:sqlalchemy.engine.base.Engine.0x..74:Row (30021, 106, 2, 2006, > > > > 91, 106822, u'a', u'E', None, 0, 0, 106818, 0, 0, None, None, 0, > > > u'', > > > > 73, u'active', 179, u'19', u'TES113830', None, u'2006-11-30 > > > 19:31:28', > > > > u'126785', 0, 0, u'', u' konsole19.xx.xxxx.com ', 1, None, 0, u'', > > > 0, 1, > > > > 1135015620, u'') > > > > DEBUG:sqlalchemy.orm.strategies.LazyLoader:set class-level lazy > > > loader > > > > on [EMAIL PROTECTED] > > > > DEBUG:sqlalchemy.orm.strategies.LazyLoader:set class-level lazy > > > loader > > > > on [EMAIL PROTECTED] > > > > DEBUG:sqlalchemy.orm.strategies.LazyLoader:set class-level lazy > > > loader > > > > on [EMAIL PROTECTED] > > > > DEBUG:sqlalchemy.orm.strategies.LazyLoader:set class-level lazy > > > loader > > > > on [EMAIL PROTECTED] > > > > DEBUG:sqlalchemy.orm.strategies.LazyLoader:set class-level lazy > > > loader > > > > on [EMAIL PROTECTED] > > > > DEBUG:sqlalchemy.orm.strategies.LazyLoader:set class-level lazy > > > loader > > > > on [EMAIL PROTECTED] > > > > DEBUG:sqlalchemy.orm.strategies.LazyLoader:set class-level lazy > > > loader > > > > on [EMAIL PROTECTED] > > > > DEBUG:sqlalchemy.orm.strategies.LazyLoader:set class-level lazy > > > loader > > > > on [EMAIL PROTECTED] > > > > DEBUG:sqlalchemy.orm.strategies.LazyLoader:set class-level lazy > > > loader > > > > on [EMAIL PROTECTED] > > > > DEBUG:sqlalchemy.orm.strategies.LazyLoader:set class-level lazy > > > loader > > > > on [EMAIL PROTECTED] > > > > DEBUG:sqlalchemy.orm.strategies.LazyLoader:set class-level lazy > > > loader > > > > on [EMAIL PROTECTED] > > > > DEBUG:sqlalchemy.orm.strategies.LazyLoader:set class-level lazy > > > loader > > > > on [EMAIL PROTECTED] > > > > DEBUG:sqlalchemy.orm.strategies.LazyLoader:set class-level lazy > > > loader > > > > on [EMAIL PROTECTED] > > > > DEBUG:sqlalchemy.orm.strategies.LazyLoader:set class-level lazy > > > loader > > > > on [EMAIL PROTECTED] > > > > DEBUG:sqlalchemy.orm.strategies.LazyLoader:set class-level lazy > > > loader > > > > on [EMAIL PROTECTED] > > > > INFO:sqlalchemy.engine.base.Engine.0x..74:SELECT node.cport AS > > > > node_cport, node.type_id AS node_type_id, node.locshelf AS > > > > node_locshelf, node.s_time AS node_s_time, node.site_id AS > > > > node_site_id, node.console_id AS node_console_id, node.locside AS > > > > node_locside, node.locarea AS node_locarea, node.netswitch_id AS > > > > node_netswitch_id, node.bmodule2 AS node_bmodule2, node.sport AS > > > > node_sport, node.node_id AS node_node_id, node.bplug AS node_bplug, > > > > node.bmodule AS node_bmodule, node.parent_id AS node_parent_id, > > > > node.os_id AS node_os_id, node.bport2 AS node_bport2, node.backplane > > > > AS node_backplane, node.prop_id AS node_prop_id, node.status AS > > > > node_status, node.model_id AS node_model_id, node.locrow AS > > > > node_locrow, node.serialno AS node_serialno, node.bootbox2_id AS > > > > node_bootbox2_id, node.m_time AS node_m_time, node.ytag AS > > > node_ytag, > > > > node.bplug2 AS node_bplug2, node.loccage AS node_loccage, > > > > node.ponumber AS node_ponumber, node.name AS node_name, > > > node.racksize > > > > AS node_racksize, node.bootbox_id AS node_bootbox_id, node.locroomAS > > > > node_locroom, node.notes AS node_notes, node.bport AS node_bport, > > > > node.locrack AS node_locrack, node.c_time AS node_c_time, node.pduAS > > > > node_pdu > > > > FROM node > > > > WHERE node.name = ? ORDER BY node.oid > > > > INFO:sqlalchemy.engine.base.Engine.0x..74:['konsole23.xx.xxxx.com '] > > > > DEBUG:sqlalchemy.orm.query.Query:instances() > > > > DEBUG: sqlalchemy.engine.base.Engine.0x..74:Row (0, 106, 0, 2006, > > > 71, > > > > None, u'a', u'', None, 0, 0, 107751, 0, 0, None, None, 0, u'', 73, > > > > u'active', 179, u'1', u'', None, u'2006-11-30 19:31:31', None, 0, 0, > > > > > > > u'', u'konsole23.xx.xxxx.com', 1, None, 0, u'', 0, 1, 1135127145, > > > u'') > > > > Traceback (most recent call last): > > > > File "test1.py ", line 13, in ? > > > > node = query.select_by(name='konsole23.ird.yahoo.com')[0] > > > > IndexError: list index out of range > > > > > > > > ======================================================= > > > > The schema: > > > > > > > > ======================================================= > > > > sqlite> .schema node > > > > CREATE TABLE node ( > > > > node_id integer(10) NOT NULL, > > > > name VARCHAR(255), > > > > ytag VARCHAR(32), > > > > type_id integer(10), > > > > status varchar(32) NOT NULL, > > > > parent_id integer(10), > > > > prop_id integer(10), > > > > site_id integer(10), > > > > backplane CHAR(1) NOT NULL, > > > > model_id integer(10), > > > > os_id integer(10), > > > > serialno VARCHAR(32) NOT NULL, > > > > locroom integer(10) NOT NULL, > > > > loccage integer(10) NOT NULL, > > > > locarea VARCHAR(8) NOT NULL, > > > > locrow CHAR(2) NOT NULL, > > > > locrack integer(10) NOT NULL, > > > > locside char(1) NOT NULL, > > > > locshelf integer(3) NOT NULL, > > > > racksize INTEGER(2), > > > > bootbox_id integer(10), > > > > bport integer(10) NOT NULL, > > > > bmodule integer(10) NOT NULL, > > > > bplug integer(10) NOT NULL, > > > > bootbox2_id integer(10), > > > > bport2 integer(10) NOT NULL, > > > > bmodule2 integer(10) NOT NULL, > > > > bplug2 integer(10) NOT NULL, > > > > console_id integer(10), > > > > cport integer(10) NOT NULL, > > > > netswitch_id integer(10), > > > > sport integer(10) NOT NULL, > > > > ponumber VARCHAR(32) NOT NULL, > > > > notes TEXT NOT NULL, > > > > pdu VARCHAR(255) NOT NULL, > > > > c_time integer(10) NOT NULL, > > > > m_time integer(14) NOT NULL, > > > > s_time integer(10) NOT NULL, > > > > UNIQUE (node_id), > > > > UNIQUE (name), > > > > UNIQUE (ytag)); > > > > ======================================================= > > > > > > > > code > > > > ======================================================= > > > > _nodeTable = self._nodeTable = Table( > > > > 'node', _metadata, > > > > Column('prop_id', Integer, > > > > ForeignKey('property.prop_id')), > > > > Column('site_id', Integer, ForeignKey('site.site_id')), > > > > Column('os_id', Integer, ForeignKey('os.os_id' )), > > > > Column('console_id', Integer, ForeignKey('node.node_id' > > > )), > > > > Column('bootbox_id', Integer, ForeignKey('node.node_id' > > > )), > > > > Column('bootbox2_id', Integer, > > > > ForeignKey('node.node_id')), > > > > Column('netswitch_id', Integer, > > > > ForeignKey('node.node_id')), > > > > Column('type_id', Integer, > > > > ForeignKey(' dictionary.dict_id')), > > > > Column('model_id', Integer, ForeignKey('model.model_id' > > > )), > > > > autoload=True > > > > ) > > > > > > > > mapper(OpsDB.Node, self._nodeTable, properties={ > > > > 'id': self._nodeTable.c.node_id, > > > > 'property': relation(OpsDB.Property, backref='nodes'), > > > > 'site': relation(OpsDB.Site, backref='nodes'), > > > > 'os': relation(OpsDB.OS, backref='nodes'), > > > > 'console': relation( > > > > OpsDB.Node, > > > > primaryjoin = _nodeTable.c.console_id == > > > > _nodeTable.c.node_id, > > > > remote_side = [_nodeTable.c.node_id], > > > > backref = 'consoleNodes', > > > > ), > > > > 'bootbox' : relation( > > > > OpsDB.Node, > > > > primaryjoin = _nodeTable.c.bootbox_id == > > > > _nodeTable.c.node_id, > > > > remote_side=[_nodeTable.c.node_id], > > > > backref = 'bootboxNodes', > > > > ), > > > > 'bootbox2' : relation( > > > > OpsDB.Node, > > > > primaryjoin = _nodeTable.c.bootbox2_id == > > > > _nodeTable.c.node_id, > > > > remote_side=[_nodeTable.c.node_id], > > > > backref = 'bootbox2Nodes', > > > > ), > > > > 'switch' : relation( > > > > OpsDB.Node, > > > > primaryjoin = _nodeTable.c.netswitch_id == > > > > _nodeTable.c.node_id, > > > > remote_side=[_nodeTable.c.node_id], > > > > backref = 'switchNodes', > > > > ), > > > > 'type' : relation(OpsDB.Dictionary , > > > backref='typeNodes'), > > > > 'model' : relation(OpsDB.Dictionary, > > > > backref='modelNodes'), > > > > }) > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---