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 [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.
>
--
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.
