[sqlalchemy] Re: Reconnect while loosing Mysql connection
Too obvious or too complicated? On Monday, June 4, 2012 7:10:14 PM UTC+4, Pavel Skvazh wrote: Hi, everyone! Due to an obviously lacking skills of an admin, the connection with Mysql sever is really spotty. And I keep getting OperationalError 'Can't connect to MySQL server' error every once in a while. What's the best practice approach to handle this kind of errors. Logical way would be to try to reconnect N times every N seconds and if it fails - throw this error. Doing it manually doesn't seem appropriate and natural. Code won't be pretty either. Connection pool looks like the way to go. i.e. try to get another connection if this one dies for some reason. It doesn't look like it's the way it is. Any help would be highly appreciated. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/QKPokWMzPbkJ. 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.
[sqlalchemy] Reconnect while loosing Mysql connection
Hi, everyone! Due to an obviously lacking skills of an admin, the connection with Mysql sever is really spotty. And I keep getting OperationalError 'Can't connect to MySQL server' error every once in a while. What's the best practice approach to handle this kind of errors. Logical way would be to try to reconnect N times every N seconds and if it fails - throw this error. Doing it manually doesn't seem appropriate and natural. Code won't be pretty either. Connection pool looks like the way to go. i.e. try to get another connection if this one dies for some reason. It doesn't look like it's the way it is. Any help would be highly appreciated. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/uGlVtC3HXJIJ. 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.
[sqlalchemy] Mysql dialects comparison
Hi, everybody! Looks like mysql_python is getting some competition lately. Not that there's a lot to complain about it, but having options never heart anybody. I'd be nice to get a grasp of what's the experience people had with official connector and the our_sql (pure python sounds like not a good way to go when we're talking speed, even SA is going C extentions). If some of you guys have made any test/comparison between them, I'd be just great. Speed of course being the main factor here. Wiil appreciate any feedback, especially from Mike, since he's the one to be dealing with them first hand. (Congrats on the new release by the way! Moving to 0.6 shortly), -- 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: Textual SQL
Thank you so much guys!
Now I figured it out. Hope this will help someone else as well :)
On Feb 2, 9:53 pm, MikeCo mconl...@gmail.com wrote:
Commit behavior depends on how you configure the session's autocommit
property. Follow the log messages in this little test.
from sqlalchemy import MetaData, Table, Column, String
from sqlalchemy.orm import sessionmaker
meta = MetaData('sqlite:///')
Session = sessionmaker(bind=meta.bind)
t = Table('something',meta,
Column('stuff',String)
)
print '+ create_all()'
meta.create_all()
meta.bind.echo=True
session1 = Session(autocommit=False) # this is default behavior
print '+ execute() will not include commit'
session1.execute(insert into something(stuff) values('some stuff'))
print '+ commit() need to do it yourself'
session1.commit()
session2 = Session(autocommit=True) # commit is configurable
print '+ execute() will include commit'
session2.execute(insert into something(stuff) values('some more
stuff'))
On Feb 2, 1:15 pm, Bob Farrell robertanthonyfarr...@googlemail.com
wrote:
On Mon, Feb 02, 2009 at 09:56:15AM -0800, Pavel Skvazh wrote:
Session.execute('INSERT INTO SOMETHING SOMETHING / DELETE/ UPDATE')
Do I have to call Session.commit() after this or it's already taken
care of? In other words does the literal sql statements follow the
session transaction rules or they act on there own?
sess.execute() will execute whatever you pass it immediately.
And since this works and worked for me for a long time now, what's the
benefit of from sqlalchemy.sql import text that I noticed in the docs
lately?
Using text() creates a ClauseElement that you can whack together with other
constructs. See the docs here for more
info:http://www.sqlalchemy.org/docs/05/sqlexpression.html#using-text
Thanks!
--
--
Bob Farrell
pH, an Experian Companywww.phgroup.com
Office Line: 020 7598 0310
Fax: 020 7598 0311
--
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
[sqlalchemy] Textual SQL
Session.execute('INSERT INTO SOMETHING SOMETHING / DELETE/ UPDATE')
Do I have to call Session.commit() after this or it's already taken
care of? In other words does the literal sql statements follow the
session transaction rules or they act on there own?
And since this works and worked for me for a long time now, what's the
benefit of from sqlalchemy.sql import text that I noticed in the docs
lately?
Thanks!
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
[sqlalchemy] Re: how to print a constructed query with it's parameters?
Thanks for the solution!
But i get the warning for this query:
SELECT address.name AS address_lang, user.name AS user_name
FROM addresses
LEFT OUTER JOIN user ON user.id = address.user_id
Throws
sqlalchemy-0.5.0rc2dev_r5150-py2.5.egg\sqlalchemy\sql\expression.py:
1616: SAWarning: Column 'name' on table 'Select object' being replaced
by another column with the same key. Consider use_labels for select()
statements.
self[column.key] = column
On Oct 15, 5:09 pm, Ants Aasma [EMAIL PROTECTED] wrote:
This seems to come up often. I took a few minutes and threw together a
semi-robust way to do this on 0.5 series. I posted it under usage
recipes in the wiki:http://www.sqlalchemy.org/trac/wiki/DebugInlineParams
It has some flaws, but should be somewhat helpful for debugging.
Ants
On Oct 15, 2:42 pm, alex bodnaru [EMAIL PROTECTED] wrote:
hi friends,
i have a lot to learn from both approaches, but i have sadly appeared too
lazy.
there will be no problem to imagine what the sql will be, only by
looking at the
template statement (with ?'s) and at the list of parameters.
since the template is available to print (probably by __str__), i'd
onlu ask where
the bindparams list is. eventual quotes and escapes may be imagined by
the types of
the columns.
thanks in advance,
alex
On Wed, Oct 15, 2008 at 12:54, [EMAIL PROTECTED] wrote:
i have another approach, which may or may not serve you.
All those '?' are bindparams, and one can eventualy get them printed
with their names - and put names where there aren't.
that's what i needed, i guess replacing names with values would be
easy job.
the code is part of tests/convertertest.py of sqlalchemyAggregator,
http://dev.gafol.net/t/aggregator/
or
http://dbcook.svn.sourceforge.net/viewvc/dbcook/trunk/dbcook/misc/agg...
class T_mark( unittest.TestCase):
...
def setUp( self):
self.m = MetaData()
#hack for better visibility
def bp( self,bindparam):
if bindparam.value is not None:
return 'const('+repr(bindparam.value)+')'
k = bindparam.key
if k.startswith( Converter._pfx): #my own bindparams
k = k[ len( Converter._pfx):]
return 'BindParam('+k+')'
self.old_bp = DefaultCompiler._truncate_bindparam
DefaultCompiler._truncate_bindparam = bp
def tearDown( self):
DefaultCompiler._truncate_bindparam = self.old_bp
...
str(expression) then does things like
:const(True) AND :BindParam(oid) = movies.id
tags.tabl = :const('movies') AND tags.oid = :BindParam(oid)
there's some more stuff going on there around compatibility with SA
0.3--0.5, but that's core.
ciao
svil
On Wednesday 15 October 2008 13:33:46 King Simon-NFHD78 wrote:
-Original Message-
From: sqlalchemy@googlegroups.com
[mailto:[EMAIL PROTECTED] On Behalf Of alex bodnaru
Sent: 15 October 2008 11:00
To: SQLAlchemy
Subject: [sqlalchemy] how to print a constructed query with
it's parameters?
hello friends,
in order to debug my code, i wish to print my query sql.
it's in the fashion of
query =
table.query().filter(table.code='XL').filter(table.name.like('
%'+q+'%')
with unicode parameters.
by just printing query, i get the select with ? parameters, but
not the additional parameters list, that contains ['XL',
%q-value%]. since it doesn't presently work ok, i'd like to print
the list as well.
thanks in advance,
alex
This question comes up a lot. For example, see
http://groups.google.com/group/sqlalchemy/browse_thread/thread/a060
2ede8 18f55c7
Firstly, if you use echo=True in your call to create_engine, all
SQL will be printed to stdout. The parameters will be displayed as
a list AFTER the SQL is printed.
Eg. (fromhttp://www.sqlalchemy.org/docs/05/ormtutorial.html)
BEGIN
INSERT INTO users (name, fullname, password) VALUES (?, ?, ?)
['ed', 'Ed Jones', 'edspassword']
SELECT users.id AS users_id, users.name AS users_name,
users.fullname AS users_fullname, users.password AS users_password
FROM users
WHERE users.name = ?
LIMIT 1 OFFSET 0
['ed']
You can control the logging more finely using the logging module -
see
http://www.sqlalchemy.org/docs/05/dbengine.html#dbengine_logging
for more details.
The problem is that SQLAlchemy doesn't ever replace those '?'
characters with the actual parameter values. Those strings are
passed directly to the DBAPI driver, along with the list of
parameter values. It is then up to the DBAPI driver how it passes
the query to the database. (This is why SQLAlchemy is fairly safe
from SQL Injection attacks).
Hope that helps,
Simon
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
[sqlalchemy] Storing DB engine in session
I've been struggling with this problem for a long time now and will
appreciate any help.
I'm using Oracle and pass users/passwords to access the database.
uri = 'oracle://' + config.get('sqlalchemy_conf.webuser', '') + ':' +
config.get('sqlalchemy_conf.webpassword', '') + '@' + bd_location
engine = create_engine(uri)
meta.bind = engine
Here's how it should work: every time the user logs in, SA creates a
new engine with his log/pass or uses the one that was already opened
and then uses it.
How do i keep the engine (or any other connection identifier) in the
Pylons session (can't think of any other way), so user can pass it to
the create_session?
I was trying to assign engine variable to the Pylons session, but it
fails.
What happens now is the new user logs in an all the users start using
the engine he created.
I'm pretty sure the way I'm doing things (unique log/pass for
connection) is fairly uncommon, but still that's the way I have to do
it for database security reasons. And my guess will be I'm not alone
with such an issue.
--~--~-~--~~~---~--~~
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: Storing DB engine in session
Thanks a lot, Paul. Great point, works just great. That pretty much solved the issue. log_dic log_typle = (session['login'], session['password']) if not log_dic.has_key((log_typle)): engine = create_engine(uri) log_dic[log_typle] = engine else: engine = log_dic[(session['login'], session['password'])] Does this create new connection with the same name or start using the existing one? I'm almost 100% that latter, just making sure. Right now I'm binding meta to the engine, which obviosly makes all the tables work with the last engine plugged in What'll be your advice for this matter. The most obvious way i see is using Contextual Session. This way I'll have to add Session = scoped_session(sessionmaker(autoflush=True, transactional=True, bind=current_user_engine)) But I'll have to rewrite all the SQL statements so that they'll start running using sessions. select([func.count(*)], from_obj=[sell_table]).sess.execute() What'll be your best practice advice on this one? On Oct 17, 3:35 pm, Paul Johnston [EMAIL PROTECTED] wrote: Hi, Here's how it should work: every time the user logs in, SA creates a new engine with his log/pass or uses the one that was already opened and then uses it. Unless something has changed recently, this pattern is not particularly supported. Still, you could probably get it working with bound sessions. If the engine doesn't exist in the users, session, create the engine and save in the session. Don't know why the Pylons session save was failing, perhaps it doesn't allow arbitrary Python objects. You could keep your own dictionary, keyed on (username, password) tuples and avoid sessions altogether. Paul --~--~-~--~~~---~--~~ 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] Oracle sequences issue
I'm trying to make sequences in the Oracle database.
Here's my model
def init():
#conf = paste.deploy.CONFIG
#uri = conf['sqlalchemy.dburi']
if 'login' in session:
uri=1
else:
conf = paste.deploy.CONFIG
uri = conf['sqlalchemy.dburi'] /// Admin rights here
engine = create_engine(uri, convert_unicode=True)
# meta.bind = engine
engine.echo = True
meta.connect(engine)
application_sequence = Sequence('Application_id_seq', optional=True)
application_table = Table ( 'Application', meta,
Column('ID_Application', Integer, application_sequence,
primary_key=True),
Column('ID_Seller', Integer, nullable=False),
Column('ID_Lot', Integer, nullable=False),
Column('ID_Sell', Integer, nullable=False),
Column('Text_Application',String(2048)),
Column('Date_Start_Application', DateTime,
default=func.current_timestamp()),
Column('ID_Status', Integer),
Column('Date_Change_Application', DateTime),
Column('Date_Finish_Application', DateTime),
ForeignKeyConstraint(['ID_Sell', 'ID_Lot'], ['data.Lot.ID_Sell',
'data.Lot.ID_Lot'], ondelete=CASCADE),
schema='data'
)
Websetup is done like this
def setup_config(command, filename, section, vars):
conf = paste.deploy.appconfig('config:' + filename)
conf.update(dict(app_conf=conf.local_conf,
global_conf=conf.global_conf))
paste.deploy.CONFIG.push_process_config(conf)
uri = conf['sqlalchemy.dburi_data'] // Here i log in as the Data
scheme
engine = create_engine(uri)
print Connecting to database %s ... % uri
model.meta.connect(engine)
# Add some basic values into the table.
//
Everything works fine during websetup. It adds the values in the
table, since i assume it can see the Sequence.
But during the work with the model, since it logs in as different
Schema, it cann't see the Sequence.
After that i changed it to
application_doc_sequence = Sequence('data.APPLICATION_DOC_ID_SEQ',
optional=True)
the model can see it just well, works great. But when it comed to
websetup it fails.
After clearing the database by hand, i decided to take a look at how
websetup will handle it from the blank database.
Now it works, but when it comes to websetup it gives me this:
name is already used by an existing object
As it turns out it creates a sequence named
'data.APPLICATION_DOC_ID_SEQ', which is not exactly right i guess.
My assumption would be that they treat this string differently. Model
can see the schema to look for, but websetup takes literally as a
string but on the other hand refuses to delete it during drop_all.
Probably that's a well known issue and it's fixed in 0.4 or maybe it's
me (which is in fact far more likely since i'm new to alchemy).
Anyway, i'd be delighted if you guys will point it out to me.
--~--~-~--~~~---~--~~
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] Oracle sequences issue
I'm trying to make sequences in the Oracle database.
Here's my model
def init():
#conf = paste.deploy.CONFIG
#uri = conf['sqlalchemy.dburi']
if 'login' in session:
uri=1
else:
conf = paste.deploy.CONFIG
uri = conf['sqlalchemy.dburi'] /// Admin rights here
engine = create_engine(uri, convert_unicode=True)
# meta.bind = engine
engine.echo = True
meta.connect(engine)
application_sequence = Sequence('Application_id_seq', optional=True)
application_table = Table ( 'Application', meta,
Column('ID_Application', Integer, application_sequence,
primary_key=True),
Column('ID_Seller', Integer, nullable=False),
Column('ID_Lot', Integer, nullable=False),
Column('ID_Sell', Integer, nullable=False),
Column('Text_Application',String(2048)),
Column('Date_Start_Application', DateTime,
default=func.current_timestamp()),
Column('ID_Status', Integer),
Column('Date_Change_Application', DateTime),
Column('Date_Finish_Application', DateTime),
ForeignKeyConstraint(['ID_Sell', 'ID_Lot'], ['data.Lot.ID_Sell',
'data.Lot.ID_Lot'], ondelete=CASCADE),
schema='data'
)
Websetup is done like this
def setup_config(command, filename, section, vars):
conf = paste.deploy.appconfig('config:' + filename)
conf.update(dict(app_conf=conf.local_conf,
global_conf=conf.global_conf))
paste.deploy.CONFIG.push_process_config(conf)
uri = conf['sqlalchemy.dburi_data'] // Here i log in as the Data
scheme
engine = create_engine(uri)
print Connecting to database %s ... % uri
model.meta.connect(engine)
# Add some basic values into the table.
//
Everything works fine during websetup. It adds the values in the
table, since i assume it can see the Sequence.
But during the work with the model, since it logs in as different
Schema, it cann't see the Sequence.
After that i changed it to
application_doc_sequence = Sequence('data.APPLICATION_DOC_ID_SEQ',
optional=True)
the model can see it just well, works great. But when it comed to
websetup it fails.
After clearing the database by hand, i decided to take a look at how
websetup will handle it from the blank database.
Now it works, but when it comes to websetup it gives me this:
name is already used by an existing object
As it turns out it creates a sequence named
'data.APPLICATION_DOC_ID_SEQ', which is not exactly right i guess.
My assumption would be that they treat this string differently. Model
can see the schema to look for, but websetup takes literally as a
string but on the other hand refuses to delete it during drop_all.
Probably that's a well known issue and it's fixed in 0.4 or maybe it's
me (which is in fact far more likely since i'm new to alchemy).
Anyway, i'd be delighted if you guys will point it out to me.
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
