[sqlalchemy] Re: Mapper extensions in declerative.
Hi Kyle, Thanks for the really thorough response, it seems you know what you're on about :-) I agree with you that it would likely be a foolish decision to rely on undocumented behaviour, this will likely come back to bite me at some point in the future. I'm going to take all these ideas away with me now, I've also upgraded to version 0.5 this morning to get the best out of the ORM, I had been holding off as it was only in the experimental Debian repos but I'm sure its stable enough, I've seen a few recent posts about it being almost production ready. I'm going to heavily assess the way I'm currently using my session, it is a complete and utter mess at the moment and I'm quite sure that it needs to be refactored, its only a small application so won't take me long. Once I'm comfortable with the way in which that is behaving I'll start to look at implementing these hooks for the file saves and see how it gets along. It's quite clear in my mind now and seems like a fairly safe approach to it, I certainly can't see any downsides to it just yet anyway. I'll keep you posted over the next few days as to my progress and we can then perhaps review the approach as needed. Thanks again mate, I really appreciate you taking the time to be so thorough. Heston --~--~-~--~~~---~--~~ 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] Question about when to use connection pooling
Hi, I'm currently developing a web application using TurboGears which makes use of sqlalchemy (0.4.6). Turbogears exposes a global 'session' object, which is initialised as scoped_session(sqlalchemy.orm.create_session()). E.g. each thread gets its own session object. Other web-accessible applications need to make use of this very same session object as well, and they can receive many concurrent connections. Each such connection needs a session object. As I can see, the turbogears way of creating the session as I described above does not use a connection pool. First of all, am I right to assume this? The default way is to create a new connection to the database whenever a session object is instantiated this way? Since it seems to me that using a connection pool would be a good thing performance-wise, given that many sessions are needed, I changed the turbogears code to make use of the QueuePool class for connection pooling. This, however, has the (undesired) side effect that connections drawn from the pool at the start of each web application's request contain cached database information. In my application, I have to explicitly issue a close() at the start of each request to delete the cache and any lingering transaction states etc. It's not trivial to issue a close() at the *end* of each request, hence at the start. Now, of course, I can hack this into the turbogears session initialisation as well. But I wonder, is using a connection pool still a good idea in this case? Is fetching a session from a pool and always closing it at the start of each application request more sane than simply opening a new connection to the database? Or is there another option that I am not currently seeing? Best regards, Bram --~--~-~--~~~---~--~~ 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: Question about when to use connection pooling
As I can see, the turbogears way of creating the session as I described above does not use a connection pool. First of all, am I right to assume this? The default way is to create a new connection to the database whenever a session object is instantiated this way? the Session usually binds to an Engine, and when it needs to perform work witha connection and/or open a transaction, it consults the Engine for a connection. This uses the connection pool in all cases. SQLA routes all connection access through the pool (which can be configured to not actually be a pool, but this is very rare). Ok, thanks for clearing that up. Since it seems to me that using a connection pool would be a good thing performance-wise, given that many sessions are needed, I changed the turbogears code to make use of the QueuePool class for connection pooling. this should not be necessary. QueuePool is already being used unless TG does some very weird stuff (which Im fairly certain they dont). I couldn't tell this from the docs, so I assumed another default. I do notice a difference in behaviour however, when I explicitly specify QueuePool as poolclass argument to the create_engine function (that is the hack on the turbogears initialisation). Without specifying the poolclass, the session object never has state at the start of an application request. With the poolclass, I can clearly see that it caches database objects, e.g. after a session.query() on an object, it will not reflect changes I made in the database if it had already queried the very same object in a previous application request. With the poolclass parameter removed, it will always reflect these changes. The extra poolclass argument to create_session() being the *only* change, how can this behaviour be explained, if the default is to use the QueuePool implementation? How can I detect from my code what is used if I don't specify the poolclass? a connection drawn from the pool will not have any transactional state on it from the previous checkout. The pool issues a rollback() on connections when they are checked in. Its possible you're seeing Session state still present. Ok. There definitely is the state indeed when using the QueuePool poolclass. in 0.4, the Session often needs to be cleared, expired, or closed to remove previous state, although if the previous set of data was fully committed, it typically falls out of scope and is garbage collected (the Session is weak referencing). 0.5's Session automatically expires all content upon commit() or rollback() and makes it very hard to see stale data with its default settings (though this also has the effect of much more SQL being issued). Ok, that is an obvious side-effect. I figured this is still desirable for my application, as caching data is done on another level anyway. --~--~-~--~~~---~--~~ 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: Adding additional conditions to an outer join
Bobby Impollonia wrote: Outerjoin takes a second argument which is the join condition. If you want it to have multiple conditions, you can combine them into a single condition using and_: table1.outerjoin(table2, and_(table1.something == table2.something, table1.somethingelse == somevalue)) Gorgeous! I was trying to use outerjoin() on the mapper before which doesn't have the onclause parameter so I didn't think to look for this. Here's the completed code for anyone searching the list later:: from sqlalchemy import and_, select RoleGroupJoin = PersonRolesTable.join(GroupsTable, and_(PersonRoles.group_id==Groups.id, Groups.name=='test')) PeopleJoin = PeopleTable.outerjoin(RoleGroupJoin, PersonRoles.person_id==People.id) stmt = select([PeopleTable, PersonRolesTable.c.role_status], from_obj=[PeopleJoin]).order_by(People.username) people = People.query.add_column(PersonRoles.role_status).from_statement(stmt) print people.all() [(User(admin,Admin User), u'approved'), (User(public,Public Man), None), (User(toshio,Toshio Kuratomi), u'approved')] Thanks to Bobby and Kipb for the various parts of this solution! -Toshio signature.asc Description: OpenPGP digital signature
[sqlalchemy] py2exe and SQLAlchemy
Hi, I use py2exe to package an app that uses SQLAlchemy through Elixir. When running the app in a non-packaged way, it works. But executing the exe gives me the following traceback: Traceback (most recent call last): File app.py, line 89, in module File wx\_core.pyc, line 7912, in __init__ File wx\_core.pyc, line 7487, in _BootstrapApp File app.py, line 20, in OnInit File frame.pyc, line 52, in __init__ File sqlalchemy\schema.pyc, line 1438, in _bind_to File sqlalchemy\engine\__init__.pyc, line 160, in create_engine File sqlalchemy\engine\strategies.pyc, line 48, in create File sqlalchemy\engine\url.pyc, line 92, in get_dialect ImportError: No module named sqlite I neither understand why this error only appears when launching the exe nor why it tries to import sqlite and not sqlite3. Any help is highly appreciated! Regards, Markus --~--~-~--~~~---~--~~ 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: MySQL encoding problems
Raoul Snyman wrote: Hi, I'm writing a Pylons app, connecting to an existing oldish database, and while connecting from my Mac desktop everything is fine, but when I connect from our dev server, I get the following error: LookupError: unknown encoding: latin1_swedish_ci I've done some Googling, found a couple of posts on here, as well as elsewhere, and I'm not sure what they're talking about in those posts (specifically, I don't see how they solved the problem). Desktop versions: Mac OS X 10.4 Python 2.5 SQLAlchemy: 0.5.0beta2 MySQLdb: 1.2.2 final Pylons: 0.9.6.2 Dev server versions: Linux Server: Gentoo 3.3.5.20050130-r1 MySQL Server: 4.1.9-max-log Python: 2.4.4 SQLAlchemy: 0.5.0beta2 MySQLdb: 1.2.2 final Pylons: 0.9.6.2 Unfortunately I can't change the db in any way, as this app is simply pulling a subsection of data out of an already existing system. Any ideas? Do you need more info? A stack trace? LookupError is pretty general... Would need to see a stack trace. --~--~-~--~~~---~--~~ 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: MySQL encoding problems
On Jul 25, 2008, at 5:34 AM, Raoul Snyman wrote:
I'm writing a Pylons app, connecting to an existing oldish database,
and while connecting from my Mac desktop everything is fine, but when
I connect from our dev server, I get the following error:
LookupError: unknown encoding: latin1_swedish_ci
I've done some Googling, found a couple of posts on here, as well as
elsewhere, and I'm not sure what they're talking about in those posts
(specifically, I don't see how they solved the problem).
Hi Raoul,
I'd guess that this error is coming from Python's codecs module,
probably indirectly from a call to unicode() with the encoding param
set to 'latin1_swedish_ci'.
It looks like that's the default for MySQL installations:
http://dev.mysql.com/doc/refman/5.0/en/charset-we-sets.html
But Python's never heard of it:
http://docs.python.org/lib/standard-encodings.html
import codecs
codecs.lookup(latin1_swedish_ci)
Traceback (most recent call last):
File stdin, line 1, in module
LookupError: unknown encoding: latin1_swedish_ci
So my guess is that SA is reading the encoding from the database and
then trying to convert text fields to Unicode and getting the error
above. Why this happens on one box and not another is mysterious. I'm
also on a Mac and it knows nothing about latin1_swedish_ci, so I don't
know why yours would be any different. Has your Mac Python perhaps
been compiled with some MySQL-awareness? If you run this at the
command line on your Mac, what does it report?
python -c import codecs; codecs.lookup('latin1_swedish_ci')
Cheers
Philip
--~--~-~--~~~---~--~~
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: py2exe and SQLAlchemy
With lots of hints I was able to solve this problem. Thanks!
Traceback (most recent call last):
File app.py, line 89, in module
File wx\_core.pyc, line 7912, in __init__
File wx\_core.pyc, line 7487, in _BootstrapApp
File app.py, line 20, in OnInit
File frame.pyc, line 52, in __init__
File sqlalchemy\schema.pyc, line 1438, in _bind_to
File sqlalchemy\engine\__init__.pyc, line 160, in create_engine
File sqlalchemy\engine\strategies.pyc, line 48, in create
File sqlalchemy\engine\url.pyc, line 92, in get_dialect
ImportError: No module named sqlite
py2exe did not package sqlite and therefore raised an ImportError
when the exe was launched. sqlite does not refer to the database
which is sqlite3 but to the module sqlalchemy.databases.sqlite.
SQLite3 was packaged correctly.
Probably py2exe failed to detect my use of SQLite because SQLAlchemy
does not know which database I use until runtime. (A beginner's
guess!)
How to fix this? Specify the package sqlalchemy.databases.sqlite
explicitly as a needed package in py2exe's options dictionary. This
can either be done in your setup.py:
setup(
options={py2exe:{
packages:
[sqlalchemy.databases.sqlite], # py2exe does not know that sqlite
is used in advance!
}},
Or from the command line (untested):
python setup.py py2exe -p sqlalchemy.databases.sqlite
Regards, Markus
--~--~-~--~~~---~--~~
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: Question about when to use connection pooling
On Jul 25, 2008, at 8:23 AM, Bram Avontuur wrote: As I can see, the turbogears way of creating the session as I described above does not use a connection pool. First of all, am I right to assume this? The default way is to create a new connection to the database whenever a session object is instantiated this way? the Session usually binds to an Engine, and when it needs to perform work witha connection and/or open a transaction, it consults the Engine for a connection. This uses the connection pool in all cases. SQLA routes all connection access through the pool (which can be configured to not actually be a pool, but this is very rare). Since it seems to me that using a connection pool would be a good thing performance-wise, given that many sessions are needed, I changed the turbogears code to make use of the QueuePool class for connection pooling. this should not be necessary. QueuePool is already being used unless TG does some very weird stuff (which Im fairly certain they dont). This, however, has the (undesired) side effect that connections drawn from the pool at the start of each web application's request contain cached database information. In my application, I have to explicitly issue a close() at the start of each request to delete the cache and any lingering transaction states etc. It's not trivial to issue a close() at the *end* of each request, hence at the start. a connection drawn from the pool will not have any transactional state on it from the previous checkout. The pool issues a rollback() on connections when they are checked in. Its possible you're seeing Session state still present.in 0.4, the Session often needs to be cleared, expired, or closed to remove previous state, although if the previous set of data was fully committed, it typically falls out of scope and is garbage collected (the Session is weak referencing). 0.5's Session automatically expires all content upon commit() or rollback() and makes it very hard to see stale data with its default settings (though this also has the effect of much more SQL being issued). Now, of course, I can hack this into the turbogears session initialisation as well. But I wonder, is using a connection pool still a good idea in this case? Is fetching a session from a pool and always closing it at the start of each application request more sane than simply opening a new connection to the database? Or is there another option that I am not currently seeing? In general, pulling a connection from a pool is faster than opening and closing it each time. There is a NullPool which will perform the latter if preferred. Also, if the Session is used in its transactional state (in 0.5 called autocommit=False), a single connection is maintained until a rollback() or commit() occurs, so this naturally has the effect of many operations taking place on a single connection. for reference: http://www.sqlalchemy.org/docs/04/dbengine.html http://www.sqlalchemy.org/docs/04/session.html the 0.5 docs for session are rewritten and feature more detail than those of 0.4 (but are specfic to 0.5's behavior): http://www.sqlalchemy.org/docs/05/session.html --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
