[sqlalchemy] Re: multiple databases with same table names not working
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. The table structures are slightly different. I created a separate declarative base as you suggested and all is working correctly now. Thank you for the quick response, this should get be going until the next problem! -- 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] Re: Multiple databases or multiple schemas?
On Sun, Jun 13, 2010 at 09:23:22PM +, Faheem Mitha wrote:
Sqlalchemy's table can take the qschema as argument, eg.
pheno_table = Table(
'pheno', metadata,
Column('patientid', String(60), primary_key=True),
Column('famid', String(60), nullable=True),
Column('sex_id', None, ForeignKey(schemaname+'.sex.val',
onupdate='CASCADE', ondelete='CASCADE'), index=True),
Column('race_id', None, ForeignKey(schemaname+'.race.val',
onupdate='CASCADE', ondelete='CASCADE'), index=True),
Column('phenotype', SmallInteger),
schema = schemaname,
)
So I don't think you do have to do that.
The thing is that each table will be present in every schema. So I can't
use the schema parameter (or I can subclass Table like Mike suggested).
Cheers,
--
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] Re: Multiple databases or multiple schemas?
[This message has also been posted.]
On Sun, 13 Jun 2010 13:27:34 -0700, Henry Precheur he...@precheur.org 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.
Sqlalchemy's table can take the qschema as argument, eg.
pheno_table = Table(
'pheno', metadata,
Column('patientid', String(60), primary_key=True),
Column('famid', String(60), nullable=True),
Column('sex_id', None, ForeignKey(schemaname+'.sex.val',
onupdate='CASCADE', ondelete='CASCADE'), index=True),
Column('race_id', None, ForeignKey(schemaname+'.race.val',
onupdate='CASCADE', ondelete='CASCADE'), index=True),
Column('phenotype', SmallInteger),
schema = schemaname,
)
So I don't think you do have to do that.
Solution #2 seems much more lightweight to me. The only problem is the
small overhead that might be created by the additional query.
I'm actually using multiple schemas in one db myself, and it seems to
me sqla supports this just fine. The only time I have to do
SET search_path TO MY_SITE
is when I access the db directly using psql. Of course, you might have
to worry whether the web end of things support schemas too.
Faheem.
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] Re: multiple databases ?
On Tue, Apr 22, 2008 at 11:54 AM, Lukasz Szybalski [EMAIL PROTECTED] wrote:
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
Just an FYI.
Setting up 2 meta data, 2 sessions worked out pretty good.
Lucas
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
[sqlalchemy] Re: multiple databases ?
On Tuesday 22 April 2008 19:54:16 Lukasz Szybalski wrote:
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?dr
iver=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
well... u dont really need 2nd metadata, and u dont need any
session/mappers/etc ORM-stuff at all. re-bind the metadata to new
engine, then metadata.createall(), and then for each record in each
table in metadata, tbl.select via src_engine, tbl.insert via
dst_engine
but u can try this way too..
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
[sqlalchemy] Re: multiple databases ?
On Tue, Apr 22, 2008 at 6:00 PM, [EMAIL PROTECTED] wrote:
On Tuesday 22 April 2008 19:54:16 Lukasz Szybalski wrote:
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?dr
iver=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
well... u dont really need 2nd metadata, and u dont need any
session/mappers/etc ORM-stuff at all. re-bind the metadata to new
engine, then metadata.createall(), and then for each record in each
table in metadata, tbl.select via src_engine, tbl.insert via
dst_engine
but u can try this way too..
these are 2 different databases...
one is all autoload
and two is completely new and will not have any tables from 1.
Lucas
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
