[sqlalchemy] SQLAlchemy + mod_python best practices
Hello list,
I've been having many stability problems with my first SQLAlchemy +
mod_python application. By now I'm thinking it's all my fault and I'm
not using these tools the way they are intended to be used. So I would
like to know how do others organize their code to get something that works.
I got the following advice from a previous post by Michael Bayer:
mappers are intended to be create-once-per-class objects (usually at
the module level), whereas sessions are usually instantiated
once-per-request, queries once-per-operation
What I do is to create an engine in a separate module that is imported
at the beginning of my app. This module, which I call dbinit.py,
resembles this:
db = sa.create_engine('mysql://user:[EMAIL PROTECTED]/mydb',
use_threadlocal=True, echo_pool=True)
metadata = sa.BoundMetaData(db)
users_table = sa.Table('Users', metadata, autoload=True)
class User(object):
pass
sa.orm.clear_mappers()
sa.mapper(User, users_table)
That's it. I import it once at the beginning of the app and just assume
SQLALchemy keeps a pool of connections for me and whenever I use the db
variable to create sessions it'll be available and do the right thing. I
don't use any connect/disconnect methods or anything like that. In fact
a typical method in my mod_python site looks just like this:
import logging
import sqlalchemy as sa
from dbinit import *
def justatest(req):
dbsession = sa.create_session(bind_to=db)
query = dbsession.query(User)
requested_uid = sanitize(req.form['uid'])
try:
user = query.get_by(Uid=requested_uid)
except sa.exceptions.SQLError, details:
logging.debug(got this error: %s % details)
dbsession.close()
return Yet another crash
else:
dbsession.close()
return show_message(req, Looks good)
I have many methods that follow that same structure. And the whole thing
works rather well while I'm the only one using it... but in the real
world I get InterfaceError exceptions all the time, which others in this
list identified as threading issues.
Michael Bayer also added that I should...
ensure that you arent sharing connections or sessions between concurrently
executing threads.
I believe my code above is free from these errors... Right? I leave
SQLAlchemy the task of handling connections and I create and close
sessions with each http request.
So that's it. I don't know if my app uses a weird structure that brings
SQLAlchemy to its knees and I should be doing something else. How do you
people organize your code in a simple mod_python + SQLAlchemy
application? Is my code just plain odd? Any particular hints? Does
anyone know of a solid open source mod_python + SQLALchemy application I
could use as an example?
Best regards,
--~--~-~--~~~---~--~~
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: SQLAlchemy + mod_python best practices
Thanks for the reply Michael, d make sure of are that youre on 0.3.7 of SA, youre on a very recent version of MySQLDB. I have SQLAlchemy 0.3.7 and MySQL 5.0.27-standard he use_threadlocal is probably not needed on your engine() and its worth trying without it since its not as widely tested. I added this recently in yet another attempt to bring stability to my app. But the errors appear with and without it. id also ensure youre on the very latest mod_python I have an older mod_python, I'll try to update it and see what happens. it might not be a bad test to adapt your applciation to a WSGI interface and see if you still have the threading issues sans apache. Uh oh, looks like a lot of work... but I guess I'll eventually have to. 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Google Email Verification
Hi, I'm randomly getting InterfaceError exceptions in my app while doing a simple query.get_by(Uid=user_id) I don't even know where to start to look about this, I found nothing in the docs. Any pointers to the direction I should take? 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Randomly getting InterfaceError exceptions
Produce the simplest program you can that causes the error, then post
both the code and full exception here.
Paul
Sorry about the wrong subject in the last post. Google groups are quite
annoying.
This is the minimal program that causes the error:
db = sa.create_engine('mysql://myuser:[EMAIL PROTECTED]/mydatabase')
metadata = sa.BoundMetaData(db)
def user_mapper():
'''Create the object/relational link for users'''
users_table = sa.Table('Users', metadata, autoload=True)
sa.orm.clear_mappers()
sa.mapper(User, users_table)
session = sa.create_session(bind_to=db)
query = session.query(User)
return session, query
def authenticate(req, username, password):
'''Grant access if password matches the one stored in the db'''
dbsession, query = user_mapper()
try:
user = query.get_by(Uid=username)
except sa.exceptions.SQLError, details:
return Database error: %s % details
else:
if user == None:
return print_error(req, That user does not exist!)
The error I get is:
Database error: (InterfaceError) (0, '') u'SELECT `Users`.`Uid` AS `Users_Uid`,
`Users`.`Country` AS `Users_Country`, `Users`.`LName` AS `Users_LName`,
`Users`.`Credits` AS `Users_Credits`, `Users`.`Pwd` AS `Users_Pwd`,
`Users`.`PhoneNumber` AS `Users_PhoneNumber`, `Users`.`FName` AS `Users_FName`,
`Users`.`MoneyBalance` AS `Users_MoneyBalance`, `Users`.`Email` AS
`Users_Email` \nFROM `Users` \nWHERE `Users`.`Uid` = %s ORDER BY `Users`.`Uid`
\n LIMIT 1' ['testuser']
Notice this sometimes works and sometime it doesn't.
This is under mod_python with SQLAlchemy 0.3.7 in case it matters.
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Randomly getting InterfaceError exceptions
Thanks for the reply. You are probably right about those things don't belonging together, it certainly makes a lot of sense now you mention it. I'll reorganize my code better and see if the errors go away. 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
