Hi John,
Check out the doc section entitled "Mapping a class with table
inheritance":
http://www.sqlalchemy.org/docs/adv_datamapping.html#advdatamapping_inheritance
Although it's not the only way to do it, you might be interested in
polymorphic multiple-table inheritance, which SQLAlchemy already
handles really well.
As for your current code: if set up for polymorphic inheritance,
sqlalchemy will handle the connection of an object with the proper
table for its type and you won't need that select statement in your
__init__ method at all. Furthermore, sqlalchemy doesn't even call
your __init__ methods when it loads items from storage; it uses
__new__ (see item 5.6 on the FAQ page for explanation: "why isn't my
init called when i load objects?"). Init is just for when you first
make a new object of your own, before you've ever persisted the
object.
Read and ye shall find!
Here is a reworked version that has the behavior you might be looking
for:
from sqlalchemy import *
#..other imports
engine = create_engine('mysql://[EMAIL PROTECTED]) #example uri
metadata = BoundMetaData(engine)
items_table = Table('items', metadata,
Column('id', Integer, primary_key = True),
Column('item_type', String(20))
# add any other columns here for properties that should be
# carried by every item regardless of its item_type
)
tools_table = Table('tools', metadata,
Column('item_id', Integer, ForeignKey('items.id'),
primary_key=True),
Column('tool_name', String(20)))
# add any other columns for properties of tools only
widgets_table = Table('widgets', metadata,
Column('item_id', Integer, ForeignKey('items.id'),
primary_key=True),
Column('widget_name', String(20)))
# add any other columns for properties of widgets only
# class definitions
class Item(object):
def __init__(self, item_type):
self.item_type = item_type
# id will be provided automatically
class Tool(Item): ## <-- note the inheritance
def __init__(self, tool_name):
self.tool_name = tool_name
# etc., set any other values for tools_table columns
def __repr__(self):
return 'Tool (%s)' % self.tool_name
class Widget(Item):
def __init__(self, widget_name):
self.widget_name = widget_name
# etc., set any other values for widgets_table columns
def __repr__(self):
return 'Widget (%s)' % self.widget_name
item_join = polymorphic_union(
{
'tool': items_table.join(tools_table),
'widget': items_table.join(widgets_table),
'item': items_table.select(items_table.c.item_type=='item'),
}, None, 'ijoin')
# note: by assigning an item type 'item' you can create generic items
# which are neither tools nor widgets (etc.)
item_mapper = mapper(Item, items_table,
select_table = item_join,
polymorphic_on = item_join.c.item_type,
polymorphic_identity = 'item')
tool_mapper = mapper(Tool, tools_table,
inherits=item_mapper,
polymorphic_identity='tool')
widget_mapper = mapper(Widget, widgets_table,
inherits=item_mapper,
polymorphic_identity='widget')
# note, no need to name the mappers if you don't wish to
# refer to them later
if __name__ == '__main__':
metadata.create_all()
session = create_session()
metadata.engine.echo = True
item1 = Tool('sample hammer')
item2 = Tool('screwdriver')
item3 = Widget('spam')
item4 = Widget('eggs')
# note: items don't *have* ids until saved AND flushed
# note also: item ids are assigned automatically
session.save(item1)
session.save(item2)
session.save(item3)
session.save(item4)
session.flush()
allitems = session.query(Item).select()
metadata.engine.echo = False
for item in allitems:
print item.id, item
session.close()
metadata.drop_all()
On Jun 5, 6:20 pm, John Lorance <[EMAIL PROTECTED]> wrote:
> I'm newish to SqlAlchemy and for the life of me I can't figure out how
> to properly set things up so that lookup tables(objects) are cached
> and/or it is easy for new parent objects to associate to their
> childing upon initialization. See below for code snippet and sample
> problem.
>
> from sqlalchemy import *
> #..other imports
>
> metadata = MetaData()
>
> item_types_table = Table('item_types', metadata,
> Column('id', Integer, autoincrement=False, primary_key = True),
> Column('name', String(50))
> )
>
> items_table = Table('items', metadata,
> Column('id', Integer, primary_key = True),
> Column('item_type_id', Integer, ForeignKey('item_types.id')),
> Column('name', String(150))
> )
>
> engine = create_engine('mysql://[EMAIL PROTECTED]) #example uri
> engine.echo = True
> metadata.create_all(engine)
>
> # class definitions
> class ItemType(object):
> def __init__(self, id, name=None):
> self.id = id
> self.name = name
>
> class Item(object):
> def __init__(self, type_name, name):
> self.item_type =
> session.query(ItemType).selectfirst(ItemType.c.name==type_name)
> # ^^^^ Is there a better way to create this association?
>
> self.name = name
>
> mapper(ItemType, item_types_table)
> mapper(Item, item_table, properties = dict(
> item_type = relation(ItemType)
> )
> )
>
> session = create_session(bind_to=engine)
>
> #create item types
> t1 = ItemType(0, 'tool')
> t2 = ItemType(1, 'widget')
> session.save(t1)
> session.save(t2)
>
> # ####### Everything works above as expected
>
> item1 =Item('tool', 'sample hammer')
> item2 =Item(''tool', 'screwdriver'')
>
> #end of code
> ####################
>
> So two questions.
> 1) Upon encountering the last line of code, I see that when I look at
> the SQL generated, i see that the select called in the Item __init__
> method is sending a query again, instead of using what should in
> theory by in the object cache for the exact same query.
>
> 2) Basically, I'm trying to get new Item objects to relate to the
> correct Item Type via using the name to create the foreignkey (id)
> relationship. So the questions is, is the Item Init method setup
> right for this, or is there a better way?
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
