[sqlalchemy] sqlalchemy how to enlarge current using connections
Hi all, I have a question here. Here is my db init code snippet: engine = create_engine(db_url, pool_size=100,max_overflow=150,echo=engine_echo,pool_recycle=3600) session = scoped_session(sessionmaker(bind=engine)) metadata = MetaData(bind=engine) Then, I use engine to do update/insert operations. There is no problem here, then I find during the performance test that, it's too slow to insert/update mysql server. When my script running, on mysql server, use mysql 'show processlist;' command, I see only 2 or 3 active connections kept. Then,within my script, I use multiprocessing.pool(defaults to cpu core size, which is 4 for me) to do mysql insert/update operations. Finally, I see 4 or 5 connections kept from mysql server. So, seems db operation is bottleneck because I cannot use more connections yet. How to push the conncurrent connections up? I am sure script generating db tasks are fast enough. Thanks. Wesley -- 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/d/optout.
Re: [sqlalchemy] sqlalchemy how to enlarge current using connections
To make more connections, call connect(), and/or use more session objects. Each session uses one connection. If you see three connections, that means your script has only worked with three connections at once, such as, you opened three Session objects concurrently. Sqlalchemy does not initiate any concurrent operations on its own. Sent from my iPhone On Apr 8, 2014, at 5:03 AM, Ni Wesley nisp...@gmail.com wrote: Hi all, I have a question here. Here is my db init code snippet: engine = create_engine(db_url, pool_size=100,max_overflow=150,echo=engine_echo,pool_recycle=3600) session = scoped_session(sessionmaker(bind=engine)) metadata = MetaData(bind=engine) Then, I use engine to do update/insert operations. There is no problem here, then I find during the performance test that, it's too slow to insert/update mysql server. When my script running, on mysql server, use mysql 'show processlist;' command, I see only 2 or 3 active connections kept. Then,within my script, I use multiprocessing.pool(defaults to cpu core size, which is 4 for me) to do mysql insert/update operations. Finally, I see 4 or 5 connections kept from mysql server. So, seems db operation is bottleneck because I cannot use more connections yet. How to push the conncurrent connections up? I am sure script generating db tasks are fast enough. Thanks. Wesley -- 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/d/optout. -- 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/d/optout.
Re: [sqlalchemy] database design question
perhaps this would help? http://techspot.zzzeek.org/2012/01/11/django-style-database-routers-in-sqlalchemy/ i'm willing to use different schemas to my question. if anyone have a better idea, let me know :) best regards, richard. On 04/04/2014 01:58 PM, Richard Gerd Kuesters wrote: hi all! i have a question about sqlalchemy and database design. i'm developing an app, where each client may receive an alert about the total space usage of their data (files and database), so, using postgresql, i can get them (data usage size) using a different tablespace, database or schema, if i'm not wrong. given these premises, which is the best way to design my database using sqlalchemy, having in mind that I need also to integrate the client databases to the core database? 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 mailto:sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com mailto:sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. -- 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/d/optout.
Re: [sqlalchemy] sqlalchemy how to enlarge current using connections
For me, I just use engine.execute most of the time, any problem with this? Or if I use session or connect, do I need to close the session or connection everytime? otherwise, it will trigger the pool size limit error as the connection is increasing, right? 在 2014年4月8日星期二UTC+8下午8时49分16秒，Michael Bayer写道： To make more connections, call connect(), and/or use more session objects. Each session uses one connection. If you see three connections, that means your script has only worked with three connections at once, such as, you opened three Session objects concurrently. Sqlalchemy does not initiate any concurrent operations on its own. Sent from my iPhone On Apr 8, 2014, at 5:03 AM, Ni Wesley nis...@gmail.com javascript: wrote: Hi all, I have a question here. Here is my db init code snippet: engine = create_engine(db_url, pool_size=100,max_overflow=150,echo=engine_echo,pool_recycle=3600) session = scoped_session(sessionmaker(bind=engine)) metadata = MetaData(bind=engine) Then, I use engine to do update/insert operations. There is no problem here, then I find during the performance test that, it's too slow to insert/update mysql server. When my script running, on mysql server, use mysql 'show processlist;' command, I see only 2 or 3 active connections kept. Then,within my script, I use multiprocessing.pool(defaults to cpu core size, which is 4 for me) to do mysql insert/update operations. Finally, I see 4 or 5 connections kept from mysql server. So, seems db operation is bottleneck because I cannot use more connections yet. How to push the conncurrent connections up? I am sure script generating db tasks are fast enough. Thanks. Wesley -- 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/d/optout. -- 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/d/optout.
Re: [sqlalchemy] database design question
this is a vague and open ended question, is this a multi-tenancy application? does each client have their own database? or is that the question?if its multi tenancy, Id probably give each client a different database, why not? On Apr 4, 2014, at 12:58 PM, Richard Gerd Kuesters rich...@humantech.com.br wrote: hi all! i have a question about sqlalchemy and database design. i'm developing an app, where each client may receive an alert about the total space usage of their data (files and database), so, using postgresql, i can get them (data usage size) using a different tablespace, database or schema, if i'm not wrong. given these premises, which is the best way to design my database using sqlalchemy, having in mind that I need also to integrate the client databases to the core database? 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/d/optout. -- 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/d/optout.
Re: [sqlalchemy] sqlalchemy how to enlarge current using connections
The above procedure can be performed in a shorthand way by using the
execute()http://docs.sqlalchemy.org/en/rel_0_9/core/connections.html#sqlalchemy.engine.Engine.execute
method
of
Enginehttp://docs.sqlalchemy.org/en/rel_0_9/core/connections.html#sqlalchemy.engine.Engine
itself:
result = engine.execute(select username from users)for row in result:
print username:, row['username']
So, seems engine.execute is shorthand of connection.execute.
Here is my db apis :
#!/usr/bin/env python
#coding: utf-8
from sqlalchemy.sql.expression import text, bindparam
from sqlalchemy.sql import select,insert, delete, update
from sqlalchemy.schema import Table
from sqlalchemy.orm import sessionmaker,scoped_session
from db import dba_logger,metadata,engine#session
from datetime import datetime
from exctrace import exctrace
from sqlalchemy import and_
direct_engine = True
use_raw = False
#import gevent
#from gevent import monkey
#monkey.patch_all()
import multiprocessing
from db import tables
def tmp_dbwrite(tablename,**kwargs):
Used to insert exception info into database.
Params:
module : module name, indicating who raises the exception, e.g.
android,ios,psg,adpns,db .etc
type : exception type, 0 means service level while 1 is system
level.
message : exception description,length limit to 256 bytes
try:
#_table=Table(tablename, metadata, autoload=True)
_table = tables[tablename]
i=_table.insert().values(**kwargs)
if direct_engine:
engine.execute(i)
#gevent.spawn(engine.execute,i)
#gevent.sleep(0)
#gevent.joinall([gevent.spawn(engine.execute,i)])
else:
session = scoped_session(sessionmaker(bind=engine))
session.execute(i)
session.commit()
session.close()
except Exception,e:
#dba_logger.log(40,'Exception when dbwriter:%s' % str(e))
#dba_logger.log(20,'Exception detail:%s' % str(kwargs))
exctrace('db','1','Error happened when writing
db',dba_logger,'Exception when dbwriter:%s' % str(e),'Exception detail:%s'
% str(kwargs))
if not direct_engine:
session.rollback()
session.close()
def tmp_dbupdate(_table,whereclause,**kwargs):
Used to insert exception info into database.
Params:
module : module name, indicating who raises the exception, e.g.
android,ios,psg,adpns,db .etc
type : exception type, 0 means service level while 1 is system
level.
message : exception description,length limit to 256 bytes
try:
#_table=Table(tablename, metadata, autoload=True)
#_table = tables[tablename]
i=_table.update().values(**kwargs).where(whereclause)
if direct_engine:
engine.execute(i)
#gevent.spawn(engine.execute,i)
else:
session = scoped_session(sessionmaker(bind=engine))
session.execute(i)
session.commit()
session.close()
except Exception,e:
#dba_logger.log(40,'Exception when dbwriter:%s' % str(e))
#dba_logger.log(20,'Exception detail:%s' % str(kwargs))
exctrace('db','1','Error happened when updating
db',dba_logger,'Exception when dbupdate:%s' % str(e),'Exception detail:%s'
% str(kwargs))
if not direct_engine:
session.rollback()
session.close()
def dbquery(_table,whereclause):
try:
#_table=Table(tablename, metadata, autoload=True)
#_table = tables[tablename]
i=_table.select().where(whereclause)
if direct_engine:
res = engine.execute(i)
return res
else:
session = scoped_session(sessionmaker(bind=engine))
res = session.execute(i)
return res
session.close()
except Exception,e:
#dba_logger.log(40,'Exception when dbwriter:%s' % str(e))
#dba_logger.log(20,'Exception detail:%s' % str(kwargs))
exctrace('db','1','Error happened when querying
db',dba_logger,'Exception when dbquery:%s' % str(e),'Exception detail:%s' %
str(whereclause))
#session.rollback()
if not direct_engine:
session.close()
#res =
session.execute(connection_writer._table.select().where(connection_writer._table.c.app_key==self.app_key).where(connection_writer._table.c.device_token==self._devicetoken))
pool = multiprocessing.Pool(10)
def dbwrite(tablename,**kwargs):
pool.apply_async(tmp_dbwrite, (tablename,), kwargs)
def dbupdate(tablename,whereclause,**kwargs):
pool.apply_async(tmp_dbupdate, (tablename,whereclause), kwargs)
You can see in the bottom that I use multiprocessing to do dbwrite and
dbupdate which are called by other modules.
Based on the above code, mostly I only see 4-5 processlist from mysql
server side...
That's means, I haven't reached the bottleneck of mysql server
Re: [sqlalchemy] database design question
thanks mike. i know it's a vague question, but that just the point: i don't know the answer because it can be both :) let me clarify: assuming the application can be self hosted (in our ift), hence also multi-tenancy can be used for multiple clients. if not, the same codebase would run in the client's server space, in which some rules will does not comply - as a core client database will not be available, as an example. assuming that i have a lot of clients in our self hosted platform, i could enable multi-tenancy, but can i query or join between different databases? fyi, the server is postgres 9.x thanks a lot, richard. On 04/08/2014 10:57 AM, Michael Bayer wrote: this is a vague and open ended question, is this a multi-tenancy application? does each client have their own database? or is that the question?if its multi tenancy, Id probably give each client a different database, why not? On Apr 4, 2014, at 12:58 PM, Richard Gerd Kuesters rich...@humantech.com.br mailto:rich...@humantech.com.br wrote: hi all! i have a question about sqlalchemy and database design. i'm developing an app, where each client may receive an alert about the total space usage of their data (files and database), so, using postgresql, i can get them (data usage size) using a different tablespace, database or schema, if i'm not wrong. given these premises, which is the best way to design my database using sqlalchemy, having in mind that I need also to integrate the client databases to the core database? 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 mailto:sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com mailto:sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. -- 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 mailto:sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com mailto:sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. -- 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/d/optout.
Re: [sqlalchemy] database design question
On Apr 8, 2014, at 11:01 AM, Richard Gerd Kuesters rich...@humantech.com.br wrote: thanks mike. i know it's a vague question, but that just the point: i don't know the answer because it can be both :) let me clarify: assuming the application can be self hosted (in our ift), hence also multi-tenancy can be used for multiple clients. if not, the same codebase would run in the client's server space, in which some rules will does not comply - as a core client database will not be available, as an example. assuming that i have a lot of clients in our self hosted platform, i could enable multi-tenancy, but can i query or join between different databases? there might be some way that PG can refer to different databases like schemas, but if you need to join between different databases, that means the app isn't multi-tenancy, its one big app with shared data. I suspect that this join between different databases use case is more like, customers can access system A that has a set of common tables for everyone, or system B that is their database.A and B alone might be organized as separate databases to start with, so you don't need to join between different databases. I'd prefer to approach this in as much of a service oriented way as possible. System A and B would share data strictly at the level of two services sending messages to each other. fyi, the server is postgres 9.x thanks a lot, richard. On 04/08/2014 10:57 AM, Michael Bayer wrote: this is a vague and open ended question, is this a multi-tenancy application? does each client have their own database? or is that the question?if its multi tenancy, Id probably give each client a different database, why not? On Apr 4, 2014, at 12:58 PM, Richard Gerd Kuesters rich...@humantech.com.br wrote: hi all! i have a question about sqlalchemy and database design. i'm developing an app, where each client may receive an alert about the total space usage of their data (files and database), so, using postgresql, i can get them (data usage size) using a different tablespace, database or schema, if i'm not wrong. given these premises, which is the best way to design my database using sqlalchemy, having in mind that I need also to integrate the client databases to the core database? 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/d/optout. -- 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/d/optout. -- 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/d/optout. -- 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/d/optout.
Re: [sqlalchemy] sqlalchemy how to enlarge current using connections
There is no need to use a scoped_session() in a function that is just running a
single query. There’s no need to use a Session in a function that is not
running an ORM query - session.execute() and engine.execute() are equivalent.
Most of this code is unnecessary.
There is however a need to make a new Engine within each child process if you
are using multiprocessing, the code as is isn’t safe for multiple processes.
multiprocessing.Pool provides the “initializer” hook for this purpose.
A reasonable pattern for that would be:
engine = create_engine(url)
def setup_fork():
engine.dispose() # ensure all connections are gone
pool = multiprocessing.Pool(10, initializer=setup_fork)
Then as always, if the code is only opening 4 or 5 connections, that because
you’re only asking it to do 4 or 5 things at once. If you want it to use 100
connections, you need to have it doing 100 things at a time, which means your
multiprocessing.Pool would have to be of size 100 and you’d need to send at
least 100 tasks into it.
On Apr 8, 2014, at 10:21 AM, Ni Wesley nisp...@gmail.com wrote:
The above procedure can be performed in a shorthand way by using the
execute() method of Engine itself:
result = engine.execute(select username from users)
for row in result:
print username:, row['username']
So, seems engine.execute is shorthand of connection.execute.
Here is my db apis :
#!/usr/bin/env python
#coding: utf-8
from sqlalchemy.sql.expression import text, bindparam
from sqlalchemy.sql import select,insert, delete, update
from sqlalchemy.schema import Table
from sqlalchemy.orm import sessionmaker,scoped_session
from db import dba_logger,metadata,engine#session
from datetime import datetime
from exctrace import exctrace
from sqlalchemy import and_
direct_engine = True
use_raw = False
#import gevent
#from gevent import monkey
#monkey.patch_all()
import multiprocessing
from db import tables
def tmp_dbwrite(tablename,**kwargs):
Used to insert exception info into database.
Params:
module : module name, indicating who raises the exception, e.g.
android,ios,psg,adpns,db .etc
type : exception type, 0 means service level while 1 is system level.
message : exception description,length limit to 256 bytes
try:
#_table=Table(tablename, metadata, autoload=True)
_table = tables[tablename]
i=_table.insert().values(**kwargs)
if direct_engine:
engine.execute(i)
#gevent.spawn(engine.execute,i)
#gevent.sleep(0)
#gevent.joinall([gevent.spawn(engine.execute,i)])
else:
session = scoped_session(sessionmaker(bind=engine))
session.execute(i)
session.commit()
session.close()
except Exception,e:
#dba_logger.log(40,'Exception when dbwriter:%s' % str(e))
#dba_logger.log(20,'Exception detail:%s' % str(kwargs))
exctrace('db','1','Error happened when writing
db',dba_logger,'Exception when dbwriter:%s' % str(e),'Exception detail:%s' %
str(kwargs))
if not direct_engine:
session.rollback()
session.close()
def tmp_dbupdate(_table,whereclause,**kwargs):
Used to insert exception info into database.
Params:
module : module name, indicating who raises the exception, e.g.
android,ios,psg,adpns,db .etc
type : exception type, 0 means service level while 1 is system level.
message : exception description,length limit to 256 bytes
try:
#_table=Table(tablename, metadata, autoload=True)
#_table = tables[tablename]
i=_table.update().values(**kwargs).where(whereclause)
if direct_engine:
engine.execute(i)
#gevent.spawn(engine.execute,i)
else:
session = scoped_session(sessionmaker(bind=engine))
session.execute(i)
session.commit()
session.close()
except Exception,e:
#dba_logger.log(40,'Exception when dbwriter:%s' % str(e))
#dba_logger.log(20,'Exception detail:%s' % str(kwargs))
exctrace('db','1','Error happened when updating
db',dba_logger,'Exception when dbupdate:%s' % str(e),'Exception detail:%s' %
str(kwargs))
if not direct_engine:
session.rollback()
session.close()
def dbquery(_table,whereclause):
try:
#_table=Table(tablename, metadata, autoload=True)
#_table = tables[tablename]
i=_table.select().where(whereclause)
if direct_engine:
res = engine.execute(i)
return res
else:
session = scoped_session(sessionmaker(bind=engine))
res = session.execute(i)
return res
session.close()
except Exception,e:
Re: [sqlalchemy] sqlalchemy how to enlarge current using connections
在 2014年4月8日星期二UTC+8下午11时15分27秒，Michael Bayer写道：
There is no need to use a scoped_session() in a function that is just
running a single query. There’s no need to use a Session in a function
that is not running an ORM query - session.execute() and engine.execute()
are equivalent. Most of this code is unnecessary.
[wni] I see this, so , i use direct_engine= True to control all if
statement goes to engine.execute
There is however a need to make a new Engine within each child process if
you are using multiprocessing, the code as is isn’t safe for multiple
processes. multiprocessing.Pool provides the “initializer” hook for this
purpose.
A reasonable pattern for that would be:
engine = create_engine(url)
def setup_fork():
engine.dispose() # ensure all connections are gone
pool = multiprocessing.Pool(10, initializer=setup_fork)
Then as always, if the code is only opening 4 or 5 connections, that
because you’re only asking it to do 4 or 5 things at once. If you want it
to use 100 connections, you need to have it doing 100 things at a time,
which means your multiprocessing.Pool would have to be of size 100 and
you’d need to send at least 100 tasks into it.
[wni] I will try creating new engine for each sub process later :-)
On Apr 8, 2014, at 10:21 AM, Ni Wesley nis...@gmail.com javascript:
wrote:
The above procedure can be performed in a shorthand way by using the
execute()http://docs.sqlalchemy.org/en/rel_0_9/core/connections.html#sqlalchemy.engine.Engine.execute
method
of
Enginehttp://docs.sqlalchemy.org/en/rel_0_9/core/connections.html#sqlalchemy.engine.Engine
itself:
result = engine.execute(select username from users)for row in result:
print username:, row['username']
So, seems engine.execute is shorthand of connection.execute.
Here is my db apis :
#!/usr/bin/env python
#coding: utf-8
from sqlalchemy.sql.expression import text, bindparam
from sqlalchemy.sql import select,insert, delete, update
from sqlalchemy.schema import Table
from sqlalchemy.orm import sessionmaker,scoped_session
from db import dba_logger,metadata,engine#session
from datetime import datetime
from exctrace import exctrace
from sqlalchemy import and_
direct_engine = True
use_raw = False
#import gevent
#from gevent import monkey
#monkey.patch_all()
import multiprocessing
from db import tables
def tmp_dbwrite(tablename,**kwargs):
Used to insert exception info into database.
Params:
module : module name, indicating who raises the exception, e.g.
android,ios,psg,adpns,db .etc
type : exception type, 0 means service level while 1 is system
level.
message : exception description,length limit to 256 bytes
try:
#_table=Table(tablename, metadata, autoload=True)
_table = tables[tablename]
i=_table.insert().values(**kwargs)
if direct_engine:
engine.execute(i)
#gevent.spawn(engine.execute,i)
#gevent.sleep(0)
#gevent.joinall([gevent.spawn(engine.execute,i)])
else:
session = scoped_session(sessionmaker(bind=engine))
session.execute(i)
session.commit()
session.close()
except Exception,e:
#dba_logger.log(40,'Exception when dbwriter:%s' % str(e))
#dba_logger.log(20,'Exception detail:%s' % str(kwargs))
exctrace('db','1','Error happened when writing
db',dba_logger,'Exception when dbwriter:%s' % str(e),'Exception detail:%s'
% str(kwargs))
if not direct_engine:
session.rollback()
session.close()
def tmp_dbupdate(_table,whereclause,**kwargs):
Used to insert exception info into database.
Params:
module : module name, indicating who raises the exception, e.g.
android,ios,psg,adpns,db .etc
type : exception type, 0 means service level while 1 is system
level.
message : exception description,length limit to 256 bytes
try:
#_table=Table(tablename, metadata, autoload=True)
#_table = tables[tablename]
i=_table.update().values(**kwargs).where(whereclause)
if direct_engine:
engine.execute(i)
#gevent.spawn(engine.execute,i)
else:
session = scoped_session(sessionmaker(bind=engine))
session.execute(i)
session.commit()
session.close()
except Exception,e:
#dba_logger.log(40,'Exception when dbwriter:%s' % str(e))
#dba_logger.log(20,'Exception detail:%s' % str(kwargs))
exctrace('db','1','Error happened when updating
db',dba_logger,'Exception when dbupdate:%s' % str(e),'Exception detail:%s'
% str(kwargs))
if not direct_engine:
session.rollback()
session.close()
def dbquery(_table,whereclause):
try:
Re: [sqlalchemy] database design question
thanks again mike. well, most of our software is already message driven, but we still need to group information if we want to; ex: search from all our clients databases for some value in a model that's shared between them. thanks a lot, richard. On 04/08/2014 12:07 PM, Michael Bayer wrote: On Apr 8, 2014, at 11:01 AM, Richard Gerd Kuesters rich...@humantech.com.br mailto:rich...@humantech.com.br wrote: thanks mike. i know it's a vague question, but that just the point: i don't know the answer because it can be both :) let me clarify: assuming the application can be self hosted (in our ift), hence also multi-tenancy can be used for multiple clients. if not, the same codebase would run in the client's server space, in which some rules will does not comply - as a core client database will not be available, as an example. assuming that i have a lot of clients in our self hosted platform, i could enable multi-tenancy, but can i query or join between different databases? there might be some way that PG can refer to different databases like schemas, but if you need to join between different databases, that means the app isn't multi-tenancy, its one big app with shared data. I suspect that this join between different databases use case is more like, customers can access system A that has a set of common tables for everyone, or system B that is their database.A and B alone might be organized as separate databases to start with, so you don't need to join between different databases. I'd prefer to approach this in as much of a service oriented way as possible. System A and B would share data strictly at the level of two services sending messages to each other. fyi, the server is postgres 9.x thanks a lot, richard. On 04/08/2014 10:57 AM, Michael Bayer wrote: this is a vague and open ended question, is this a multi-tenancy application? does each client have their own database? or is that the question?if its multi tenancy, Id probably give each client a different database, why not? On Apr 4, 2014, at 12:58 PM, Richard Gerd Kuesters rich...@humantech.com.br mailto:rich...@humantech.com.br wrote: hi all! i have a question about sqlalchemy and database design. i'm developing an app, where each client may receive an alert about the total space usage of their data (files and database), so, using postgresql, i can get them (data usage size) using a different tablespace, database or schema, if i'm not wrong. given these premises, which is the best way to design my database using sqlalchemy, having in mind that I need also to integrate the client databases to the core database? 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 mailto:sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com mailto:sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. -- 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 mailto:sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com mailto:sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. -- 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 mailto:sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com mailto:sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. -- 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 mailto:sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com mailto:sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. -- 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
[sqlalchemy] create relationship after mapping
hi all! another question: i'm mapping some classes dinamically, using the mapper() function. i would like to create relationships, but not in the properties kwarg of mapper, but after: *mapper(cls, my_select)** **...** **# detects any relationship** **...** **setattr(cls, 'my_attr'**, relationship(othercls, ...))* is that possible? 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/d/optout.
Re: [sqlalchemy] ORM Different SELECT/INSERT Tables
On Monday, April 7, 2014 5:11:48 PM UTC-4, Michael Bayer wrote:
On Apr 7, 2014, at 2:46 PM, Michael Weylandt
michael@gmail.comjavascript:
wrote:
Since the logic creating the view is a little hairy, I'd like to avoid
replicating it in Python and just
have SQLAlchemy redirect all SELECT statements to the view instead of
the underlying table.
the with_polymorphic setting of mapper will allow this:
class A(Base):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)
s1 = select([A.__table__])
A.__mapper__.with_polymorphic = ('*', s1)
Hi Mike,
Thanks for the reply -- looking at the docs, with_polymorphic sounds like
just what I need. SQLAlchemy really is an amazing system.
I've almost got it working: I can send selects to the view _and_ the
original table, but not _just_ to the view.
Reading the docs, I think I'm stuck at the point where select()'s from_obj
can only add elements to the FROM instead of replacing them. [1]
Minimal example:
from sqlalchemy import create_engine, select
from sqlalchemy.orm import sessionmaker
from sqlalchemy import Column, Integer, String, DateTime
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import event, DDL
from datetime import datetime
Base = declarative_base()
class Foo(Base):
__tablename__ = 'FooTable'
__viewname__ = 'FooView'
id = Column(Integer, primary_key=True)
name = Column(String)
description = Column(String)
time = Column(DateTime, default=datetime.now)
ddl_str = CREATE VIEW %s AS
SELECT * FROM %s GROUP BY name HAVING time = max(time)
event.listen(Foo.__table__, after_create,
DDL(ddl_str % (Foo.__viewname__, Foo.__tablename__)))
## You didn't have a from_obj here so this may be wrong, but there's no
re-direct without one
s1 = select([Foo.__table__], from_obj = Foo.__viewname__)
Foo.__mapper__.with_polymorphic = (*, s1)
engine = create_engine(sqlite:///:memory:, echo=True)
Base.metadata.create_all(engine)
session = sessionmaker(bind=engine)()
session.add(Foo(name=Foo 1, description=The First Foo))
session.add(Foo(name=Foo 2, description=The Second Foo))
session.add(Foo(name=Foo 3, description=The Third Foo))
session.commit()
session.add(Foo(name=Foo 2, description=The Second Foo (ed)))
session.commit()
print session.query(Foo.description).all()
[1]
http://docs.sqlalchemy.org/en/rel_0_8/core/selectable.html#sqlalchemy.sql.expression.select.params.from_obj
--
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/d/optout.
Re: [sqlalchemy] create relationship after mapping
stupid me. *mp = **mapper(cls, my_select)** **...** **# detects any relationship** **... mp.add_property(my_attr, relationship(othercls, ...)) *** sorry for thinking out loud (into the group) ... best regards, richard. On 04/08/2014 04:31 PM, Richard Gerd Kuesters wrote: hi all! another question: i'm mapping some classes dinamically, using the mapper() function. i would like to create relationships, but not in the properties kwarg of mapper, but after: *mapper(cls, my_select)** **...** **# detects any relationship** **...** **setattr(cls, 'my_attr'**, relationship(othercls, ...))* is that possible? 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 mailto:sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com mailto:sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. -- 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/d/optout.
[sqlalchemy] Re: SQLAlchemy Netezza Dialect
I created an actual repo for this, so if anyone finds bugs, or wants to submit patches: https://github.com/deontologician/netezza_sqlalchemy On Tue, Mar 4, 2014 at 7:18 PM, Josh Kuhn deontologic...@gmail.com wrote: Since there wasn't one out there already, I took a shot at it. It's really rough, and mostly cribs its implementation from the Postgres dialect (since Netezza is a postgresql derivative). https://gist.github.com/deontologician/9358574 It handles a couple of quirks I ran across, like adding distribute on clauses to create table statements, and ensuring limit clauses don't get sql params (since it doesn't like that). With pandas incorporating SQLA, I figure this might be useful to some people out there. --Josh -- 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/d/optout.
[sqlalchemy] Re: database design question
I had a similar situation years ago. We had software that helped automate online promotions for music releases. Everyone insisted on keeping their data separate; forcing different databases was required by contract and we were strong-armed into it. Today, things would be different. I ended up having everything in a different client-specific database for each promotion. The company stuff , like our global email/user directory, was handled in a company database. Originally, I used SQL users and permissions that would give read/write to the company and a single client. Eventually, I moved all of the company stuff into a centralized service -- multiple applications would talk to that service instead of the database. The reason for all this trickery was a mix of permissions/security, DB administration ( being able to distribute different clients onto different machines ), and reporting/analysis usage that was much simpler with a per-database option. there were a few other things I can't remember. it was still f*** pain, and we ran into performance issues as we couldn't recycle database connections as aggressively as i wanted. we sometimes had legacy projects that could tie up db connections for newer projects that were more popular. Given the chance, I would never touch something like that again. I just want to bring this up, because database connections do end up being an issue. if you've got a single db connection string, you can really be aggressive with your connections. If you have multiple db connection strings, you have to think about planning for database connectivity. -- 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/d/optout.
