[sqlalchemy] Multiple databases and event.listen?
Say I've got more than one database (in my specific example, they're sqlite3 databases). All the databases have the same table declarations in their schemas. I want to listen for insert/update/delete events in one specific database and then propagate data from that database to the others. What I've seen in SQLAlchemy 0.7 (we're a ways back, yes) is that there doesn't seem to be an easy way for me to either filter out the event notifications to just the one databases of interest OTOH or work out which database the data came from when the event is triggered OTO. Does anyone know of an easy way to limit event notifications to just one database? Thanks! -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Multiple databases and event.listen?
On Jan 11, 2014, at 1:11 PM, Ed Willis ewil...@jesande.com wrote: Say I've got more than one database (in my specific example, they're sqlite3 databases). All the databases have the same table declarations in their schemas. I want to listen for insert/update/delete events in one specific database and then propagate data from that database to the others. What I've seen in SQLAlchemy 0.7 (we're a ways back, yes) is that there doesn't seem to be an easy way for me to either filter out the event notifications to just the one databases of interest OTOH or work out which database the data came from when the event is triggered OTO. Does anyone know of an easy way to limit event notifications to just one database? event.listen() accepts an Engine instance, not just a class, as a target: db1 = create_engine(..) db2 = create_engine(..) db3 = create_engine(..) @event.listens_for(db1, “execute”) def only_db1_events(…): … more info: http://docs.sqlalchemy.org/en/rel_0_9/core/event.html#targets hope this helps Thanks! -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out. signature.asc Description: Message signed with OpenPGP using GPGMail
Re: [sqlalchemy] Multiple databases and event.listen?
Wow, thanks a ton. So if I get you right, I'd register for insert/update/delete events on the engine pointing at the database of interest and then in my callbacks I'd have to first work out the type of the object that was changed and then take appropriate action. Correct?If so, this will absolutely solve my problem. E Sent from my BlackBerry 10 smartphone on the Rogers network. From: Michael BayerSent: Saturday, January 11, 2014 1:22 PMTo: sqlalchemy@googlegroups.comReply To: sqlalchemy@googlegroups.comSubject: Re: [sqlalchemy] Multiple databases and event.listen?On Jan 11, 2014, at 1:11 PM, Ed Willis ewil...@jesande.com wrote:Say I've got more than one database (in my specific example, they're sqlite3 databases). All the databases have the same table declarations in their schemas. I want to listen for insert/update/delete events in one specific database and then propagate data from that database to the others. What I've seen in SQLAlchemy 0.7 (we're a ways back, yes) is that there doesn't seem to be an easy way for me to either filter out the event notifications to just the one databases of interest OTOH or work out which database the data came from when the event is triggered OTO. Does anyone know of an easy way to limit event notifications to just one database?event.listen() accepts an Engine instance, not just a class, as a target:db1 = create_engine(..)db2 = create_engine(..)db3 = create_engine(..)@event.listens_for(db1, “execute”)def only_db1_events(…): …more info:http://docs.sqlalchemy.org/en/rel_0_9/core/event.html#targetshope this helpsThanks! -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Multiple databases and event.listen?
On Jan 11, 2014, at 2:20 PM, ewil...@jesande.com wrote: Wow, thanks a ton. So if I get you right, I'd register for insert/update/delete events on the engine pointing at the database of interest and then in my callbacks I'd have to first work out the type of the object that was changed and then take appropriate action. Correct? If so, this will absolutely solve my problem. oh, well it depends on what kind of event you’re listening for - I was referring specifically to the “execute()” event which is a connection/engine level event. If you’re talking about an ORM level MapperEvent before_update/before_insert/before_delete, the “Engine” is not the target, the mapped class is. You'd need to check on the connection within the event: @event.listens_for(MyMappedClass, “before_insert”) def before_insert_db1(mapper, connection, target): if connection.engine is db1: # do the thing else: # don’t do the thing E Sent from my BlackBerry 10 smartphone on the Rogers network. From: Michael Bayer Sent: Saturday, January 11, 2014 1:22 PM To: sqlalchemy@googlegroups.com Reply To: sqlalchemy@googlegroups.com Subject: Re: [sqlalchemy] Multiple databases and event.listen? On Jan 11, 2014, at 1:11 PM, Ed Willis ewil...@jesande.com wrote: Say I've got more than one database (in my specific example, they're sqlite3 databases). All the databases have the same table declarations in their schemas. I want to listen for insert/update/delete events in one specific database and then propagate data from that database to the others. What I've seen in SQLAlchemy 0.7 (we're a ways back, yes) is that there doesn't seem to be an easy way for me to either filter out the event notifications to just the one databases of interest OTOH or work out which database the data came from when the event is triggered OTO. Does anyone know of an easy way to limit event notifications to just one database? event.listen() accepts an Engine instance, not just a class, as a target: db1 = create_engine(..) db2 = create_engine(..) db3 = create_engine(..) @event.listens_for(db1, “execute”) def only_db1_events(…): … more info: http://docs.sqlalchemy.org/en/rel_0_9/core/event.html#targets hope this helps Thanks! -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out. signature.asc Description: Message signed with OpenPGP using GPGMail
Re: [sqlalchemy] Multiple databases and event.listen?
That's perfect - thanks a ton! Sent from my BlackBerry 10 smartphone on the Rogers network. From: Michael BayerSent: Saturday, January 11, 2014 2:32 PMTo: sqlalchemy@googlegroups.comReply To: sqlalchemy@googlegroups.comSubject: Re: [sqlalchemy] Multiple databases and event.listen?On Jan 11, 2014, at 2:20 PM, ewil...@jesande.com wrote:Wow, thanks a ton. So if I get you right, I'd register for insert/update/delete events on the engine pointing at the database of interest and then in my callbacks I'd have to first work out the type of the object that was changed and then take appropriate action. Correct?If so, this will absolutely solve my problem.oh, well it depends on what kind of event you’re listening for - I was referring specifically to the “execute()” event which is a connection/engine level event. If you’re talking about an ORM level MapperEvent before_update/before_insert/before_delete, the “Engine” is not the target, the mapped class is. You'd need to check on the connection within the event:@event.listens_for(MyMappedClass, “before_insert”)def before_insert_db1(mapper, connection, target): if connection.engine is db1:# do the thing else:# don’t do the thing E Sent from my BlackBerry 10 smartphone on the Rogers network. From: Michael BayerSent: Saturday, January 11, 2014 1:22 PMTo: sqlalchemy@googlegroups.comReply To: sqlalchemy@googlegroups.comSubject: Re: [sqlalchemy] Multiple databases and event.listen?On Jan 11, 2014, at 1:11 PM, Ed Willis ewil...@jesande.com wrote:Say I've got more than one database (in my specific example, they're sqlite3 databases). All the databases have the same table declarations in their schemas. I want to listen for insert/update/delete events in one specific database and then propagate data from that database to the others. What I've seen in SQLAlchemy 0.7 (we're a ways back, yes) is that there doesn't seem to be an easy way for me to either filter out the event notifications to just the one databases of interest OTOH or work out which database the data came from when the event is triggered OTO. Does anyone know of an easy way to limit event notifications to just one database?event.listen() accepts an Engine instance, not just a class, as a target:db1 = create_engine(..)db2 = create_engine(..)db3 = create_engine(..)@event.listens_for(db1, “execute”)def only_db1_events(…): …more info:http://docs.sqlalchemy.org/en/rel_0_9/core/event.html#targetshope this helpsThanks! -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] multiple databases?
thanks! in fact i'm using twisted, and i make use of some abstractions on top of query so they can be treated as a deferred :) but either way, i'm using a very modified version of this: http://techspot.zzzeek.org/2012/01/11/django-style-database-routers-in-sqlalchemy/ it's been working good so far (or better, since yesterday), with multiple connections to a postgres database and also to sqlite :) my best regards, richard. On 12/11/2013 03:48 AM, Jameson Lee wrote: If you are using Flask, check out binds[1] with Flask-SQLAlchemy. [1] http://pythonhosted.org/Flask-SQLAlchemy/binds.html On Monday, December 9, 2013 12:33:46 PM UTC-8, warwickp wrote: Hi Richard There are no problems connecting to multiple database sources in the one application - we do it all the time :-) Cheers Warwick On 10 Dec 2013, at 4:08 am, Richard Gerd Kuesters ric...@humantech.com.br javascript: wrote: hi all, i don't know if anyone have to go through this, but here's a question: is it possible to use multiple databases sources (like postgres and mysql) in a single application? my best regards, richard. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com javascript:. To post to this group, send email to sqlal...@googlegroups.com javascript:. Visit this group at http://groups.google.com/group/sqlalchemy http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out https://groups.google.com/groups/opt_out. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] multiple databases?
richard - how are you handle the scoping and management of sessions ? i'm wanting (badly) to convert some twisted code that is using raw sql through their db api onto the sqlalchemy model for my core app. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] multiple databases?
quite frankly, i'm handling them using deferreds, in a similar way you can find in sqlalchemy-future: https://github.com/lunant/SQLAlchemy-Future with this -- and using scoped sessions based on the callbacks, i can close the session quite nice. the code is very, very ugly for now, but i plan to share it somewhere in the near future :) i did use some ideas behind txpostgres impl, too. best regards, richard. On 12/11/2013 02:52 PM, Jonathan Vanasco wrote: richard - how are you handle the scoping and management of sessions ? i'm wanting (badly) to convert some twisted code that is using raw sql through their db api onto the sqlalchemy model for my core app. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] multiple databases?
If you are using Flask, check out binds[1] with Flask-SQLAlchemy. [1] http://pythonhosted.org/Flask-SQLAlchemy/binds.html On Monday, December 9, 2013 12:33:46 PM UTC-8, warwickp wrote: Hi Richard There are no problems connecting to multiple database sources in the one application - we do it all the time :-) Cheers Warwick On 10 Dec 2013, at 4:08 am, Richard Gerd Kuesters ric...@humantech.com.brjavascript: wrote: hi all, i don't know if anyone have to go through this, but here's a question: is it possible to use multiple databases sources (like postgres and mysql) in a single application? my best regards, richard. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com javascript:. To post to this group, send email to sqlal...@googlegroups.comjavascript: . Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] multiple databases?
hi all, i don't know if anyone have to go through this, but here's a question: is it possible to use multiple databases sources (like postgres and mysql) in a single application? my best regards, richard. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] multiple databases?
ok, disconsider, i may use mike's session router: http://techspot.zzzeek.org/2012/01/11/django-style-database-routers-in-sqlalchemy/ On 12/09/2013 04:08 PM, Richard Gerd Kuesters wrote: hi all, i don't know if anyone have to go through this, but here's a question: is it possible to use multiple databases sources (like postgres and mysql) in a single application? my best regards, richard. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] multiple databases?
Hi Richard There are no problems connecting to multiple database sources in the one application - we do it all the time :-) Cheers Warwick On 10 Dec 2013, at 4:08 am, Richard Gerd Kuesters rich...@humantech.com.br wrote: hi all, i don't know if anyone have to go through this, but here's a question: is it possible to use multiple databases sources (like postgres and mysql) in a single application? my best regards, richard. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] multiple databases with same table names not working
I have two databases (MySQL and MSSQL) that I'm working with using
SQLAlchemy 0.7, the databases share table names and therefore I'm
getting an error message when running the code.
The error message is :
sqlalchemy.exc.InvalidRequestError: Table 'wo' is already defined for
this MetaData instance. Specify 'extend_existing=True' to redefine
options and columns on an existing Table object.
The simplified code is:
#*
from sqlalchemy import create_engine, Column, Integer, String,
DateTime, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship, backref
from mysql.connector.connection import MySQLConnection
Base = declarative_base()
def get_characterset_info(self):
return self.get_charset()
MySQLConnection.get_characterset_info = MySQLConnection.get_charset
mysqlengine = create_engine('mysql+mysqlconnector:///mp2',
echo=True)
MYSQLSession = sessionmaker(bind=mysqlengine)
mysqlsession= MYSQLSession()
MP2engine = create_engine('mssql+pyodbc:///mp2', echo=True)
MP2Session = sessionmaker(bind=MP2engine)
mp2session= MP2Session()
class MYSQLWo(Base):
__tablename__= 'wo'
wonum = Column(String, primary_key=True)
taskdesc = Column(String)
comments = relationship(MYSQLWocom, order_by=MYSQLWocom.wonum,
backref='wo')
class MYSQLWocom (Base):
__tablename__='wocom'
wonum = Column(String, ForeignKey('wo.wonum'), primary_key=True)
comments = Column(String, primary_key=True)
class MP2Wo(Base):
__tablename__= 'wo'
wonum = Column(String, primary_key=True)
taskdesc = Column(String)
comments = relationship(MP2Wocom, order_by=MP2Wocom.wonum,
backref='wo')
class MP2Wocom (Base):
__tablename__='woc'
wonum = Column(String, ForeignKey('wo.wonum'), primary_key=True)
location = Column(String)
sublocation1 = Column(String)
texts = Column(String, primary_key=True)
#*
Any suggestions? I've looked through both the documentation and the
group posts and cannot find anything definitive. I'm thinking the
answer has something to do with the metadata, but I really don't know
where to go from here.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@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.
Re: [sqlalchemy] multiple databases with same table names not working
On Nov 24, 2011, at 11:29 PM, dangel wrote:
I have two databases (MySQL and MSSQL) that I'm working with using
SQLAlchemy 0.7, the databases share table names and therefore I'm
getting an error message when running the code.
The error message is :
sqlalchemy.exc.InvalidRequestError: Table 'wo' is already defined for
this MetaData instance. Specify 'extend_existing=True' to redefine
options and columns on an existing Table object.
The simplified code is:
#*
from sqlalchemy import create_engine, Column, Integer, String,
DateTime, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship, backref
from mysql.connector.connection import MySQLConnection
Base = declarative_base()
def get_characterset_info(self):
return self.get_charset()
MySQLConnection.get_characterset_info = MySQLConnection.get_charset
mysqlengine = create_engine('mysql+mysqlconnector:///mp2',
echo=True)
MYSQLSession = sessionmaker(bind=mysqlengine)
mysqlsession= MYSQLSession()
MP2engine = create_engine('mssql+pyodbc:///mp2', echo=True)
MP2Session = sessionmaker(bind=MP2engine)
mp2session= MP2Session()
class MYSQLWo(Base):
__tablename__= 'wo'
wonum = Column(String, primary_key=True)
taskdesc = Column(String)
comments = relationship(MYSQLWocom, order_by=MYSQLWocom.wonum,
backref='wo')
class MYSQLWocom (Base):
__tablename__='wocom'
wonum = Column(String, ForeignKey('wo.wonum'), primary_key=True)
comments = Column(String, primary_key=True)
class MP2Wo(Base):
__tablename__= 'wo'
wonum = Column(String, primary_key=True)
taskdesc = Column(String)
comments = relationship(MP2Wocom, order_by=MP2Wocom.wonum,
backref='wo')
class MP2Wocom (Base):
__tablename__='woc'
wonum = Column(String, ForeignKey('wo.wonum'), primary_key=True)
location = Column(String)
sublocation1 = Column(String)
texts = Column(String, primary_key=True)
#*
Any suggestions? I've looked through both the documentation and the
group posts and cannot find anything definitive. I'm thinking the
answer has something to do with the metadata, but I really don't know
where to go from here.
Are the table structures identical in both databases ? if so, then I would not
double up the declarative classes in this way - I'd use one class to represent
a particular table structure, which can then be used in either session.
You're already using two different Session objects, so there is no need to
specify different classes just to distinguish these.
OTOH, if the table structures do differ among the two databases, and you do in
fact need to define each class twice, you just need to use two separate
declarative bases so that they no longer share the same MetaData object.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@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.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@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.
Re: [sqlalchemy] Multiple databases or multiple schemas?
On Jun 13, 2010, at 4:27 PM, Henry Precheur wrote:
I'm starting a new project which is roughly hosting a bunch of sites. I
want the sites to be isolated, they'll all have the same schema (data
definition), but will store their data on different 'name-spaces'.
The front end will be a python web app. And I'd like to have this
application talk to all the 'name-spaces' at the same time:
With a request on example.com/site1, the web app will use the
'name-space' site1, with example.com/site2 it will use site2.
I am using Postgres. There are 2 options for the 'name-space': Database
or Schema [1]:
1. One database per site
2. One database for all the sites and 1 schema per site
Solution #1 would require to maintain 1 connection per site per python
process. That means: lots of connections, lots of memory needed. One
the other hand, this solution is supported by SQLAlchemy out-of-the-box.
I'll have a dictionary like that:
{'site1': Engine('postgres://.../site1',
'site2': Engine('postgres://.../site2', ...}
And whenever a request comes in I get the right engine via this
dictionary.
Solution #2 is not supported natively by SQLAlchemy. Each time a request
comes-in I'll have to issue an additional query SET search_path TO
MY_SITE where MY_SITE is the schema associated with the site.
Solution #2 seems much more lightweight to me. The only problem is the
small overhead that might be created by the additional query.
What do you guys think? Will I get into trouble with solution #2?
If you have alternative suggestions I'd like to hear them :)
you can do the multi-schema thing using either a PoolListener, or a Table
subclass that implements a @property for the schema attribute - in both cases
these would be linked to a thread local variable that you would set per
request. If the load per site is low, you can also forego the usage of
pooling to eliminate the issue of dormant connections (possibly look into
something like pgpool, perhaps they have some feature that optimizes this use
case).
As far as approach, consider the load each per-user site will have, and how you
might scale out to include multiple database servers. I'd grep around the
PG docs and wiki carefully to get a full grasp of the implications of each
approach.
--
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.
Re: [sqlalchemy] Multiple databases or multiple schemas?
On Mon, Jun 14, 2010 at 01:06:04PM -0400, Michael Bayer wrote: you can do the multi-schema thing using either a PoolListener, or a Table subclass that implements a @property for the schema attribute - in both cases these would be linked to a thread local variable that you would set per request. If the load per site is low, you can also forego the usage of pooling to eliminate the issue of dormant connections (possibly look into something like pgpool, perhaps they have some feature that optimizes this use case). Both approach would work. The PoolListener looks a bit better, the Table subclass looks a bit hackish. BTW I am not planning to use threads, I'll just use a dozen python processes to make sure the site is responsive under heavy load. This should simplify pooling quite a bit, I'll just use a StaticPool. As far as approach, consider the load each per-user site will have, and how you might scale out to include multiple database servers. I'd grep around the PG docs and wiki carefully to get a full grasp of the implications of each approach. Yes, scaling was one of the motivation for this approach, I definitely need to learn more about how PG would handle this. Simplifying the architecture was the main motivation. I don't want to have a column `site_id` in every table. Also I think that putting everything in its own schema would improve performance dramatically: instead of big indexes for all sites, there'll be small indexes for each site. Thank-you so much, your reply helped a great deal. -- Henry Prêcheur -- 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.
[sqlalchemy] Multiple databases or multiple schemas?
I'm starting a new project which is roughly hosting a bunch of sites. I
want the sites to be isolated, they'll all have the same schema (data
definition), but will store their data on different 'name-spaces'.
The front end will be a python web app. And I'd like to have this
application talk to all the 'name-spaces' at the same time:
With a request on example.com/site1, the web app will use the
'name-space' site1, with example.com/site2 it will use site2.
I am using Postgres. There are 2 options for the 'name-space': Database
or Schema [1]:
1. One database per site
2. One database for all the sites and 1 schema per site
Solution #1 would require to maintain 1 connection per site per python
process. That means: lots of connections, lots of memory needed. One
the other hand, this solution is supported by SQLAlchemy out-of-the-box.
I'll have a dictionary like that:
{'site1': Engine('postgres://.../site1',
'site2': Engine('postgres://.../site2', ...}
And whenever a request comes in I get the right engine via this
dictionary.
Solution #2 is not supported natively by SQLAlchemy. Each time a request
comes-in I'll have to issue an additional query SET search_path TO
MY_SITE where MY_SITE is the schema associated with the site.
Solution #2 seems much more lightweight to me. The only problem is the
small overhead that might be created by the additional query.
What do you guys think? Will I get into trouble with solution #2?
If you have alternative suggestions I'd like to hear them :)
Regards,
[1]: http://www.postgresql.org/docs/8.4/interactive/ddl-schemas.html
--
Henry Prêcheur
--
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.
[sqlalchemy] multiple databases ?
Hello again,
So now that I have mssql connection ready and data filtered out and
processed I need to save it to a different database.
mssql - process data - save to mysql
I am wondering how should I create a second database connection? In
second database I will create a table and populate the records.
# First database
e = sqlalchemy.create_engine(mssql://user:[EMAIL
PROTECTED]:1433/dbname?driver=TDSodbc_options='TDS_Version=8.0')
#e.echo=True
metadata=sqlalchemy.MetaData(e)
#session stuff
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind=e, autoflush=True, transactional=True)
session = Session()
#table stuff
class th(object):
pass
th_table = sqlalchemy.Table('', metadata, autoload=True)
mapper(th,th_table)
# database number 2. Is this the way I should create second database
connection/session/mapper?
e2 = sqlalchemy.create_engine('mysql://user:[EMAIL PROTECTED]/dbname')
Do I create new metadata?
metadata2=sqlalchemy.MetaData(e2)
And then new session2?
Session2 = sessionmaker(bind=e2, autoflush=True, transactional=True)
What does this line do?
session = Session()
Is the above correct way of doing this?
I would like to keep these connections separate so there will be no
confusion of what I am using.
Thanks,
Lucas
--
Automotive Recall Database. Cars, Trucks, etc.
http://www.lucasmanual.com/recall/
TurboGears Manual-Howto
http://lucasmanual.com/pdf/TurboGears-Manual-Howto.pdf
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
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
-~--~~~~--~~--~--~---
