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'))]
countries = {}
all_post_codes = []
for city, country, postcodes in data:
try:
country = countries[country]
except KeyError:
countries[country] = country = Country(country)
city = City(city, country)
pc = [PostalCode(code, city) for code in postcodes]
Session.add_all(pc)
all_post_codes.extend(pc)
for i in xrange(1, 51):
person = Person(
"person %.2d" % i,
Address(street="street %.2d" % i,
postal_code=all_post_codes[random.randint(
0,
len(all_post_codes) - 1)]))
Session.add(person)
Session.commit()
# start the demo fresh
Session.remove()
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'))
]
countries = {}
all_post_codes = []
for city, country, postcodes in data:
try:
country = countries[country]
except KeyError:
countries[country] = country = Country(country)
city = City(city, country)
pc = [PostalCode(code, city) for code in postcodes]
Session.add_all(pc)
all_post_codes.extend(pc)
for i in xrange(1, 51):
person = Person(
"person %.2d" % i,
Address(
street="street %.2d" % i,
postal_code=all_post_codes[
random.randint(0, len(all_post_codes) - 1)]
)
)
Session.add(person)
Session.commit()
# start the demo fresh
Session.remove()
def load_name_range(start, end, invalidate=False):
"""Load Person objects on a range of names.
start/end are integers, range is then
"person <start>" - "person <end>".
The cache option we set up is called "name_range", indicating
a range of names for the Person class.
The `Person.addresses` collections are also cached. Its basically
another level of tuning here, as that particular cache option
can be transparently replaced with joinedload(Person.addresses).
The effect is that each Person and his/her Address collection
is cached either together or separately, affecting the kind of
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"))
# have the "addresses" collection cached separately
# each lazyload of Person.addresses loads from cache.
q = q.options(RelationshipCache("default", "by_person", Person.addresses))
# alternatively, eagerly load the "addresses" collection, so that they'd
# 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))
# if requested, invalidate the cache on current criterion.
if invalidate:
q.invalidate()
return q.all()
def load_name_range(start, end, invalidate=False):
"""Load Person objects on a range of names.
start/end are integers, range is then
"person <start>" - "person <end>".
The cache option we set up is called "name_range", indicating
a range of names for the Person class.
The `Person.addresses` collections are also cached. Its basically
another level of tuning here, as that particular cache option
can be transparently replaced with joinedload(Person.addresses).
The effect is that each Person and his/her Address collection
is cached either together or separately, affecting the kind of
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"))
# have the "addresses" collection cached separately
# each lazyload of Person.addresses loads from cache.
q = q.options(RelationshipCache("default", "by_person", Person.addresses))
# alternatively, eagerly load the "addresses" collection, so that they'd
# 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))
# if requested, invalidate the cache on current criterion.
if invalidate:
q.invalidate()
return q.all()
"""relationship_caching.py
Load a set of Person and Address objects, specifying that
related PostalCode, City, Country objects should be pulled from long
term cache.
"""
from environment import Session, root
from model import Person, Address, cache_address_bits
from sqlalchemy.orm import joinedload
import os
for p in Session.query(Person).options(joinedload(Person.addresses),
cache_address_bits):
print p.format_full()
print "\n\nIf this was the first run of relationship_caching.py, SQL was likely emitted to "\
"load postal codes, cities, countries.\n"\
"If run a second time, only a single SQL statement will run - all "\
"related data is pulled from cache.\n"\
"To clear the cache, delete the directory %r. \n"\
"This will cause a re-load of cities, postal codes and countries on "\
"the next run.\n"\
% os.path.join(root, 'container_file')
from environment import Session, cache_manager
from caching_query import FromCache
# create a Beaker container type called "ext:local_session".
# it will reference the ScopedSession in meta.
ScopedSessionNamespace.create_session_container("ext:local_session", Session)
# set up a region based on this new container type.
cache_manager.regions['local_session'] ={'type':'ext:local_session'}
from model import Person
# query to load Person by name, with criterion
# of "person 10"
q = Session.query(Person).\
options(FromCache("local_session", "by_name")).\
filter(Person.name=="person 10")
# load from DB
person10 = q.one()
# next call, the query is cached.
person10 = q.one()
# clear out the Session. The "_beaker_cache" dictionary
# disappears with it.
Session.remove()
# query calls from DB again
person10 = q.one()
"""relationship_caching.py
Load a set of Person and Address objects, specifying that
related PostalCode, City, Country objects should be pulled from long
term cache.
"""
from environment import Session, root
from model import Person, Address, cache_address_bits
from sqlalchemy.orm import joinedload
import os
for p in Session.query(Person).options(joinedload(Person.addresses), cache_address_bits):
print p.format_full()
print "\n\nIf this was the first run of relationship_caching.py, SQL was likely emitted to "\
"load postal codes, cities, countries.\n"\
"If run a second time, only a single SQL statement will run - all "\
"related data is pulled from cache.\n"\
"To clear the cache, delete the directory %r. \n"\
"This will cause a re-load of cities, postal codes and countries on "\
"the next run.\n"\
% os.path.join(root, 'container_file')
"""helloworld.py
Illustrate how to load some data, and cache the results.
"""
import pudb; pu.db
from environment import Session
from model import Person
from caching_query import FromCache
# load Person objects. cache the result in the "default" cache region
print("loading people....")
people = Session.query(Person).options(FromCache("default")).all()
# remove the Session. next query starts from scratch.
Session.remove()
# load again, using the same FromCache option. now they're cached,
# so no SQL is emitted.
print("loading people....again!")
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()
from caching_query import FromCache
# create a Beaker container type called "ext:local_session".
# it will reference the ScopedSession in meta.
ScopedSessionNamespace.create_session_container("ext:local_session",
Session)
# set up a region based on this new container type.
cache_manager.regions['local_session'] = {'type': 'ext:local_session'}
from model import Person
# query to load Person by name, with criterion
# of "person 10"
q = Session.query(Person).\
options(FromCache("local_session", "by_name")).\
filter(Person.name=="person 10")
# load from DB
person10 = q.one()
# next call, the query is cached.
person10 = q.one()
# clear out the Session. The "_beaker_cache" dictionary
# disappears with it.
Session.remove()
# query calls from DB again
person10 = q.one()
"""helloworld.py
Illustrate how to load some data, and cache the results.
"""
from environment import Session
from model import Person
from caching_query import FromCache
# load Person objects. cache the result under the namespace "all_people".
print "loading people...."
people = Session.query(Person).options(FromCache("default", "all_people")).all()
# remove the Session. next query starts from scratch.
Session.remove()
# load again, using the same FromCache option. now they're cached
# under "all_people", no SQL is emitted.
print "loading people....again!"
people = Session.query(Person).options(FromCache("default", "all_people")).all()
# want to load on some different kind of query ? change the namespace
# you send to FromCache
print "loading people two through twelve"
people_two_through_twelve = Session.query(Person).\
options(FromCache("default", "people_on_range")).\
filter(Person.name.between("person 02", "person 12")).\
all()
# the data is cached under the "namespace" you send to FromCache, *plus*
