I did use a plain session and set "bind" to either database as needed. It's
working fine.
Thank you.
On Fri, Dec 3, 2010 at 12:24 AM, Michael Bayer <mike...@zzzcomputing.com>wrote:
>
> On Dec 2, 2010, at 1:34 PM, gayatri wrote:
>
> > 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:
>
> The core concept of horizontal sharding is that different records belong
> exclusively to one database node or another, which are blended together as
> results that are loaded from one or both nodes simultaneously based on
> rules. Your approach below uses a global flag, meaning the session can only
> reference one shard at a time, so any query against the Session is only
> capable of returning results from one database at a time. If you only
> want to look at one database at a time for all records, there's no reason to
> use ShardedSession, just use a plain session and set "bind" to either
> database as needed.
>
>
>
>
> >
> > #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 sqlalch...@googlegroups.com.
> > To unsubscribe from this group, send email to
> sqlalchemy+unsubscr...@googlegroups.com<sqlalchemy%2bunsubscr...@googlegroups.com>
> .
> > For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
> >
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to sqlalch...@googlegroups.com.
> To unsubscribe from this group, send email to
> sqlalchemy+unsubscr...@googlegroups.com<sqlalchemy%2bunsubscr...@googlegroups.com>
> .
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
>
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
