[sqlalchemy] Re: sqlalchemy orm doesn't create an instance of certain rows - Karthik Krishnamurthy - com.googlegroups.sqlalchemy

9 messages in com.googlegroups.sqlalchemy[sqlalchemy] Re: sqlalchemy orm doesn...

From	Sent On	Attachments
Karthik Krishnamurthy	26 Mar 2007 04:17
Michael Bayer	26 Mar 2007 08:47
Karthik Krishnamurthy	26 Mar 2007 22:53
Karthik Krishnamurthy	26 Mar 2007 23:30
Karthik Krishnamurthy	27 Mar 2007 03:07
Michael Bayer	27 Mar 2007 07:01
Michael Bayer	27 Mar 2007 07:19
Karthik Krishnamurthy	28 Mar 2007 09:42	.py
Michael Bayer	28 Mar 2007 10:51

Subject:	[sqlalchemy] Re: sqlalchemy orm doesn't create an instance of certain rows
From:	Karthik Krishnamurthy (kart...@gmail.com)
Date:	03/26/2007 11:30:00 PM
List:	com.googlegroups.sqlalchemy

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_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.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

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 <mike...@zzzcomputing.com> 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.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.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 Node@-0x49725ef4.switchNodes DEBUG:sqlalchemy.orm.strategies.LazyLoader:set class-level lazy loader on Node@-0x49725ef4.nodeGroups DEBUG:sqlalchemy.orm.strategies.LazyLoader:set class-level lazy loader on No...@-0x49725ef4.site DEBUG:sqlalchemy.orm.strategies.LazyLoader:set class-level lazy loader on Node@-0x49725ef4.consoleNodes DEBUG:sqlalchemy.orm.strategies.LazyLoader:set class-level lazy loader on Node@-0x49725ef4.console DEBUG:sqlalchemy.orm.strategies.LazyLoader:set class-level lazy loader on Node@-0x49725ef4.bootbox2Nodes DEBUG:sqlalchemy.orm.strategies.LazyLoader:set class-level lazy loader on Node@-0x49725ef4.bootboxNodes DEBUG:sqlalchemy.orm.strategies.LazyLoader:set class-level lazy loader on No...@-0x49725ef4.type DEBUG:sqlalchemy.orm.strategies.LazyLoader:set class-level lazy loader on No...@-0x49725ef4.macs DEBUG:sqlalchemy.orm.strategies.LazyLoader:set class-level lazy loader on Node@-0x49725ef4.bootbox DEBUG:sqlalchemy.orm.strategies.LazyLoader:set class-level lazy loader on Node@-0x49725ef4.bootbox2 DEBUG:sqlalchemy.orm.strategies.LazyLoader:set class-level lazy loader on Node@-0x49725ef4.switch DEBUG:sqlalchemy.orm.strategies.LazyLoader:set class-level lazy loader on Node@-0x49725ef4.model DEBUG:sqlalchemy.orm.strategies.LazyLoader:set class-level lazy loader on Node@-0x49725ef4.property DEBUG:sqlalchemy.orm.strategies.LazyLoader:set class-level lazy loader on No...@-0x49725ef4.os 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.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.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'), })