I have a client I'm doing new product integration work that requires
some complicated joins against legacy db tables.
Most of their tables all have primary keys which SqlSoup can cope with
readily. There are a few, however, that lack a primary key. A table I
need to process is called itemattributes, and when I try to select it,
SqlSoup retorts with:
sqlalchemy.ext.sqlsoup.PKNotFoundError: table 'itemattribs' does not
have a primary key defined
I've examined the table and the following is the table definition:
CREATE TABLE `itemattribs` (
`CatalogID` int(11) NOT NULL default '0',
`ItemID` int(11) NOT NULL default '0',
`AttribID` int(11) NOT NULL default '0',
`TSAttribID` int(11) NOT NULL default '0',
`IsPreDefined` varchar(5) NOT NULL default '',
`Name` varchar(255) NOT NULL default '',
`TSName` varchar(255) NOT NULL default '',
`Value` text,
`TSValue` text,
`Measure` varchar(255) NOT NULL default '',
`TSMeasure` varchar(255) NOT NULL default '',
`loadtime` int(11) NOT NULL default '0',
KEY `CatalogID` (`CatalogID`),
KEY `ItemID` (`ItemID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
I'm not in the position to request they add a primary key when this
issue comes up.
By using raw SQL execute() I am able to query the table, but I get raw
results that are just a list of column values, not a nice
MappedItemattribs() object. Here is what I am doing (I know this style
of query has the risk of injection, its short for example sake):
query = """SELECT * FROM itemattribs AS attr """ \
"""WHERE attr.CatalogID = %d AND attr.ItemID = %d""" \
% (item.CatalogID, item.ItemID)
results = db.bind.execute(query)
for attr in results:
print attr
...this gives me a tuple like this when I print attr:
(1001L, 950768L, 10L, 0L, 'true', 'Item #', '', 'REL 667R-4M4F-B,
5-50 psi', 'REL 667R-4M4F-B,5-50 psi', '', '', 1250660289L)
1) Is there a way to give SqlSoup hints on how to handle the lack of a
primary key so that the selection and mapping magic will work
correctly instead of raising a PKNotFoundError exception?
2) Barring that, is there a way to pass row column values to a
function that will generate a MappedItemattribs instance so I can
access fields as members instead of having to use offsets I'd need to
via execute() results?
I really really like SqlSoup and was hoping it would save me grief of
raw SQL against these legacy tables, but if I find more tables I need
to query that lack primary keys, I'd wondering if it might just be
easier to use raw MySQLdb instead of trying to mix-n-match approaches.
I'm worried about anyone else (or myself) being able to follow/
maintain this code later if I have to use a full hybrid approach.
Thank you for your time! :)
-Michael
--~--~---------~--~----~------------~-------~--~----~
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
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
