Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>The link to vertical/vertical.py is broken. The example had been renamed to <code>dictlike-polymorphic.py</code> and <code>dictlike.py.</code></p> <p>I am pasting in the contents of <code>dictlike.py</code>:</p> <pre><code>"""Mapping a vertical table as a dictionary. This example illustrates accessing and modifying a "vertical" (or "properties", or pivoted) table via a dict-like interface. These are tables that store free-form object properties as rows instead of columns. For example, instead of:: # A regular ("horizontal") table has columns for 'species' and 'size' Table('animal', metadata, Column('id', Integer, primary_key=True), Column('species', Unicode), Column('size', Unicode)) A vertical table models this as two tables: one table for the base or parent entity, and another related table holding key/value pairs:: Table('animal', metadata, Column('id', Integer, primary_key=True)) # The properties table will have one row for a 'species' value, and # another row for the 'size' value. Table('properties', metadata Column('animal_id', Integer, ForeignKey('animal.id'), primary_key=True), Column('key', UnicodeText), Column('value', UnicodeText)) Because the key/value pairs in a vertical scheme are not fixed in advance, accessing them like a Python dict can be very convenient. The example below can be used with many common vertical schemas as-is or with minor adaptations. """ class VerticalProperty(object): """A key/value pair. This class models rows in the vertical table. """ def __init__(self, key, value): self.key = key self.value = value def __repr__(self): return '&lt;%s %r=%r&gt;' % (self.__class__.__name__, self.key, self.value) class VerticalPropertyDictMixin(object): """Adds obj[key] access to a mapped class. This is a mixin class. It can be inherited from directly, or included with multiple inheritence. Classes using this mixin must define two class properties:: _property_type: The mapped type of the vertical key/value pair instances. Will be invoked with two positional arugments: key, value _property_mapping: A string, the name of the Python attribute holding a dict-based relationship of _property_type instances. Using the VerticalProperty class above as an example,:: class MyObj(VerticalPropertyDictMixin): _property_type = VerticalProperty _property_mapping = 'props' mapper(MyObj, sometable, properties={ 'props': relationship(VerticalProperty, collection_class=attribute_mapped_collection('key'))}) Dict-like access to MyObj is proxied through to the 'props' relationship:: myobj['key'] = 'value' # ...is shorthand for: myobj.props['key'] = VerticalProperty('key', 'value') myobj['key'] = 'updated value'] # ...is shorthand for: myobj.props['key'].value = 'updated value' print myobj['key'] # ...is shorthand for: print myobj.props['key'].value """ _property_type = VerticalProperty _property_mapping = None __map = property(lambda self: getattr(self, self._property_mapping)) def __getitem__(self, key): return self.__map[key].value def __setitem__(self, key, value): property = self.__map.get(key, None) if property is None: self.__map[key] = self._property_type(key, value) else: property.value = value def __delitem__(self, key): del self.__map[key] def __contains__(self, key): return key in self.__map # Implement other dict methods to taste. Here are some examples: def keys(self): return self.__map.keys() def values(self): return [prop.value for prop in self.__map.values()] def items(self): return [(key, prop.value) for key, prop in self.__map.items()] def __iter__(self): return iter(self.keys()) if __name__ == '__main__': from sqlalchemy import (MetaData, Table, Column, Integer, Unicode, ForeignKey, UnicodeText, and_, not_) from sqlalchemy.orm import mapper, relationship, create_session from sqlalchemy.orm.collections import attribute_mapped_collection metadata = MetaData() # Here we have named animals, and a collection of facts about them. animals = Table('animal', metadata, Column('id', Integer, primary_key=True), Column('name', Unicode(100))) facts = Table('facts', metadata, Column('animal_id', Integer, ForeignKey('animal.id'), primary_key=True), Column('key', Unicode(64), primary_key=True), Column('value', UnicodeText, default=None),) class AnimalFact(VerticalProperty): """A fact about an animal.""" class Animal(VerticalPropertyDictMixin): """An animal. Animal facts are available via the 'facts' property or by using dict-like accessors on an Animal instance:: cat['color'] = 'calico' # or, equivalently: cat.facts['color'] = AnimalFact('color', 'calico') """ _property_type = AnimalFact _property_mapping = 'facts' def __init__(self, name): self.name = name def __repr__(self): return '&lt;%s %r&gt;' % (self.__class__.__name__, self.name) mapper(Animal, animals, properties={ 'facts': relationship( AnimalFact, backref='animal', collection_class=attribute_mapped_collection('key')), }) mapper(AnimalFact, facts) metadata.bind = 'sqlite:///' metadata.create_all() session = create_session() stoat = Animal(u'stoat') stoat[u'color'] = u'reddish' stoat[u'cuteness'] = u'somewhat' # dict-like assignment transparently creates entries in the # stoat.facts collection: print stoat.facts[u'color'] session.add(stoat) session.flush() session.expunge_all() critter = session.query(Animal).filter(Animal.name == u'stoat').one() print critter[u'color'] print critter[u'cuteness'] critter[u'cuteness'] = u'very' print 'changing cuteness:' metadata.bind.echo = True session.flush() metadata.bind.echo = False marten = Animal(u'marten') marten[u'color'] = u'brown' marten[u'cuteness'] = u'somewhat' session.add(marten) shrew = Animal(u'shrew') shrew[u'cuteness'] = u'somewhat' shrew[u'poisonous-part'] = u'saliva' session.add(shrew) loris = Animal(u'slow loris') loris[u'cuteness'] = u'fairly' loris[u'poisonous-part'] = u'elbows' session.add(loris) session.flush() q = (session.query(Animal). filter(Animal.facts.any( and_(AnimalFact.key == u'color', AnimalFact.value == u'reddish')))) print 'reddish animals', q.all() # Save some typing by wrapping that up in a function: with_characteristic = lambda key, value: and_(AnimalFact.key == key, AnimalFact.value == value) q = (session.query(Animal). filter(Animal.facts.any( with_characteristic(u'color', u'brown')))) print 'brown animals', q.all() q = (session.query(Animal). filter(not_(Animal.facts.any( with_characteristic(u'poisonous-part', u'elbows'))))) print 'animals without poisonous-part == elbows', q.all() q = (session.query(Animal). filter(Animal.facts.any(AnimalFact.value == u'somewhat'))) print 'any animal with any .value of "somewhat"', q.all() # Facts can be queried as well. q = (session.query(AnimalFact). filter(with_characteristic(u'cuteness', u'very'))) print 'just the facts', q.all() metadata.drop_all() </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POHow to map one class against multiple tables with SQLAlchemy?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. This table or related slice is empty.
    1. USJesse Vogt
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload