Most elegant SqlSoup solution cope with legacy client db tables without primary keys?
Xavian
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
Michael Bayer
> 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?
SQLSoup tables are all mapped classes. a mapper() always needs a primary
key. so at the very least you'd need to *identify* a primary key on the
table and specify it to the mapper, although I don't know that SQLSoup
allows options like that through.
>
> 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?
unfortunately SQLSoup is a lot simpler than that and you'd have to hack
into it to get the results you want.
>
> 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.
there is the option to use the SQLAlchemy ORM directly ? sqlsoup is not
really supported very much.