diff options
Diffstat (limited to 'examples')
62 files changed, 2435 insertions, 1742 deletions
diff --git a/examples/adjacency_list/adjacency_list.py b/examples/adjacency_list/adjacency_list.py index 3c9144323..f1628a632 100644 --- a/examples/adjacency_list/adjacency_list.py +++ b/examples/adjacency_list/adjacency_list.py @@ -8,7 +8,7 @@ Base = declarative_base() class TreeNode(Base): - __tablename__ = 'tree' + __tablename__ = "tree" id = Column(Integer, primary_key=True) parent_id = Column(Integer, ForeignKey(id)) name = Column(String(50), nullable=False) @@ -17,15 +17,13 @@ class TreeNode(Base): "TreeNode", # cascade deletions cascade="all, delete-orphan", - # many to one + adjacency list - remote_side # is required to reference the 'remote' # column in the join condition. backref=backref("parent", remote_side=id), - # children will be represented as a dictionary # on the "name" attribute. - collection_class=attribute_mapped_collection('name'), + collection_class=attribute_mapped_collection("name"), ) def __init__(self, name, parent=None): @@ -36,20 +34,20 @@ class TreeNode(Base): return "TreeNode(name=%r, id=%r, parent_id=%r)" % ( self.name, self.id, - self.parent_id + self.parent_id, ) def dump(self, _indent=0): - return " " * _indent + repr(self) + \ - "\n" + \ - "".join([ - c.dump(_indent + 1) - for c in self.children.values() - ]) + return ( + " " * _indent + + repr(self) + + "\n" + + "".join([c.dump(_indent + 1) for c in self.children.values()]) + ) -if __name__ == '__main__': - engine = create_engine('sqlite://', echo=True) +if __name__ == "__main__": + engine = create_engine("sqlite://", echo=True) def msg(msg, *args): msg = msg % args @@ -63,14 +61,14 @@ if __name__ == '__main__': session = Session(engine) - node = TreeNode('rootnode') - TreeNode('node1', parent=node) - TreeNode('node3', parent=node) + node = TreeNode("rootnode") + TreeNode("node1", parent=node) + TreeNode("node3", parent=node) - node2 = TreeNode('node2') - TreeNode('subnode1', parent=node2) - node.children['node2'] = node2 - TreeNode('subnode2', parent=node.children['node2']) + node2 = TreeNode("node2") + TreeNode("subnode1", parent=node2) + node.children["node2"] = node2 + TreeNode("subnode2", parent=node.children["node2"]) msg("Created new tree structure:\n%s", node.dump()) @@ -81,28 +79,33 @@ if __name__ == '__main__': msg("Tree After Save:\n %s", node.dump()) - TreeNode('node4', parent=node) - TreeNode('subnode3', parent=node.children['node4']) - TreeNode('subnode4', parent=node.children['node4']) - TreeNode('subsubnode1', parent=node.children['node4'].children['subnode3']) + TreeNode("node4", parent=node) + TreeNode("subnode3", parent=node.children["node4"]) + TreeNode("subnode4", parent=node.children["node4"]) + TreeNode("subsubnode1", parent=node.children["node4"].children["subnode3"]) # remove node1 from the parent, which will trigger a delete # via the delete-orphan cascade. - del node.children['node1'] + del node.children["node1"] msg("Removed node1. flush + commit:") session.commit() msg("Tree after save:\n %s", node.dump()) - msg("Emptying out the session entirely, selecting tree on root, using " - "eager loading to join four levels deep.") + msg( + "Emptying out the session entirely, selecting tree on root, using " + "eager loading to join four levels deep." + ) session.expunge_all() - node = session.query(TreeNode).\ - options(joinedload_all("children", "children", - "children", "children")).\ - filter(TreeNode.name == "rootnode").\ - first() + node = ( + session.query(TreeNode) + .options( + joinedload_all("children", "children", "children", "children") + ) + .filter(TreeNode.name == "rootnode") + .first() + ) msg("Full Tree:\n%s", node.dump()) diff --git a/examples/association/basic_association.py b/examples/association/basic_association.py index 6714aa681..52476f184 100644 --- a/examples/association/basic_association.py +++ b/examples/association/basic_association.py @@ -12,8 +12,16 @@ of "items", with a particular price paid associated with each "item". from datetime import datetime -from sqlalchemy import (create_engine, Column, Integer, String, DateTime, - Float, ForeignKey, and_) +from sqlalchemy import ( + create_engine, + Column, + Integer, + String, + DateTime, + Float, + ForeignKey, + and_, +) from sqlalchemy.orm import relationship, Session from sqlalchemy.ext.declarative import declarative_base @@ -21,20 +29,21 @@ Base = declarative_base() class Order(Base): - __tablename__ = 'order' + __tablename__ = "order" order_id = Column(Integer, primary_key=True) customer_name = Column(String(30), nullable=False) order_date = Column(DateTime, nullable=False, default=datetime.now()) - order_items = relationship("OrderItem", cascade="all, delete-orphan", - backref='order') + order_items = relationship( + "OrderItem", cascade="all, delete-orphan", backref="order" + ) def __init__(self, customer_name): self.customer_name = customer_name class Item(Base): - __tablename__ = 'item' + __tablename__ = "item" item_id = Column(Integer, primary_key=True) description = Column(String(30), nullable=False) price = Column(Float, nullable=False) @@ -44,41 +53,40 @@ class Item(Base): self.price = price def __repr__(self): - return 'Item(%r, %r)' % ( - self.description, self.price - ) + return "Item(%r, %r)" % (self.description, self.price) class OrderItem(Base): - __tablename__ = 'orderitem' - order_id = Column(Integer, ForeignKey('order.order_id'), primary_key=True) - item_id = Column(Integer, ForeignKey('item.item_id'), primary_key=True) + __tablename__ = "orderitem" + order_id = Column(Integer, ForeignKey("order.order_id"), primary_key=True) + item_id = Column(Integer, ForeignKey("item.item_id"), primary_key=True) price = Column(Float, nullable=False) def __init__(self, item, price=None): self.item = item self.price = price or item.price - item = relationship(Item, lazy='joined') + + item = relationship(Item, lazy="joined") -if __name__ == '__main__': - engine = create_engine('sqlite://') +if __name__ == "__main__": + engine = create_engine("sqlite://") Base.metadata.create_all(engine) session = Session(engine) # create catalog tshirt, mug, hat, crowbar = ( - Item('SA T-Shirt', 10.99), - Item('SA Mug', 6.50), - Item('SA Hat', 8.99), - Item('MySQL Crowbar', 16.99) + Item("SA T-Shirt", 10.99), + Item("SA Mug", 6.50), + Item("SA Hat", 8.99), + Item("MySQL Crowbar", 16.99), ) session.add_all([tshirt, mug, hat, crowbar]) session.commit() # create an order - order = Order('john smith') + order = Order("john smith") # add three OrderItem associations to the Order and save order.order_items.append(OrderItem(mug)) @@ -88,13 +96,18 @@ if __name__ == '__main__': session.commit() # query the order, print items - order = session.query(Order).filter_by(customer_name='john smith').one() - print([(order_item.item.description, order_item.price) - for order_item in order.order_items]) + order = session.query(Order).filter_by(customer_name="john smith").one() + print( + [ + (order_item.item.description, order_item.price) + for order_item in order.order_items + ] + ) # print customers who bought 'MySQL Crowbar' on sale - q = session.query(Order).join('order_items', 'item') - q = q.filter(and_(Item.description == 'MySQL Crowbar', - Item.price > OrderItem.price)) + q = session.query(Order).join("order_items", "item") + q = q.filter( + and_(Item.description == "MySQL Crowbar", Item.price > OrderItem.price) + ) print([order.customer_name for order in q]) diff --git a/examples/association/dict_of_sets_with_default.py b/examples/association/dict_of_sets_with_default.py index fb9b6aa06..7f668c087 100644 --- a/examples/association/dict_of_sets_with_default.py +++ b/examples/association/dict_of_sets_with_default.py @@ -37,7 +37,9 @@ class A(Base): __tablename__ = "a" associations = relationship( "B", - collection_class=lambda: GenDefaultCollection(operator.attrgetter("key")) + collection_class=lambda: GenDefaultCollection( + operator.attrgetter("key") + ), ) collections = association_proxy("associations", "values") @@ -71,19 +73,15 @@ class C(Base): self.value = value -if __name__ == '__main__': - engine = create_engine('sqlite://', echo=True) +if __name__ == "__main__": + engine = create_engine("sqlite://", echo=True) Base.metadata.create_all(engine) session = Session(engine) # only "A" is referenced explicitly. Using "collections", # we deal with a dict of key/sets of integers directly. - session.add_all([ - A(collections={ - "1": set([1, 2, 3]), - }) - ]) + session.add_all([A(collections={"1": set([1, 2, 3])})]) session.commit() a1 = session.query(A).first() diff --git a/examples/association/proxied_association.py b/examples/association/proxied_association.py index 3393fdd1d..46785c6e2 100644 --- a/examples/association/proxied_association.py +++ b/examples/association/proxied_association.py @@ -7,8 +7,15 @@ to ``OrderItem`` optional. from datetime import datetime -from sqlalchemy import (create_engine, Column, Integer, String, DateTime, - Float, ForeignKey) +from sqlalchemy import ( + create_engine, + Column, + Integer, + String, + DateTime, + Float, + ForeignKey, +) from sqlalchemy.orm import relationship, Session from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.ext.associationproxy import association_proxy @@ -17,13 +24,14 @@ Base = declarative_base() class Order(Base): - __tablename__ = 'order' + __tablename__ = "order" order_id = Column(Integer, primary_key=True) customer_name = Column(String(30), nullable=False) order_date = Column(DateTime, nullable=False, default=datetime.now()) - order_items = relationship("OrderItem", cascade="all, delete-orphan", - backref='order') + order_items = relationship( + "OrderItem", cascade="all, delete-orphan", backref="order" + ) items = association_proxy("order_items", "item") def __init__(self, customer_name): @@ -31,7 +39,7 @@ class Order(Base): class Item(Base): - __tablename__ = 'item' + __tablename__ = "item" item_id = Column(Integer, primary_key=True) description = Column(String(30), nullable=False) price = Column(Float, nullable=False) @@ -41,39 +49,40 @@ class Item(Base): self.price = price def __repr__(self): - return 'Item(%r, %r)' % (self.description, self.price) + return "Item(%r, %r)" % (self.description, self.price) class OrderItem(Base): - __tablename__ = 'orderitem' - order_id = Column(Integer, ForeignKey('order.order_id'), primary_key=True) - item_id = Column(Integer, ForeignKey('item.item_id'), primary_key=True) + __tablename__ = "orderitem" + order_id = Column(Integer, ForeignKey("order.order_id"), primary_key=True) + item_id = Column(Integer, ForeignKey("item.item_id"), primary_key=True) price = Column(Float, nullable=False) def __init__(self, item, price=None): self.item = item self.price = price or item.price - item = relationship(Item, lazy='joined') + + item = relationship(Item, lazy="joined") -if __name__ == '__main__': - engine = create_engine('sqlite://') +if __name__ == "__main__": + engine = create_engine("sqlite://") Base.metadata.create_all(engine) session = Session(engine) # create catalog tshirt, mug, hat, crowbar = ( - Item('SA T-Shirt', 10.99), - Item('SA Mug', 6.50), - Item('SA Hat', 8.99), - Item('MySQL Crowbar', 16.99) + Item("SA T-Shirt", 10.99), + Item("SA Mug", 6.50), + Item("SA Hat", 8.99), + Item("MySQL Crowbar", 16.99), ) session.add_all([tshirt, mug, hat, crowbar]) session.commit() # create an order - order = Order('john smith') + order = Order("john smith") # add items via the association proxy. # the OrderItem is created automatically. @@ -87,19 +96,24 @@ if __name__ == '__main__': session.commit() # query the order, print items - order = session.query(Order).filter_by(customer_name='john smith').one() + order = session.query(Order).filter_by(customer_name="john smith").one() # print items based on the OrderItem collection directly - print([(assoc.item.description, assoc.price, assoc.item.price) - for assoc in order.order_items]) + print( + [ + (assoc.item.description, assoc.price, assoc.item.price) + for assoc in order.order_items + ] + ) # print items based on the "proxied" items collection - print([(item.description, item.price) - for item in order.items]) + print([(item.description, item.price) for item in order.items]) # print customers who bought 'MySQL Crowbar' on sale - orders = session.query(Order).\ - join('order_items', 'item').\ - filter(Item.description == 'MySQL Crowbar').\ - filter(Item.price > OrderItem.price) + orders = ( + session.query(Order) + .join("order_items", "item") + .filter(Item.description == "MySQL Crowbar") + .filter(Item.price > OrderItem.price) + ) print([o.customer_name for o in orders]) diff --git a/examples/custom_attributes/__init__.py b/examples/custom_attributes/__init__.py index cbc65dfed..8d73d27e3 100644 --- a/examples/custom_attributes/__init__.py +++ b/examples/custom_attributes/__init__.py @@ -4,4 +4,4 @@ system. .. autosource:: -"""
\ No newline at end of file +""" diff --git a/examples/custom_attributes/active_column_defaults.py b/examples/custom_attributes/active_column_defaults.py index dd823e814..f05a53173 100644 --- a/examples/custom_attributes/active_column_defaults.py +++ b/examples/custom_attributes/active_column_defaults.py @@ -35,6 +35,7 @@ def default_listener(col_attr, default): user integrating this feature. """ + @event.listens_for(col_attr, "init_scalar", retval=True, propagate=True) def init_scalar(target, value, dict_): @@ -52,7 +53,8 @@ def default_listener(col_attr, default): # or can procure a connection from an Engine # or Session and actually run the SQL, if desired. raise NotImplementedError( - "Can't invoke pre-default for a SQL-level column default") + "Can't invoke pre-default for a SQL-level column default" + ) # set the value in the given dict_; this won't emit any further # attribute set events or create attribute "history", but the value @@ -63,7 +65,7 @@ def default_listener(col_attr, default): return value -if __name__ == '__main__': +if __name__ == "__main__": from sqlalchemy import Column, Integer, DateTime, create_engine from sqlalchemy.orm import Session @@ -72,10 +74,10 @@ if __name__ == '__main__': Base = declarative_base() - event.listen(Base, 'mapper_configured', configure_listener, propagate=True) + event.listen(Base, "mapper_configured", configure_listener, propagate=True) class Widget(Base): - __tablename__ = 'widget' + __tablename__ = "widget" id = Column(Integer, primary_key=True) @@ -96,8 +98,8 @@ if __name__ == '__main__': # Column-level default for the "timestamp" column will no longer fire # off. current_time = w1.timestamp - assert ( - current_time > datetime.datetime.now() - datetime.timedelta(seconds=5) + assert current_time > datetime.datetime.now() - datetime.timedelta( + seconds=5 ) # persist @@ -107,7 +109,7 @@ if __name__ == '__main__': # data is persisted. The timestamp is also the one we generated above; # e.g. the default wasn't re-invoked later. - assert ( - sess.query(Widget.radius, Widget.timestamp).first() == - (30, current_time) + assert sess.query(Widget.radius, Widget.timestamp).first() == ( + 30, + current_time, ) diff --git a/examples/custom_attributes/custom_management.py b/examples/custom_attributes/custom_management.py index 2199e0138..812385906 100644 --- a/examples/custom_attributes/custom_management.py +++ b/examples/custom_attributes/custom_management.py @@ -9,31 +9,44 @@ descriptors with a user-defined system. """ -from sqlalchemy import create_engine, MetaData, Table, Column, Integer, Text,\ - ForeignKey +from sqlalchemy import ( + create_engine, + MetaData, + Table, + Column, + Integer, + Text, + ForeignKey, +) from sqlalchemy.orm import mapper, relationship, Session -from sqlalchemy.orm.attributes import set_attribute, get_attribute, \ - del_attribute +from sqlalchemy.orm.attributes import ( + set_attribute, + get_attribute, + del_attribute, +) from sqlalchemy.orm.instrumentation import is_instrumented from sqlalchemy.ext.instrumentation import InstrumentationManager + class MyClassState(InstrumentationManager): def get_instance_dict(self, class_, instance): return instance._goofy_dict def initialize_instance_dict(self, class_, instance): - instance.__dict__['_goofy_dict'] = {} + instance.__dict__["_goofy_dict"] = {} def install_state(self, class_, instance, state): - instance.__dict__['_goofy_dict']['state'] = state + instance.__dict__["_goofy_dict"]["state"] = state def state_getter(self, class_): def find(instance): - return instance.__dict__['_goofy_dict']['state'] + return instance.__dict__["_goofy_dict"]["state"] + return find + class MyClass(object): __sa_instrumentation_manager__ = MyClassState @@ -63,17 +76,23 @@ class MyClass(object): del self._goofy_dict[key] -if __name__ == '__main__': - engine = create_engine('sqlite://') +if __name__ == "__main__": + engine = create_engine("sqlite://") meta = MetaData() - table1 = Table('table1', meta, - Column('id', Integer, primary_key=True), - Column('name', Text)) - table2 = Table('table2', meta, - Column('id', Integer, primary_key=True), - Column('name', Text), - Column('t1id', Integer, ForeignKey('table1.id'))) + table1 = Table( + "table1", + meta, + Column("id", Integer, primary_key=True), + Column("name", Text), + ) + table2 = Table( + "table2", + meta, + Column("id", Integer, primary_key=True), + Column("name", Text), + Column("t1id", Integer, ForeignKey("table1.id")), + ) meta.create_all(engine) class A(MyClass): @@ -82,16 +101,14 @@ if __name__ == '__main__': class B(MyClass): pass - mapper(A, table1, properties={ - 'bs': relationship(B) - }) + mapper(A, table1, properties={"bs": relationship(B)}) mapper(B, table2) - a1 = A(name='a1', bs=[B(name='b1'), B(name='b2')]) + a1 = A(name="a1", bs=[B(name="b1"), B(name="b2")]) - assert a1.name == 'a1' - assert a1.bs[0].name == 'b1' + assert a1.name == "a1" + assert a1.bs[0].name == "b1" sess = Session(engine) sess.add(a1) @@ -100,8 +117,8 @@ if __name__ == '__main__': a1 = sess.query(A).get(a1.id) - assert a1.name == 'a1' - assert a1.bs[0].name == 'b1' + assert a1.name == "a1" + assert a1.bs[0].name == "b1" a1.bs.remove(a1.bs[0]) diff --git a/examples/custom_attributes/listen_for_events.py b/examples/custom_attributes/listen_for_events.py index 0aeebc1d1..e3ef4cbea 100644 --- a/examples/custom_attributes/listen_for_events.py +++ b/examples/custom_attributes/listen_for_events.py @@ -5,6 +5,7 @@ and listen for change events. from sqlalchemy import event + def configure_listener(class_, key, inst): def append(instance, value, initiator): instance.receive_change_event("append", key, value, None) @@ -15,19 +16,18 @@ def configure_listener(class_, key, inst): def set_(instance, value, oldvalue, initiator): instance.receive_change_event("set", key, value, oldvalue) - event.listen(inst, 'append', append) - event.listen(inst, 'remove', remove) - event.listen(inst, 'set', set_) + event.listen(inst, "append", append) + event.listen(inst, "remove", remove) + event.listen(inst, "set", set_) -if __name__ == '__main__': +if __name__ == "__main__": from sqlalchemy import Column, Integer, String, ForeignKey from sqlalchemy.orm import relationship from sqlalchemy.ext.declarative import declarative_base class Base(object): - def receive_change_event(self, verb, key, value, oldvalue): s = "Value '%s' %s on attribute '%s', " % (value, verb, key) if oldvalue: @@ -37,7 +37,7 @@ if __name__ == '__main__': Base = declarative_base(cls=Base) - event.listen(Base, 'attribute_instrument', configure_listener) + event.listen(Base, "attribute_instrument", configure_listener) class MyMappedClass(Base): __tablename__ = "mytable" @@ -61,9 +61,7 @@ if __name__ == '__main__': # classes are instrumented. Demonstrate the events ! - m1 = MyMappedClass(data='m1', related=Related(data='r1')) - m1.data = 'm1mod' - m1.related.mapped.append(MyMappedClass(data='m2')) + m1 = MyMappedClass(data="m1", related=Related(data="r1")) + m1.data = "m1mod" + m1.related.mapped.append(MyMappedClass(data="m2")) del m1.data - - diff --git a/examples/dogpile_caching/advanced.py b/examples/dogpile_caching/advanced.py index dc2ed0771..8f395bd7b 100644 --- a/examples/dogpile_caching/advanced.py +++ b/examples/dogpile_caching/advanced.py @@ -7,6 +7,7 @@ from .environment import Session from .model import Person, cache_address_bits from .caching_query import FromCache, RelationshipCache + def load_name_range(start, end, invalidate=False): """Load Person objects on a range of names. @@ -24,10 +25,14 @@ def load_name_range(start, end, invalidate=False): SQL that emits for unloaded Person objects as well as the distribution of data within the cache. """ - q = Session.query(Person).\ - filter(Person.name.between("person %.2d" % start, "person %.2d" % end)).\ - options(cache_address_bits).\ - options(FromCache("default", "name_range")) + q = ( + Session.query(Person) + .filter( + Person.name.between("person %.2d" % start, "person %.2d" % end) + ) + .options(cache_address_bits) + .options(FromCache("default", "name_range")) + ) # have the "addresses" collection cached separately # each lazyload of Person.addresses loads from cache. @@ -37,7 +42,7 @@ def load_name_range(start, end, invalidate=False): # be cached together. This issues a bigger SQL statement and caches # a single, larger value in the cache per person rather than two # separate ones. - #q = q.options(joinedload(Person.addresses)) + # q = q.options(joinedload(Person.addresses)) # if requested, invalidate the cache on current criterion. if invalidate: @@ -45,6 +50,7 @@ def load_name_range(start, end, invalidate=False): return q.all() + print("two through twelve, possibly from cache:\n") print(", ".join([p.name for p in load_name_range(2, 12)])) @@ -61,7 +67,9 @@ print(", ".join([p.name for p in load_name_range(25, 40, True)])) # illustrate the address loading from either cache/already # on the Person -print("\n\nPeople plus addresses, two through twelve, addresses possibly from cache") +print( + "\n\nPeople plus addresses, two through twelve, addresses possibly from cache" +) for p in load_name_range(2, 12): print(p.format_full()) @@ -71,5 +79,7 @@ print("\n\nPeople plus addresses, two through twelve, addresses from cache") for p in load_name_range(2, 12): print(p.format_full()) -print("\n\nIf this was the first run of advanced.py, try "\ - "a second run. Only one SQL statement will be emitted.") +print( + "\n\nIf this was the first run of advanced.py, try " + "a second run. Only one SQL statement will be emitted." +) diff --git a/examples/dogpile_caching/caching_query.py b/examples/dogpile_caching/caching_query.py index 6ad2dba4d..060c14613 100644 --- a/examples/dogpile_caching/caching_query.py +++ b/examples/dogpile_caching/caching_query.py @@ -59,7 +59,7 @@ class CachingQuery(Query): """ super_ = super(CachingQuery, self) - if hasattr(self, '_cache_region'): + if hasattr(self, "_cache_region"): return self.get_value(createfunc=lambda: list(super_.__iter__())) else: return super_.__iter__() @@ -78,13 +78,11 @@ class CachingQuery(Query): """ super_ = super(CachingQuery, self) - if context.query is not self and hasattr(self, '_cache_region'): + if context.query is not self and hasattr(self, "_cache_region"): # special logic called when the Query._execute_and_instances() # method is called directly from the baked query return self.get_value( - createfunc=lambda: list( - super_._execute_and_instances(context) - ) + createfunc=lambda: list(super_._execute_and_instances(context)) ) else: return super_._execute_and_instances(context) @@ -105,8 +103,13 @@ class CachingQuery(Query): dogpile_region, cache_key = self._get_cache_plus_key() dogpile_region.delete(cache_key) - def get_value(self, merge=True, createfunc=None, - expiration_time=None, ignore_expiration=False): + def get_value( + self, + merge=True, + createfunc=None, + expiration_time=None, + ignore_expiration=False, + ): """Return the value from the cache for this query. Raise KeyError if no value present and no @@ -119,19 +122,20 @@ class CachingQuery(Query): # but is expired, return it anyway. This doesn't make sense # with createfunc, which says, if the value is expired, generate # a new value. - assert not ignore_expiration or not createfunc, \ - "Can't ignore expiration and also provide createfunc" + assert ( + not ignore_expiration or not createfunc + ), "Can't ignore expiration and also provide createfunc" if ignore_expiration or not createfunc: - cached_value = dogpile_region.get(cache_key, - expiration_time=expiration_time, - ignore_expiration=ignore_expiration) + cached_value = dogpile_region.get( + cache_key, + expiration_time=expiration_time, + ignore_expiration=ignore_expiration, + ) else: cached_value = dogpile_region.get_or_create( - cache_key, - createfunc, - expiration_time=expiration_time - ) + cache_key, createfunc, expiration_time=expiration_time + ) if cached_value is NO_VALUE: raise KeyError(cache_key) if merge: @@ -144,11 +148,14 @@ class CachingQuery(Query): dogpile_region, cache_key = self._get_cache_plus_key() dogpile_region.set(cache_key, value) + def query_callable(regions, query_cls=CachingQuery): def query(*arg, **kw): return query_cls(regions, *arg, **kw) + return query + def _key_from_query(query, qualifier=None): """Given a Query, create a cache key. @@ -168,9 +175,8 @@ def _key_from_query(query, qualifier=None): # here we return the key as a long string. our "key mangler" # set up with the region will boil it down to an md5. - return " ".join( - [str(compiled)] + - [str(params[k]) for k in sorted(params)]) + return " ".join([str(compiled)] + [str(params[k]) for k in sorted(params)]) + class FromCache(MapperOption): """Specifies that a Query should load results from a cache.""" @@ -198,6 +204,7 @@ class FromCache(MapperOption): """Process a Query during normal loading operation.""" query._cache_region = self + class RelationshipCache(MapperOption): """Specifies that a Query as called within a "lazy load" should load results from a cache.""" @@ -237,7 +244,9 @@ class RelationshipCache(MapperOption): for cls in mapper.class_.__mro__: if (cls, key) in self._relationship_options: - relationship_option = self._relationship_options[(cls, key)] + relationship_option = self._relationship_options[ + (cls, key) + ] query._cache_region = relationship_option break @@ -264,4 +273,3 @@ class RelationshipCache(MapperOption): """ return None - diff --git a/examples/dogpile_caching/environment.py b/examples/dogpile_caching/environment.py index 130dfdb2b..13bd0a310 100644 --- a/examples/dogpile_caching/environment.py +++ b/examples/dogpile_caching/environment.py @@ -10,6 +10,7 @@ from dogpile.cache.region import make_region import os from hashlib import md5 import sys + py2k = sys.version_info < (3, 0) if py2k: @@ -23,9 +24,7 @@ regions = {} # using a callable that will associate the dictionary # of regions with the Query. Session = scoped_session( - sessionmaker( - query_cls=caching_query.query_callable(regions) - ) + sessionmaker(query_cls=caching_query.query_callable(regions)) ) # global declarative base class. @@ -42,7 +41,7 @@ if not os.path.exists(root): os.makedirs(root) dbfile = os.path.join(root, "dogpile_demo.db") -engine = create_engine('sqlite:///%s' % dbfile, echo=True) +engine = create_engine("sqlite:///%s" % dbfile, echo=True) Session.configure(bind=engine) @@ -51,10 +50,11 @@ def md5_key_mangler(key): distill them into an md5 hash. """ - return md5(key.encode('ascii')).hexdigest() + return md5(key.encode("ascii")).hexdigest() + # configure the "default" cache region. -regions['default'] = make_region( +regions["default"] = make_region( # the "dbm" backend needs # string-encoded keys key_mangler=md5_key_mangler @@ -63,11 +63,9 @@ regions['default'] = make_region( # serialized persistence. Normally # memcached or similar is a better choice # for caching. - 'dogpile.cache.dbm', + "dogpile.cache.dbm", expiration_time=3600, - arguments={ - "filename": os.path.join(root, "cache.dbm") - } + arguments={"filename": os.path.join(root, "cache.dbm")}, ) # optional; call invalidate() on the region @@ -83,6 +81,7 @@ installed = False def bootstrap(): global installed from . import fixture_data + if not os.path.exists(dbfile): fixture_data.install() - installed = True
\ No newline at end of file + installed = True diff --git a/examples/dogpile_caching/fixture_data.py b/examples/dogpile_caching/fixture_data.py index 465171891..e301db2a4 100644 --- a/examples/dogpile_caching/fixture_data.py +++ b/examples/dogpile_caching/fixture_data.py @@ -12,13 +12,19 @@ def install(): Base.metadata.create_all(Session().bind) data = [ - ('Chicago', 'United States', ('60601', '60602', '60603', '60604')), - ('Montreal', 'Canada', ('H2S 3K9', 'H2B 1V4', 'H7G 2T8')), - ('Edmonton', 'Canada', ('T5J 1R9', 'T5J 1Z4', 'T5H 1P6')), - ('New York', 'United States', - ('10001', '10002', '10003', '10004', '10005', '10006')), - ('San Francisco', 'United States', - ('94102', '94103', '94104', '94105', '94107', '94108')) + ("Chicago", "United States", ("60601", "60602", "60603", "60604")), + ("Montreal", "Canada", ("H2S 3K9", "H2B 1V4", "H7G 2T8")), + ("Edmonton", "Canada", ("T5J 1R9", "T5J 1Z4", "T5H 1P6")), + ( + "New York", + "United States", + ("10001", "10002", "10003", "10004", "10005", "10006"), + ), + ( + "San Francisco", + "United States", + ("94102", "94103", "94104", "94105", "94107", "94108"), + ), ] countries = {} @@ -40,8 +46,9 @@ def install(): Address( street="street %.2d" % i, postal_code=all_post_codes[ - random.randint(0, len(all_post_codes) - 1)] - ) + random.randint(0, len(all_post_codes) - 1) + ], + ), ) Session.add(person) diff --git a/examples/dogpile_caching/helloworld.py b/examples/dogpile_caching/helloworld.py index 0dbde5eaf..eb565344e 100644 --- a/examples/dogpile_caching/helloworld.py +++ b/examples/dogpile_caching/helloworld.py @@ -21,28 +21,34 @@ people = Session.query(Person).options(FromCache("default")).all() # Specifying a different query produces a different cache key, so # these results are independently cached. print("loading people two through twelve") -people_two_through_twelve = Session.query(Person).\ - options(FromCache("default")).\ - filter(Person.name.between("person 02", "person 12")).\ - all() +people_two_through_twelve = ( + Session.query(Person) + .options(FromCache("default")) + .filter(Person.name.between("person 02", "person 12")) + .all() +) # the data is cached under string structure of the SQL statement, *plus* # the bind parameters of the query. So this query, having # different literal parameters under "Person.name.between()" than the # previous one, issues new SQL... print("loading people five through fifteen") -people_five_through_fifteen = Session.query(Person).\ - options(FromCache("default")).\ - filter(Person.name.between("person 05", "person 15")).\ - all() +people_five_through_fifteen = ( + Session.query(Person) + .options(FromCache("default")) + .filter(Person.name.between("person 05", "person 15")) + .all() +) # ... but using the same params as are already cached, no SQL print("loading people two through twelve...again!") -people_two_through_twelve = Session.query(Person).\ - options(FromCache("default")).\ - filter(Person.name.between("person 02", "person 12")).\ - all() +people_two_through_twelve = ( + Session.query(Person) + .options(FromCache("default")) + .filter(Person.name.between("person 02", "person 12")) + .all() +) # invalidate the cache for the three queries we've done. Recreate @@ -51,10 +57,9 @@ people_two_through_twelve = Session.query(Person).\ # same order, then call invalidate(). print("invalidating everything") Session.query(Person).options(FromCache("default")).invalidate() -Session.query(Person).\ - options(FromCache("default")).\ - filter(Person.name.between("person 02", "person 12")).invalidate() -Session.query(Person).\ - options(FromCache("default", "people_on_range")).\ - filter(Person.name.between("person 05", "person 15")).invalidate() - +Session.query(Person).options(FromCache("default")).filter( + Person.name.between("person 02", "person 12") +).invalidate() +Session.query(Person).options(FromCache("default", "people_on_range")).filter( + Person.name.between("person 05", "person 15") +).invalidate() diff --git a/examples/dogpile_caching/local_session_caching.py b/examples/dogpile_caching/local_session_caching.py index 633252fc7..358886bf0 100644 --- a/examples/dogpile_caching/local_session_caching.py +++ b/examples/dogpile_caching/local_session_caching.py @@ -30,7 +30,7 @@ class ScopedSessionBackend(CacheBackend): """ def __init__(self, arguments): - self.scoped_session = arguments['scoped_session'] + self.scoped_session = arguments["scoped_session"] def get(self, key): return self._cache_dictionary.get(key, NO_VALUE) @@ -52,10 +52,11 @@ class ScopedSessionBackend(CacheBackend): sess._cache_dictionary = cache_dict = {} return cache_dict + register_backend("sqlalchemy.session", __name__, "ScopedSessionBackend") -if __name__ == '__main__': +if __name__ == "__main__": from .environment import Session, regions from .caching_query import FromCache from dogpile.cache import make_region @@ -63,20 +64,19 @@ if __name__ == '__main__': # set up a region based on the ScopedSessionBackend, # pointing to the scoped_session declared in the example # environment. - regions['local_session'] = make_region().configure( - 'sqlalchemy.session', - arguments={ - "scoped_session": Session - } + regions["local_session"] = make_region().configure( + "sqlalchemy.session", arguments={"scoped_session": Session} ) from .model import Person # query to load Person by name, with criterion # of "person 10" - q = Session.query(Person).\ - options(FromCache("local_session")).\ - filter(Person.name == "person 10") + q = ( + Session.query(Person) + .options(FromCache("local_session")) + .filter(Person.name == "person 10") + ) # load from DB person10 = q.one() diff --git a/examples/dogpile_caching/model.py b/examples/dogpile_caching/model.py index 3eb02108c..f6a259820 100644 --- a/examples/dogpile_caching/model.py +++ b/examples/dogpile_caching/model.py @@ -14,7 +14,7 @@ from .environment import Base, bootstrap class Country(Base): - __tablename__ = 'country' + __tablename__ = "country" id = Column(Integer, primary_key=True) name = Column(String(100), nullable=False) @@ -24,11 +24,11 @@ class Country(Base): class City(Base): - __tablename__ = 'city' + __tablename__ = "city" id = Column(Integer, primary_key=True) name = Column(String(100), nullable=False) - country_id = Column(Integer, ForeignKey('country.id'), nullable=False) + country_id = Column(Integer, ForeignKey("country.id"), nullable=False) country = relationship(Country) def __init__(self, name, country): @@ -37,11 +37,11 @@ class City(Base): class PostalCode(Base): - __tablename__ = 'postal_code' + __tablename__ = "postal_code" id = Column(Integer, primary_key=True) code = Column(String(10), nullable=False) - city_id = Column(Integer, ForeignKey('city.id'), nullable=False) + city_id = Column(Integer, ForeignKey("city.id"), nullable=False) city = relationship(City) @property @@ -54,12 +54,12 @@ class PostalCode(Base): class Address(Base): - __tablename__ = 'address' + __tablename__ = "address" id = Column(Integer, primary_key=True) - person_id = Column(Integer, ForeignKey('person.id'), nullable=False) + person_id = Column(Integer, ForeignKey("person.id"), nullable=False) street = Column(String(200), nullable=False) - postal_code_id = Column(Integer, ForeignKey('postal_code.id')) + postal_code_id = Column(Integer, ForeignKey("postal_code.id")) postal_code = relationship(PostalCode) @property @@ -71,15 +71,16 @@ class Address(Base): return self.postal_code.country def __str__(self): - return ( - "%s\t%s, %s\t%s" % ( - self.street, self.city.name, - self.postal_code.code, self.country.name) + return "%s\t%s, %s\t%s" % ( + self.street, + self.city.name, + self.postal_code.code, + self.country.name, ) class Person(Base): - __tablename__ = 'person' + __tablename__ = "person" id = Column(Integer, primary_key=True) name = Column(String(100), nullable=False) @@ -98,14 +99,14 @@ class Person(Base): def format_full(self): return "\t".join([str(x) for x in [self] + list(self.addresses)]) + # Caching options. A set of three RelationshipCache options # which can be applied to Query(), causing the "lazy load" # of these attributes to be loaded from cache. -cache_address_bits = RelationshipCache(PostalCode.city, "default").\ - and_( - RelationshipCache(City.country, "default") -).and_( - RelationshipCache(Address.postal_code, "default") +cache_address_bits = ( + RelationshipCache(PostalCode.city, "default") + .and_(RelationshipCache(City.country, "default")) + .and_(RelationshipCache(Address.postal_code, "default")) ) bootstrap() diff --git a/examples/dogpile_caching/relationship_caching.py b/examples/dogpile_caching/relationship_caching.py index 920d696f8..76c7e767f 100644 --- a/examples/dogpile_caching/relationship_caching.py +++ b/examples/dogpile_caching/relationship_caching.py @@ -12,7 +12,8 @@ from sqlalchemy.orm import joinedload import os for p in Session.query(Person).options( - joinedload(Person.addresses), cache_address_bits): + joinedload(Person.addresses), cache_address_bits +): print(p.format_full()) @@ -25,5 +26,5 @@ print( "related data is pulled from cache.\n" "To clear the cache, delete the file %r. \n" "This will cause a re-load of cities, postal codes and countries on " - "the next run.\n" - % os.path.join(root, 'cache.dbm')) + "the next run.\n" % os.path.join(root, "cache.dbm") +) diff --git a/examples/dynamic_dict/__init__.py b/examples/dynamic_dict/__init__.py index e592ea200..ed31df062 100644 --- a/examples/dynamic_dict/__init__.py +++ b/examples/dynamic_dict/__init__.py @@ -5,4 +5,4 @@ full collection at once. .. autosource:: -"""
\ No newline at end of file +""" diff --git a/examples/dynamic_dict/dynamic_dict.py b/examples/dynamic_dict/dynamic_dict.py index 530674f2e..62da55c38 100644 --- a/examples/dynamic_dict/dynamic_dict.py +++ b/examples/dynamic_dict/dynamic_dict.py @@ -14,7 +14,7 @@ class ProxyDict(object): return [x[0] for x in self.collection.values(descriptor)] def __getitem__(self, key): - x = self.collection.filter_by(**{self.keyname:key}).first() + x = self.collection.filter_by(**{self.keyname: key}).first() if x: return x else: @@ -28,43 +28,48 @@ class ProxyDict(object): pass self.collection.append(value) + from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import create_engine, Column, Integer, String, ForeignKey from sqlalchemy.orm import sessionmaker, relationship -engine = create_engine('sqlite://', echo=True) +engine = create_engine("sqlite://", echo=True) Base = declarative_base(engine) + class Parent(Base): - __tablename__ = 'parent' + __tablename__ = "parent" id = Column(Integer, primary_key=True) name = Column(String(50)) - _collection = relationship("Child", lazy="dynamic", - cascade="all, delete-orphan") + _collection = relationship( + "Child", lazy="dynamic", cascade="all, delete-orphan" + ) @property def child_map(self): - return ProxyDict(self, '_collection', Child, 'key') + return ProxyDict(self, "_collection", Child, "key") + class Child(Base): - __tablename__ = 'child' + __tablename__ = "child" id = Column(Integer, primary_key=True) key = Column(String(50)) - parent_id = Column(Integer, ForeignKey('parent.id')) + parent_id = Column(Integer, ForeignKey("parent.id")) def __repr__(self): return "Child(key=%r)" % self.key + Base.metadata.create_all() sess = sessionmaker()() -p1 = Parent(name='p1') +p1 = Parent(name="p1") sess.add(p1) print("\n---------begin setting nodes, autoflush occurs\n") -p1.child_map['k1'] = Child(key='k1') -p1.child_map['k2'] = Child(key='k2') +p1.child_map["k1"] = Child(key="k1") +p1.child_map["k2"] = Child(key="k2") # this will autoflush the current map. # ['k1', 'k2'] @@ -73,16 +78,15 @@ print(list(p1.child_map.keys())) # k1 print("\n---------print 'k1' node\n") -print(p1.child_map['k1']) +print(p1.child_map["k1"]) print("\n---------update 'k2' node - must find existing, and replace\n") -p1.child_map['k2'] = Child(key='k2') +p1.child_map["k2"] = Child(key="k2") print("\n---------print 'k2' key - flushes first\n") # k2 -print(p1.child_map['k2']) +print(p1.child_map["k2"]) print("\n---------print all child nodes\n") # [k1, k2b] print(sess.query(Child).all()) - diff --git a/examples/elementtree/__init__.py b/examples/elementtree/__init__.py index 66e9cfbbe..82d00ff5a 100644 --- a/examples/elementtree/__init__.py +++ b/examples/elementtree/__init__.py @@ -22,4 +22,4 @@ E.g.:: .. autosource:: :files: pickle.py, adjacency_list.py, optimized_al.py -"""
\ No newline at end of file +""" diff --git a/examples/elementtree/adjacency_list.py b/examples/elementtree/adjacency_list.py index 5e27ba9ca..1f7161212 100644 --- a/examples/elementtree/adjacency_list.py +++ b/examples/elementtree/adjacency_list.py @@ -15,42 +15,63 @@ styles of persistence are identical, as is the structure of the main Document cl """ ################################# PART I - Imports/Coniguration #################################### -from sqlalchemy import (MetaData, Table, Column, Integer, String, ForeignKey, - Unicode, and_, create_engine) +from sqlalchemy import ( + MetaData, + Table, + Column, + Integer, + String, + ForeignKey, + Unicode, + and_, + create_engine, +) from sqlalchemy.orm import mapper, relationship, Session, lazyload import sys, os, io, re from xml.etree import ElementTree -e = create_engine('sqlite://') +e = create_engine("sqlite://") meta = MetaData() ################################# PART II - Table Metadata ######################################### # stores a top level record of an XML document. -documents = Table('documents', meta, - Column('document_id', Integer, primary_key=True), - Column('filename', String(30), unique=True), - Column('element_id', Integer, ForeignKey('elements.element_id')) +documents = Table( + "documents", + meta, + Column("document_id", Integer, primary_key=True), + Column("filename", String(30), unique=True), + Column("element_id", Integer, ForeignKey("elements.element_id")), ) # stores XML nodes in an adjacency list model. This corresponds to # Element and SubElement objects. -elements = Table('elements', meta, - Column('element_id', Integer, primary_key=True), - Column('parent_id', Integer, ForeignKey('elements.element_id')), - Column('tag', Unicode(30), nullable=False), - Column('text', Unicode), - Column('tail', Unicode) - ) +elements = Table( + "elements", + meta, + Column("element_id", Integer, primary_key=True), + Column("parent_id", Integer, ForeignKey("elements.element_id")), + Column("tag", Unicode(30), nullable=False), + Column("text", Unicode), + Column("tail", Unicode), +) # stores attributes. This corresponds to the dictionary of attributes # stored by an Element or SubElement. -attributes = Table('attributes', meta, - Column('element_id', Integer, ForeignKey('elements.element_id'), primary_key=True), - Column('name', Unicode(100), nullable=False, primary_key=True), - Column('value', Unicode(255))) +attributes = Table( + "attributes", + meta, + Column( + "element_id", + Integer, + ForeignKey("elements.element_id"), + primary_key=True, + ), + Column("name", Unicode(100), nullable=False, primary_key=True), + Column("value", Unicode(255)), +) meta.create_all(e) @@ -68,6 +89,7 @@ class Document(object): self.element.write(buf) return buf.getvalue() + #################################### PART IV - Persistence Mapping ################################# # Node class. a non-public class which will represent @@ -78,6 +100,7 @@ class Document(object): class _Node(object): pass + # Attribute class. also internal, this will represent the key/value attributes stored for # a particular Node. class _Attribute(object): @@ -85,16 +108,25 @@ class _Attribute(object): self.name = name self.value = value + # setup mappers. Document will eagerly load a list of _Node objects. -mapper(Document, documents, properties={ - '_root':relationship(_Node, lazy='joined', cascade="all") -}) +mapper( + Document, + documents, + properties={"_root": relationship(_Node, lazy="joined", cascade="all")}, +) -mapper(_Node, elements, properties={ - 'children':relationship(_Node, cascade="all"), - # eagerly load attributes - 'attributes':relationship(_Attribute, lazy='joined', cascade="all, delete-orphan"), -}) +mapper( + _Node, + elements, + properties={ + "children": relationship(_Node, cascade="all"), + # eagerly load attributes + "attributes": relationship( + _Attribute, lazy="joined", cascade="all, delete-orphan" + ), + }, +) mapper(_Attribute, attributes) @@ -106,7 +138,7 @@ class ElementTreeMarshal(object): if document is None: return self - if hasattr(document, '_element'): + if hasattr(document, "_element"): return document._element def traverse(node, parent=None): @@ -132,7 +164,9 @@ class ElementTreeMarshal(object): n.text = str(node.text) n.tail = str(node.tail) n.children = [traverse(n2) for n2 in node] - n.attributes = [_Attribute(str(k), str(v)) for k, v in node.attrib.items()] + n.attributes = [ + _Attribute(str(k), str(v)) for k, v in node.attrib.items() + ] return n document._root = traverse(element.getroot()) @@ -142,6 +176,7 @@ class ElementTreeMarshal(object): del document._element document._root = [] + # override Document's "element" attribute with the marshaller. Document.element = ElementTreeMarshal() @@ -153,7 +188,7 @@ line = "\n--------------------------------------------------------" session = Session(e) # get ElementTree documents -for file in ('test.xml', 'test2.xml', 'test3.xml'): +for file in ("test.xml", "test2.xml", "test3.xml"): filename = os.path.join(os.path.dirname(__file__), file) doc = ElementTree.parse(filename) session.add(Document(file, doc)) @@ -170,10 +205,16 @@ print(document) ############################################ PART VI - Searching for Paths ######################### # manually search for a document which contains "/somefile/header/field1:hi" -d = session.query(Document).join('_root', aliased=True).filter(_Node.tag=='somefile').\ - join('children', aliased=True, from_joinpoint=True).filter(_Node.tag=='header').\ - join('children', aliased=True, from_joinpoint=True).filter( - and_(_Node.tag=='field1', _Node.text=='hi')).one() +d = ( + session.query(Document) + .join("_root", aliased=True) + .filter(_Node.tag == "somefile") + .join("children", aliased=True, from_joinpoint=True) + .filter(_Node.tag == "header") + .join("children", aliased=True, from_joinpoint=True) + .filter(and_(_Node.tag == "field1", _Node.text == "hi")) + .one() +) print(d) # generalize the above approach into an extremely impoverished xpath function: @@ -181,26 +222,39 @@ def find_document(path, compareto): j = documents prev_elements = None query = session.query(Document) - attribute = '_root' - for i, match in enumerate(re.finditer(r'/([\w_]+)(?:\[@([\w_]+)(?:=(.*))?\])?', path)): + attribute = "_root" + for i, match in enumerate( + re.finditer(r"/([\w_]+)(?:\[@([\w_]+)(?:=(.*))?\])?", path) + ): (token, attrname, attrvalue) = match.group(1, 2, 3) - query = query.join(attribute, aliased=True, from_joinpoint=True).filter(_Node.tag==token) - attribute = 'children' + query = query.join( + attribute, aliased=True, from_joinpoint=True + ).filter(_Node.tag == token) + attribute = "children" if attrname: if attrvalue: - query = query.join('attributes', aliased=True, from_joinpoint=True).filter( - and_(_Attribute.name==attrname, _Attribute.value==attrvalue)) + query = query.join( + "attributes", aliased=True, from_joinpoint=True + ).filter( + and_( + _Attribute.name == attrname, + _Attribute.value == attrvalue, + ) + ) else: - query = query.join('attributes', aliased=True, from_joinpoint=True).filter( - _Attribute.name==attrname) - return query.options(lazyload('_root')).filter(_Node.text==compareto).all() + query = query.join( + "attributes", aliased=True, from_joinpoint=True + ).filter(_Attribute.name == attrname) + return ( + query.options(lazyload("_root")).filter(_Node.text == compareto).all() + ) + for path, compareto in ( - ('/somefile/header/field1', 'hi'), - ('/somefile/field1', 'hi'), - ('/somefile/header/field2', 'there'), - ('/somefile/header/field2[@attr=foo]', 'there') - ): + ("/somefile/header/field1", "hi"), + ("/somefile/field1", "hi"), + ("/somefile/header/field2", "there"), + ("/somefile/header/field2[@attr=foo]", "there"), +): print("\nDocuments containing '%s=%s':" % (path, compareto), line) print([d.filename for d in find_document(path, compareto)]) - diff --git a/examples/elementtree/optimized_al.py b/examples/elementtree/optimized_al.py index e13f5b0ee..8e9c48b96 100644 --- a/examples/elementtree/optimized_al.py +++ b/examples/elementtree/optimized_al.py @@ -8,42 +8,63 @@ """ ##################### PART I - Imports/Configuration ######################### -from sqlalchemy import (MetaData, Table, Column, Integer, String, ForeignKey, - Unicode, and_, create_engine) +from sqlalchemy import ( + MetaData, + Table, + Column, + Integer, + String, + ForeignKey, + Unicode, + and_, + create_engine, +) from sqlalchemy.orm import mapper, relationship, Session, lazyload import sys, os, io, re from xml.etree import ElementTree -e = create_engine('sqlite://', echo=True) +e = create_engine("sqlite://", echo=True) meta = MetaData() ####################### PART II - Table Metadata ############################# # stores a top level record of an XML document. -documents = Table('documents', meta, - Column('document_id', Integer, primary_key=True), - Column('filename', String(30), unique=True), +documents = Table( + "documents", + meta, + Column("document_id", Integer, primary_key=True), + Column("filename", String(30), unique=True), ) # stores XML nodes in an adjacency list model. This corresponds to # Element and SubElement objects. -elements = Table('elements', meta, - Column('element_id', Integer, primary_key=True), - Column('parent_id', Integer, ForeignKey('elements.element_id')), - Column('document_id', Integer, ForeignKey('documents.document_id')), - Column('tag', Unicode(30), nullable=False), - Column('text', Unicode), - Column('tail', Unicode) - ) +elements = Table( + "elements", + meta, + Column("element_id", Integer, primary_key=True), + Column("parent_id", Integer, ForeignKey("elements.element_id")), + Column("document_id", Integer, ForeignKey("documents.document_id")), + Column("tag", Unicode(30), nullable=False), + Column("text", Unicode), + Column("tail", Unicode), +) # stores attributes. This corresponds to the dictionary of attributes # stored by an Element or SubElement. -attributes = Table('attributes', meta, - Column('element_id', Integer, ForeignKey('elements.element_id'), primary_key=True), - Column('name', Unicode(100), nullable=False, primary_key=True), - Column('value', Unicode(255))) +attributes = Table( + "attributes", + meta, + Column( + "element_id", + Integer, + ForeignKey("elements.element_id"), + primary_key=True, + ), + Column("name", Unicode(100), nullable=False, primary_key=True), + Column("value", Unicode(255)), +) meta.create_all(e) @@ -61,6 +82,7 @@ class Document(object): self.element.write(buf) return buf.getvalue() + ########################## PART IV - Persistence Mapping ##################### # Node class. a non-public class which will represent @@ -71,6 +93,7 @@ class Document(object): class _Node(object): pass + # Attribute class. also internal, this will represent the key/value attributes stored for # a particular Node. class _Attribute(object): @@ -78,21 +101,36 @@ class _Attribute(object): self.name = name self.value = value + # setup mappers. Document will eagerly load a list of _Node objects. # they will be ordered in primary key/insert order, so that we can reconstruct # an ElementTree structure from the list. -mapper(Document, documents, properties={ - '_nodes':relationship(_Node, lazy='joined', cascade="all, delete-orphan") -}) +mapper( + Document, + documents, + properties={ + "_nodes": relationship( + _Node, lazy="joined", cascade="all, delete-orphan" + ) + }, +) # the _Node objects change the way they load so that a list of _Nodes will organize # themselves hierarchically using the ElementTreeMarshal. this depends on the ordering of # nodes being hierarchical as well; relationship() always applies at least ROWID/primary key # ordering to rows which will suffice. -mapper(_Node, elements, properties={ - 'children':relationship(_Node, lazy=None), # doesnt load; used only for the save relationship - 'attributes':relationship(_Attribute, lazy='joined', cascade="all, delete-orphan"), # eagerly load attributes -}) +mapper( + _Node, + elements, + properties={ + "children": relationship( + _Node, lazy=None + ), # doesnt load; used only for the save relationship + "attributes": relationship( + _Attribute, lazy="joined", cascade="all, delete-orphan" + ), # eagerly load attributes + }, +) mapper(_Attribute, attributes) @@ -104,7 +142,7 @@ class ElementTreeMarshal(object): if document is None: return self - if hasattr(document, '_element'): + if hasattr(document, "_element"): return document._element nodes = {} @@ -134,7 +172,9 @@ class ElementTreeMarshal(object): n.tail = str(node.tail) document._nodes.append(n) n.children = [traverse(n2) for n2 in node] - n.attributes = [_Attribute(str(k), str(v)) for k, v in node.attrib.items()] + n.attributes = [ + _Attribute(str(k), str(v)) for k, v in node.attrib.items() + ] return n traverse(element.getroot()) @@ -144,6 +184,7 @@ class ElementTreeMarshal(object): del document._element document._nodes = [] + # override Document's "element" attribute with the marshaller. Document.element = ElementTreeMarshal() @@ -155,7 +196,7 @@ line = "\n--------------------------------------------------------" session = Session(e) # get ElementTree documents -for file in ('test.xml', 'test2.xml', 'test3.xml'): +for file in ("test.xml", "test2.xml", "test3.xml"): filename = os.path.join(os.path.dirname(__file__), file) doc = ElementTree.parse(filename) session.add(Document(file, doc)) @@ -173,13 +214,16 @@ print(document) # manually search for a document which contains "/somefile/header/field1:hi" print("\nManual search for /somefile/header/field1=='hi':", line) -d = session.query(Document).join('_nodes', aliased=True).\ - filter(and_(_Node.parent_id==None, _Node.tag=='somefile')).\ - join('children', aliased=True, from_joinpoint=True).\ - filter(_Node.tag=='header').\ - join('children', aliased=True, from_joinpoint=True).\ - filter(and_(_Node.tag=='field1', _Node.text=='hi')).\ - one() +d = ( + session.query(Document) + .join("_nodes", aliased=True) + .filter(and_(_Node.parent_id == None, _Node.tag == "somefile")) + .join("children", aliased=True, from_joinpoint=True) + .filter(_Node.tag == "header") + .join("children", aliased=True, from_joinpoint=True) + .filter(and_(_Node.tag == "field1", _Node.text == "hi")) + .one() +) print(d) # generalize the above approach into an extremely impoverished xpath function: @@ -188,28 +232,39 @@ def find_document(path, compareto): prev_elements = None query = session.query(Document) first = True - for i, match in enumerate(re.finditer(r'/([\w_]+)(?:\[@([\w_]+)(?:=(.*))?\])?', path)): + for i, match in enumerate( + re.finditer(r"/([\w_]+)(?:\[@([\w_]+)(?:=(.*))?\])?", path) + ): (token, attrname, attrvalue) = match.group(1, 2, 3) if first: - query = query.join('_nodes', aliased=True).filter(_Node.parent_id==None) + query = query.join("_nodes", aliased=True).filter( + _Node.parent_id == None + ) first = False else: - query = query.join('children', aliased=True, from_joinpoint=True) - query = query.filter(_Node.tag==token) + query = query.join("children", aliased=True, from_joinpoint=True) + query = query.filter(_Node.tag == token) if attrname: - query = query.join('attributes', aliased=True, from_joinpoint=True) + query = query.join("attributes", aliased=True, from_joinpoint=True) if attrvalue: - query = query.filter(and_(_Attribute.name==attrname, _Attribute.value==attrvalue)) + query = query.filter( + and_( + _Attribute.name == attrname, + _Attribute.value == attrvalue, + ) + ) else: - query = query.filter(_Attribute.name==attrname) - return query.options(lazyload('_nodes')).filter(_Node.text==compareto).all() + query = query.filter(_Attribute.name == attrname) + return ( + query.options(lazyload("_nodes")).filter(_Node.text == compareto).all() + ) + for path, compareto in ( - ('/somefile/header/field1', 'hi'), - ('/somefile/field1', 'hi'), - ('/somefile/header/field2', 'there'), - ('/somefile/header/field2[@attr=foo]', 'there') - ): + ("/somefile/header/field1", "hi"), + ("/somefile/field1", "hi"), + ("/somefile/header/field2", "there"), + ("/somefile/header/field2[@attr=foo]", "there"), +): print("\nDocuments containing '%s=%s':" % (path, compareto), line) print([d.filename for d in find_document(path, compareto)]) - diff --git a/examples/elementtree/pickle.py b/examples/elementtree/pickle.py index d40af275b..a86fe30e5 100644 --- a/examples/elementtree/pickle.py +++ b/examples/elementtree/pickle.py @@ -6,15 +6,22 @@ structure in distinct rows using two additional mapped entities. Note that the styles of persistence are identical, as is the structure of the main Document class. """ -from sqlalchemy import (create_engine, MetaData, Table, Column, Integer, String, - PickleType) +from sqlalchemy import ( + create_engine, + MetaData, + Table, + Column, + Integer, + String, + PickleType, +) from sqlalchemy.orm import mapper, Session import sys, os from xml.etree import ElementTree -e = create_engine('sqlite://') +e = create_engine("sqlite://") meta = MetaData() # setup a comparator for the PickleType since it's a mutable @@ -22,12 +29,15 @@ meta = MetaData() def are_elements_equal(x, y): return x == y + # stores a top level record of an XML document. # the "element" column will store the ElementTree document as a BLOB. -documents = Table('documents', meta, - Column('document_id', Integer, primary_key=True), - Column('filename', String(30), unique=True), - Column('element', PickleType(comparator=are_elements_equal)) +documents = Table( + "documents", + meta, + Column("document_id", Integer, primary_key=True), + Column("filename", String(30), unique=True), + Column("element", PickleType(comparator=are_elements_equal)), ) meta.create_all(e) @@ -39,6 +49,7 @@ class Document(object): self.filename = name self.element = element + # setup mapper. mapper(Document, documents) @@ -58,4 +69,3 @@ document = session.query(Document).filter_by(filename="test.xml").first() # print document.element.write(sys.stdout) - diff --git a/examples/generic_associations/__init__.py b/examples/generic_associations/__init__.py index b6593b4f4..9d103b73e 100644 --- a/examples/generic_associations/__init__.py +++ b/examples/generic_associations/__init__.py @@ -15,4 +15,4 @@ are modernized versions of recipes presented in the 2007 blog post .. autosource:: -"""
\ No newline at end of file +""" diff --git a/examples/generic_associations/discriminator_on_association.py b/examples/generic_associations/discriminator_on_association.py index c3501eefb..38f2370f3 100644 --- a/examples/generic_associations/discriminator_on_association.py +++ b/examples/generic_associations/discriminator_on_association.py @@ -16,27 +16,31 @@ objects, but is also slightly more complex. """ from sqlalchemy.ext.declarative import as_declarative, declared_attr -from sqlalchemy import create_engine, Integer, Column, \ - String, ForeignKey +from sqlalchemy import create_engine, Integer, Column, String, ForeignKey from sqlalchemy.orm import Session, relationship, backref from sqlalchemy.ext.associationproxy import association_proxy + @as_declarative() class Base(object): """Base class which provides automated table name and surrogate primary key column. """ + @declared_attr def __tablename__(cls): return cls.__name__.lower() + id = Column(Integer, primary_key=True) + class AddressAssociation(Base): """Associates a collection of Address objects with a particular parent. """ + __tablename__ = "address_association" discriminator = Column(String) @@ -44,6 +48,7 @@ class AddressAssociation(Base): __mapper_args__ = {"polymorphic_on": discriminator} + class Address(Base): """The Address class. @@ -51,6 +56,7 @@ class Address(Base): single table. """ + association_id = Column(Integer, ForeignKey("address_association.id")) street = Column(String) city = Column(String) @@ -60,15 +66,20 @@ class Address(Base): parent = association_proxy("association", "parent") def __repr__(self): - return "%s(street=%r, city=%r, zip=%r)" % \ - (self.__class__.__name__, self.street, - self.city, self.zip) + return "%s(street=%r, city=%r, zip=%r)" % ( + self.__class__.__name__, + self.street, + self.city, + self.zip, + ) + class HasAddresses(object): """HasAddresses mixin, creates a relationship to the address_association table for each parent. """ + @declared_attr def address_association_id(cls): return Column(Integer, ForeignKey("address_association.id")) @@ -79,63 +90,62 @@ class HasAddresses(object): discriminator = name.lower() assoc_cls = type( - "%sAddressAssociation" % name, - (AddressAssociation, ), - dict( - __tablename__=None, - __mapper_args__={ - "polymorphic_identity": discriminator - } - ) - ) + "%sAddressAssociation" % name, + (AddressAssociation,), + dict( + __tablename__=None, + __mapper_args__={"polymorphic_identity": discriminator}, + ), + ) cls.addresses = association_proxy( - "address_association", "addresses", - creator=lambda addresses: assoc_cls(addresses=addresses) - ) - return relationship(assoc_cls, - backref=backref("parent", uselist=False)) + "address_association", + "addresses", + creator=lambda addresses: assoc_cls(addresses=addresses), + ) + return relationship( + assoc_cls, backref=backref("parent", uselist=False) + ) class Customer(HasAddresses, Base): name = Column(String) + class Supplier(HasAddresses, Base): company_name = Column(String) -engine = create_engine('sqlite://', echo=True) + +engine = create_engine("sqlite://", echo=True) Base.metadata.create_all(engine) session = Session(engine) -session.add_all([ - Customer( - name='customer 1', - addresses=[ - Address( - street='123 anywhere street', - city="New York", - zip="10110"), - Address( - street='40 main street', - city="San Francisco", - zip="95732") - ] - ), - Supplier( - company_name="Ace Hammers", - addresses=[ - Address( - street='2569 west elm', - city="Detroit", - zip="56785") - ] - ), -]) +session.add_all( + [ + Customer( + name="customer 1", + addresses=[ + Address( + street="123 anywhere street", city="New York", zip="10110" + ), + Address( + street="40 main street", city="San Francisco", zip="95732" + ), + ], + ), + Supplier( + company_name="Ace Hammers", + addresses=[ + Address(street="2569 west elm", city="Detroit", zip="56785") + ], + ), + ] +) session.commit() for customer in session.query(Customer): for address in customer.addresses: print(address) - print(address.parent)
\ No newline at end of file + print(address.parent) diff --git a/examples/generic_associations/generic_fk.py b/examples/generic_associations/generic_fk.py index 31d2c138d..ded8f749d 100644 --- a/examples/generic_associations/generic_fk.py +++ b/examples/generic_associations/generic_fk.py @@ -20,8 +20,7 @@ or "table_per_association" instead of this approach. """ from sqlalchemy.ext.declarative import as_declarative, declared_attr -from sqlalchemy import create_engine, Integer, Column, \ - String, and_ +from sqlalchemy import create_engine, Integer, Column, String, and_ from sqlalchemy.orm import Session, relationship, foreign, remote, backref from sqlalchemy import event @@ -32,11 +31,14 @@ class Base(object): and surrogate primary key column. """ + @declared_attr def __tablename__(cls): return cls.__name__.lower() + id = Column(Integer, primary_key=True) + class Address(Base): """The Address class. @@ -44,6 +46,7 @@ class Address(Base): single table. """ + street = Column(String) city = Column(String) zip = Column(String) @@ -66,9 +69,13 @@ class Address(Base): return getattr(self, "parent_%s" % self.discriminator) def __repr__(self): - return "%s(street=%r, city=%r, zip=%r)" % \ - (self.__class__.__name__, self.street, - self.city, self.zip) + return "%s(street=%r, city=%r, zip=%r)" % ( + self.__class__.__name__, + self.street, + self.city, + self.zip, + ) + class HasAddresses(object): """HasAddresses mixin, creates a relationship to @@ -76,63 +83,66 @@ class HasAddresses(object): """ + @event.listens_for(HasAddresses, "mapper_configured", propagate=True) def setup_listener(mapper, class_): name = class_.__name__ discriminator = name.lower() - class_.addresses = relationship(Address, - primaryjoin=and_( - class_.id == foreign(remote(Address.parent_id)), - Address.discriminator == discriminator - ), - backref=backref( - "parent_%s" % discriminator, - primaryjoin=remote(class_.id) == foreign(Address.parent_id) - ) - ) + class_.addresses = relationship( + Address, + primaryjoin=and_( + class_.id == foreign(remote(Address.parent_id)), + Address.discriminator == discriminator, + ), + backref=backref( + "parent_%s" % discriminator, + primaryjoin=remote(class_.id) == foreign(Address.parent_id), + ), + ) + @event.listens_for(class_.addresses, "append") def append_address(target, value, initiator): value.discriminator = discriminator + class Customer(HasAddresses, Base): name = Column(String) + class Supplier(HasAddresses, Base): company_name = Column(String) -engine = create_engine('sqlite://', echo=True) + +engine = create_engine("sqlite://", echo=True) Base.metadata.create_all(engine) session = Session(engine) -session.add_all([ - Customer( - name='customer 1', - addresses=[ - Address( - street='123 anywhere street', - city="New York", - zip="10110"), - Address( - street='40 main street', - city="San Francisco", - zip="95732") - ] - ), - Supplier( - company_name="Ace Hammers", - addresses=[ - Address( - street='2569 west elm', - city="Detroit", - zip="56785") - ] - ), -]) +session.add_all( + [ + Customer( + name="customer 1", + addresses=[ + Address( + street="123 anywhere street", city="New York", zip="10110" + ), + Address( + street="40 main street", city="San Francisco", zip="95732" + ), + ], + ), + Supplier( + company_name="Ace Hammers", + addresses=[ + Address(street="2569 west elm", city="Detroit", zip="56785") + ], + ), + ] +) session.commit() for customer in session.query(Customer): for address in customer.addresses: print(address) - print(address.parent)
\ No newline at end of file + print(address.parent) diff --git a/examples/generic_associations/table_per_association.py b/examples/generic_associations/table_per_association.py index d54d2f1fa..7de246934 100644 --- a/examples/generic_associations/table_per_association.py +++ b/examples/generic_associations/table_per_association.py @@ -12,21 +12,31 @@ has no dependency on the system. """ from sqlalchemy.ext.declarative import as_declarative, declared_attr -from sqlalchemy import create_engine, Integer, Column, \ - String, ForeignKey, Table +from sqlalchemy import ( + create_engine, + Integer, + Column, + String, + ForeignKey, + Table, +) from sqlalchemy.orm import Session, relationship + @as_declarative() class Base(object): """Base class which provides automated table name and surrogate primary key column. """ + @declared_attr def __tablename__(cls): return cls.__name__.lower() + id = Column(Integer, primary_key=True) + class Address(Base): """The Address class. @@ -34,72 +44,79 @@ class Address(Base): single table. """ + street = Column(String) city = Column(String) zip = Column(String) def __repr__(self): - return "%s(street=%r, city=%r, zip=%r)" % \ - (self.__class__.__name__, self.street, - self.city, self.zip) + return "%s(street=%r, city=%r, zip=%r)" % ( + self.__class__.__name__, + self.street, + self.city, + self.zip, + ) + class HasAddresses(object): """HasAddresses mixin, creates a new address_association table for each parent. """ + @declared_attr def addresses(cls): address_association = Table( "%s_addresses" % cls.__tablename__, cls.metadata, - Column("address_id", ForeignKey("address.id"), - primary_key=True), - Column("%s_id" % cls.__tablename__, - ForeignKey("%s.id" % cls.__tablename__), - primary_key=True), + Column("address_id", ForeignKey("address.id"), primary_key=True), + Column( + "%s_id" % cls.__tablename__, + ForeignKey("%s.id" % cls.__tablename__), + primary_key=True, + ), ) return relationship(Address, secondary=address_association) + class Customer(HasAddresses, Base): name = Column(String) + class Supplier(HasAddresses, Base): company_name = Column(String) -engine = create_engine('sqlite://', echo=True) + +engine = create_engine("sqlite://", echo=True) Base.metadata.create_all(engine) session = Session(engine) -session.add_all([ - Customer( - name='customer 1', - addresses=[ - Address( - street='123 anywhere street', - city="New York", - zip="10110"), - Address( - street='40 main street', - city="San Francisco", - zip="95732") - ] - ), - Supplier( - company_name="Ace Hammers", - addresses=[ - Address( - street='2569 west elm', - city="Detroit", - zip="56785") - ] - ), -]) +session.add_all( + [ + Customer( + name="customer 1", + addresses=[ + Address( + street="123 anywhere street", city="New York", zip="10110" + ), + Address( + street="40 main street", city="San Francisco", zip="95732" + ), + ], + ), + Supplier( + company_name="Ace Hammers", + addresses=[ + Address(street="2569 west elm", city="Detroit", zip="56785") + ], + ), + ] +) session.commit() for customer in session.query(Customer): for address in customer.addresses: print(address) - # no parent here
\ No newline at end of file + # no parent here diff --git a/examples/generic_associations/table_per_related.py b/examples/generic_associations/table_per_related.py index 51c9f1b26..9c5e0e179 100644 --- a/examples/generic_associations/table_per_related.py +++ b/examples/generic_associations/table_per_related.py @@ -20,17 +20,21 @@ from sqlalchemy.ext.declarative import as_declarative, declared_attr from sqlalchemy import create_engine, Integer, Column, String, ForeignKey from sqlalchemy.orm import Session, relationship + @as_declarative() class Base(object): """Base class which provides automated table name and surrogate primary key column. """ + @declared_attr def __tablename__(cls): return cls.__name__.lower() + id = Column(Integer, primary_key=True) + class Address(object): """Define columns that will be present in each 'Address' table. @@ -40,74 +44,82 @@ class Address(object): should be set up using @declared_attr. """ + street = Column(String) city = Column(String) zip = Column(String) def __repr__(self): - return "%s(street=%r, city=%r, zip=%r)" % \ - (self.__class__.__name__, self.street, - self.city, self.zip) + return "%s(street=%r, city=%r, zip=%r)" % ( + self.__class__.__name__, + self.street, + self.city, + self.zip, + ) + class HasAddresses(object): """HasAddresses mixin, creates a new Address class for each parent. """ + @declared_attr def addresses(cls): cls.Address = type( "%sAddress" % cls.__name__, - (Address, Base,), + (Address, Base), dict( - __tablename__="%s_address" % - cls.__tablename__, - parent_id=Column(Integer, - ForeignKey("%s.id" % cls.__tablename__)), - parent=relationship(cls) - ) + __tablename__="%s_address" % cls.__tablename__, + parent_id=Column( + Integer, ForeignKey("%s.id" % cls.__tablename__) + ), + parent=relationship(cls), + ), ) return relationship(cls.Address) + class Customer(HasAddresses, Base): name = Column(String) + class Supplier(HasAddresses, Base): company_name = Column(String) -engine = create_engine('sqlite://', echo=True) + +engine = create_engine("sqlite://", echo=True) Base.metadata.create_all(engine) session = Session(engine) -session.add_all([ - Customer( - name='customer 1', - addresses=[ - Customer.Address( - street='123 anywhere street', - city="New York", - zip="10110"), - Customer.Address( - street='40 main street', - city="San Francisco", - zip="95732") - ] - ), - Supplier( - company_name="Ace Hammers", - addresses=[ - Supplier.Address( - street='2569 west elm', - city="Detroit", - zip="56785") - ] - ), -]) +session.add_all( + [ + Customer( + name="customer 1", + addresses=[ + Customer.Address( + street="123 anywhere street", city="New York", zip="10110" + ), + Customer.Address( + street="40 main street", city="San Francisco", zip="95732" + ), + ], + ), + Supplier( + company_name="Ace Hammers", + addresses=[ + Supplier.Address( + street="2569 west elm", city="Detroit", zip="56785" + ) + ], + ), + ] +) session.commit() for customer in session.query(Customer): for address in customer.addresses: print(address) - print(address.parent)
\ No newline at end of file + print(address.parent) diff --git a/examples/graphs/__init__.py b/examples/graphs/__init__.py index 57d41453b..0f8fe58a7 100644 --- a/examples/graphs/__init__.py +++ b/examples/graphs/__init__.py @@ -10,4 +10,4 @@ and querying for lower- and upper- neighbors are illustrated:: .. autosource:: -"""
\ No newline at end of file +""" diff --git a/examples/graphs/directed_graph.py b/examples/graphs/directed_graph.py index 7bcfc5683..af85a4295 100644 --- a/examples/graphs/directed_graph.py +++ b/examples/graphs/directed_graph.py @@ -1,7 +1,6 @@ """a directed graph example.""" -from sqlalchemy import Column, Integer, ForeignKey, \ - create_engine +from sqlalchemy import Column, Integer, ForeignKey, create_engine from sqlalchemy.orm import relationship, sessionmaker from sqlalchemy.ext.declarative import declarative_base @@ -9,7 +8,7 @@ Base = declarative_base() class Node(Base): - __tablename__ = 'node' + __tablename__ = "node" node_id = Column(Integer, primary_key=True) @@ -21,33 +20,26 @@ class Node(Base): class Edge(Base): - __tablename__ = 'edge' + __tablename__ = "edge" - lower_id = Column( - Integer, - ForeignKey('node.node_id'), - primary_key=True) + lower_id = Column(Integer, ForeignKey("node.node_id"), primary_key=True) - higher_id = Column( - Integer, - ForeignKey('node.node_id'), - primary_key=True) + higher_id = Column(Integer, ForeignKey("node.node_id"), primary_key=True) lower_node = relationship( - Node, - primaryjoin=lower_id == Node.node_id, - backref='lower_edges') + Node, primaryjoin=lower_id == Node.node_id, backref="lower_edges" + ) higher_node = relationship( - Node, - primaryjoin=higher_id == Node.node_id, - backref='higher_edges') + Node, primaryjoin=higher_id == Node.node_id, backref="higher_edges" + ) def __init__(self, n1, n2): self.lower_node = n1 self.higher_node = n2 -engine = create_engine('sqlite://', echo=True) + +engine = create_engine("sqlite://", echo=True) Base.metadata.create_all(engine) session = sessionmaker(engine)() @@ -80,4 +72,3 @@ assert [x for x in n3.higher_neighbors()] == [n6] assert [x for x in n3.lower_neighbors()] == [n1] assert [x for x in n2.lower_neighbors()] == [n1] assert [x for x in n2.higher_neighbors()] == [n1, n5, n7] - diff --git a/examples/inheritance/concrete.py b/examples/inheritance/concrete.py index 258f41025..2245aa4e0 100644 --- a/examples/inheritance/concrete.py +++ b/examples/inheritance/concrete.py @@ -1,7 +1,14 @@ """Concrete-table (table-per-class) inheritance example.""" -from sqlalchemy import Column, Integer, String, \ - ForeignKey, create_engine, inspect, or_ +from sqlalchemy import ( + Column, + Integer, + String, + ForeignKey, + create_engine, + inspect, + or_, +) from sqlalchemy.orm import relationship, Session, with_polymorphic from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.ext.declarative import ConcreteBase @@ -11,107 +18,105 @@ Base = declarative_base() class Company(Base): - __tablename__ = 'company' + __tablename__ = "company" id = Column(Integer, primary_key=True) name = Column(String(50)) employees = relationship( - "Person", - back_populates='company', - cascade='all, delete-orphan') + "Person", back_populates="company", cascade="all, delete-orphan" + ) def __repr__(self): return "Company %s" % self.name class Person(ConcreteBase, Base): - __tablename__ = 'person' + __tablename__ = "person" id = Column(Integer, primary_key=True) - company_id = Column(ForeignKey('company.id')) + company_id = Column(ForeignKey("company.id")) name = Column(String(50)) company = relationship("Company", back_populates="employees") - __mapper_args__ = { - 'polymorphic_identity': 'person', - } + __mapper_args__ = {"polymorphic_identity": "person"} def __repr__(self): return "Ordinary person %s" % self.name class Engineer(Person): - __tablename__ = 'engineer' + __tablename__ = "engineer" id = Column(Integer, primary_key=True) name = Column(String(50)) - company_id = Column(ForeignKey('company.id')) + company_id = Column(ForeignKey("company.id")) status = Column(String(30)) engineer_name = Column(String(30)) primary_language = Column(String(30)) company = relationship("Company", back_populates="employees") - __mapper_args__ = { - 'polymorphic_identity': 'engineer', - 'concrete': True - } + __mapper_args__ = {"polymorphic_identity": "engineer", "concrete": True} def __repr__(self): return ( "Engineer %s, status %s, engineer_name %s, " - "primary_language %s" % - ( - self.name, self.status, - self.engineer_name, self.primary_language) + "primary_language %s" + % ( + self.name, + self.status, + self.engineer_name, + self.primary_language, + ) ) class Manager(Person): - __tablename__ = 'manager' + __tablename__ = "manager" id = Column(Integer, primary_key=True) name = Column(String(50)) - company_id = Column(ForeignKey('company.id')) + company_id = Column(ForeignKey("company.id")) status = Column(String(30)) manager_name = Column(String(30)) company = relationship("Company", back_populates="employees") - __mapper_args__ = { - 'polymorphic_identity': 'manager', - 'concrete': True - } + __mapper_args__ = {"polymorphic_identity": "manager", "concrete": True} def __repr__(self): return "Manager %s, status %s, manager_name %s" % ( - self.name, self.status, self.manager_name) + self.name, + self.status, + self.manager_name, + ) -engine = create_engine('sqlite://', echo=True) +engine = create_engine("sqlite://", echo=True) Base.metadata.create_all(engine) session = Session(engine) -c = Company(name='company1', employees=[ - Manager( - name='pointy haired boss', - status='AAB', - manager_name='manager1'), - Engineer( - name='dilbert', - status='BBA', - engineer_name='engineer1', - primary_language='java'), - Person(name='joesmith'), - Engineer( - name='wally', - status='CGG', - engineer_name='engineer2', - primary_language='python'), - Manager( - name='jsmith', - status='ABA', - manager_name='manager2') -]) +c = Company( + name="company1", + employees=[ + Manager( + name="pointy haired boss", status="AAB", manager_name="manager1" + ), + Engineer( + name="dilbert", + status="BBA", + engineer_name="engineer1", + primary_language="java", + ), + Person(name="joesmith"), + Engineer( + name="wally", + status="CGG", + engineer_name="engineer2", + primary_language="python", + ), + Manager(name="jsmith", status="ABA", manager_name="manager2"), + ], +) session.add(c) session.commit() @@ -120,14 +125,15 @@ c = session.query(Company).get(1) for e in c.employees: print(e, inspect(e).key, e.company) assert set([e.name for e in c.employees]) == set( - ['pointy haired boss', 'dilbert', 'joesmith', 'wally', 'jsmith']) + ["pointy haired boss", "dilbert", "joesmith", "wally", "jsmith"] +) print("\n") -dilbert = session.query(Person).filter_by(name='dilbert').one() -dilbert2 = session.query(Engineer).filter_by(name='dilbert').one() +dilbert = session.query(Person).filter_by(name="dilbert").one() +dilbert2 = session.query(Engineer).filter_by(name="dilbert").one() assert dilbert is dilbert2 -dilbert.engineer_name = 'hes dilbert!' +dilbert.engineer_name = "hes dilbert!" session.commit() @@ -138,24 +144,28 @@ for e in c.employees: # query using with_polymorphic. eng_manager = with_polymorphic(Person, [Engineer, Manager]) print( - session.query(eng_manager). - filter( + session.query(eng_manager) + .filter( or_( - eng_manager.Engineer.engineer_name == 'engineer1', - eng_manager.Manager.manager_name == 'manager2' + eng_manager.Engineer.engineer_name == "engineer1", + eng_manager.Manager.manager_name == "manager2", ) - ).all() + ) + .all() ) # illustrate join from Company eng_manager = with_polymorphic(Person, [Engineer, Manager]) print( - session.query(Company). - join( - Company.employees.of_type(eng_manager) - ).filter( - or_(eng_manager.Engineer.engineer_name == 'engineer1', - eng_manager.Manager.manager_name == 'manager2') - ).all()) + session.query(Company) + .join(Company.employees.of_type(eng_manager)) + .filter( + or_( + eng_manager.Engineer.engineer_name == "engineer1", + eng_manager.Manager.manager_name == "manager2", + ) + ) + .all() +) session.commit() diff --git a/examples/inheritance/joined.py b/examples/inheritance/joined.py index f9322158e..a3a61d763 100644 --- a/examples/inheritance/joined.py +++ b/examples/inheritance/joined.py @@ -1,7 +1,14 @@ """Joined-table (table-per-subclass) inheritance example.""" -from sqlalchemy import Column, Integer, String, \ - ForeignKey, create_engine, inspect, or_ +from sqlalchemy import ( + Column, + Integer, + String, + ForeignKey, + create_engine, + inspect, + or_, +) from sqlalchemy.orm import relationship, Session, with_polymorphic from sqlalchemy.ext.declarative import declarative_base @@ -9,31 +16,30 @@ Base = declarative_base() class Company(Base): - __tablename__ = 'company' + __tablename__ = "company" id = Column(Integer, primary_key=True) name = Column(String(50)) employees = relationship( - "Person", - back_populates='company', - cascade='all, delete-orphan') + "Person", back_populates="company", cascade="all, delete-orphan" + ) def __repr__(self): return "Company %s" % self.name class Person(Base): - __tablename__ = 'person' + __tablename__ = "person" id = Column(Integer, primary_key=True) - company_id = Column(ForeignKey('company.id')) + company_id = Column(ForeignKey("company.id")) name = Column(String(50)) type = Column(String(50)) company = relationship("Company", back_populates="employees") __mapper_args__ = { - 'polymorphic_identity': 'person', - 'polymorphic_on': type + "polymorphic_identity": "person", + "polymorphic_on": type, } def __repr__(self): @@ -41,67 +47,70 @@ class Person(Base): class Engineer(Person): - __tablename__ = 'engineer' - id = Column(ForeignKey('person.id'), primary_key=True) + __tablename__ = "engineer" + id = Column(ForeignKey("person.id"), primary_key=True) status = Column(String(30)) engineer_name = Column(String(30)) primary_language = Column(String(30)) - __mapper_args__ = { - 'polymorphic_identity': 'engineer', - } + __mapper_args__ = {"polymorphic_identity": "engineer"} def __repr__(self): return ( "Engineer %s, status %s, engineer_name %s, " - "primary_language %s" % - ( - self.name, self.status, - self.engineer_name, self.primary_language) + "primary_language %s" + % ( + self.name, + self.status, + self.engineer_name, + self.primary_language, + ) ) class Manager(Person): - __tablename__ = 'manager' - id = Column(ForeignKey('person.id'), primary_key=True) + __tablename__ = "manager" + id = Column(ForeignKey("person.id"), primary_key=True) status = Column(String(30)) manager_name = Column(String(30)) - __mapper_args__ = { - 'polymorphic_identity': 'manager', - } + __mapper_args__ = {"polymorphic_identity": "manager"} def __repr__(self): return "Manager %s, status %s, manager_name %s" % ( - self.name, self.status, self.manager_name) + self.name, + self.status, + self.manager_name, + ) -engine = create_engine('sqlite://', echo=True) +engine = create_engine("sqlite://", echo=True) Base.metadata.create_all(engine) session = Session(engine) -c = Company(name='company1', employees=[ - Manager( - name='pointy haired boss', - status='AAB', - manager_name='manager1'), - Engineer( - name='dilbert', - status='BBA', - engineer_name='engineer1', - primary_language='java'), - Person(name='joesmith'), - Engineer( - name='wally', - status='CGG', - engineer_name='engineer2', - primary_language='python'), - Manager( - name='jsmith', - status='ABA', - manager_name='manager2') -]) +c = Company( + name="company1", + employees=[ + Manager( + name="pointy haired boss", status="AAB", manager_name="manager1" + ), + Engineer( + name="dilbert", + status="BBA", + engineer_name="engineer1", + primary_language="java", + ), + Person(name="joesmith"), + Engineer( + name="wally", + status="CGG", + engineer_name="engineer2", + primary_language="python", + ), + Manager(name="jsmith", status="ABA", manager_name="manager2"), + ], +) session.add(c) session.commit() @@ -110,14 +119,15 @@ c = session.query(Company).get(1) for e in c.employees: print(e, inspect(e).key, e.company) assert set([e.name for e in c.employees]) == set( - ['pointy haired boss', 'dilbert', 'joesmith', 'wally', 'jsmith']) + ["pointy haired boss", "dilbert", "joesmith", "wally", "jsmith"] +) print("\n") -dilbert = session.query(Person).filter_by(name='dilbert').one() -dilbert2 = session.query(Engineer).filter_by(name='dilbert').one() +dilbert = session.query(Person).filter_by(name="dilbert").one() +dilbert2 = session.query(Engineer).filter_by(name="dilbert").one() assert dilbert is dilbert2 -dilbert.engineer_name = 'hes dilbert!' +dilbert.engineer_name = "hes dilbert!" session.commit() @@ -128,13 +138,14 @@ for e in c.employees: # query using with_polymorphic. eng_manager = with_polymorphic(Person, [Engineer, Manager]) print( - session.query(eng_manager). - filter( + session.query(eng_manager) + .filter( or_( - eng_manager.Engineer.engineer_name == 'engineer1', - eng_manager.Manager.manager_name == 'manager2' + eng_manager.Engineer.engineer_name == "engineer1", + eng_manager.Manager.manager_name == "manager2", ) - ).all() + ) + .all() ) # illustrate join from Company. @@ -144,12 +155,15 @@ print( # loading. eng_manager = with_polymorphic(Person, [Engineer, Manager], flat=True) print( - session.query(Company). - join( - Company.employees.of_type(eng_manager) - ).filter( - or_(eng_manager.Engineer.engineer_name == 'engineer1', - eng_manager.Manager.manager_name == 'manager2') - ).all()) + session.query(Company) + .join(Company.employees.of_type(eng_manager)) + .filter( + or_( + eng_manager.Engineer.engineer_name == "engineer1", + eng_manager.Manager.manager_name == "manager2", + ) + ) + .all() +) session.commit() diff --git a/examples/inheritance/single.py b/examples/inheritance/single.py index 56397f540..46d690c94 100644 --- a/examples/inheritance/single.py +++ b/examples/inheritance/single.py @@ -1,7 +1,14 @@ """Single-table (table-per-hierarchy) inheritance example.""" -from sqlalchemy import Column, Integer, String, \ - ForeignKey, create_engine, inspect, or_ +from sqlalchemy import ( + Column, + Integer, + String, + ForeignKey, + create_engine, + inspect, + or_, +) from sqlalchemy.orm import relationship, Session, with_polymorphic from sqlalchemy.ext.declarative import declarative_base, declared_attr @@ -9,31 +16,30 @@ Base = declarative_base() class Company(Base): - __tablename__ = 'company' + __tablename__ = "company" id = Column(Integer, primary_key=True) name = Column(String(50)) employees = relationship( - "Person", - back_populates='company', - cascade='all, delete-orphan') + "Person", back_populates="company", cascade="all, delete-orphan" + ) def __repr__(self): return "Company %s" % self.name class Person(Base): - __tablename__ = 'person' + __tablename__ = "person" id = Column(Integer, primary_key=True) - company_id = Column(ForeignKey('company.id')) + company_id = Column(ForeignKey("company.id")) name = Column(String(50)) type = Column(String(50)) company = relationship("Company", back_populates="employees") __mapper_args__ = { - 'polymorphic_identity': 'person', - 'polymorphic_on': type + "polymorphic_identity": "person", + "polymorphic_on": type, } def __repr__(self): @@ -50,19 +56,20 @@ class Engineer(Person): # declarative/inheritance.html#resolving-column-conflicts @declared_attr def status(cls): - return Person.__table__.c.get('status', Column(String(30))) + return Person.__table__.c.get("status", Column(String(30))) - __mapper_args__ = { - 'polymorphic_identity': 'engineer', - } + __mapper_args__ = {"polymorphic_identity": "engineer"} def __repr__(self): return ( "Engineer %s, status %s, engineer_name %s, " - "primary_language %s" % - ( - self.name, self.status, - self.engineer_name, self.primary_language) + "primary_language %s" + % ( + self.name, + self.status, + self.engineer_name, + self.primary_language, + ) ) @@ -71,43 +78,45 @@ class Manager(Person): @declared_attr def status(cls): - return Person.__table__.c.get('status', Column(String(30))) + return Person.__table__.c.get("status", Column(String(30))) - __mapper_args__ = { - 'polymorphic_identity': 'manager', - } + __mapper_args__ = {"polymorphic_identity": "manager"} def __repr__(self): return "Manager %s, status %s, manager_name %s" % ( - self.name, self.status, self.manager_name) + self.name, + self.status, + self.manager_name, + ) -engine = create_engine('sqlite://', echo=True) +engine = create_engine("sqlite://", echo=True) Base.metadata.create_all(engine) session = Session(engine) -c = Company(name='company1', employees=[ - Manager( - name='pointy haired boss', - status='AAB', - manager_name='manager1'), - Engineer( - name='dilbert', - status='BBA', - engineer_name='engineer1', - primary_language='java'), - Person(name='joesmith'), - Engineer( - name='wally', - status='CGG', - engineer_name='engineer2', - primary_language='python'), - Manager( - name='jsmith', - status='ABA', - manager_name='manager2') -]) +c = Company( + name="company1", + employees=[ + Manager( + name="pointy haired boss", status="AAB", manager_name="manager1" + ), + Engineer( + name="dilbert", + status="BBA", + engineer_name="engineer1", + primary_language="java", + ), + Person(name="joesmith"), + Engineer( + name="wally", + status="CGG", + engineer_name="engineer2", + primary_language="python", + ), + Manager(name="jsmith", status="ABA", manager_name="manager2"), + ], +) session.add(c) session.commit() @@ -116,14 +125,15 @@ c = session.query(Company).get(1) for e in c.employees: print(e, inspect(e).key, e.company) assert set([e.name for e in c.employees]) == set( - ['pointy haired boss', 'dilbert', 'joesmith', 'wally', 'jsmith']) + ["pointy haired boss", "dilbert", "joesmith", "wally", "jsmith"] +) print("\n") -dilbert = session.query(Person).filter_by(name='dilbert').one() -dilbert2 = session.query(Engineer).filter_by(name='dilbert').one() +dilbert = session.query(Person).filter_by(name="dilbert").one() +dilbert2 = session.query(Engineer).filter_by(name="dilbert").one() assert dilbert is dilbert2 -dilbert.engineer_name = 'hes dilbert!' +dilbert.engineer_name = "hes dilbert!" session.commit() @@ -134,24 +144,28 @@ for e in c.employees: # query using with_polymorphic. eng_manager = with_polymorphic(Person, [Engineer, Manager]) print( - session.query(eng_manager). - filter( + session.query(eng_manager) + .filter( or_( - eng_manager.Engineer.engineer_name == 'engineer1', - eng_manager.Manager.manager_name == 'manager2' + eng_manager.Engineer.engineer_name == "engineer1", + eng_manager.Manager.manager_name == "manager2", ) - ).all() + ) + .all() ) # illustrate join from Company, eng_manager = with_polymorphic(Person, [Engineer, Manager]) print( - session.query(Company). - join( - Company.employees.of_type(eng_manager) - ).filter( - or_(eng_manager.Engineer.engineer_name == 'engineer1', - eng_manager.Manager.manager_name == 'manager2') - ).all()) + session.query(Company) + .join(Company.employees.of_type(eng_manager)) + .filter( + or_( + eng_manager.Engineer.engineer_name == "engineer1", + eng_manager.Manager.manager_name == "manager2", + ) + ) + .all() +) session.commit() diff --git a/examples/join_conditions/__init__.py b/examples/join_conditions/__init__.py index 3a561d084..d67eb68e4 100644 --- a/examples/join_conditions/__init__.py +++ b/examples/join_conditions/__init__.py @@ -4,4 +4,4 @@ of join conditions. .. autosource:: -"""
\ No newline at end of file +""" diff --git a/examples/join_conditions/cast.py b/examples/join_conditions/cast.py index 246bc1d57..7ea775689 100644 --- a/examples/join_conditions/cast.py +++ b/examples/join_conditions/cast.py @@ -35,6 +35,7 @@ from sqlalchemy.ext.declarative import declarative_base Base = declarative_base() + class StringAsInt(TypeDecorator): """Coerce string->integer type. @@ -44,52 +45,55 @@ class StringAsInt(TypeDecorator): on the child during a flush. """ + impl = Integer + def process_bind_param(self, value, dialect): if value is not None: value = int(value) return value + class A(Base): """Parent. The referenced column is a string type.""" - __tablename__ = 'a' + __tablename__ = "a" id = Column(Integer, primary_key=True) a_id = Column(String) + class B(Base): """Child. The column we reference 'A' with is an integer.""" - __tablename__ = 'b' + __tablename__ = "b" id = Column(Integer, primary_key=True) a_id = Column(StringAsInt) - a = relationship("A", - # specify primaryjoin. The string form is optional - # here, but note that Declarative makes available all - # of the built-in functions we might need, including - # cast() and foreign(). - primaryjoin="cast(A.a_id, Integer) == foreign(B.a_id)", - backref="bs") + a = relationship( + "A", + # specify primaryjoin. The string form is optional + # here, but note that Declarative makes available all + # of the built-in functions we might need, including + # cast() and foreign(). + primaryjoin="cast(A.a_id, Integer) == foreign(B.a_id)", + backref="bs", + ) + # we demonstrate with SQLite, but the important part # is the CAST rendered in the SQL output. -e = create_engine('sqlite://', echo=True) +e = create_engine("sqlite://", echo=True) Base.metadata.create_all(e) s = Session(e) -s.add_all([ - A(a_id="1"), - A(a_id="2", bs=[B(), B()]), - A(a_id="3", bs=[B()]), -]) +s.add_all([A(a_id="1"), A(a_id="2", bs=[B(), B()]), A(a_id="3", bs=[B()])]) s.commit() b1 = s.query(B).filter_by(a_id="2").first() print(b1.a) a1 = s.query(A).filter_by(a_id="2").first() -print(a1.bs)
\ No newline at end of file +print(a1.bs) diff --git a/examples/join_conditions/threeway.py b/examples/join_conditions/threeway.py index 13df0f349..257002637 100644 --- a/examples/join_conditions/threeway.py +++ b/examples/join_conditions/threeway.py @@ -39,46 +39,56 @@ from sqlalchemy.ext.declarative import declarative_base Base = declarative_base() + class First(Base): - __tablename__ = 'first' + __tablename__ = "first" first_id = Column(Integer, primary_key=True) partition_key = Column(String) def __repr__(self): - return ("First(%s, %s)" % (self.first_id, self.partition_key)) + return "First(%s, %s)" % (self.first_id, self.partition_key) + class Second(Base): - __tablename__ = 'second' + __tablename__ = "second" first_id = Column(Integer, primary_key=True) other_id = Column(Integer, primary_key=True) + class Partitioned(Base): - __tablename__ = 'partitioned' + __tablename__ = "partitioned" other_id = Column(Integer, primary_key=True) partition_key = Column(String, primary_key=True) def __repr__(self): - return ("Partitioned(%s, %s)" % (self.other_id, self.partition_key)) + return "Partitioned(%s, %s)" % (self.other_id, self.partition_key) j = join(Partitioned, Second, Partitioned.other_id == Second.other_id) -partitioned_second = mapper(Partitioned, j, non_primary=True, properties={ +partitioned_second = mapper( + Partitioned, + j, + non_primary=True, + properties={ # note we need to disambiguate columns here - the join() # will provide them as j.c.<tablename>_<colname> for access, # but they retain their real names in the mapping - "other_id": [j.c.partitioned_other_id, j.c.second_other_id], - }) + "other_id": [j.c.partitioned_other_id, j.c.second_other_id] + }, +) First.partitioned = relationship( - partitioned_second, - primaryjoin=and_( - First.partition_key == partitioned_second.c.partition_key, - First.first_id == foreign(partitioned_second.c.first_id) - ), innerjoin=True) + partitioned_second, + primaryjoin=and_( + First.partition_key == partitioned_second.c.partition_key, + First.first_id == foreign(partitioned_second.c.first_id), + ), + innerjoin=True, +) # when using any database other than SQLite, we will get a nested # join, e.g. "first JOIN (partitioned JOIN second ON ..) ON ..". @@ -87,17 +97,19 @@ e = create_engine("sqlite://", echo=True) Base.metadata.create_all(e) s = Session(e) -s.add_all([ - First(first_id=1, partition_key='p1'), - First(first_id=2, partition_key='p1'), - First(first_id=3, partition_key='p2'), - Second(first_id=1, other_id=1), - Second(first_id=2, other_id=1), - Second(first_id=3, other_id=2), - Partitioned(partition_key='p1', other_id=1), - Partitioned(partition_key='p1', other_id=2), - Partitioned(partition_key='p2', other_id=2), -]) +s.add_all( + [ + First(first_id=1, partition_key="p1"), + First(first_id=2, partition_key="p1"), + First(first_id=3, partition_key="p2"), + Second(first_id=1, other_id=1), + Second(first_id=2, other_id=1), + Second(first_id=3, other_id=2), + Partitioned(partition_key="p1", other_id=1), + Partitioned(partition_key="p1", other_id=2), + Partitioned(partition_key="p2", other_id=2), + ] +) s.commit() for row in s.query(First, Partitioned).join(First.partitioned): diff --git a/examples/large_collection/large_collection.py b/examples/large_collection/large_collection.py index 82d2e554b..eb014c6cb 100644 --- a/examples/large_collection/large_collection.py +++ b/examples/large_collection/large_collection.py @@ -1,54 +1,76 @@ - -from sqlalchemy import (MetaData, Table, Column, Integer, String, ForeignKey, - create_engine) -from sqlalchemy.orm import (mapper, relationship, sessionmaker) +from sqlalchemy import ( + MetaData, + Table, + Column, + Integer, + String, + ForeignKey, + create_engine, +) +from sqlalchemy.orm import mapper, relationship, sessionmaker meta = MetaData() -org_table = Table('organizations', meta, - Column('org_id', Integer, primary_key=True), - Column('org_name', String(50), nullable=False, key='name'), - mysql_engine='InnoDB') - -member_table = Table('members', meta, - Column('member_id', Integer, primary_key=True), - Column('member_name', String(50), nullable=False, key='name'), - Column('org_id', Integer, - ForeignKey('organizations.org_id', ondelete="CASCADE")), - mysql_engine='InnoDB') +org_table = Table( + "organizations", + meta, + Column("org_id", Integer, primary_key=True), + Column("org_name", String(50), nullable=False, key="name"), + mysql_engine="InnoDB", +) + +member_table = Table( + "members", + meta, + Column("member_id", Integer, primary_key=True), + Column("member_name", String(50), nullable=False, key="name"), + Column( + "org_id", + Integer, + ForeignKey("organizations.org_id", ondelete="CASCADE"), + ), + mysql_engine="InnoDB", +) class Organization(object): def __init__(self, name): self.name = name + class Member(object): def __init__(self, name): self.name = name -mapper(Organization, org_table, properties = { - 'members' : relationship(Member, - # Organization.members will be a Query object - no loading - # of the entire collection occurs unless requested - lazy="dynamic", - - # Member objects "belong" to their parent, are deleted when - # removed from the collection - cascade="all, delete-orphan", - - # "delete, delete-orphan" cascade does not load in objects on delete, - # allows ON DELETE CASCADE to handle it. - # this only works with a database that supports ON DELETE CASCADE - - # *not* sqlite or MySQL with MyISAM - passive_deletes=True, - ) -}) + +mapper( + Organization, + org_table, + properties={ + "members": relationship( + Member, + # Organization.members will be a Query object - no loading + # of the entire collection occurs unless requested + lazy="dynamic", + # Member objects "belong" to their parent, are deleted when + # removed from the collection + cascade="all, delete-orphan", + # "delete, delete-orphan" cascade does not load in objects on delete, + # allows ON DELETE CASCADE to handle it. + # this only works with a database that supports ON DELETE CASCADE - + # *not* sqlite or MySQL with MyISAM + passive_deletes=True, + ) + }, +) mapper(Member, member_table) -if __name__ == '__main__': - engine = create_engine("postgresql://scott:tiger@localhost/test", echo=True) +if __name__ == "__main__": + engine = create_engine( + "postgresql://scott:tiger@localhost/test", echo=True + ) meta.create_all(engine) # expire_on_commit=False means the session contents @@ -56,10 +78,10 @@ if __name__ == '__main__': sess = sessionmaker(engine, expire_on_commit=False)() # create org with some members - org = Organization('org one') - org.members.append(Member('member one')) - org.members.append(Member('member two')) - org.members.append(Member('member three')) + org = Organization("org one") + org.members.append(Member("member one")) + org.members.append(Member("member two")) + org.members.append(Member("member three")) sess.add(org) @@ -69,14 +91,14 @@ if __name__ == '__main__': # the 'members' collection is a Query. it issues # SQL as needed to load subsets of the collection. print("-------------------------\nload subset of members\n") - members = org.members.filter(member_table.c.name.like('%member t%')).all() + members = org.members.filter(member_table.c.name.like("%member t%")).all() print(members) # new Members can be appended without any # SQL being emitted to load the full collection - org.members.append(Member('member four')) - org.members.append(Member('member five')) - org.members.append(Member('member six')) + org.members.append(Member("member four")) + org.members.append(Member("member five")) + org.members.append(Member("member six")) print("-------------------------\nflush two - save 3 more members\n") sess.commit() @@ -85,7 +107,9 @@ if __name__ == '__main__': # SQL is only emitted for the head row - the Member rows # disappear automatically without the need for additional SQL. sess.delete(org) - print("-------------------------\nflush three - delete org, delete members in one statement\n") + print( + "-------------------------\nflush three - delete org, delete members in one statement\n" + ) sess.commit() print("-------------------------\nno Member rows should remain:\n") @@ -93,4 +117,4 @@ if __name__ == '__main__': sess.close() print("------------------------\ndone. dropping tables.") - meta.drop_all(engine)
\ No newline at end of file + meta.drop_all(engine) diff --git a/examples/materialized_paths/materialized_paths.py b/examples/materialized_paths/materialized_paths.py index 4ded90f7e..45ae0c193 100644 --- a/examples/materialized_paths/materialized_paths.py +++ b/examples/materialized_paths/materialized_paths.py @@ -44,21 +44,35 @@ class Node(Base): # To find the descendants of this node, we look for nodes whose path # starts with this node's path. descendants = relationship( - "Node", viewonly=True, order_by=path, - primaryjoin=remote(foreign(path)).like(path.concat(".%"))) + "Node", + viewonly=True, + order_by=path, + primaryjoin=remote(foreign(path)).like(path.concat(".%")), + ) # Finding the ancestors is a little bit trickier. We need to create a fake # secondary table since this behaves like a many-to-many join. - secondary = select([ - id.label("id"), - func.unnest(cast(func.string_to_array( - func.regexp_replace(path, r"\.?\d+$", ""), "."), - ARRAY(Integer))).label("ancestor_id") - ]).alias() - ancestors = relationship("Node", viewonly=True, secondary=secondary, - primaryjoin=id == secondary.c.id, - secondaryjoin=secondary.c.ancestor_id == id, - order_by=path) + secondary = select( + [ + id.label("id"), + func.unnest( + cast( + func.string_to_array( + func.regexp_replace(path, r"\.?\d+$", ""), "." + ), + ARRAY(Integer), + ) + ).label("ancestor_id"), + ] + ).alias() + ancestors = relationship( + "Node", + viewonly=True, + secondary=secondary, + primaryjoin=id == secondary.c.id, + secondaryjoin=secondary.c.ancestor_id == id, + order_by=path, + ) @property def depth(self): @@ -70,38 +84,44 @@ class Node(Base): def __str__(self): root_depth = self.depth s = [str(self.id)] - s.extend(((n.depth - root_depth) * " " + str(n.id)) - for n in self.descendants) + s.extend( + ((n.depth - root_depth) * " " + str(n.id)) + for n in self.descendants + ) return "\n".join(s) def move_to(self, new_parent): new_path = new_parent.path + "." + str(self.id) for n in self.descendants: - n.path = new_path + n.path[len(self.path):] + n.path = new_path + n.path[len(self.path) :] self.path = new_path if __name__ == "__main__": - engine = create_engine("postgresql://scott:tiger@localhost/test", echo=True) + engine = create_engine( + "postgresql://scott:tiger@localhost/test", echo=True + ) Base.metadata.create_all(engine) session = Session(engine) print("-" * 80) print("create a tree") - session.add_all([ - Node(id=1, path="1"), - Node(id=2, path="1.2"), - Node(id=3, path="1.3"), - Node(id=4, path="1.3.4"), - Node(id=5, path="1.3.5"), - Node(id=6, path="1.3.6"), - Node(id=7, path="1.7"), - Node(id=8, path="1.7.8"), - Node(id=9, path="1.7.9"), - Node(id=10, path="1.7.9.10"), - Node(id=11, path="1.7.11"), - ]) + session.add_all( + [ + Node(id=1, path="1"), + Node(id=2, path="1.2"), + Node(id=3, path="1.3"), + Node(id=4, path="1.3.4"), + Node(id=5, path="1.3.5"), + Node(id=6, path="1.3.6"), + Node(id=7, path="1.7"), + Node(id=8, path="1.7.8"), + Node(id=9, path="1.7.9"), + Node(id=10, path="1.7.9.10"), + Node(id=11, path="1.7.11"), + ] + ) session.flush() print(str(session.query(Node).get(1))) diff --git a/examples/nested_sets/__init__.py b/examples/nested_sets/__init__.py index 3e73bb13e..5fdfbcedc 100644 --- a/examples/nested_sets/__init__.py +++ b/examples/nested_sets/__init__.py @@ -3,4 +3,4 @@ pattern for hierarchical data using the SQLAlchemy ORM. .. autosource:: -"""
\ No newline at end of file +""" diff --git a/examples/nested_sets/nested_sets.py b/examples/nested_sets/nested_sets.py index c64b15b61..705a3d279 100644 --- a/examples/nested_sets/nested_sets.py +++ b/examples/nested_sets/nested_sets.py @@ -4,19 +4,27 @@ http://www.intelligententerprise.com/001020/celko.jhtml """ -from sqlalchemy import (create_engine, Column, Integer, String, select, case, - func) +from sqlalchemy import ( + create_engine, + Column, + Integer, + String, + select, + case, + func, +) from sqlalchemy.orm import Session, aliased from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import event Base = declarative_base() + class Employee(Base): - __tablename__ = 'personnel' + __tablename__ = "personnel" __mapper_args__ = { - 'batch': False # allows extension to fire for each - # instance before going to the next. + "batch": False # allows extension to fire for each + # instance before going to the next. } parent = None @@ -29,6 +37,7 @@ class Employee(Base): def __repr__(self): return "Employee(%s, %d, %d)" % (self.emp, self.left, self.right) + @event.listens_for(Employee, "before_insert") def before_insert(mapper, connection, instance): if not instance.parent: @@ -37,23 +46,31 @@ def before_insert(mapper, connection, instance): else: personnel = mapper.mapped_table right_most_sibling = connection.scalar( - select([personnel.c.rgt]). - where(personnel.c.emp == instance.parent.emp) + select([personnel.c.rgt]).where( + personnel.c.emp == instance.parent.emp + ) ) connection.execute( - personnel.update( - personnel.c.rgt >= right_most_sibling).values( - lft=case( - [(personnel.c.lft > right_most_sibling, - personnel.c.lft + 2)], - else_=personnel.c.lft - ), - rgt=case( - [(personnel.c.rgt >= right_most_sibling, - personnel.c.rgt + 2)], - else_=personnel.c.rgt - ) + personnel.update(personnel.c.rgt >= right_most_sibling).values( + lft=case( + [ + ( + personnel.c.lft > right_most_sibling, + personnel.c.lft + 2, + ) + ], + else_=personnel.c.lft, + ), + rgt=case( + [ + ( + personnel.c.rgt >= right_most_sibling, + personnel.c.rgt + 2, + ) + ], + else_=personnel.c.rgt, + ), ) ) instance.left = right_most_sibling @@ -62,18 +79,19 @@ def before_insert(mapper, connection, instance): # before_update() would be needed to support moving of nodes # after_delete() would be needed to support removal of nodes. -engine = create_engine('sqlite://', echo=True) + +engine = create_engine("sqlite://", echo=True) Base.metadata.create_all(engine) session = Session(bind=engine) -albert = Employee(emp='Albert') -bert = Employee(emp='Bert') -chuck = Employee(emp='Chuck') -donna = Employee(emp='Donna') -eddie = Employee(emp='Eddie') -fred = Employee(emp='Fred') +albert = Employee(emp="Albert") +bert = Employee(emp="Bert") +chuck = Employee(emp="Chuck") +donna = Employee(emp="Donna") +eddie = Employee(emp="Eddie") +fred = Employee(emp="Fred") bert.parent = albert chuck.parent = albert @@ -90,22 +108,28 @@ print(session.query(Employee).all()) # 1. Find an employee and all their supervisors, no matter how deep the tree. ealias = aliased(Employee) -print(session.query(Employee).\ - filter(ealias.left.between(Employee.left, Employee.right)).\ - filter(ealias.emp == 'Eddie').all()) - -#2. Find the employee and all their subordinates. +print( + session.query(Employee) + .filter(ealias.left.between(Employee.left, Employee.right)) + .filter(ealias.emp == "Eddie") + .all() +) + +# 2. Find the employee and all their subordinates. # (This query has a nice symmetry with the first query.) -print(session.query(Employee).\ - filter(Employee.left.between(ealias.left, ealias.right)).\ - filter(ealias.emp == 'Chuck').all()) - -#3. Find the level of each node, so you can print the tree +print( + session.query(Employee) + .filter(Employee.left.between(ealias.left, ealias.right)) + .filter(ealias.emp == "Chuck") + .all() +) + +# 3. Find the level of each node, so you can print the tree # as an indented listing. -for indentation, employee in session.query( - func.count(Employee.emp).label('indentation') - 1, ealias).\ - filter(ealias.left.between(Employee.left, Employee.right)).\ - group_by(ealias.emp).\ - order_by(ealias.left): +for indentation, employee in ( + session.query(func.count(Employee.emp).label("indentation") - 1, ealias) + .filter(ealias.left.between(Employee.left, Employee.right)) + .group_by(ealias.emp) + .order_by(ealias.left) +): print(" " * indentation + str(employee)) - diff --git a/examples/performance/__init__.py b/examples/performance/__init__.py index 6264ae9f7..b66199f3c 100644 --- a/examples/performance/__init__.py +++ b/examples/performance/__init__.py @@ -255,7 +255,8 @@ class Profiler(object): def profile(cls, fn): if cls.name is None: raise ValueError( - "Need to call Profile.init(<suitename>, <default_num>) first.") + "Need to call Profile.init(<suitename>, <default_num>) first." + ) cls.tests.append(fn) return fn @@ -270,7 +271,8 @@ class Profiler(object): def setup_once(cls, fn): if cls._setup_once is not None: raise ValueError( - "setup_once function already set to %s" % cls._setup_once) + "setup_once function already set to %s" % cls._setup_once + ) cls._setup_once = staticmethod(fn) return fn @@ -298,7 +300,7 @@ class Profiler(object): finally: pr.disable() - stats = pstats.Stats(pr).sort_stats('cumulative') + stats = pstats.Stats(pr).sort_stats("cumulative") self.stats.append(TestResult(self, fn, stats=stats)) return result @@ -326,7 +328,8 @@ class Profiler(object): if cls.name is None: parser.add_argument( - "name", choices=cls._suite_names(), help="suite to run") + "name", choices=cls._suite_names(), help="suite to run" + ) if len(sys.argv) > 1: potential_name = sys.argv[1] @@ -335,35 +338,44 @@ class Profiler(object): except ImportError: pass - parser.add_argument( - "--test", type=str, - help="run specific test name" - ) + parser.add_argument("--test", type=str, help="run specific test name") parser.add_argument( - '--dburl', type=str, default="sqlite:///profile.db", - help="database URL, default sqlite:///profile.db" + "--dburl", + type=str, + default="sqlite:///profile.db", + help="database URL, default sqlite:///profile.db", ) parser.add_argument( - '--num', type=int, default=cls.num, + "--num", + type=int, + default=cls.num, help="Number of iterations/items/etc for tests; " - "default is %d module-specific" % cls.num + "default is %d module-specific" % cls.num, ) parser.add_argument( - '--profile', action='store_true', - help='run profiling and dump call counts') + "--profile", + action="store_true", + help="run profiling and dump call counts", + ) parser.add_argument( - '--dump', action='store_true', - help='dump full call profile (implies --profile)') + "--dump", + action="store_true", + help="dump full call profile (implies --profile)", + ) parser.add_argument( - '--callers', action='store_true', - help='print callers as well (implies --dump)') + "--callers", + action="store_true", + help="print callers as well (implies --dump)", + ) parser.add_argument( - '--runsnake', action='store_true', - help='invoke runsnakerun (implies --profile)') + "--runsnake", + action="store_true", + help="invoke runsnakerun (implies --profile)", + ) parser.add_argument( - '--echo', action='store_true', - help="Echo SQL output") + "--echo", action="store_true", help="Echo SQL output" + ) args = parser.parse_args() args.dump = args.dump or args.callers @@ -378,7 +390,7 @@ class Profiler(object): def _suite_names(cls): suites = [] for file_ in os.listdir(os.path.dirname(__file__)): - match = re.match(r'^([a-z].*).py$', file_) + match = re.match(r"^([a-z].*).py$", file_) if match: suites.append(match.group(1)) return suites @@ -398,7 +410,10 @@ class TestResult(object): def _summary(self): summary = "%s : %s (%d iterations)" % ( - self.test.__name__, self.test.__doc__, self.profile.num) + self.test.__name__, + self.test.__doc__, + self.profile.num, + ) if self.total_time: summary += "; total time %f sec" % self.total_time if self.stats: @@ -412,7 +427,7 @@ class TestResult(object): self._dump() def _dump(self): - self.stats.sort_stats('time', 'calls') + self.stats.sort_stats("time", "calls") self.stats.print_stats() if self.profile.callers: self.stats.print_callers() @@ -424,5 +439,3 @@ class TestResult(object): os.system("runsnake %s" % filename) finally: os.remove(filename) - - diff --git a/examples/performance/__main__.py b/examples/performance/__main__.py index 5e05143bf..945458651 100644 --- a/examples/performance/__main__.py +++ b/examples/performance/__main__.py @@ -2,6 +2,5 @@ from . import Profiler -if __name__ == '__main__': +if __name__ == "__main__": Profiler.main() - diff --git a/examples/performance/bulk_inserts.py b/examples/performance/bulk_inserts.py index 9c3cff5b2..52f0f32e6 100644 --- a/examples/performance/bulk_inserts.py +++ b/examples/performance/bulk_inserts.py @@ -36,12 +36,15 @@ def test_flush_no_pk(n): """Individual INSERT statements via the ORM, calling upon last row id""" session = Session(bind=engine) for chunk in range(0, n, 1000): - session.add_all([ - Customer( - name='customer name %d' % i, - description='customer description %d' % i) - for i in range(chunk, chunk + 1000) - ]) + session.add_all( + [ + Customer( + name="customer name %d" % i, + description="customer description %d" % i, + ) + for i in range(chunk, chunk + 1000) + ] + ) session.flush() session.commit() @@ -50,13 +53,16 @@ def test_flush_no_pk(n): def test_bulk_save_return_pks(n): """Individual INSERT statements in "bulk", but calling upon last row id""" session = Session(bind=engine) - session.bulk_save_objects([ - Customer( - name='customer name %d' % i, - description='customer description %d' % i - ) - for i in range(n) - ], return_defaults=True) + session.bulk_save_objects( + [ + Customer( + name="customer name %d" % i, + description="customer description %d" % i, + ) + for i in range(n) + ], + return_defaults=True, + ) session.commit() @@ -65,13 +71,16 @@ def test_flush_pk_given(n): """Batched INSERT statements via the ORM, PKs already defined""" session = Session(bind=engine) for chunk in range(0, n, 1000): - session.add_all([ - Customer( - id=i + 1, - name='customer name %d' % i, - description='customer description %d' % i) - for i in range(chunk, chunk + 1000) - ]) + session.add_all( + [ + Customer( + id=i + 1, + name="customer name %d" % i, + description="customer description %d" % i, + ) + for i in range(chunk, chunk + 1000) + ] + ) session.flush() session.commit() @@ -80,13 +89,15 @@ def test_flush_pk_given(n): def test_bulk_save(n): """Batched INSERT statements via the ORM in "bulk", discarding PKs.""" session = Session(bind=engine) - session.bulk_save_objects([ - Customer( - name='customer name %d' % i, - description='customer description %d' % i - ) - for i in range(n) - ]) + session.bulk_save_objects( + [ + Customer( + name="customer name %d" % i, + description="customer description %d" % i, + ) + for i in range(n) + ] + ) session.commit() @@ -94,13 +105,16 @@ def test_bulk_save(n): def test_bulk_insert_mappings(n): """Batched INSERT statements via the ORM "bulk", using dictionaries.""" session = Session(bind=engine) - session.bulk_insert_mappings(Customer, [ - dict( - name='customer name %d' % i, - description='customer description %d' % i - ) - for i in range(n) - ]) + session.bulk_insert_mappings( + Customer, + [ + dict( + name="customer name %d" % i, + description="customer description %d" % i, + ) + for i in range(n) + ], + ) session.commit() @@ -112,11 +126,12 @@ def test_core_insert(n): Customer.__table__.insert(), [ dict( - name='customer name %d' % i, - description='customer description %d' % i + name="customer name %d" % i, + description="customer description %d" % i, ) for i in range(n) - ]) + ], + ) @Profiler.profile @@ -125,30 +140,30 @@ def test_dbapi_raw(n): conn = engine.pool._creator() cursor = conn.cursor() - compiled = Customer.__table__.insert().values( - name=bindparam('name'), - description=bindparam('description')).\ - compile(dialect=engine.dialect) + compiled = ( + Customer.__table__.insert() + .values(name=bindparam("name"), description=bindparam("description")) + .compile(dialect=engine.dialect) + ) if compiled.positional: args = ( - ('customer name %d' % i, 'customer description %d' % i) - for i in range(n)) + ("customer name %d" % i, "customer description %d" % i) + for i in range(n) + ) else: args = ( dict( - name='customer name %d' % i, - description='customer description %d' % i + name="customer name %d" % i, + description="customer description %d" % i, ) for i in range(n) ) - cursor.executemany( - str(compiled), - list(args) - ) + cursor.executemany(str(compiled), list(args)) conn.commit() conn.close() -if __name__ == '__main__': + +if __name__ == "__main__": Profiler.main() diff --git a/examples/performance/bulk_updates.py b/examples/performance/bulk_updates.py index 9522e4bf5..ebb700068 100644 --- a/examples/performance/bulk_updates.py +++ b/examples/performance/bulk_updates.py @@ -32,12 +32,16 @@ def setup_database(dburl, echo, num): s = Session(engine) for chunk in range(0, num, 10000): - s.bulk_insert_mappings(Customer, [ - { - 'name': 'customer name %d' % i, - 'description': 'customer description %d' % i - } for i in range(chunk, chunk + 10000) - ]) + s.bulk_insert_mappings( + Customer, + [ + { + "name": "customer name %d" % i, + "description": "customer description %d" % i, + } + for i in range(chunk, chunk + 10000) + ], + ) s.commit() @@ -46,8 +50,11 @@ def test_orm_flush(n): """UPDATE statements via the ORM flush process.""" session = Session(bind=engine) for chunk in range(0, n, 1000): - customers = session.query(Customer).\ - filter(Customer.id.between(chunk, chunk + 1000)).all() + customers = ( + session.query(Customer) + .filter(Customer.id.between(chunk, chunk + 1000)) + .all() + ) for customer in customers: customer.description += "updated" session.flush() diff --git a/examples/performance/large_resultsets.py b/examples/performance/large_resultsets.py index c13683040..ad1c23194 100644 --- a/examples/performance/large_resultsets.py +++ b/examples/performance/large_resultsets.py @@ -46,9 +46,12 @@ def setup_database(dburl, echo, num): Customer.__table__.insert(), params=[ { - 'name': 'customer name %d' % i, - 'description': 'customer description %d' % i - } for i in range(chunk, chunk + 10000)]) + "name": "customer name %d" % i, + "description": "customer description %d" % i, + } + for i in range(chunk, chunk + 10000) + ], + ) s.commit() @@ -74,8 +77,9 @@ def test_orm_bundles(n): """Load lightweight "bundle" objects using the ORM.""" sess = Session(engine) - bundle = Bundle('customer', - Customer.id, Customer.name, Customer.description) + bundle = Bundle( + "customer", Customer.id, Customer.name, Customer.description + ) for row in sess.query(bundle).yield_per(10000).limit(n): pass @@ -85,9 +89,11 @@ def test_orm_columns(n): """Load individual columns into named tuples using the ORM.""" sess = Session(engine) - for row in sess.query( - Customer.id, Customer.name, - Customer.description).yield_per(10000).limit(n): + for row in ( + sess.query(Customer.id, Customer.name, Customer.description) + .yield_per(10000) + .limit(n) + ): pass @@ -98,7 +104,7 @@ def test_core_fetchall(n): with engine.connect() as conn: result = conn.execute(Customer.__table__.select().limit(n)).fetchall() for row in result: - data = row['id'], row['name'], row['description'] + data = row["id"], row["name"], row["description"] @Profiler.profile @@ -106,14 +112,15 @@ def test_core_fetchmany_w_streaming(n): """Load Core result rows using fetchmany/streaming.""" with engine.connect() as conn: - result = conn.execution_options(stream_results=True).\ - execute(Customer.__table__.select().limit(n)) + result = conn.execution_options(stream_results=True).execute( + Customer.__table__.select().limit(n) + ) while True: chunk = result.fetchmany(10000) if not chunk: break for row in chunk: - data = row['id'], row['name'], row['description'] + data = row["id"], row["name"], row["description"] @Profiler.profile @@ -127,7 +134,7 @@ def test_core_fetchmany(n): if not chunk: break for row in chunk: - data = row['id'], row['name'], row['description'] + data = row["id"], row["name"], row["description"] @Profiler.profile @@ -145,10 +152,13 @@ def test_dbapi_fetchall_no_object(n): def _test_dbapi_raw(n, make_objects): - compiled = Customer.__table__.select().limit(n).\ - compile( - dialect=engine.dialect, - compile_kwargs={"literal_binds": True}) + compiled = ( + Customer.__table__.select() + .limit(n) + .compile( + dialect=engine.dialect, compile_kwargs={"literal_binds": True} + ) + ) if make_objects: # because if you're going to roll your own, you're probably @@ -170,7 +180,8 @@ def _test_dbapi_raw(n, make_objects): for row in cursor.fetchall(): # ensure that we fully fetch! customer = SimpleCustomer( - id=row[0], name=row[1], description=row[2]) + id=row[0], name=row[1], description=row[2] + ) else: for row in cursor.fetchall(): # ensure that we fully fetch! @@ -178,5 +189,6 @@ def _test_dbapi_raw(n, make_objects): conn.close() -if __name__ == '__main__': + +if __name__ == "__main__": Profiler.main() diff --git a/examples/performance/short_selects.py b/examples/performance/short_selects.py index 6f64aa63e..4a8d401ad 100644 --- a/examples/performance/short_selects.py +++ b/examples/performance/short_selects.py @@ -6,8 +6,14 @@ record by primary key from . import Profiler from sqlalchemy.ext.declarative import declarative_base -from sqlalchemy import Column, Integer, String, create_engine, \ - bindparam, select +from sqlalchemy import ( + Column, + Integer, + String, + create_engine, + bindparam, + select, +) from sqlalchemy.orm import Session, deferred from sqlalchemy.ext import baked import random @@ -29,6 +35,7 @@ class Customer(Base): y = deferred(Column(Integer)) z = deferred(Column(Integer)) + Profiler.init("short_selects", num=10000) @@ -39,16 +46,20 @@ def setup_database(dburl, echo, num): Base.metadata.drop_all(engine) Base.metadata.create_all(engine) sess = Session(engine) - sess.add_all([ - Customer( - id=i, name='c%d' % i, description="c%d" % i, - q=i * 10, - p=i * 20, - x=i * 30, - y=i * 40, - ) - for i in ids - ]) + sess.add_all( + [ + Customer( + id=i, + name="c%d" % i, + description="c%d" % i, + q=i * 10, + p=i * 20, + x=i * 30, + y=i * 40, + ) + for i in ids + ] + ) sess.commit() @@ -65,9 +76,9 @@ def test_orm_query_cols_only(n): """test an ORM query of only the entity columns.""" session = Session(bind=engine) for id_ in random.sample(ids, n): - session.query( - Customer.id, Customer.name, Customer.description - ).filter(Customer.id == id_).one() + session.query(Customer.id, Customer.name, Customer.description).filter( + Customer.id == id_ + ).one() @Profiler.profile @@ -77,7 +88,7 @@ def test_baked_query(n): s = Session(bind=engine) for id_ in random.sample(ids, n): q = bakery(lambda s: s.query(Customer)) - q += lambda q: q.filter(Customer.id == bindparam('id')) + q += lambda q: q.filter(Customer.id == bindparam("id")) q(s).params(id=id_).one() @@ -88,9 +99,9 @@ def test_baked_query_cols_only(n): s = Session(bind=engine) for id_ in random.sample(ids, n): q = bakery( - lambda s: s.query( - Customer.id, Customer.name, Customer.description)) - q += lambda q: q.filter(Customer.id == bindparam('id')) + lambda s: s.query(Customer.id, Customer.name, Customer.description) + ) + q += lambda q: q.filter(Customer.id == bindparam("id")) q(s).params(id=id_).one() @@ -109,7 +120,7 @@ def test_core_new_stmt_each_time(n): def test_core_reuse_stmt(n): """test core, reusing the same statement (but recompiling each time).""" - stmt = select([Customer.__table__]).where(Customer.id == bindparam('id')) + stmt = select([Customer.__table__]).where(Customer.id == bindparam("id")) with engine.connect() as conn: for id_ in random.sample(ids, n): @@ -122,13 +133,14 @@ def test_core_reuse_stmt_compiled_cache(n): """test core, reusing the same statement + compiled cache.""" compiled_cache = {} - stmt = select([Customer.__table__]).where(Customer.id == bindparam('id')) - with engine.connect().\ - execution_options(compiled_cache=compiled_cache) as conn: + stmt = select([Customer.__table__]).where(Customer.id == bindparam("id")) + with engine.connect().execution_options( + compiled_cache=compiled_cache + ) as conn: for id_ in random.sample(ids, n): row = conn.execute(stmt, id=id_).first() tuple(row) -if __name__ == '__main__': +if __name__ == "__main__": Profiler.main() diff --git a/examples/performance/single_inserts.py b/examples/performance/single_inserts.py index cfce90300..79e34dfe6 100644 --- a/examples/performance/single_inserts.py +++ b/examples/performance/single_inserts.py @@ -28,7 +28,7 @@ Profiler.init("single_inserts", num=10000) def setup_database(dburl, echo, num): global engine engine = create_engine(dburl, echo=echo) - if engine.dialect.name == 'sqlite': + if engine.dialect.name == "sqlite": engine.pool = pool.StaticPool(creator=engine.pool._creator) Base.metadata.drop_all(engine) Base.metadata.create_all(engine) @@ -42,8 +42,9 @@ def test_orm_commit(n): session = Session(bind=engine) session.add( Customer( - name='customer name %d' % i, - description='customer description %d' % i) + name="customer name %d" % i, + description="customer description %d" % i, + ) ) session.commit() @@ -54,11 +55,14 @@ def test_bulk_save(n): for i in range(n): session = Session(bind=engine) - session.bulk_save_objects([ - Customer( - name='customer name %d' % i, - description='customer description %d' % i - )]) + session.bulk_save_objects( + [ + Customer( + name="customer name %d" % i, + description="customer description %d" % i, + ) + ] + ) session.commit() @@ -68,11 +72,15 @@ def test_bulk_insert_dictionaries(n): for i in range(n): session = Session(bind=engine) - session.bulk_insert_mappings(Customer, [ - dict( - name='customer name %d' % i, - description='customer description %d' % i - )]) + session.bulk_insert_mappings( + Customer, + [ + dict( + name="customer name %d" % i, + description="customer description %d" % i, + ) + ], + ) session.commit() @@ -85,9 +93,9 @@ def test_core(n): conn.execute( Customer.__table__.insert(), dict( - name='customer name %d' % i, - description='customer description %d' % i - ) + name="customer name %d" % i, + description="customer description %d" % i, + ), ) @@ -102,9 +110,9 @@ def test_core_query_caching(n): conn.execution_options(compiled_cache=cache).execute( ins, dict( - name='customer name %d' % i, - description='customer description %d' % i - ) + name="customer name %d" % i, + description="customer description %d" % i, + ), ) @@ -123,20 +131,22 @@ def test_dbapi_raw_w_pool(n): def _test_dbapi_raw(n, connect): - compiled = Customer.__table__.insert().values( - name=bindparam('name'), - description=bindparam('description')).\ - compile(dialect=engine.dialect) + compiled = ( + Customer.__table__.insert() + .values(name=bindparam("name"), description=bindparam("description")) + .compile(dialect=engine.dialect) + ) if compiled.positional: args = ( - ('customer name %d' % i, 'customer description %d' % i) - for i in range(n)) + ("customer name %d" % i, "customer description %d" % i) + for i in range(n) + ) else: args = ( dict( - name='customer name %d' % i, - description='customer description %d' % i + name="customer name %d" % i, + description="customer description %d" % i, ) for i in range(n) ) @@ -162,5 +172,5 @@ def _test_dbapi_raw(n, connect): conn.close() -if __name__ == '__main__': +if __name__ == "__main__": Profiler.main() diff --git a/examples/postgis/__init__.py b/examples/postgis/__init__.py index 250d9ce87..66ae65d3c 100644 --- a/examples/postgis/__init__.py +++ b/examples/postgis/__init__.py @@ -36,4 +36,3 @@ E.g.:: .. autosource:: """ - diff --git a/examples/postgis/postgis.py b/examples/postgis/postgis.py index ffea3d018..508d63398 100644 --- a/examples/postgis/postgis.py +++ b/examples/postgis/postgis.py @@ -5,6 +5,7 @@ import binascii # Python datatypes + class GisElement(object): """Represents a geometry value.""" @@ -12,16 +13,21 @@ class GisElement(object): return self.desc def __repr__(self): - return "<%s at 0x%x; %r>" % (self.__class__.__name__, - id(self), self.desc) + return "<%s at 0x%x; %r>" % ( + self.__class__.__name__, + id(self), + self.desc, + ) + class BinaryGisElement(GisElement, expression.Function): """Represents a Geometry value expressed as binary.""" def __init__(self, data): self.data = data - expression.Function.__init__(self, "ST_GeomFromEWKB", data, - type_=Geometry(coerce_="binary")) + expression.Function.__init__( + self, "ST_GeomFromEWKB", data, type_=Geometry(coerce_="binary") + ) @property def desc(self): @@ -31,24 +37,26 @@ class BinaryGisElement(GisElement, expression.Function): def as_hex(self): return binascii.hexlify(self.data) + class TextualGisElement(GisElement, expression.Function): """Represents a Geometry value expressed as text.""" def __init__(self, desc, srid=-1): self.desc = desc - expression.Function.__init__(self, "ST_GeomFromText", desc, srid, - type_=Geometry) + expression.Function.__init__( + self, "ST_GeomFromText", desc, srid, type_=Geometry + ) # SQL datatypes. + class Geometry(UserDefinedType): """Base PostGIS Geometry column type.""" name = "GEOMETRY" - def __init__(self, dimension=None, srid=-1, - coerce_="text"): + def __init__(self, dimension=None, srid=-1, coerce_="text"): self.dimension = dimension self.srid = srid self.coerce = coerce_ @@ -58,11 +66,11 @@ class Geometry(UserDefinedType): # override the __eq__() operator def __eq__(self, other): - return self.op('~=')(other) + return self.op("~=")(other) # add a custom operator def intersects(self, other): - return self.op('&&')(other) + return self.op("&&")(other) # any number of GIS operators can be overridden/added here # using the techniques above. @@ -95,6 +103,7 @@ class Geometry(UserDefinedType): return value.desc else: return value + return process def result_processor(self, dialect, coltype): @@ -104,27 +113,35 @@ class Geometry(UserDefinedType): fac = BinaryGisElement else: assert False + def process(value): if value is not None: return fac(value) else: return value + return process def adapt(self, impltype): - return impltype(dimension=self.dimension, - srid=self.srid, coerce_=self.coerce) + return impltype( + dimension=self.dimension, srid=self.srid, coerce_=self.coerce + ) + # other datatypes can be added as needed. + class Point(Geometry): - name = 'POINT' + name = "POINT" + class Curve(Geometry): - name = 'CURVE' + name = "CURVE" + class LineString(Curve): - name = 'LINESTRING' + name = "LINESTRING" + # ... etc. @@ -135,6 +152,7 @@ class LineString(Curve): # versions don't appear to require these special steps anymore. However, # here we illustrate how to set up these features in any case. + def setup_ddl_events(): @event.listens_for(Table, "before_create") def before_create(target, connection, **kw): @@ -153,9 +171,10 @@ def setup_ddl_events(): dispatch("after-drop", target, connection) def dispatch(event, table, bind): - if event in ('before-create', 'before-drop'): - regular_cols = [c for c in table.c if not - isinstance(c.type, Geometry)] + if event in ("before-create", "before-drop"): + regular_cols = [ + c for c in table.c if not isinstance(c.type, Geometry) + ] gis_cols = set(table.c).difference(regular_cols) table.info["_saved_columns"] = table.c @@ -163,85 +182,129 @@ def setup_ddl_events(): # Geometry columns table.columns = expression.ColumnCollection(*regular_cols) - if event == 'before-drop': + if event == "before-drop": for c in gis_cols: bind.execute( - select([ + select( + [ func.DropGeometryColumn( - 'public', table.name, c.name)], - autocommit=True) - ) + "public", table.name, c.name + ) + ], + autocommit=True, + ) + ) - elif event == 'after-create': - table.columns = table.info.pop('_saved_columns') + elif event == "after-create": + table.columns = table.info.pop("_saved_columns") for c in table.c: if isinstance(c.type, Geometry): bind.execute( - select([ - func.AddGeometryColumn( - table.name, c.name, - c.type.srid, - c.type.name, - c.type.dimension)], - autocommit=True) + select( + [ + func.AddGeometryColumn( + table.name, + c.name, + c.type.srid, + c.type.name, + c.type.dimension, + ) + ], + autocommit=True, ) - elif event == 'after-drop': - table.columns = table.info.pop('_saved_columns') -setup_ddl_events() + ) + elif event == "after-drop": + table.columns = table.info.pop("_saved_columns") +setup_ddl_events() + # illustrate usage -if __name__ == '__main__': - from sqlalchemy import (create_engine, MetaData, Column, Integer, String, - func, select) +if __name__ == "__main__": + from sqlalchemy import ( + create_engine, + MetaData, + Column, + Integer, + String, + func, + select, + ) from sqlalchemy.orm import sessionmaker from sqlalchemy.ext.declarative import declarative_base - engine = create_engine('postgresql://scott:tiger@localhost/test', echo=True) + engine = create_engine( + "postgresql://scott:tiger@localhost/test", echo=True + ) metadata = MetaData(engine) Base = declarative_base(metadata=metadata) class Road(Base): - __tablename__ = 'roads' + __tablename__ = "roads" road_id = Column(Integer, primary_key=True) road_name = Column(String) road_geom = Column(Geometry(2)) - metadata.drop_all() metadata.create_all() session = sessionmaker(bind=engine)() # Add objects. We can use strings... - session.add_all([ - Road(road_name='Jeff Rd', road_geom='LINESTRING(191232 243118,191108 243242)'), - Road(road_name='Geordie Rd', road_geom='LINESTRING(189141 244158,189265 244817)'), - Road(road_name='Paul St', road_geom='LINESTRING(192783 228138,192612 229814)'), - Road(road_name='Graeme Ave', road_geom='LINESTRING(189412 252431,189631 259122)'), - Road(road_name='Phil Tce', road_geom='LINESTRING(190131 224148,190871 228134)'), - ]) + session.add_all( + [ + Road( + road_name="Jeff Rd", + road_geom="LINESTRING(191232 243118,191108 243242)", + ), + Road( + road_name="Geordie Rd", + road_geom="LINESTRING(189141 244158,189265 244817)", + ), + Road( + road_name="Paul St", + road_geom="LINESTRING(192783 228138,192612 229814)", + ), + Road( + road_name="Graeme Ave", + road_geom="LINESTRING(189412 252431,189631 259122)", + ), + Road( + road_name="Phil Tce", + road_geom="LINESTRING(190131 224148,190871 228134)", + ), + ] + ) # or use an explicit TextualGisElement (similar to saying func.GeomFromText()) - r = Road(road_name='Dave Cres', road_geom=TextualGisElement('LINESTRING(198231 263418,198213 268322)', -1)) + r = Road( + road_name="Dave Cres", + road_geom=TextualGisElement( + "LINESTRING(198231 263418,198213 268322)", -1 + ), + ) session.add(r) # pre flush, the TextualGisElement represents the string we sent. - assert str(r.road_geom) == 'LINESTRING(198231 263418,198213 268322)' + assert str(r.road_geom) == "LINESTRING(198231 263418,198213 268322)" session.commit() # after flush and/or commit, all the TextualGisElements become PersistentGisElements. assert str(r.road_geom) == "LINESTRING(198231 263418,198213 268322)" - r1 = session.query(Road).filter(Road.road_name == 'Graeme Ave').one() + r1 = session.query(Road).filter(Road.road_name == "Graeme Ave").one() # illustrate the overridden __eq__() operator. # strings come in as TextualGisElements - r2 = session.query(Road).filter(Road.road_geom == 'LINESTRING(189412 252431,189631 259122)').one() + r2 = ( + session.query(Road) + .filter(Road.road_geom == "LINESTRING(189412 252431,189631 259122)") + .one() + ) r3 = session.query(Road).filter(Road.road_geom == r1.road_geom).one() @@ -250,22 +313,29 @@ if __name__ == '__main__': # core usage just fine: road_table = Road.__table__ - stmt = select([road_table]).where(road_table.c.road_geom.intersects(r1.road_geom)) + stmt = select([road_table]).where( + road_table.c.road_geom.intersects(r1.road_geom) + ) print(session.execute(stmt).fetchall()) # TODO: for some reason the auto-generated labels have the internal replacement # strings exposed, even though PG doesn't complain # look up the hex binary version, using SQLAlchemy casts - as_binary = session.scalar(select([type_coerce(r.road_geom, Geometry(coerce_="binary"))])) - assert as_binary.as_hex == \ - '01020000000200000000000000b832084100000000e813104100000000283208410000000088601041' + as_binary = session.scalar( + select([type_coerce(r.road_geom, Geometry(coerce_="binary"))]) + ) + assert ( + as_binary.as_hex + == "01020000000200000000000000b832084100000000e813104100000000283208410000000088601041" + ) # back again, same method ! - as_text = session.scalar(select([type_coerce(as_binary, Geometry(coerce_="text"))])) + as_text = session.scalar( + select([type_coerce(as_binary, Geometry(coerce_="text"))]) + ) assert as_text.desc == "LINESTRING(198231 263418,198213 268322)" - session.rollback() metadata.drop_all() diff --git a/examples/sharding/attribute_shard.py b/examples/sharding/attribute_shard.py index 0e19b69f3..48a3dc932 100644 --- a/examples/sharding/attribute_shard.py +++ b/examples/sharding/attribute_shard.py @@ -1,5 +1,13 @@ -from sqlalchemy import (create_engine, Table, Column, Integer, - String, ForeignKey, Float, DateTime) +from sqlalchemy import ( + create_engine, + Table, + Column, + Integer, + String, + ForeignKey, + Float, + DateTime, +) from sqlalchemy.orm import sessionmaker, relationship from sqlalchemy.ext.horizontal_shard import ShardedSession from sqlalchemy.sql import operators, visitors @@ -12,22 +20,24 @@ import datetime # causes the id_generator() to use the same connection as that # of an ongoing transaction within db1. echo = True -db1 = create_engine('sqlite://', echo=echo, pool_threadlocal=True) -db2 = create_engine('sqlite://', echo=echo) -db3 = create_engine('sqlite://', echo=echo) -db4 = create_engine('sqlite://', echo=echo) +db1 = create_engine("sqlite://", echo=echo, pool_threadlocal=True) +db2 = create_engine("sqlite://", echo=echo) +db3 = create_engine("sqlite://", echo=echo) +db4 = create_engine("sqlite://", echo=echo) # create session function. this binds the shard ids # to databases within a ShardedSession and returns it. create_session = sessionmaker(class_=ShardedSession) -create_session.configure(shards={ - 'north_america': db1, - 'asia': db2, - 'europe': db3, - 'south_america': db4 -}) +create_session.configure( + shards={ + "north_america": db1, + "asia": db2, + "europe": db3, + "south_america": db4, + } +) # mappings and tables @@ -40,9 +50,7 @@ Base = declarative_base() # #1. Any other method will do just as well; UUID, hilo, application-specific, # etc. -ids = Table( - 'ids', Base.metadata, - Column('nextid', Integer, nullable=False)) +ids = Table("ids", Base.metadata, Column("nextid", Integer, nullable=False)) def id_generator(ctx): @@ -52,6 +60,7 @@ def id_generator(ctx): conn.execute(ids.update(values={ids.c.nextid: ids.c.nextid + 1})) return nextid + # table setup. we'll store a lead table of continents/cities, and a secondary # table storing locations. a particular row will be placed in the database # whose shard id corresponds to the 'continent'. in this setup, secondary rows @@ -67,7 +76,7 @@ class WeatherLocation(Base): continent = Column(String(30), nullable=False) city = Column(String(50), nullable=False) - reports = relationship("Report", backref='location') + reports = relationship("Report", backref="location") def __init__(self, continent, city): self.continent = continent @@ -79,14 +88,17 @@ class Report(Base): id = Column(Integer, primary_key=True) location_id = Column( - 'location_id', Integer, ForeignKey('weather_locations.id')) - temperature = Column('temperature', Float) + "location_id", Integer, ForeignKey("weather_locations.id") + ) + temperature = Column("temperature", Float) report_time = Column( - 'report_time', DateTime, default=datetime.datetime.now) + "report_time", DateTime, default=datetime.datetime.now + ) def __init__(self, temperature): self.temperature = temperature + # create tables for db in (db1, db2, db3, db4): Base.metadata.drop_all(db) @@ -101,10 +113,10 @@ db1.execute(ids.insert(), nextid=1) # we'll use a straight mapping of a particular set of "country" # attributes to shard id. shard_lookup = { - 'North America': 'north_america', - 'Asia': 'asia', - 'Europe': 'europe', - 'South America': 'south_america' + "North America": "north_america", + "Asia": "asia", + "Europe": "europe", + "South America": "south_america", } @@ -139,7 +151,7 @@ def id_chooser(query, ident): # set things up. return [query.lazy_loaded_from.identity_token] else: - return ['north_america', 'asia', 'europe', 'south_america'] + return ["north_america", "asia", "europe", "south_america"] def query_chooser(query): @@ -168,7 +180,7 @@ def query_chooser(query): ids.extend(shard_lookup[v] for v in value) if len(ids) == 0: - return ['north_america', 'asia', 'europe', 'south_america'] + return ["north_america", "asia", "europe", "south_america"] else: return ids @@ -208,13 +220,16 @@ def _get_query_comparisons(query): def visit_binary(binary): # special handling for "col IN (params)" - if binary.left in clauses and \ - binary.operator == operators.in_op and \ - hasattr(binary.right, 'clauses'): + if ( + binary.left in clauses + and binary.operator == operators.in_op + and hasattr(binary.right, "clauses") + ): comparisons.append( ( - binary.left, binary.operator, - tuple(binds[bind] for bind in binary.right.clauses) + binary.left, + binary.operator, + tuple(binds[bind] for bind in binary.right.clauses), ) ) elif binary.left in clauses and binary.right in binds: @@ -232,29 +247,33 @@ def _get_query_comparisons(query): # into a list. if query._criterion is not None: visitors.traverse_depthfirst( - query._criterion, {}, - {'bindparam': visit_bindparam, - 'binary': visit_binary, - 'column': visit_column} + query._criterion, + {}, + { + "bindparam": visit_bindparam, + "binary": visit_binary, + "column": visit_column, + }, ) return comparisons + # further configure create_session to use these functions create_session.configure( shard_chooser=shard_chooser, id_chooser=id_chooser, - query_chooser=query_chooser + query_chooser=query_chooser, ) # save and load objects! -tokyo = WeatherLocation('Asia', 'Tokyo') -newyork = WeatherLocation('North America', 'New York') -toronto = WeatherLocation('North America', 'Toronto') -london = WeatherLocation('Europe', 'London') -dublin = WeatherLocation('Europe', 'Dublin') -brasilia = WeatherLocation('South America', 'Brasila') -quito = WeatherLocation('South America', 'Quito') +tokyo = WeatherLocation("Asia", "Tokyo") +newyork = WeatherLocation("North America", "New York") +toronto = WeatherLocation("North America", "Toronto") +london = WeatherLocation("Europe", "London") +dublin = WeatherLocation("Europe", "Dublin") +brasilia = WeatherLocation("South America", "Brasila") +quito = WeatherLocation("South America", "Quito") tokyo.reports.append(Report(80.0)) newyork.reports.append(Report(75)) @@ -271,12 +290,14 @@ assert t.city == tokyo.city assert t.reports[0].temperature == 80.0 north_american_cities = sess.query(WeatherLocation).filter( - WeatherLocation.continent == 'North America') -assert {c.city for c in north_american_cities} == {'New York', 'Toronto'} + WeatherLocation.continent == "North America" +) +assert {c.city for c in north_american_cities} == {"New York", "Toronto"} asia_and_europe = sess.query(WeatherLocation).filter( - WeatherLocation.continent.in_(['Europe', 'Asia'])) -assert {c.city for c in asia_and_europe} == {'Tokyo', 'London', 'Dublin'} + WeatherLocation.continent.in_(["Europe", "Asia"]) +) +assert {c.city for c in asia_and_europe} == {"Tokyo", "London", "Dublin"} # the Report class uses a simple integer primary key. So across two databases, # a primary key will be repeated. The "identity_token" tracks in memory @@ -284,8 +305,8 @@ assert {c.city for c in asia_and_europe} == {'Tokyo', 'London', 'Dublin'} newyork_report = newyork.reports[0] tokyo_report = tokyo.reports[0] -assert inspect(newyork_report).identity_key == (Report, (1, ), "north_america") -assert inspect(tokyo_report).identity_key == (Report, (1, ), "asia") +assert inspect(newyork_report).identity_key == (Report, (1,), "north_america") +assert inspect(tokyo_report).identity_key == (Report, (1,), "asia") # the token representing the originating shard is also available directly diff --git a/examples/space_invaders/__init__.py b/examples/space_invaders/__init__.py index 8816045dc..944f8bb46 100644 --- a/examples/space_invaders/__init__.py +++ b/examples/space_invaders/__init__.py @@ -21,4 +21,4 @@ enjoy! .. autosource:: -"""
\ No newline at end of file +""" diff --git a/examples/space_invaders/space_invaders.py b/examples/space_invaders/space_invaders.py index 3ce280aec..d5437d8cf 100644 --- a/examples/space_invaders/space_invaders.py +++ b/examples/space_invaders/space_invaders.py @@ -1,7 +1,6 @@ import sys from sqlalchemy.ext.declarative import declarative_base -from sqlalchemy import create_engine, Integer, Column, ForeignKey, \ - String, func +from sqlalchemy import create_engine, Integer, Column, ForeignKey, String, func from sqlalchemy.orm import relationship, Session, joinedload from sqlalchemy.ext.hybrid import hybrid_property, hybrid_method import curses @@ -18,7 +17,7 @@ if _PY3: logging.basicConfig( filename="space_invaders.log", - format="%(asctime)s,%(msecs)03d %(levelname)-5.5s %(message)s" + format="%(asctime)s,%(msecs)03d %(levelname)-5.5s %(message)s", ) logging.getLogger("sqlalchemy.engine").setLevel(logging.INFO) @@ -47,7 +46,7 @@ COLOR_MAP = { "M": curses.COLOR_MAGENTA, "R": curses.COLOR_RED, "W": curses.COLOR_WHITE, - "Y": curses.COLOR_YELLOW + "Y": curses.COLOR_YELLOW, } @@ -56,7 +55,8 @@ class Glyph(Base): to be painted on the screen. """ - __tablename__ = 'glyph' + + __tablename__ = "glyph" id = Column(Integer, primary_key=True) name = Column(String) type = Column(String) @@ -68,11 +68,9 @@ class Glyph(Base): def __init__(self, name, img, alt=None): self.name = name - self.data, self.width, self.height = \ - self._encode_glyph(img) + self.data, self.width, self.height = self._encode_glyph(img) if alt is not None: - self.alt_data, alt_w, alt_h = \ - self._encode_glyph(alt) + self.alt_data, alt_w, alt_h = self._encode_glyph(alt) def _encode_glyph(self, img): """Receive a textual description of the glyph and @@ -80,7 +78,7 @@ class Glyph(Base): GlyphCoordinate.render(). """ - img = re.sub(r'^\n', "", textwrap.dedent(img)) + img = re.sub(r"^\n", "", textwrap.dedent(img)) color = "W" lines = [line.rstrip() for line in img.split("\n")] data = [] @@ -89,15 +87,15 @@ class Glyph(Base): line = list(line) while line: char = line.pop(0) - if char == '#': + if char == "#": color = line.pop(0) continue render_line.append((color, char)) data.append(render_line) width = max([len(rl) for rl in data]) data = "".join( - "".join("%s%s" % (color, char) for color, char in render_line) + - ("W " * (width - len(render_line))) + "".join("%s%s" % (color, char) for color, char in render_line) + + ("W " * (width - len(render_line))) for render_line in data ) return data, width, len(lines) @@ -121,9 +119,10 @@ class GlyphCoordinate(Base): score value. """ - __tablename__ = 'glyph_coordinate' + + __tablename__ = "glyph_coordinate" id = Column(Integer, primary_key=True) - glyph_id = Column(Integer, ForeignKey('glyph.id')) + glyph_id = Column(Integer, ForeignKey("glyph.id")) x = Column(Integer) y = Column(Integer) tick = Column(Integer) @@ -132,11 +131,9 @@ class GlyphCoordinate(Base): glyph = relationship(Glyph, innerjoin=True) def __init__( - self, - session, glyph_name, x, y, - tick=None, label=None, score=None): - self.glyph = session.query(Glyph).\ - filter_by(name=glyph_name).one() + self, session, glyph_name, x, y, tick=None, label=None, score=None + ): + self.glyph = session.query(Glyph).filter_by(name=glyph_name).one() self.x = x self.y = y self.tick = tick @@ -152,8 +149,7 @@ class GlyphCoordinate(Base): glyph = self.glyph data = glyph.glyph_for_state(self, state) for color, char in [ - (data[i], data[i + 1]) - for i in xrange(0, len(data), 2) + (data[i], data[i + 1]) for i in xrange(0, len(data), 2) ]: x = self.x + col @@ -163,7 +159,8 @@ class GlyphCoordinate(Base): y + VERT_PADDING, x + HORIZ_PADDING, char, - _COLOR_PAIRS[color]) + _COLOR_PAIRS[color], + ) col += 1 if col == glyph.width: col = 0 @@ -186,10 +183,7 @@ class GlyphCoordinate(Base): width = min(glyph.width, MAX_X - x) or 1 for y_a in xrange(self.y, self.y + glyph.height): y = y_a - window.addstr( - y + VERT_PADDING, - x + HORIZ_PADDING, - " " * width) + window.addstr(y + VERT_PADDING, x + HORIZ_PADDING, " " * width) if self.label: self._render_label(window, True) @@ -236,21 +230,22 @@ class GlyphCoordinate(Base): the given GlyphCoordinate.""" return ~( - (self.x + self.width < other.x) | - (self.x > other.x + other.width) + (self.x + self.width < other.x) | (self.x > other.x + other.width) ) & ~( - (self.y + self.height < other.y) | - (self.y > other.y + other.height) + (self.y + self.height < other.y) + | (self.y > other.y + other.height) ) class EnemyGlyph(Glyph): """Describe an enemy.""" + __mapper_args__ = {"polymorphic_identity": "enemy"} class ArmyGlyph(EnemyGlyph): """Describe an enemy that's part of the "army". """ + __mapper_args__ = {"polymorphic_identity": "army"} def glyph_for_state(self, coord, state): @@ -262,6 +257,7 @@ class ArmyGlyph(EnemyGlyph): class SaucerGlyph(EnemyGlyph): """Describe the enemy saucer flying overhead.""" + __mapper_args__ = {"polymorphic_identity": "saucer"} def glyph_for_state(self, coord, state): @@ -273,21 +269,25 @@ class SaucerGlyph(EnemyGlyph): class MessageGlyph(Glyph): """Describe a glyph for displaying a message.""" + __mapper_args__ = {"polymorphic_identity": "message"} class PlayerGlyph(Glyph): """Describe a glyph representing the player.""" + __mapper_args__ = {"polymorphic_identity": "player"} class MissileGlyph(Glyph): """Describe a glyph representing a missile.""" + __mapper_args__ = {"polymorphic_identity": "missile"} class SplatGlyph(Glyph): """Describe a glyph representing a "splat".""" + __mapper_args__ = {"polymorphic_identity": "splat"} def glyph_for_state(self, coord, state): @@ -302,36 +302,39 @@ def init_glyph(session): """Create the glyphs used during play.""" enemy1 = ArmyGlyph( - "enemy1", """ + "enemy1", + """ #W-#B^#R-#B^#W- #G| | """, """ #W>#B^#R-#B^#W< #G^ ^ - """ + """, ) enemy2 = ArmyGlyph( - "enemy2", """ + "enemy2", + """ #W*** #R<#C~~~#R> """, """ #W@@@ #R<#C---#R> - """ + """, ) enemy3 = ArmyGlyph( - "enemy3", """ + "enemy3", + """ #Y((--)) #M-~-~-~ """, """ #Y[[--]] #M~-~-~- - """ + """, ) saucer = SaucerGlyph( @@ -351,35 +354,49 @@ def init_glyph(session): #M| #M- #Y+++ #M- #M| - """ + """, ) - ship = PlayerGlyph("ship", """ + ship = PlayerGlyph( + "ship", + """ #Y^ #G===== - """) + """, + ) - missile = MissileGlyph("missile", """ + missile = MissileGlyph( + "missile", + """ | - """) + """, + ) start = MessageGlyph( "start_message", "J = move left; L = move right; SPACE = fire\n" - " #GPress any key to start") - lose = MessageGlyph("lose_message", - "#YY O U L O S E ! ! !") - win = MessageGlyph( - "win_message", - "#RL E V E L C L E A R E D ! ! !" + " #GPress any key to start", ) + lose = MessageGlyph("lose_message", "#YY O U L O S E ! ! !") + win = MessageGlyph("win_message", "#RL E V E L C L E A R E D ! ! !") paused = MessageGlyph( - "pause_message", - "#WP A U S E D\n#GPress P to continue") + "pause_message", "#WP A U S E D\n#GPress P to continue" + ) session.add_all( - [enemy1, enemy2, enemy3, ship, saucer, - missile, start, lose, win, - paused, splat1]) + [ + enemy1, + enemy2, + enemy3, + ship, + saucer, + missile, + start, + lose, + win, + paused, + splat1, + ] + ) def setup_curses(): @@ -392,7 +409,8 @@ def setup_curses(): WINDOW_HEIGHT + (VERT_PADDING * 2), WINDOW_WIDTH + (HORIZ_PADDING * 2), WINDOW_TOP - VERT_PADDING, - WINDOW_LEFT - HORIZ_PADDING) + WINDOW_LEFT - HORIZ_PADDING, + ) curses.start_color() global _COLOR_PAIRS @@ -416,24 +434,25 @@ def init_positions(session): session.add( GlyphCoordinate( - session, "ship", - WINDOW_WIDTH // 2 - 2, - WINDOW_HEIGHT - 4) + session, "ship", WINDOW_WIDTH // 2 - 2, WINDOW_HEIGHT - 4 + ) ) arrangement = ( - ("enemy3", 50), ("enemy2", 25), - ("enemy1", 10), ("enemy2", 25), - ("enemy1", 10)) + ("enemy3", 50), + ("enemy2", 25), + ("enemy1", 10), + ("enemy2", 25), + ("enemy1", 10), + ) for (ship_vert, (etype, score)) in zip( - xrange(5, 30, ENEMY_VERT_SPACING), arrangement): + xrange(5, 30, ENEMY_VERT_SPACING), arrangement + ): for ship_horiz in xrange(0, 50, 10): session.add( GlyphCoordinate( - session, etype, - ship_horiz, - ship_vert, - score=score) + session, etype, ship_horiz, ship_vert, score=score + ) ) @@ -442,12 +461,9 @@ def draw(session, window, state): database and render. """ - for gcoord in session.query(GlyphCoordinate).\ - options(joinedload("glyph")): + for gcoord in session.query(GlyphCoordinate).options(joinedload("glyph")): gcoord.render(window, state) - window.addstr( - 1, WINDOW_WIDTH - 5, - "Score: %.4d" % state['score']) + window.addstr(1, WINDOW_WIDTH - 5, "Score: %.4d" % state["score"]) window.move(0, 0) window.refresh() @@ -456,11 +472,11 @@ def check_win(session, state): """Return the number of army glyphs remaining - the player wins if this is zero.""" - return session.query( - func.count(GlyphCoordinate.id) - ).join( - GlyphCoordinate.glyph.of_type(ArmyGlyph) - ).scalar() + return ( + session.query(func.count(GlyphCoordinate.id)) + .join(GlyphCoordinate.glyph.of_type(ArmyGlyph)) + .scalar() + ) def check_lose(session, state): @@ -470,12 +486,14 @@ def check_lose(session, state): The player loses if this is non-zero.""" player = state["player"] - return session.query(GlyphCoordinate).join( - GlyphCoordinate.glyph.of_type(ArmyGlyph) - ).filter( - GlyphCoordinate.intersects(player) | - GlyphCoordinate.bottom_bound - ).count() + return ( + session.query(GlyphCoordinate) + .join(GlyphCoordinate.glyph.of_type(ArmyGlyph)) + .filter( + GlyphCoordinate.intersects(player) | GlyphCoordinate.bottom_bound + ) + .count() + ) def render_message(session, window, msg, x, y): @@ -490,9 +508,11 @@ def render_message(session, window, msg, x, y): msg = GlyphCoordinate(session, msg, x, y) # clear existing glyphs which intersect - for gly in session.query(GlyphCoordinate).join( - GlyphCoordinate.glyph - ).filter(GlyphCoordinate.intersects(msg)): + for gly in ( + session.query(GlyphCoordinate) + .join(GlyphCoordinate.glyph) + .filter(GlyphCoordinate.intersects(msg)) + ): gly.blank(window) # render @@ -551,12 +571,14 @@ def move_army(session, window, state): # get the lower/upper boundaries of the army # along the X axis. - min_x, max_x = session.query( - func.min(GlyphCoordinate.x), - func.max(GlyphCoordinate.x + GlyphCoordinate.width), - ).join( - GlyphCoordinate.glyph.of_type(ArmyGlyph) - ).first() + min_x, max_x = ( + session.query( + func.min(GlyphCoordinate.x), + func.max(GlyphCoordinate.x + GlyphCoordinate.width), + ) + .join(GlyphCoordinate.glyph.of_type(ArmyGlyph)) + .first() + ) if min_x is None or max_x is None: # no enemies @@ -603,27 +625,26 @@ def move_player(session, window, state): player.x -= 1 elif ch == FIRE_KEY and state["missile"] is None: state["missile"] = GlyphCoordinate( - session, - "missile", - player.x + 3, - player.y - 1) + session, "missile", player.x + 3, player.y - 1 + ) def move_missile(session, window, state): """Update the status of the current missile, if any.""" - if state["missile"] is None or \ - state["tick"] % 2 != 0: + if state["missile"] is None or state["tick"] % 2 != 0: return missile = state["missile"] # locate enemy glyphs which intersect with the # missile's current position; i.e. a hit - glyph = session.query(GlyphCoordinate).\ - join(GlyphCoordinate.glyph.of_type(EnemyGlyph)).\ - filter(GlyphCoordinate.intersects(missile)).\ - first() + glyph = ( + session.query(GlyphCoordinate) + .join(GlyphCoordinate.glyph.of_type(EnemyGlyph)) + .filter(GlyphCoordinate.intersects(missile)) + .first() + ) missile.blank(window) if glyph or missile.top_bound: # missle is done @@ -642,15 +663,13 @@ def move_saucer(session, window, state): saucer_interval = 500 saucer_speed_interval = 4 - if state["saucer"] is None and \ - state["tick"] % saucer_interval != 0: + if state["saucer"] is None and state["tick"] % saucer_interval != 0: return if state["saucer"] is None: state["saucer"] = saucer = GlyphCoordinate( - session, - "saucer", -6, 1, - score=random.randrange(100, 600, 100)) + session, "saucer", -6, 1, score=random.randrange(100, 600, 100) + ) elif state["tick"] % saucer_speed_interval == 0: saucer = state["saucer"] saucer.blank(window) @@ -663,8 +682,9 @@ def move_saucer(session, window, state): def update_splat(session, window, state): """Render splat animations.""" - for splat in session.query(GlyphCoordinate).\ - join(GlyphCoordinate.glyph.of_type(SplatGlyph)): + for splat in session.query(GlyphCoordinate).join( + GlyphCoordinate.glyph.of_type(SplatGlyph) + ): age = state["tick"] - splat.tick if age > 10: splat.blank(window) @@ -683,8 +703,13 @@ def score(session, window, state, glyph): state["score"] += glyph.score # render a splat ! GlyphCoordinate( - session, "splat1", glyph.x, glyph.y, - tick=state["tick"], label=str(glyph.score)) + session, + "splat1", + glyph.x, + glyph.y, + tick=state["tick"], + label=str(glyph.score), + ) def update_state(session, window, state): @@ -713,19 +738,23 @@ def start(session, window, state, continue_=False): init_positions(session) - player = session.query(GlyphCoordinate).join( - GlyphCoordinate.glyph.of_type(PlayerGlyph) - ).one() - state.update({ - "field_pos": 0, - "alt": False, - "tick": 0, - "missile": None, - "saucer": None, - "player": player, - "army_direction": 0, - "flip": False - }) + player = ( + session.query(GlyphCoordinate) + .join(GlyphCoordinate.glyph.of_type(PlayerGlyph)) + .one() + ) + state.update( + { + "field_pos": 0, + "alt": False, + "tick": 0, + "missile": None, + "saucer": None, + "player": player, + "army_direction": 0, + "flip": False, + } + ) if not continue_: state["score"] = 0 @@ -748,7 +777,8 @@ def main(): while True: update_state(session, window, state) draw(session, window, state) - time.sleep(.01) + time.sleep(0.01) + -if __name__ == '__main__': +if __name__ == "__main__": main() diff --git a/examples/versioned_history/__init__.py b/examples/versioned_history/__init__.py index 7478450ac..7670cd613 100644 --- a/examples/versioned_history/__init__.py +++ b/examples/versioned_history/__init__.py @@ -60,4 +60,4 @@ can be applied:: .. autosource:: -"""
\ No newline at end of file +""" diff --git a/examples/versioned_history/history_meta.py b/examples/versioned_history/history_meta.py index bad60a398..749a6a5ca 100644 --- a/examples/versioned_history/history_meta.py +++ b/examples/versioned_history/history_meta.py @@ -30,7 +30,7 @@ def _history_mapper(local_mapper): getattr(local_mapper.class_, prop.key).impl.active_history = True super_mapper = local_mapper.inherits - super_history_mapper = getattr(cls, '__history_mapper__', None) + super_history_mapper = getattr(cls, "__history_mapper__", None) polymorphic_on = None super_fks = [] @@ -38,18 +38,20 @@ def _history_mapper(local_mapper): def _col_copy(col): orig = col col = col.copy() - orig.info['history_copy'] = col + orig.info["history_copy"] = col col.unique = False col.default = col.server_default = None col.autoincrement = False return col properties = util.OrderedDict() - if not super_mapper or \ - local_mapper.local_table is not super_mapper.local_table: + if ( + not super_mapper + or local_mapper.local_table is not super_mapper.local_table + ): cols = [] version_meta = {"version_meta": True} # add column.info to identify - # columns specific to versioning + # columns specific to versioning for column in local_mapper.local_table.c: if _is_versioning_col(column): @@ -57,12 +59,13 @@ def _history_mapper(local_mapper): col = _col_copy(column) - if super_mapper and \ - col_references_table(column, super_mapper.local_table): + if super_mapper and col_references_table( + column, super_mapper.local_table + ): super_fks.append( ( col.key, - list(super_history_mapper.local_table.primary_key)[0] + list(super_history_mapper.local_table.primary_key)[0], ) ) @@ -73,38 +76,48 @@ def _history_mapper(local_mapper): orig_prop = local_mapper.get_property_by_column(column) # carry over column re-mappings - if len(orig_prop.columns) > 1 or \ - orig_prop.columns[0].key != orig_prop.key: + if ( + len(orig_prop.columns) > 1 + or orig_prop.columns[0].key != orig_prop.key + ): properties[orig_prop.key] = tuple( - col.info['history_copy'] for col in orig_prop.columns) + col.info["history_copy"] for col in orig_prop.columns + ) if super_mapper: super_fks.append( - ( - 'version', super_history_mapper.local_table.c.version - ) + ("version", super_history_mapper.local_table.c.version) ) # "version" stores the integer version id. This column is # required. cols.append( Column( - 'version', Integer, primary_key=True, - autoincrement=False, info=version_meta)) + "version", + Integer, + primary_key=True, + autoincrement=False, + info=version_meta, + ) + ) # "changed" column stores the UTC timestamp of when the # history row was created. # This column is optional and can be omitted. - cols.append(Column( - 'changed', DateTime, - default=datetime.datetime.utcnow, - info=version_meta)) + cols.append( + Column( + "changed", + DateTime, + default=datetime.datetime.utcnow, + info=version_meta, + ) + ) if super_fks: cols.append(ForeignKeyConstraint(*zip(*super_fks))) table = Table( - local_mapper.local_table.name + '_history', + local_mapper.local_table.name + "_history", local_mapper.local_table.metadata, *cols, schema=local_mapper.local_table.schema @@ -122,9 +135,8 @@ def _history_mapper(local_mapper): bases = (super_history_mapper.class_,) if table is not None: - properties['changed'] = ( - (table.c.changed, ) + - tuple(super_history_mapper.attrs.changed.columns) + properties["changed"] = (table.c.changed,) + tuple( + super_history_mapper.attrs.changed.columns ) else: @@ -137,16 +149,17 @@ def _history_mapper(local_mapper): inherits=super_history_mapper, polymorphic_on=polymorphic_on, polymorphic_identity=local_mapper.polymorphic_identity, - properties=properties + properties=properties, ) cls.__history_mapper__ = m if not super_history_mapper: local_mapper.local_table.append_column( - Column('version', Integer, default=1, nullable=False) + Column("version", Integer, default=1, nullable=False) ) local_mapper.add_property( - "version", local_mapper.local_table.c.version) + "version", local_mapper.local_table.c.version + ) class Versioned(object): @@ -156,16 +169,17 @@ class Versioned(object): mp = mapper(cls, *arg, **kw) _history_mapper(mp) return mp + return map - __table_args__ = {'sqlite_autoincrement': True} + __table_args__ = {"sqlite_autoincrement": True} """Use sqlite_autoincrement, to ensure unique integer values are used for new rows even for rows taht have been deleted.""" def versioned_objects(iter): for obj in iter: - if hasattr(obj, '__history_mapper__'): + if hasattr(obj, "__history_mapper__"): yield obj @@ -181,8 +195,7 @@ def create_version(obj, session, deleted=False): obj_changed = False for om, hm in zip( - obj_mapper.iterate_to_root(), - history_mapper.iterate_to_root() + obj_mapper.iterate_to_root(), history_mapper.iterate_to_root() ): if hm.single: continue @@ -228,10 +241,12 @@ def create_version(obj, session, deleted=False): # not changed, but we have relationships. OK # check those too for prop in obj_mapper.iterate_properties: - if isinstance(prop, RelationshipProperty) and \ - attributes.get_history( - obj, prop.key, - passive=attributes.PASSIVE_NO_INITIALIZE).has_changes(): + if ( + isinstance(prop, RelationshipProperty) + and attributes.get_history( + obj, prop.key, passive=attributes.PASSIVE_NO_INITIALIZE + ).has_changes() + ): for p in prop.local_columns: if p.foreign_keys: obj_changed = True @@ -242,7 +257,7 @@ def create_version(obj, session, deleted=False): if not obj_changed and not deleted: return - attr['version'] = obj.version + attr["version"] = obj.version hist = history_cls() for key, value in attr.items(): setattr(hist, key, value) @@ -251,7 +266,7 @@ def create_version(obj, session, deleted=False): def versioned_session(session): - @event.listens_for(session, 'before_flush') + @event.listens_for(session, "before_flush") def before_flush(session, flush_context, instances): for obj in versioned_objects(session.dirty): create_version(obj, session) diff --git a/examples/versioned_history/test_versioning.py b/examples/versioned_history/test_versioning.py index 37ef73936..3270ad5fd 100644 --- a/examples/versioned_history/test_versioning.py +++ b/examples/versioned_history/test_versioning.py @@ -4,10 +4,22 @@ module functions.""" from unittest import TestCase from sqlalchemy.ext.declarative import declarative_base from .history_meta import Versioned, versioned_session -from sqlalchemy import create_engine, Column, Integer, String, \ - ForeignKey, Boolean, select -from sqlalchemy.orm import clear_mappers, Session, deferred, relationship, \ - column_property +from sqlalchemy import ( + create_engine, + Column, + Integer, + String, + ForeignKey, + Boolean, + select, +) +from sqlalchemy.orm import ( + clear_mappers, + Session, + deferred, + relationship, + column_property, +) from sqlalchemy.testing import AssertsCompiledSQL, eq_, assert_raises, ne_ from sqlalchemy.testing.entities import ComparableEntity from sqlalchemy.orm import exc as orm_exc @@ -20,11 +32,11 @@ engine = None def setup_module(): global engine - engine = create_engine('sqlite://', echo=True) + engine = create_engine("sqlite://", echo=True) class TestVersioning(TestCase, AssertsCompiledSQL): - __dialect__ = 'default' + __dialect__ = "default" def setUp(self): self.session = Session(engine) @@ -41,18 +53,18 @@ class TestVersioning(TestCase, AssertsCompiledSQL): def test_plain(self): class SomeClass(Versioned, self.Base, ComparableEntity): - __tablename__ = 'sometable' + __tablename__ = "sometable" id = Column(Integer, primary_key=True) name = Column(String(50)) self.create_tables() sess = self.session - sc = SomeClass(name='sc1') + sc = SomeClass(name="sc1") sess.add(sc) sess.commit() - sc.name = 'sc1modified' + sc.name = "sc1modified" sess.commit() assert sc.version == 2 @@ -60,56 +72,60 @@ class TestVersioning(TestCase, AssertsCompiledSQL): SomeClassHistory = SomeClass.__history_mapper__.class_ eq_( - sess.query(SomeClassHistory).filter( - SomeClassHistory.version == 1).all(), - [SomeClassHistory(version=1, name='sc1')] + sess.query(SomeClassHistory) + .filter(SomeClassHistory.version == 1) + .all(), + [SomeClassHistory(version=1, name="sc1")], ) - sc.name = 'sc1modified2' + sc.name = "sc1modified2" eq_( - sess.query(SomeClassHistory).order_by( - SomeClassHistory.version).all(), + sess.query(SomeClassHistory) + .order_by(SomeClassHistory.version) + .all(), [ - SomeClassHistory(version=1, name='sc1'), - SomeClassHistory(version=2, name='sc1modified') - ] + SomeClassHistory(version=1, name="sc1"), + SomeClassHistory(version=2, name="sc1modified"), + ], ) assert sc.version == 3 sess.commit() - sc.name = 'temp' - sc.name = 'sc1modified2' + sc.name = "temp" + sc.name = "sc1modified2" sess.commit() eq_( - sess.query(SomeClassHistory).order_by( - SomeClassHistory.version).all(), + sess.query(SomeClassHistory) + .order_by(SomeClassHistory.version) + .all(), [ - SomeClassHistory(version=1, name='sc1'), - SomeClassHistory(version=2, name='sc1modified') - ] + SomeClassHistory(version=1, name="sc1"), + SomeClassHistory(version=2, name="sc1modified"), + ], ) sess.delete(sc) sess.commit() eq_( - sess.query(SomeClassHistory).order_by( - SomeClassHistory.version).all(), + sess.query(SomeClassHistory) + .order_by(SomeClassHistory.version) + .all(), [ - SomeClassHistory(version=1, name='sc1'), - SomeClassHistory(version=2, name='sc1modified'), - SomeClassHistory(version=3, name='sc1modified2') - ] + SomeClassHistory(version=1, name="sc1"), + SomeClassHistory(version=2, name="sc1modified"), + SomeClassHistory(version=3, name="sc1modified2"), + ], ) def test_w_mapper_versioning(self): class SomeClass(Versioned, self.Base, ComparableEntity): - __tablename__ = 'sometable' + __tablename__ = "sometable" id = Column(Integer, primary_key=True) name = Column(String(50)) @@ -118,27 +134,24 @@ class TestVersioning(TestCase, AssertsCompiledSQL): self.create_tables() sess = self.session - sc = SomeClass(name='sc1') + sc = SomeClass(name="sc1") sess.add(sc) sess.commit() s2 = Session(sess.bind) sc2 = s2.query(SomeClass).first() - sc2.name = 'sc1modified' + sc2.name = "sc1modified" - sc.name = 'sc1modified_again' + sc.name = "sc1modified_again" sess.commit() eq_(sc.version, 2) - assert_raises( - orm_exc.StaleDataError, - s2.flush - ) + assert_raises(orm_exc.StaleDataError, s2.flush) def test_from_null(self): class SomeClass(Versioned, self.Base, ComparableEntity): - __tablename__ = 'sometable' + __tablename__ = "sometable" id = Column(Integer, primary_key=True) name = Column(String(50)) @@ -149,14 +162,14 @@ class TestVersioning(TestCase, AssertsCompiledSQL): sess.add(sc) sess.commit() - sc.name = 'sc1' + sc.name = "sc1" sess.commit() assert sc.version == 2 def test_insert_null(self): class SomeClass(Versioned, self.Base, ComparableEntity): - __tablename__ = 'sometable' + __tablename__ = "sometable" id = Column(Integer, primary_key=True) boole = Column(Boolean, default=False) @@ -176,9 +189,10 @@ class TestVersioning(TestCase, AssertsCompiledSQL): SomeClassHistory = SomeClass.__history_mapper__.class_ eq_( - sess.query(SomeClassHistory.boole).order_by( - SomeClassHistory.id).all(), - [(True, ), (None, )] + sess.query(SomeClassHistory.boole) + .order_by(SomeClassHistory.id) + .all(), + [(True,), (None,)], ) eq_(sc.version, 3) @@ -187,7 +201,7 @@ class TestVersioning(TestCase, AssertsCompiledSQL): """test versioning of unloaded, deferred columns.""" class SomeClass(Versioned, self.Base, ComparableEntity): - __tablename__ = 'sometable' + __tablename__ = "sometable" id = Column(Integer, primary_key=True) name = Column(String(50)) @@ -195,15 +209,15 @@ class TestVersioning(TestCase, AssertsCompiledSQL): self.create_tables() sess = self.session - sc = SomeClass(name='sc1', data='somedata') + sc = SomeClass(name="sc1", data="somedata") sess.add(sc) sess.commit() sess.close() sc = sess.query(SomeClass).first() - assert 'data' not in sc.__dict__ + assert "data" not in sc.__dict__ - sc.name = 'sc1modified' + sc.name = "sc1modified" sess.commit() assert sc.version == 2 @@ -211,137 +225,149 @@ class TestVersioning(TestCase, AssertsCompiledSQL): SomeClassHistory = SomeClass.__history_mapper__.class_ eq_( - sess.query(SomeClassHistory).filter( - SomeClassHistory.version == 1).all(), - [SomeClassHistory(version=1, name='sc1', data='somedata')] + sess.query(SomeClassHistory) + .filter(SomeClassHistory.version == 1) + .all(), + [SomeClassHistory(version=1, name="sc1", data="somedata")], ) def test_joined_inheritance(self): class BaseClass(Versioned, self.Base, ComparableEntity): - __tablename__ = 'basetable' + __tablename__ = "basetable" id = Column(Integer, primary_key=True) name = Column(String(50)) type = Column(String(20)) __mapper_args__ = { - 'polymorphic_on': type, - 'polymorphic_identity': 'base'} + "polymorphic_on": type, + "polymorphic_identity": "base", + } class SubClassSeparatePk(BaseClass): - __tablename__ = 'subtable1' + __tablename__ = "subtable1" id = column_property( - Column(Integer, primary_key=True), - BaseClass.id + Column(Integer, primary_key=True), BaseClass.id ) - base_id = Column(Integer, ForeignKey('basetable.id')) + base_id = Column(Integer, ForeignKey("basetable.id")) subdata1 = Column(String(50)) - __mapper_args__ = {'polymorphic_identity': 'sep'} + __mapper_args__ = {"polymorphic_identity": "sep"} class SubClassSamePk(BaseClass): - __tablename__ = 'subtable2' + __tablename__ = "subtable2" - id = Column( - Integer, ForeignKey('basetable.id'), primary_key=True) + id = Column(Integer, ForeignKey("basetable.id"), primary_key=True) subdata2 = Column(String(50)) - __mapper_args__ = {'polymorphic_identity': 'same'} + __mapper_args__ = {"polymorphic_identity": "same"} self.create_tables() sess = self.session - sep1 = SubClassSeparatePk(name='sep1', subdata1='sep1subdata') - base1 = BaseClass(name='base1') - same1 = SubClassSamePk(name='same1', subdata2='same1subdata') + sep1 = SubClassSeparatePk(name="sep1", subdata1="sep1subdata") + base1 = BaseClass(name="base1") + same1 = SubClassSamePk(name="same1", subdata2="same1subdata") sess.add_all([sep1, base1, same1]) sess.commit() - base1.name = 'base1mod' - same1.subdata2 = 'same1subdatamod' - sep1.name = 'sep1mod' + base1.name = "base1mod" + same1.subdata2 = "same1subdatamod" + sep1.name = "sep1mod" sess.commit() BaseClassHistory = BaseClass.__history_mapper__.class_ - SubClassSeparatePkHistory = \ + SubClassSeparatePkHistory = ( SubClassSeparatePk.__history_mapper__.class_ + ) SubClassSamePkHistory = SubClassSamePk.__history_mapper__.class_ eq_( sess.query(BaseClassHistory).order_by(BaseClassHistory.id).all(), [ SubClassSeparatePkHistory( - id=1, name='sep1', type='sep', version=1), - BaseClassHistory(id=2, name='base1', type='base', version=1), + id=1, name="sep1", type="sep", version=1 + ), + BaseClassHistory(id=2, name="base1", type="base", version=1), SubClassSamePkHistory( - id=3, name='same1', type='same', version=1) - ] + id=3, name="same1", type="same", version=1 + ), + ], ) - same1.subdata2 = 'same1subdatamod2' + same1.subdata2 = "same1subdatamod2" eq_( - sess.query(BaseClassHistory).order_by( - BaseClassHistory.id, BaseClassHistory.version).all(), + sess.query(BaseClassHistory) + .order_by(BaseClassHistory.id, BaseClassHistory.version) + .all(), [ SubClassSeparatePkHistory( - id=1, name='sep1', type='sep', version=1), - BaseClassHistory(id=2, name='base1', type='base', version=1), + id=1, name="sep1", type="sep", version=1 + ), + BaseClassHistory(id=2, name="base1", type="base", version=1), SubClassSamePkHistory( - id=3, name='same1', type='same', version=1), + id=3, name="same1", type="same", version=1 + ), SubClassSamePkHistory( - id=3, name='same1', type='same', version=2) - ] + id=3, name="same1", type="same", version=2 + ), + ], ) - base1.name = 'base1mod2' + base1.name = "base1mod2" eq_( - sess.query(BaseClassHistory).order_by( - BaseClassHistory.id, BaseClassHistory.version).all(), + sess.query(BaseClassHistory) + .order_by(BaseClassHistory.id, BaseClassHistory.version) + .all(), [ SubClassSeparatePkHistory( - id=1, name='sep1', type='sep', version=1), - BaseClassHistory(id=2, name='base1', type='base', version=1), + id=1, name="sep1", type="sep", version=1 + ), + BaseClassHistory(id=2, name="base1", type="base", version=1), BaseClassHistory( - id=2, name='base1mod', type='base', version=2), + id=2, name="base1mod", type="base", version=2 + ), SubClassSamePkHistory( - id=3, name='same1', type='same', version=1), + id=3, name="same1", type="same", version=1 + ), SubClassSamePkHistory( - id=3, name='same1', type='same', version=2) - ] + id=3, name="same1", type="same", version=2 + ), + ], ) def test_joined_inheritance_multilevel(self): class BaseClass(Versioned, self.Base, ComparableEntity): - __tablename__ = 'basetable' + __tablename__ = "basetable" id = Column(Integer, primary_key=True) name = Column(String(50)) type = Column(String(20)) __mapper_args__ = { - 'polymorphic_on': type, - 'polymorphic_identity': 'base'} + "polymorphic_on": type, + "polymorphic_identity": "base", + } class SubClass(BaseClass): - __tablename__ = 'subtable' + __tablename__ = "subtable" id = column_property( - Column(Integer, primary_key=True), - BaseClass.id + Column(Integer, primary_key=True), BaseClass.id ) - base_id = Column(Integer, ForeignKey('basetable.id')) + base_id = Column(Integer, ForeignKey("basetable.id")) subdata1 = Column(String(50)) - __mapper_args__ = {'polymorphic_identity': 'sub'} + __mapper_args__ = {"polymorphic_identity": "sub"} class SubSubClass(SubClass): - __tablename__ = 'subsubtable' + __tablename__ = "subsubtable" - id = Column(Integer, ForeignKey('subtable.id'), primary_key=True) + id = Column(Integer, ForeignKey("subtable.id"), primary_key=True) subdata2 = Column(String(50)) - __mapper_args__ = {'polymorphic_identity': 'subsub'} + __mapper_args__ = {"polymorphic_identity": "subsub"} self.create_tables() @@ -350,27 +376,18 @@ class TestVersioning(TestCase, AssertsCompiledSQL): q = sess.query(SubSubHistory) self.assert_compile( q, - - "SELECT " - "subsubtable_history.id AS subsubtable_history_id, " "subtable_history.id AS subtable_history_id, " "basetable_history.id AS basetable_history_id, " - "subsubtable_history.changed AS subsubtable_history_changed, " "subtable_history.changed AS subtable_history_changed, " "basetable_history.changed AS basetable_history_changed, " - "basetable_history.name AS basetable_history_name, " - "basetable_history.type AS basetable_history_type, " - "subsubtable_history.version AS subsubtable_history_version, " "subtable_history.version AS subtable_history_version, " "basetable_history.version AS basetable_history_version, " - - "subtable_history.base_id AS subtable_history_base_id, " "subtable_history.subdata1 AS subtable_history_subdata1, " "subsubtable_history.subdata2 AS subsubtable_history_subdata2 " @@ -380,64 +397,73 @@ class TestVersioning(TestCase, AssertsCompiledSQL): "AND basetable_history.version = subtable_history.version " "JOIN subsubtable_history ON subtable_history.id = " "subsubtable_history.id AND subtable_history.version = " - "subsubtable_history.version" + "subsubtable_history.version", ) - ssc = SubSubClass(name='ss1', subdata1='sd1', subdata2='sd2') + ssc = SubSubClass(name="ss1", subdata1="sd1", subdata2="sd2") sess.add(ssc) sess.commit() + eq_(sess.query(SubSubHistory).all(), []) + ssc.subdata1 = "sd11" + ssc.subdata2 = "sd22" + sess.commit() eq_( sess.query(SubSubHistory).all(), - [] + [ + SubSubHistory( + name="ss1", + subdata1="sd1", + subdata2="sd2", + type="subsub", + version=1, + ) + ], ) - ssc.subdata1 = 'sd11' - ssc.subdata2 = 'sd22' - sess.commit() eq_( - sess.query(SubSubHistory).all(), - [SubSubHistory(name='ss1', subdata1='sd1', - subdata2='sd2', type='subsub', version=1)] + ssc, + SubSubClass( + name="ss1", subdata1="sd11", subdata2="sd22", version=2 + ), ) - eq_(ssc, SubSubClass( - name='ss1', subdata1='sd11', - subdata2='sd22', version=2)) def test_joined_inheritance_changed(self): class BaseClass(Versioned, self.Base, ComparableEntity): - __tablename__ = 'basetable' + __tablename__ = "basetable" id = Column(Integer, primary_key=True) name = Column(String(50)) type = Column(String(20)) __mapper_args__ = { - 'polymorphic_on': type, - 'polymorphic_identity': 'base' + "polymorphic_on": type, + "polymorphic_identity": "base", } class SubClass(BaseClass): - __tablename__ = 'subtable' + __tablename__ = "subtable" - id = Column(Integer, ForeignKey('basetable.id'), primary_key=True) + id = Column(Integer, ForeignKey("basetable.id"), primary_key=True) - __mapper_args__ = {'polymorphic_identity': 'sep'} + __mapper_args__ = {"polymorphic_identity": "sep"} self.create_tables() BaseClassHistory = BaseClass.__history_mapper__.class_ SubClassHistory = SubClass.__history_mapper__.class_ sess = self.session - s1 = SubClass(name='s1') + s1 = SubClass(name="s1") sess.add(s1) sess.commit() - s1.name = 's2' + s1.name = "s2" sess.commit() actual_changed_base = sess.scalar( - select([BaseClass.__history_mapper__.local_table.c.changed])) + select([BaseClass.__history_mapper__.local_table.c.changed]) + ) actual_changed_sub = sess.scalar( - select([SubClass.__history_mapper__.local_table.c.changed])) + select([SubClass.__history_mapper__.local_table.c.changed]) + ) h1 = sess.query(BaseClassHistory).first() eq_(h1.changed, actual_changed_base) eq_(h1.changed, actual_changed_sub) @@ -448,53 +474,57 @@ class TestVersioning(TestCase, AssertsCompiledSQL): def test_single_inheritance(self): class BaseClass(Versioned, self.Base, ComparableEntity): - __tablename__ = 'basetable' + __tablename__ = "basetable" id = Column(Integer, primary_key=True) name = Column(String(50)) type = Column(String(50)) __mapper_args__ = { - 'polymorphic_on': type, - 'polymorphic_identity': 'base'} + "polymorphic_on": type, + "polymorphic_identity": "base", + } class SubClass(BaseClass): subname = Column(String(50), unique=True) - __mapper_args__ = {'polymorphic_identity': 'sub'} + __mapper_args__ = {"polymorphic_identity": "sub"} self.create_tables() sess = self.session - b1 = BaseClass(name='b1') - sc = SubClass(name='s1', subname='sc1') + b1 = BaseClass(name="b1") + sc = SubClass(name="s1", subname="sc1") sess.add_all([b1, sc]) sess.commit() - b1.name = 'b1modified' + b1.name = "b1modified" BaseClassHistory = BaseClass.__history_mapper__.class_ SubClassHistory = SubClass.__history_mapper__.class_ eq_( - sess.query(BaseClassHistory).order_by( - BaseClassHistory.id, BaseClassHistory.version).all(), - [BaseClassHistory(id=1, name='b1', type='base', version=1)] + sess.query(BaseClassHistory) + .order_by(BaseClassHistory.id, BaseClassHistory.version) + .all(), + [BaseClassHistory(id=1, name="b1", type="base", version=1)], ) - sc.name = 's1modified' - b1.name = 'b1modified2' + sc.name = "s1modified" + b1.name = "b1modified2" eq_( - sess.query(BaseClassHistory).order_by( - BaseClassHistory.id, BaseClassHistory.version).all(), + sess.query(BaseClassHistory) + .order_by(BaseClassHistory.id, BaseClassHistory.version) + .all(), [ - BaseClassHistory(id=1, name='b1', type='base', version=1), + BaseClassHistory(id=1, name="b1", type="base", version=1), BaseClassHistory( - id=1, name='b1modified', type='base', version=2), - SubClassHistory(id=2, name='s1', type='sub', version=1) - ] + id=1, name="b1modified", type="base", version=2 + ), + SubClassHistory(id=2, name="s1", type="sub", version=1), + ], ) # test the unique constraint on the subclass @@ -504,7 +534,7 @@ class TestVersioning(TestCase, AssertsCompiledSQL): def test_unique(self): class SomeClass(Versioned, self.Base, ComparableEntity): - __tablename__ = 'sometable' + __tablename__ = "sometable" id = Column(Integer, primary_key=True) name = Column(String(50), unique=True) @@ -512,40 +542,39 @@ class TestVersioning(TestCase, AssertsCompiledSQL): self.create_tables() sess = self.session - sc = SomeClass(name='sc1', data='sc1') + sc = SomeClass(name="sc1", data="sc1") sess.add(sc) sess.commit() - sc.data = 'sc1modified' + sc.data = "sc1modified" sess.commit() assert sc.version == 2 - sc.data = 'sc1modified2' + sc.data = "sc1modified2" sess.commit() assert sc.version == 3 def test_relationship(self): - class SomeRelated(self.Base, ComparableEntity): - __tablename__ = 'somerelated' + __tablename__ = "somerelated" id = Column(Integer, primary_key=True) class SomeClass(Versioned, self.Base, ComparableEntity): - __tablename__ = 'sometable' + __tablename__ = "sometable" id = Column(Integer, primary_key=True) name = Column(String(50)) - related_id = Column(Integer, ForeignKey('somerelated.id')) - related = relationship("SomeRelated", backref='classes') + related_id = Column(Integer, ForeignKey("somerelated.id")) + related = relationship("SomeRelated", backref="classes") SomeClassHistory = SomeClass.__history_mapper__.class_ self.create_tables() sess = self.session - sc = SomeClass(name='sc1') + sc = SomeClass(name="sc1") sess.add(sc) sess.commit() @@ -558,36 +587,37 @@ class TestVersioning(TestCase, AssertsCompiledSQL): assert sc.version == 2 eq_( - sess.query(SomeClassHistory).filter( - SomeClassHistory.version == 1).all(), - [SomeClassHistory(version=1, name='sc1', related_id=None)] + sess.query(SomeClassHistory) + .filter(SomeClassHistory.version == 1) + .all(), + [SomeClassHistory(version=1, name="sc1", related_id=None)], ) sc.related = None eq_( - sess.query(SomeClassHistory).order_by( - SomeClassHistory.version).all(), + sess.query(SomeClassHistory) + .order_by(SomeClassHistory.version) + .all(), [ - SomeClassHistory(version=1, name='sc1', related_id=None), - SomeClassHistory(version=2, name='sc1', related_id=sr1.id) - ] + SomeClassHistory(version=1, name="sc1", related_id=None), + SomeClassHistory(version=2, name="sc1", related_id=sr1.id), + ], ) assert sc.version == 3 def test_backref_relationship(self): - class SomeRelated(self.Base, ComparableEntity): - __tablename__ = 'somerelated' + __tablename__ = "somerelated" id = Column(Integer, primary_key=True) name = Column(String(50)) - related_id = Column(Integer, ForeignKey('sometable.id')) - related = relationship("SomeClass", backref='related') + related_id = Column(Integer, ForeignKey("sometable.id")) + related = relationship("SomeClass", backref="related") class SomeClass(Versioned, self.Base, ComparableEntity): - __tablename__ = 'sometable' + __tablename__ = "sometable" id = Column(Integer, primary_key=True) @@ -599,13 +629,13 @@ class TestVersioning(TestCase, AssertsCompiledSQL): assert sc.version == 1 - sr = SomeRelated(name='sr', related=sc) + sr = SomeRelated(name="sr", related=sc) sess.add(sr) sess.commit() assert sc.version == 1 - sr.name = 'sr2' + sr.name = "sr2" sess.commit() assert sc.version == 1 @@ -616,9 +646,8 @@ class TestVersioning(TestCase, AssertsCompiledSQL): assert sc.version == 1 def test_create_double_flush(self): - class SomeClass(Versioned, self.Base, ComparableEntity): - __tablename__ = 'sometable' + __tablename__ = "sometable" id = Column(Integer, primary_key=True) name = Column(String(30)) @@ -629,56 +658,56 @@ class TestVersioning(TestCase, AssertsCompiledSQL): sc = SomeClass() self.session.add(sc) self.session.flush() - sc.name = 'Foo' + sc.name = "Foo" self.session.flush() assert sc.version == 2 def test_mutate_plain_column(self): class Document(self.Base, Versioned): - __tablename__ = 'document' + __tablename__ = "document" id = Column(Integer, primary_key=True, autoincrement=True) name = Column(String, nullable=True) - description_ = Column('description', String, nullable=True) + description_ = Column("description", String, nullable=True) self.create_tables() document = Document() self.session.add(document) - document.name = 'Foo' + document.name = "Foo" self.session.commit() - document.name = 'Bar' + document.name = "Bar" self.session.commit() DocumentHistory = Document.__history_mapper__.class_ v2 = self.session.query(Document).one() v1 = self.session.query(DocumentHistory).one() self.assertEqual(v1.id, v2.id) - self.assertEqual(v2.name, 'Bar') - self.assertEqual(v1.name, 'Foo') + self.assertEqual(v2.name, "Bar") + self.assertEqual(v1.name, "Foo") def test_mutate_named_column(self): class Document(self.Base, Versioned): - __tablename__ = 'document' + __tablename__ = "document" id = Column(Integer, primary_key=True, autoincrement=True) name = Column(String, nullable=True) - description_ = Column('description', String, nullable=True) + description_ = Column("description", String, nullable=True) self.create_tables() document = Document() self.session.add(document) - document.description_ = 'Foo' + document.description_ = "Foo" self.session.commit() - document.description_ = 'Bar' + document.description_ = "Bar" self.session.commit() DocumentHistory = Document.__history_mapper__.class_ v2 = self.session.query(Document).one() v1 = self.session.query(DocumentHistory).one() self.assertEqual(v1.id, v2.id) - self.assertEqual(v2.description_, 'Bar') - self.assertEqual(v1.description_, 'Foo') + self.assertEqual(v2.description_, "Bar") + self.assertEqual(v1.description_, "Foo") def test_unique_identifiers_across_deletes(self): """Ensure unique integer values are used for the primary table. @@ -690,21 +719,21 @@ class TestVersioning(TestCase, AssertsCompiledSQL): """ class SomeClass(Versioned, self.Base, ComparableEntity): - __tablename__ = 'sometable' + __tablename__ = "sometable" id = Column(Integer, primary_key=True) name = Column(String(50)) self.create_tables() sess = self.session - sc = SomeClass(name='sc1') + sc = SomeClass(name="sc1") sess.add(sc) sess.commit() sess.delete(sc) sess.commit() - sc2 = SomeClass(name='sc2') + sc2 = SomeClass(name="sc2") sess.add(sc2) sess.commit() @@ -721,5 +750,5 @@ class TestVersioning(TestCase, AssertsCompiledSQL): ne_(sc2.id, scdeleted.id) # If previous assertion fails, this will also fail: - sc2.name = 'sc2 modified' + sc2.name = "sc2 modified" sess.commit() diff --git a/examples/versioned_rows/__init__.py b/examples/versioned_rows/__init__.py index 637e1aca6..e5016a740 100644 --- a/examples/versioned_rows/__init__.py +++ b/examples/versioned_rows/__init__.py @@ -9,4 +9,4 @@ history row to a separate history table. .. autosource:: -"""
\ No newline at end of file +""" diff --git a/examples/versioned_rows/versioned_map.py b/examples/versioned_rows/versioned_map.py index 6a5c86a3a..46bdbb783 100644 --- a/examples/versioned_rows/versioned_map.py +++ b/examples/versioned_rows/versioned_map.py @@ -28,11 +28,17 @@ those additional values. """ -from sqlalchemy import Column, String, Integer, ForeignKey, \ - create_engine +from sqlalchemy import Column, String, Integer, ForeignKey, create_engine from sqlalchemy.ext.declarative import declarative_base -from sqlalchemy.orm import attributes, relationship, backref, \ - sessionmaker, make_transient, validates, Session +from sqlalchemy.orm import ( + attributes, + relationship, + backref, + sessionmaker, + make_transient, + validates, + Session, +) from sqlalchemy.ext.associationproxy import association_proxy from sqlalchemy.orm.collections import attribute_mapped_collection from sqlalchemy import event @@ -45,8 +51,9 @@ def before_flush(session, flush_context, instances): """ for instance in session.dirty: - if hasattr(instance, 'new_version') and \ - session.is_modified(instance, passive=True): + if hasattr(instance, "new_version") and session.is_modified( + instance, passive=True + ): # make it transient instance.new_version(session) @@ -54,6 +61,7 @@ def before_flush(session, flush_context, instances): # re-add session.add(instance) + Base = declarative_base() @@ -67,7 +75,8 @@ class ConfigData(Base): string name mapped to a string/int value. """ - __tablename__ = 'config' + + __tablename__ = "config" id = Column(Integer, primary_key=True) """Primary key column of this ConfigData.""" @@ -76,7 +85,7 @@ class ConfigData(Base): "ConfigValueAssociation", collection_class=attribute_mapped_collection("name"), backref=backref("config_data"), - lazy="subquery" + lazy="subquery", ) """Dictionary-backed collection of ConfigValueAssociation objects, keyed to the name of the associated ConfigValue. @@ -97,7 +106,7 @@ class ConfigData(Base): def __init__(self, data): self.data = data - @validates('elements') + @validates("elements") def _associate_with_element(self, key, element): """Associate incoming ConfigValues with this ConfigData, if not already associated. @@ -117,11 +126,11 @@ class ConfigData(Base): # history of the 'elements' collection. # this is a tuple of groups: (added, unchanged, deleted) - hist = attributes.get_history(self, 'elements') + hist = attributes.get_history(self, "elements") # rewrite the 'elements' collection # from scratch, removing all history - attributes.set_committed_value(self, 'elements', {}) + attributes.set_committed_value(self, "elements", {}) # new elements in the "added" group # are moved to our new collection. @@ -133,7 +142,8 @@ class ConfigData(Base): # the old ones stay associated with the old ConfigData for elem in hist.unchanged: self.elements[elem.name] = ConfigValueAssociation( - elem.config_value) + elem.config_value + ) # we also need to expire changes on each ConfigValueAssociation # that is to remain associated with the old ConfigData. @@ -144,12 +154,12 @@ class ConfigData(Base): class ConfigValueAssociation(Base): """Relate ConfigData objects to associated ConfigValue objects.""" - __tablename__ = 'config_value_association' + __tablename__ = "config_value_association" - config_id = Column(ForeignKey('config.id'), primary_key=True) + config_id = Column(ForeignKey("config.id"), primary_key=True) """Reference the primary key of the ConfigData object.""" - config_value_id = Column(ForeignKey('config_value.id'), primary_key=True) + config_value_id = Column(ForeignKey("config_value.id"), primary_key=True) """Reference the primary key of the ConfigValue object.""" config_value = relationship("ConfigValue", lazy="joined", innerjoin=True) @@ -182,8 +192,7 @@ class ConfigValueAssociation(Base): """ if value != self.config_value.value: - self.config_data.elements[self.name] = \ - ConfigValueAssociation( + self.config_data.elements[self.name] = ConfigValueAssociation( ConfigValue(self.config_value.name, value) ) @@ -194,13 +203,14 @@ class ConfigValue(Base): ConfigValue is immutable. """ - __tablename__ = 'config_value' + + __tablename__ = "config_value" id = Column(Integer, primary_key=True) name = Column(String(50), nullable=False) originating_config_id = Column( - Integer, ForeignKey('config.id'), - nullable=False) + Integer, ForeignKey("config.id"), nullable=False + ) int_value = Column(Integer) string_value = Column(String(255)) @@ -221,7 +231,7 @@ class ConfigValue(Base): @property def value(self): - for k in ('int_value', 'string_value'): + for k in ("int_value", "string_value"): v = getattr(self, k) if v is not None: return v @@ -237,25 +247,23 @@ class ConfigValue(Base): self.string_value = str(value) self.int_value = None -if __name__ == '__main__': - engine = create_engine('sqlite://', echo=True) + +if __name__ == "__main__": + engine = create_engine("sqlite://", echo=True) Base.metadata.create_all(engine) Session = sessionmaker(engine) sess = Session() - config = ConfigData({ - 'user_name': 'twitter', - 'hash_id': '4fedffca37eaf', - 'x': 27, - 'y': 450 - }) + config = ConfigData( + {"user_name": "twitter", "hash_id": "4fedffca37eaf", "x": 27, "y": 450} + ) sess.add(config) sess.commit() version_one = config.id - config.data['user_name'] = 'yahoo' + config.data["user_name"] = "yahoo" sess.commit() version_two = config.id @@ -265,27 +273,29 @@ if __name__ == '__main__': # two versions have been created. assert config.data == { - 'user_name': 'yahoo', - 'hash_id': '4fedffca37eaf', - 'x': 27, - 'y': 450 + "user_name": "yahoo", + "hash_id": "4fedffca37eaf", + "x": 27, + "y": 450, } old_config = sess.query(ConfigData).get(version_one) assert old_config.data == { - 'user_name': 'twitter', - 'hash_id': '4fedffca37eaf', - 'x': 27, - 'y': 450 + "user_name": "twitter", + "hash_id": "4fedffca37eaf", + "x": 27, + "y": 450, } # the history of any key can be acquired using # the originating_config_id attribute - history = sess.query(ConfigValue).\ - filter(ConfigValue.name == 'user_name').\ - order_by(ConfigValue.originating_config_id).\ - all() + history = ( + sess.query(ConfigValue) + .filter(ConfigValue.name == "user_name") + .order_by(ConfigValue.originating_config_id) + .all() + ) assert [(h.value, h.originating_config_id) for h in history] == ( - [('twitter', version_one), ('yahoo', version_two)] + [("twitter", version_one), ("yahoo", version_two)] ) diff --git a/examples/versioned_rows/versioned_rows.py b/examples/versioned_rows/versioned_rows.py index ca896190d..03e1c3510 100644 --- a/examples/versioned_rows/versioned_rows.py +++ b/examples/versioned_rows/versioned_rows.py @@ -3,8 +3,13 @@ an UPDATE statement on a single row into an INSERT statement, so that a new row is inserted with the new data, keeping the old row intact. """ -from sqlalchemy.orm import sessionmaker, relationship, make_transient, \ - backref, Session +from sqlalchemy.orm import ( + sessionmaker, + relationship, + make_transient, + backref, + Session, +) from sqlalchemy import Column, ForeignKey, create_engine, Integer, String from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.orm import attributes @@ -38,9 +43,10 @@ def before_flush(session, flush_context, instances): # re-add session.add(instance) + Base = declarative_base() -engine = create_engine('sqlite://', echo=True) +engine = create_engine("sqlite://", echo=True) Session = sessionmaker(engine) @@ -48,43 +54,44 @@ Session = sessionmaker(engine) class Example(Versioned, Base): - __tablename__ = 'example' + __tablename__ = "example" id = Column(Integer, primary_key=True) data = Column(String) + Base.metadata.create_all(engine) session = Session() -e1 = Example(data='e1') +e1 = Example(data="e1") session.add(e1) session.commit() -e1.data = 'e2' +e1.data = "e2" session.commit() assert session.query(Example.id, Example.data).order_by(Example.id).all() == ( - [(1, 'e1'), (2, 'e2')] + [(1, "e1"), (2, "e2")] ) # example 2, versioning with a parent class Parent(Base): - __tablename__ = 'parent' + __tablename__ = "parent" id = Column(Integer, primary_key=True) - child_id = Column(Integer, ForeignKey('child.id')) - child = relationship("Child", backref=backref('parent', uselist=False)) + child_id = Column(Integer, ForeignKey("child.id")) + child = relationship("Child", backref=backref("parent", uselist=False)) class Child(Versioned, Base): - __tablename__ = 'child' + __tablename__ = "child" id = Column(Integer, primary_key=True) data = Column(String) def new_version(self, session): # expire parent's reference to us - session.expire(self.parent, ['child']) + session.expire(self.parent, ["child"]) # create new version Versioned.new_version(self, session) @@ -92,18 +99,19 @@ class Child(Versioned, Base): # re-add ourselves to the parent self.parent.child = self + Base.metadata.create_all(engine) session = Session() -p1 = Parent(child=Child(data='c1')) +p1 = Parent(child=Child(data="c1")) session.add(p1) session.commit() -p1.child.data = 'c2' +p1.child.data = "c2" session.commit() assert p1.child_id == 2 assert session.query(Child.id, Child.data).order_by(Child.id).all() == ( - [(1, 'c1'), (2, 'c2')] + [(1, "c1"), (2, "c2")] ) diff --git a/examples/versioned_rows/versioned_rows_w_versionid.py b/examples/versioned_rows/versioned_rows_w_versionid.py index 8445401c5..5fd6f9fc4 100644 --- a/examples/versioned_rows/versioned_rows_w_versionid.py +++ b/examples/versioned_rows/versioned_rows_w_versionid.py @@ -6,10 +6,24 @@ This example adds a numerical version_id to the Versioned class as well as the ability to see which row is the most "current" vesion. """ -from sqlalchemy.orm import sessionmaker, relationship, make_transient, \ - backref, Session, column_property -from sqlalchemy import Column, ForeignKeyConstraint, create_engine, \ - Integer, String, Boolean, select, func +from sqlalchemy.orm import ( + sessionmaker, + relationship, + make_transient, + backref, + Session, + column_property, +) +from sqlalchemy import ( + Column, + ForeignKeyConstraint, + create_engine, + Integer, + String, + Boolean, + select, + func, +) from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.orm import attributes from sqlalchemy import event @@ -38,7 +52,8 @@ class Versioned(object): # optional - set previous version to have is_current_version=False old_id = self.id session.query(self.__class__).filter_by(id=old_id).update( - values=dict(is_current_version=False), synchronize_session=False) + values=dict(is_current_version=False), synchronize_session=False + ) # make us transient (removes persistent # identity). @@ -65,9 +80,10 @@ def before_flush(session, flush_context, instances): # re-add session.add(instance) + Base = declarative_base() -engine = create_engine('sqlite://', echo=True) +engine = create_engine("sqlite://", echo=True) Session = sessionmaker(engine) @@ -75,17 +91,18 @@ Session = sessionmaker(engine) class Example(Versioned, Base): - __tablename__ = 'example' + __tablename__ = "example" data = Column(String) + Base.metadata.create_all(engine) session = Session() -e1 = Example(id=1, data='e1') +e1 = Example(id=1, data="e1") session.add(e1) session.commit() -e1.data = 'e2' +e1.data = "e2" session.commit() assert session.query( @@ -93,36 +110,36 @@ assert session.query( Example.version_id, Example.is_current_version, Example.calc_is_current_version, - Example.data).order_by(Example.id, Example.version_id).all() == ( - [(1, 1, False, False, 'e1'), (1, 2, True, True, 'e2')] + Example.data, +).order_by(Example.id, Example.version_id).all() == ( + [(1, 1, False, False, "e1"), (1, 2, True, True, "e2")] ) # example 2, versioning with a parent class Parent(Base): - __tablename__ = 'parent' + __tablename__ = "parent" id = Column(Integer, primary_key=True) child_id = Column(Integer) child_version_id = Column(Integer) - child = relationship("Child", backref=backref('parent', uselist=False)) + child = relationship("Child", backref=backref("parent", uselist=False)) __table_args__ = ( ForeignKeyConstraint( - ['child_id', 'child_version_id'], - ['child.id', 'child.version_id'], + ["child_id", "child_version_id"], ["child.id", "child.version_id"] ), ) class Child(Versioned, Base): - __tablename__ = 'child' + __tablename__ = "child" data = Column(String) def new_version(self, session): # expire parent's reference to us - session.expire(self.parent, ['child']) + session.expire(self.parent, ["child"]) # create new version Versioned.new_version(self, session) @@ -131,15 +148,16 @@ class Child(Versioned, Base): # parent foreign key to be updated also self.parent.child = self + Base.metadata.create_all(engine) session = Session() -p1 = Parent(child=Child(id=1, data='c1')) +p1 = Parent(child=Child(id=1, data="c1")) session.add(p1) session.commit() -p1.child.data = 'c2' +p1.child.data = "c2" session.commit() assert p1.child_id == 1 @@ -150,6 +168,7 @@ assert session.query( Child.version_id, Child.is_current_version, Child.calc_is_current_version, - Child.data).order_by(Child.id, Child.version_id).all() == ( - [(1, 1, False, False, 'c1'), (1, 2, True, True, 'c2')] + Child.data, +).order_by(Child.id, Child.version_id).all() == ( + [(1, 1, False, False, "c1"), (1, 2, True, True, "c2")] ) diff --git a/examples/versioned_rows/versioned_update_old_row.py b/examples/versioned_rows/versioned_update_old_row.py index 0159d2567..17c82fdc3 100644 --- a/examples/versioned_rows/versioned_update_old_row.py +++ b/examples/versioned_rows/versioned_update_old_row.py @@ -6,12 +6,23 @@ to only the most recent version. """ from sqlalchemy import ( - create_engine, Integer, String, event, Column, DateTime, - inspect, literal + create_engine, + Integer, + String, + event, + Column, + DateTime, + inspect, + literal, ) from sqlalchemy.orm import ( - make_transient, Session, relationship, attributes, backref, - make_transient_to_detached, Query + make_transient, + Session, + relationship, + attributes, + backref, + make_transient_to_detached, + Query, ) from sqlalchemy.ext.declarative import declarative_base import datetime @@ -50,7 +61,8 @@ class VersionedStartEnd(object): # make the "old" version of us, which we will turn into an # UPDATE old_copy_of_us = self.__class__( - id=self.id, start=self.start, end=self.end) + id=self.id, start=self.start, end=self.end + ) # turn old_copy_of_us into an UPDATE make_transient_to_detached(old_copy_of_us) @@ -95,11 +107,11 @@ def before_compile(query): """ensure all queries for VersionedStartEnd include criteria """ for ent in query.column_descriptions: - entity = ent['entity'] + entity = ent["entity"] if entity is None: continue - insp = inspect(ent['entity']) - mapper = getattr(insp, 'mapper', None) + insp = inspect(ent["entity"]) + mapper = getattr(insp, "mapper", None) if mapper and issubclass(mapper.class_, VersionedStartEnd): query = query.enable_assertions(False).filter( # using a literal "now" because SQLite's "between" @@ -107,14 +119,14 @@ def before_compile(query): # ``func.now()`` and we'd be using PostgreSQL literal( current_time() + datetime.timedelta(seconds=1) - ).between(ent['entity'].start, ent['entity'].end) + ).between(ent["entity"].start, ent["entity"].end) ) return query class Parent(VersionedStartEnd, Base): - __tablename__ = 'parent' + __tablename__ = "parent" id = Column(Integer, primary_key=True) start = Column(DateTime, primary_key=True) end = Column(DateTime, primary_key=True) @@ -124,10 +136,7 @@ class Parent(VersionedStartEnd, Base): child = relationship( "Child", - primaryjoin=( - "Child.id == foreign(Parent.child_n)" - ), - + primaryjoin=("Child.id == foreign(Parent.child_n)"), # note the primaryjoin can also be: # # "and_(Child.id == foreign(Parent.child_n), " @@ -138,14 +147,13 @@ class Parent(VersionedStartEnd, Base): # as well, it just means the criteria will be present twice for most # parent->child load operations # - uselist=False, - backref=backref('parent', uselist=False) + backref=backref("parent", uselist=False), ) class Child(VersionedStartEnd, Base): - __tablename__ = 'child' + __tablename__ = "child" id = Column(Integer, primary_key=True) start = Column(DateTime, primary_key=True) @@ -155,7 +163,7 @@ class Child(VersionedStartEnd, Base): def new_version(self, session): # expire parent's reference to us - session.expire(self.parent, ['child']) + session.expire(self.parent, ["child"]) # create new version VersionedStartEnd.new_version(self, session) @@ -163,6 +171,7 @@ class Child(VersionedStartEnd, Base): # re-add ourselves to the parent self.parent.child = self + times = [] @@ -185,27 +194,37 @@ def time_passes(s): assert times[-1] > times[-2] return times[-1] -e = create_engine("sqlite://", echo='debug') + +e = create_engine("sqlite://", echo="debug") Base.metadata.create_all(e) s = Session(e) now = time_passes(s) -c1 = Child(id=1, data='child 1') -p1 = Parent(id=1, data='c1', child=c1) +c1 = Child(id=1, data="child 1") +p1 = Parent(id=1, data="c1", child=c1) s.add(p1) s.commit() # assert raw DB data assert s.query(Parent.__table__).all() == [ - (1, times[0] - datetime.timedelta(days=3), - times[0] + datetime.timedelta(days=3), 'c1', 1) + ( + 1, + times[0] - datetime.timedelta(days=3), + times[0] + datetime.timedelta(days=3), + "c1", + 1, + ) ] assert s.query(Child.__table__).all() == [ - (1, times[0] - datetime.timedelta(days=3), - times[0] + datetime.timedelta(days=3), 'child 1') + ( + 1, + times[0] - datetime.timedelta(days=3), + times[0] + datetime.timedelta(days=3), + "child 1", + ) ] now = time_passes(s) @@ -214,7 +233,7 @@ p1_check = s.query(Parent).first() assert p1_check is p1 assert p1_check.child is c1 -p1.child.data = 'elvis presley' +p1.child.data = "elvis presley" s.commit() @@ -226,40 +245,51 @@ c2_check = p2_check.child assert p2_check.child is c1 # new data -assert c1.data == 'elvis presley' +assert c1.data == "elvis presley" # new end time assert c1.end == now + datetime.timedelta(days=2) # assert raw DB data assert s.query(Parent.__table__).all() == [ - (1, times[0] - datetime.timedelta(days=3), - times[0] + datetime.timedelta(days=3), 'c1', 1) + ( + 1, + times[0] - datetime.timedelta(days=3), + times[0] + datetime.timedelta(days=3), + "c1", + 1, + ) ] assert s.query(Child.__table__).order_by(Child.end).all() == [ - (1, times[0] - datetime.timedelta(days=3), times[1], 'child 1'), - (1, times[1], times[1] + datetime.timedelta(days=2), 'elvis presley') + (1, times[0] - datetime.timedelta(days=3), times[1], "child 1"), + (1, times[1], times[1] + datetime.timedelta(days=2), "elvis presley"), ] now = time_passes(s) -p1.data = 'c2 elvis presley' +p1.data = "c2 elvis presley" s.commit() # assert raw DB data. now there are two parent rows. assert s.query(Parent.__table__).order_by(Parent.end).all() == [ - (1, times[0] - datetime.timedelta(days=3), times[2], 'c1', 1), - (1, times[2], times[2] + datetime.timedelta(days=2), 'c2 elvis presley', 1) + (1, times[0] - datetime.timedelta(days=3), times[2], "c1", 1), + ( + 1, + times[2], + times[2] + datetime.timedelta(days=2), + "c2 elvis presley", + 1, + ), ] assert s.query(Child.__table__).order_by(Child.end).all() == [ - (1, times[0] - datetime.timedelta(days=3), times[1], 'child 1'), - (1, times[1], times[1] + datetime.timedelta(days=2), 'elvis presley') + (1, times[0] - datetime.timedelta(days=3), times[1], "child 1"), + (1, times[1], times[1] + datetime.timedelta(days=2), "elvis presley"), ] # add some more rows to test that these aren't coming back for # queries -s.add(Parent(id=2, data='unrelated', child=Child(id=2, data='unrelated'))) +s.add(Parent(id=2, data="unrelated", child=Child(id=2, data="unrelated"))) s.commit() @@ -274,6 +304,6 @@ c3_check = s.query(Child).filter(Child.parent == p3_check).one() assert c3_check is c1 # one child one parent.... -c3_check = s.query(Child).join(Parent.child).filter( - Parent.id == p3_check.id).one() - +c3_check = ( + s.query(Child).join(Parent.child).filter(Parent.id == p3_check.id).one() +) diff --git a/examples/vertical/__init__.py b/examples/vertical/__init__.py index 0b69f32ea..0e09b9a55 100644 --- a/examples/vertical/__init__.py +++ b/examples/vertical/__init__.py @@ -31,4 +31,4 @@ Example:: .. autosource:: -"""
\ No newline at end of file +""" diff --git a/examples/vertical/dictlike-polymorphic.py b/examples/vertical/dictlike-polymorphic.py index 7147ac40b..c000ff3cf 100644 --- a/examples/vertical/dictlike-polymorphic.py +++ b/examples/vertical/dictlike-polymorphic.py @@ -30,6 +30,7 @@ from sqlalchemy import event from sqlalchemy import literal_column from .dictlike import ProxiedDictMixin + class PolymorphicVerticalProperty(object): """A key/value pair with polymorphic value storage. @@ -69,6 +70,7 @@ class PolymorphicVerticalProperty(object): """A comparator for .value, builds a polymorphic comparison via CASE. """ + def __init__(self, cls): self.cls = cls @@ -77,40 +79,59 @@ class PolymorphicVerticalProperty(object): whens = [ ( literal_column("'%s'" % discriminator), - cast(getattr(self.cls, attribute), String) - ) for attribute, discriminator in pairs + cast(getattr(self.cls, attribute), String), + ) + for attribute, discriminator in pairs if attribute is not None ] return case(whens, self.cls.type, null()) + def __eq__(self, other): return self._case() == cast(other, String) + def __ne__(self, other): return self._case() != cast(other, String) def __repr__(self): - return '<%s %r=%r>' % (self.__class__.__name__, self.key, self.value) + return "<%s %r=%r>" % (self.__class__.__name__, self.key, self.value) + -@event.listens_for(PolymorphicVerticalProperty, "mapper_configured", propagate=True) +@event.listens_for( + PolymorphicVerticalProperty, "mapper_configured", propagate=True +) def on_new_class(mapper, cls_): """Look for Column objects with type info in them, and work up a lookup table.""" info_dict = {} - info_dict[type(None)] = (None, 'none') - info_dict['none'] = (None, 'none') + info_dict[type(None)] = (None, "none") + info_dict["none"] = (None, "none") for k in mapper.c.keys(): col = mapper.c[k] - if 'type' in col.info: - python_type, discriminator = col.info['type'] + if "type" in col.info: + python_type, discriminator = col.info["type"] info_dict[python_type] = (k, discriminator) info_dict[discriminator] = (k, discriminator) cls_.type_map = info_dict -if __name__ == '__main__': - from sqlalchemy import (Column, Integer, Unicode, - ForeignKey, UnicodeText, and_, or_, String, Boolean, cast, - null, case, create_engine) + +if __name__ == "__main__": + from sqlalchemy import ( + Column, + Integer, + Unicode, + ForeignKey, + UnicodeText, + and_, + or_, + String, + Boolean, + cast, + null, + case, + create_engine, + ) from sqlalchemy.orm import relationship, Session from sqlalchemy.orm.collections import attribute_mapped_collection from sqlalchemy.ext.declarative import declarative_base @@ -118,36 +139,38 @@ if __name__ == '__main__': Base = declarative_base() - class AnimalFact(PolymorphicVerticalProperty, Base): """A fact about an animal.""" - __tablename__ = 'animal_fact' + __tablename__ = "animal_fact" - animal_id = Column(ForeignKey('animal.id'), primary_key=True) + animal_id = Column(ForeignKey("animal.id"), primary_key=True) key = Column(Unicode(64), primary_key=True) type = Column(Unicode(16)) # add information about storage for different types # in the info dictionary of Columns - int_value = Column(Integer, info={'type': (int, 'integer')}) - char_value = Column(UnicodeText, info={'type': (str, 'string')}) - boolean_value = Column(Boolean, info={'type': (bool, 'boolean')}) + int_value = Column(Integer, info={"type": (int, "integer")}) + char_value = Column(UnicodeText, info={"type": (str, "string")}) + boolean_value = Column(Boolean, info={"type": (bool, "boolean")}) class Animal(ProxiedDictMixin, Base): """an Animal""" - __tablename__ = 'animal' + __tablename__ = "animal" id = Column(Integer, primary_key=True) name = Column(Unicode(100)) - facts = relationship("AnimalFact", - collection_class=attribute_mapped_collection('key')) + facts = relationship( + "AnimalFact", collection_class=attribute_mapped_collection("key") + ) - _proxied = association_proxy("facts", "value", - creator= - lambda key, value: AnimalFact(key=key, value=value)) + _proxied = association_proxy( + "facts", + "value", + creator=lambda key, value: AnimalFact(key=key, value=value), + ) def __init__(self, name): self.name = name @@ -159,66 +182,66 @@ if __name__ == '__main__': def with_characteristic(self, key, value): return self.facts.any(key=key, value=value) - engine = create_engine('sqlite://', echo=True) + engine = create_engine("sqlite://", echo=True) Base.metadata.create_all(engine) session = Session(engine) - stoat = Animal('stoat') - stoat['color'] = 'red' - stoat['cuteness'] = 7 - stoat['weasel-like'] = True + stoat = Animal("stoat") + stoat["color"] = "red" + stoat["cuteness"] = 7 + stoat["weasel-like"] = True session.add(stoat) session.commit() - critter = session.query(Animal).filter(Animal.name == 'stoat').one() - print(critter['color']) - print(critter['cuteness']) + critter = session.query(Animal).filter(Animal.name == "stoat").one() + print(critter["color"]) + print(critter["cuteness"]) print("changing cuteness value and type:") - critter['cuteness'] = 'very cute' + critter["cuteness"] = "very cute" session.commit() - marten = Animal('marten') - marten['cuteness'] = 5 - marten['weasel-like'] = True - marten['poisonous'] = False + marten = Animal("marten") + marten["cuteness"] = 5 + marten["weasel-like"] = True + marten["poisonous"] = False session.add(marten) - shrew = Animal('shrew') - shrew['cuteness'] = 5 - shrew['weasel-like'] = False - shrew['poisonous'] = True + shrew = Animal("shrew") + shrew["cuteness"] = 5 + shrew["weasel-like"] = False + shrew["poisonous"] = True session.add(shrew) session.commit() - q = (session.query(Animal). - filter(Animal.facts.any( - and_(AnimalFact.key == 'weasel-like', - AnimalFact.value == True)))) - print('weasel-like animals', q.all()) - - q = (session.query(Animal). - filter(Animal.with_characteristic('weasel-like', True))) - print('weasel-like animals again', q.all()) - - q = (session.query(Animal). - filter(Animal.with_characteristic('poisonous', False))) - print('animals with poisonous=False', q.all()) - - q = (session.query(Animal). - filter(or_( - Animal.with_characteristic('poisonous', False), - ~Animal.facts.any(AnimalFact.key == 'poisonous') - ) - ) + q = session.query(Animal).filter( + Animal.facts.any( + and_(AnimalFact.key == "weasel-like", AnimalFact.value == True) ) - print('non-poisonous animals', q.all()) - - q = (session.query(Animal). - filter(Animal.facts.any(AnimalFact.value == 5))) - print('any animal with a .value of 5', q.all()) + ) + print("weasel-like animals", q.all()) + + q = session.query(Animal).filter( + Animal.with_characteristic("weasel-like", True) + ) + print("weasel-like animals again", q.all()) + + q = session.query(Animal).filter( + Animal.with_characteristic("poisonous", False) + ) + print("animals with poisonous=False", q.all()) + + q = session.query(Animal).filter( + or_( + Animal.with_characteristic("poisonous", False), + ~Animal.facts.any(AnimalFact.key == "poisonous"), + ) + ) + print("non-poisonous animals", q.all()) + q = session.query(Animal).filter(Animal.facts.any(AnimalFact.value == 5)) + print("any animal with a .value of 5", q.all()) diff --git a/examples/vertical/dictlike.py b/examples/vertical/dictlike.py index 08989d8c2..f1f364207 100644 --- a/examples/vertical/dictlike.py +++ b/examples/vertical/dictlike.py @@ -32,6 +32,7 @@ can be used with many common vertical schemas as-is or with minor adaptations. """ from __future__ import unicode_literals + class ProxiedDictMixin(object): """Adds obj[key] access to a mapped class. @@ -60,9 +61,16 @@ class ProxiedDictMixin(object): del self._proxied[key] -if __name__ == '__main__': - from sqlalchemy import (Column, Integer, Unicode, - ForeignKey, UnicodeText, and_, create_engine) +if __name__ == "__main__": + from sqlalchemy import ( + Column, + Integer, + Unicode, + ForeignKey, + UnicodeText, + and_, + create_engine, + ) from sqlalchemy.orm import relationship, Session from sqlalchemy.orm.collections import attribute_mapped_collection from sqlalchemy.ext.declarative import declarative_base @@ -73,26 +81,29 @@ if __name__ == '__main__': class AnimalFact(Base): """A fact about an animal.""" - __tablename__ = 'animal_fact' + __tablename__ = "animal_fact" - animal_id = Column(ForeignKey('animal.id'), primary_key=True) + animal_id = Column(ForeignKey("animal.id"), primary_key=True) key = Column(Unicode(64), primary_key=True) value = Column(UnicodeText) class Animal(ProxiedDictMixin, Base): """an Animal""" - __tablename__ = 'animal' + __tablename__ = "animal" id = Column(Integer, primary_key=True) name = Column(Unicode(100)) - facts = relationship("AnimalFact", - collection_class=attribute_mapped_collection('key')) + facts = relationship( + "AnimalFact", collection_class=attribute_mapped_collection("key") + ) - _proxied = association_proxy("facts", "value", - creator= - lambda key, value: AnimalFact(key=key, value=value)) + _proxied = association_proxy( + "facts", + "value", + creator=lambda key, value: AnimalFact(key=key, value=value), + ) def __init__(self, name): self.name = name @@ -109,57 +120,56 @@ if __name__ == '__main__': session = Session(bind=engine) - stoat = Animal('stoat') - stoat['color'] = 'reddish' - stoat['cuteness'] = 'somewhat' + stoat = Animal("stoat") + stoat["color"] = "reddish" + stoat["cuteness"] = "somewhat" # dict-like assignment transparently creates entries in the # stoat.facts collection: - print(stoat.facts['color']) + print(stoat.facts["color"]) session.add(stoat) session.commit() - critter = session.query(Animal).filter(Animal.name == 'stoat').one() - print(critter['color']) - print(critter['cuteness']) + critter = session.query(Animal).filter(Animal.name == "stoat").one() + print(critter["color"]) + print(critter["cuteness"]) - critter['cuteness'] = 'very' + critter["cuteness"] = "very" - print('changing cuteness:') + print("changing cuteness:") - marten = Animal('marten') - marten['color'] = 'brown' - marten['cuteness'] = 'somewhat' + marten = Animal("marten") + marten["color"] = "brown" + marten["cuteness"] = "somewhat" session.add(marten) - shrew = Animal('shrew') - shrew['cuteness'] = 'somewhat' - shrew['poisonous-part'] = 'saliva' + shrew = Animal("shrew") + shrew["cuteness"] = "somewhat" + shrew["poisonous-part"] = "saliva" session.add(shrew) - loris = Animal('slow loris') - loris['cuteness'] = 'fairly' - loris['poisonous-part'] = 'elbows' + loris = Animal("slow loris") + loris["cuteness"] = "fairly" + loris["poisonous-part"] = "elbows" session.add(loris) - q = (session.query(Animal). - filter(Animal.facts.any( - and_(AnimalFact.key == 'color', - AnimalFact.value == 'reddish')))) - print('reddish animals', q.all()) + q = session.query(Animal).filter( + Animal.facts.any( + and_(AnimalFact.key == "color", AnimalFact.value == "reddish") + ) + ) + print("reddish animals", q.all()) - q = session.query(Animal).\ - filter(Animal.with_characteristic("color", 'brown')) - print('brown animals', q.all()) + q = session.query(Animal).filter( + Animal.with_characteristic("color", "brown") + ) + print("brown animals", q.all()) - q = session.query(Animal).\ - filter(~Animal.with_characteristic("poisonous-part", 'elbows')) - print('animals without poisonous-part == elbows', q.all()) + q = session.query(Animal).filter( + ~Animal.with_characteristic("poisonous-part", "elbows") + ) + print("animals without poisonous-part == elbows", q.all()) - q = (session.query(Animal). - filter(Animal.facts.any(value='somewhat'))) + q = session.query(Animal).filter(Animal.facts.any(value="somewhat")) print('any animal with any .value of "somewhat"', q.all()) - - - |