Hi all,
Since SA was proving to be difficult to get working, and I was
spending way more time just trying to get it working than I was
actually running queries and outputting the results, I thought I'd
give Pyodbc a shot. Within a couple days, the query was working
perfectly. I'll post it below, as I'd be curious how this could be
made easier using SA.
since it's working so well in Pyodbc, but I'm curious all the same.
So, no hurry on this, it's only for my own interest. Anyway, the query
I finally came up with is below. It returns multiple rows for items
that have attributes, but other than that it works great. A for loop
with a check on the current ID takes care of making multiple rows for
the same item into a single row (recall this is all going to a
spreadsheet).
itemsQuery = """
select items.itm_id as itemID, items.itm_proddesc as itemTitle,
items.itm_num as itemNumber, items.itm_listprice1 as msrp,
items.itm_dftuom as itemUnitOfMeasure, items.itm_manufitem as
itemManufacturer, items.itm_vendornum as itemVendorNumber,
items.itm_weight as itemWeight, items.itm_width as itemWidth,
items.itm_length as itemLength, items.itm_height as itemHeight,
attachments.description as description,
imagePaths.imagePath1 as imagePath1, imagePaths.imagePath2 as
imagePath2, imagePaths.imagePath3 as imagePath3,
attributes.attributeName as attributeName, attributes.attributeValue
as attributeValue,
vendor.vendorName as vendorName
from (
select itm_id, itm_proddesc, itm_num, itm_vendornum,
itm_listprice1, itm_length, itm_width, itm_height, itm_weight,
itm_dftuom, itm_manufitem
from item
where itm_webflag <> 'Y' and itm_suspflag <> 'Y'
) items
left outer join (
select distinct attr_desc as attributeName, attr_value as
attributeValue, itm_id
from attributevalueassign
join attribute
on attribute.attr_id = attributevalueassign.attr_id
join attributevalue
on attributevalue.attr_value_id = attributevalueassign.attr_value_id
where attributevalueassign.itm_id = itm_id
) attributes
on attributes.itm_id = items.itm_id
left outer join (
select PVUS15 as vendorName, PVVNNO as vendorNumber, itm_id
from VENDR
join item on item.itm_id = itm_id
) vendor
on vendor.vendorNumber = items.itm_vendornum and vendor.itm_id =
items.itm_id
left outer join (
select attach_text.att_text as description, itm_id
from assignment
join attachment on attachment.att_id = assignment.att_id
join attach_text on attach_text.att_id = assignment.att_id
where assignment.itm_id = itm_id
) attachments
on attachments.itm_id = items.itm_id
left outer join (
select attachment.att_path as imagePath1, attachment.att_path2 as
imagePath2, attachment.att_path3 as imagePath3, itm_id
from assignment
join attachment on attachment.att_id = assignment.att_id
) imagePaths
on imagePaths.itm_id = items.itm_id
"""
On 3/21/16, Simon King <si...@simonking.org.uk> wrote:
Can you extract your code into a single standalone script that
demonstrates
the problem? This should be possible even with automap; the script can
start by creating just the tables that are involved in this problem
(ideally in an in-memory sqlite db), then use automap to map classes to
those tables.
Simon
On Mon, Mar 21, 2016 at 3:12 PM, Alex Hall <ah...@autodist.com> wrote:
Wow, thanks guys, especially for the sample code! I'm trying to use
the example (and fully understand it at the same time) but am running
into an error. This is the same error that made me look for a way
other than this last week.
sqlalchemy.exc.InvalidRequestError: when initializing mapper
Mapper|assignmentTable|assignment, expression 'item' failed to to
locate an item (name 'item' is not defined). If this is a class name,
consider adding this relationship() to the
<class.__main__.assignmentTable> class after both dependent classes
have been defined.
This all starts from the line where my query begins:
items = session.query(itemTable)\
Again, I'm using automap. I put the class definitions in the same
place I put my vendor table definition last week, where it worked
perfectly. That's just after I set
base = automap_base()
but before I reflect anything. I can paste the full code if you want,
but it's pretty long.
On 3/17/16, Mike Bayer <clas...@zzzcomputing.com> wrote:
On 03/17/2016 03:11 PM, Alex Hall wrote:
Hello all,
It seems like I can't go a day without running into some kind of
wall.
This one is a conceptual one regarding foreign keys. I have to
somehow
get the same FK column in table A pointing to IDs in tables B and C.
So a real foreign key constraint is not capable of this. Repurposing
a
single column to occasionally point to one table or another is a
famous
anti-pattern I've spoke of much (warning, this is *extremely* old, but
the general idea still holds):
http://techspot.zzzeek.org/2007/05/29/polymorphic-associations-with-sqlalchemy/
I have an updated version of all the various "polymoprhic association"
examples in SQLAlchemy itself at
http://docs.sqlalchemy.org/en/rel_1_0/orm/examples.html#module-examples.generic_associations
.
This includes the "single column pointing to multiple tables" hack,
as
well as three other versions of the same business object geometry
which
preserve relational integrity within the schema design.
At one person's suggestion, I'm making classes for my tables, even
though I'm using automap. This is to let me stop doing a ton of
joins,
making querying much easier... I hope! I'm defining all the foreign
keys between my tables manually. For instance:
class item(base):
__tablename__ = "item"
itm_id = Column(Integer, primary_key=True)
vendornum = Column(String, ForeignKey(VENDR.PVVNNO))
class vendorTable(base):
__tablename__ = "VENDR"
PVVNNO = Column(String, primary_key=True)
If I've understood correctly, I'll now be able to say
item.vendornum.vendor_full_name
to get the vendor's full name for any item.
Here's the problem. Items have attachments, and attached text,
respectively held in attach and attach_text tables. Binding them to
items is a table called assignment. Assignment is pretty
straightforward, with an itm_id and an attachment id (att_id). The
trouble is that this att_id occurs in both attach and attach_text. I
can make att_id a foreign key to one table or the other, but I'm not
sure how to make it go to both tables.
the "generic_fk" example illustrates a pattern for working with this.
Getting this all to work with automap is another layer of complexity,
you certainly want all of this part of it laid out before you reflect
the rest of the database columns.
class assignmentTable(base):
__tablename__ = "assignment"
itm_id = Column(Integer, ForeignKey(item.itm_id))
#the following column has to point to attach_text.att_id AS WELL
att_id = Column(Integer, ForeignKey(attachment.att_id))
seq_num = Column(Integer)
asn_primary = Column(Integer, nullable=True)
class attachmentTable(base):
__tablename__ = "attachment"
att_id = Column(Integer, primary_key=True)
class attachmentTextTable(base):
__tablename__ = "attach_text"
att_id = Column(Integer, primary_key=True)
--
You received this message because you are subscribed to the Google
Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send
an
email to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
You received this message because you are subscribed to the Google
Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send
an
email to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
You received this message because you are subscribed to the Google
Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send
an
email to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
