Hi All,
I'm totally new to the usage of Sharding. I have just tried a sample
with sqlalchemy ShardedSession.
I have two databases (old and new) created with a Projects table. I
have a global shardID that indicates which database to operate on.
shardID - 'n' - for new DB and
shardID - 'o' - for old DB
With the initial shardID pointing to the new DB, I have programmed the
following steps.
Added a record to Projects table, and queried it -- received back the
new DB record
Changed the shardID to 'o'
Added a record to Projects table, and queried it -- received back the
old DB record
Changed the shardID to 'n'
Queried Projects table - Here, as the shardID is pointing to the new
DB i should get records from the newDB projects table, whereas I'm
receiving the record from th old DB
But, If I add another record to the Projects table before this step,
then the qury returns me the new DB records.
Can anyone let me know the reason for this behaviour? Or is there any
mistake in the program?
Below is the code which I have used:
#test_shard.py
from sqlalchemy import create_engine
from sqlalchemy import Table, Column, Integer, String, MetaData
from sqlalchemy.orm import sessionmaker, mapper
from sqlalchemy.ext.horizontal_shard import ShardedSession
#code added to use sharding
shardID = 'n' #default
oldDBEngine = create_engine('sqlite:///:memory:')
newDBEngine = create_engine('sqlite:///:memory:')
DBSession = sessionmaker(class_=ShardedSession)
DBSession.configure(shards={'o':oldDBEngine, 'n':newDBEngine})
metadata = MetaData()
def shard_chooser(mapper, instance, clause=None):
return shardID
def id_chooser(query, ident):
return shardID
def query_chooser(query):
return shardID
# further configure create_session to use these functions
DBSession.configure(shard_chooser=shard_chooser,
id_chooser=id_chooser,
query_chooser=query_chooser)
projects_table = Table('projects', metadata,
Column('id', Integer, primary_key=True), #
primary key
Column('name', String(40)), # foreign key
Column('prefix', String(6))
)
class Project(object):
def __init__(self, name, prefix):
self.name = name
self.prefix = prefix
mapper(Project, projects_table)
# create tables
for dbEngine in (newDBEngine, oldDBEngine):
metadata.drop_all(dbEngine)
metadata.create_all(dbEngine)
############################################ test_shard
session = DBSession()
print "---------- add to new db with shardID", shardID #default
shardID in 'n' ponting to new database
p1_new = Project('p1_new', 'p1_v1_new')
session.add(p1_new)
session.commit()
print
print "---------- get from new db with shardID", shardID
query = session.query(Project)
projects = query.all()
for p in projects:
print p.id, p.name, p.prefix
print
shardID = 'o'
print "---------- add to old db with shardID", shardID
p1_old = Project('p1_old', 'p1_v1_old')
session.add(p1_old)
session.commit()
print
print "---------- get from old db with shardID", shardID
query = session.query(Project)
projects = query.all()
for p in projects:
print p.id, p.name, p.prefix
print
shardID = 'n'
##print "---------- add to new db with shardID", shardID
##p2_new = Project('p2_new', 'p2_v2_new')
##session.add(p2_new)
##session.commit()
##print
print "---------- get from new db with shardID", shardID
query = session.query(Project)
projects = query.all()
for p in projects:
print p.id, p.name, p.prefix
Thanks,
Gayatri
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
