[sqlalchemy] Problem with using global metadata/connection in a threaded context
Hi, I'm using SQLAlchemy in an application where I have multiple threads needing to talk to a database. I am using only the direct SQL contruction and querying API, none of the ORM stuff. Currently, I create a single instance of my "persistence layer API", which makes a call to global_connect(), and optionally to default_metadata.create_all() (for tests). I then pass this instance to all the threads that need to talk to my database. When I then use the tables (defined at the module scope, hooked to the default metadata object) in a single-threaded context, everything works fine. However, if I switch to trying to use the persistence layer from another thread, I get the following exception: InvalidRequestError: This Compiled object is not bound to any engine. I am guessing that somewhere in the mix the engine information is severed from the metadata when jumping to the other thread, but I have no idea how to fix this problem. Can anyone give me pointers on how to get my threads talking to the database correctly? Thanks in advance, and thanks for the awesomeness of SA! - Dave --~--~-~--~~~---~--~~ 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: SQL newbie question: How to select the last message for each user
On 16/05/07, Glauco <[EMAIL PROTECTED]> wrote:
>
> are you searhing for ?
>
> select max( changeTime ) from OrderDiscount group by orderID ; ?
>
> i don't understand well what you are searching for
>
Thanks to all of you!
I actually needed the last message itself - more than just its date.
Thanks to your help, I came up with this:
# Create the 'maxdates' select, which gives for each user (which
posted a message) the date of his most recent post
maxdates = select([messages.c.user_id.label('uid'),
func.max(messages.c.date).label('maxdate')],
group_by=messages.c.user_id).alias('maxdates')
# Select only the last message for each user
lastmessages = select([messages],
and_(messages.c.user_id==maxdates.c.uid,
messages.c.date==maxdates.c.maxdate), group_by=messages.c.user_id)
I added the group_by to the last select so that if there are two
messages from the same date only one will be selected.
I added the alias maxdates to the first select because otherwise mysql
complained that 'Every derived table must have its own alias'. I think
that it's a bug in sqlalchemy - adding the alias added no information
about the query, so sqlalchemy should have created an alias by itself.
Am I correct?
Thanks again,
Noam
--~--~-~--~~~---~--~~
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: unsave instance on a cascade='all, delete-orphans'
new to the FAQ: http://www.sqlalchemy.org/trac/wiki/ FAQ#FlushError:instancesomeinstanceisanunsavedpendinginstanceandisanorph an On May 16, 2007, at 3:44 AM, Glauco wrote: > > in a mapper where i've a 1:n relation i've some problem with Tubo > Gears in delete, after the cascade="delete-orphan, all" options, all > times i add a new instance of child this error occours: > > instance is an unsaved, pending instance and is an orphan > (is not attached to any parent 'Operatore' instance via that classes' > 'groups' attribute) > > > If i remove the cascade="delete-orphan, all" option, the insert run > perfeclty. > > > > Thank you > Glauco > > > > --~--~-~--~~~---~--~~ 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: cascade='all, delete-orphan' causing error about unsaved, pending instances
new to the FAQ: http://www.sqlalchemy.org/trac/wiki/ FAQ#FlushError:instancesomeinstanceisanunsavedpendinginstanceandisanorph an On May 16, 2007, at 11:16 AM, Andreas Jung wrote: > > > > I am building a media database using SA where the model basically maps > > Medium --1:N--> Versions --1:N--> Files > > My code for creating new Medium instances based on an import script > basically is doing the following: > > f = File(...) > v = Version() > v.files.append(f) > m = Medium() > m.versions.append(v) > session.save(m) > > This works perfectly for the import however I have to deal with > File orphans > (caused by some business logic of the media database). > > To get rid of orphans I added relation(..., cascade='all, delete- > orphan') > to the mapper definitions. However running the import with this change > causes the following error: > > > File "lib/python/mediendb/misc/import_medien.py", line 86, in > import_medien >TH(import_medium, fullname) > File "/local2/HRS2/HEAD.Zope28/HaufeCMS/Products/HaufeCMS/ > Transactions.py", line 29, in __call__ >return TH.__call__(self, f, *args, **kw) > File "/local2/HRS2/HEAD.Zope28/HaufeCMS/lib/python/Haufe/ > Transactions/TransactionHandler.py", line 94, in __call__ >else: tmgr.get().commit() # old: transaction.get().commit() > File "/local2/HRS2/mediendb-ajung/HaufeCMS/Base/lib/python/ > transaction/_transaction.py", line 390, in commit >self._saveCommitishError() # This raises! > File "/local2/HRS2/mediendb-ajung/HaufeCMS/Base/lib/python/ > transaction/_transaction.py", line 388, in commit >self._commitResources() > File "/local2/HRS2/mediendb-ajung/HaufeCMS/Base/lib/python/ > transaction/_transaction.py", line 433, in _commitResources >rm.commit(self) > File "build/bdist.linux-x86_64/egg/z3c/sqlalchemy/base.py", line > 151, in commit > File "build/bdist.linux-i686/egg/sqlalchemy/orm/session.py", line > 302, in flush > File "build/bdist.linux-i686/egg/sqlalchemy/orm/unitofwork.py", > line 200, in flush > File "build/bdist.linux-i686/egg/sqlalchemy/orm/mapper.py", line > 290, in _is_orphan > FlushError: instance 0xb3610eac> is an unsaved, pending instance and is an orphan (is > not attached to any parent '_mapped_versions' instance via that > classes' 'files' attribute) > > Why does this cascade rule causes this error? > > Andreas > > -- > ZOPYX Ltd. & Co. KG - Charlottenstr. 37/1 - 72070 Tübingen - Germany > Web: www.zopyx.com - Email: [EMAIL PROTECTED] - Phone +49 - 7071 - 793376 > Registergericht: Amtsgericht Stuttgart, Handelsregister A 381535 > Geschäftsführer/Gesellschafter: ZOPYX Limited, Birmingham, UK > -- > -- > E-Publishing, Python, Zope & Plone development, Consulting --~--~-~--~~~---~--~~ 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] cascade='all, delete-orphan' causing error about unsaved, pending instances
I am building a media database using SA where the model basically maps Medium --1:N--> Versions --1:N--> Files My code for creating new Medium instances based on an import script basically is doing the following: f = File(...) v = Version() v.files.append(f) m = Medium() m.versions.append(v) session.save(m) This works perfectly for the import however I have to deal with File orphans (caused by some business logic of the media database). To get rid of orphans I added relation(..., cascade='all, delete-orphan') to the mapper definitions. However running the import with this change causes the following error: File "lib/python/mediendb/misc/import_medien.py", line 86, in import_medien TH(import_medium, fullname) File "/local2/HRS2/HEAD.Zope28/HaufeCMS/Products/HaufeCMS/Transactions.py", line 29, in __call__ return TH.__call__(self, f, *args, **kw) File "/local2/HRS2/HEAD.Zope28/HaufeCMS/lib/python/Haufe/Transactions/TransactionHandler.py", line 94, in __call__ else: tmgr.get().commit() # old: transaction.get().commit() File "/local2/HRS2/mediendb-ajung/HaufeCMS/Base/lib/python/transaction/_transaction.py", line 390, in commit self._saveCommitishError() # This raises! File "/local2/HRS2/mediendb-ajung/HaufeCMS/Base/lib/python/transaction/_transaction.py", line 388, in commit self._commitResources() File "/local2/HRS2/mediendb-ajung/HaufeCMS/Base/lib/python/transaction/_transaction.py", line 433, in _commitResources rm.commit(self) File "build/bdist.linux-x86_64/egg/z3c/sqlalchemy/base.py", line 151, in commit File "build/bdist.linux-i686/egg/sqlalchemy/orm/session.py", line 302, in flush File "build/bdist.linux-i686/egg/sqlalchemy/orm/unitofwork.py", line 200, in flush File "build/bdist.linux-i686/egg/sqlalchemy/orm/mapper.py", line 290, in _is_orphan FlushError: instance 0xb3610eac> is an unsaved, pending instance and is an orphan (is not attached to any parent '_mapped_versions' instance via that classes' 'files' attribute) Why does this cascade rule causes this error? Andreas -- ZOPYX Ltd. & Co. KG - Charlottenstr. 37/1 - 72070 Tübingen - Germany Web: www.zopyx.com - Email: [EMAIL PROTECTED] - Phone +49 - 7071 - 793376 Registergericht: Amtsgericht Stuttgart, Handelsregister A 381535 Geschäftsführer/Gesellschafter: ZOPYX Limited, Birmingham, UK E-Publishing, Python, Zope & Plone development, Consulting pgpJz2ilK4tra.pgp Description: PGP signature
[sqlalchemy] Re: SQL newbie question: How to select the last message for each user
are you searhing for ? select max( changeTime ) from OrderDiscount group by orderID ; ? i don't understand well what you are searching for Glauco --~--~-~--~~~---~--~~ 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: SQL newbie question: How to select the last message for each user
I wrote a similar query in SQLObject - in one query it retrieves the most
recent discount for every order in the list orderIDs. The code is below; it
should be pretty easy to translate to SQLAlchemy.
The technique is to use nested SQL queries - the innermost query uses MAX to
determine the most recent change time, the middle query selects the highest-ID
record with change time, and the outermost query selects the interesting data
from the resulting set of records.
@classmethod
def get_discounts(klass, orderIDs):
'''From a list of orderIDs, returns a dictionary keyed by order ID
containing the discount for the orders. Runs a query, so should
be much faster than working with objects.
'''
# Build the discount dictionary. If no discount record found, discount
is 0.
discount = { }
for o in orderIDs:
discount[o] = 0
# Work around a bug in some databases. Apparently some versions of
# PostgreSQL cannot handle an empty "IN" clause.
if len(orderIDs) == 0:
return discount
# Innermost query: Highest change time among the discounts for each
order.
od2 = Alias(OrderDiscount)
q1 = Select(func.MAX(od2.q.changeTime), where=[od2.q.orderID ==
OrderDiscount.q.orderID])
# Second query: In case of a tie on change time, multiple records will
# be selected per order. Select the max ID in case of a tie.
q2 = Select(
func.MAX(OrderDiscount.q.id),
where=AND(OrderDiscount.q.changeTime == q1,
IN(OrderDiscount.q.orderID, orderIDs)),
groupBy=OrderDiscount.q.orderID)
q3 = Select(
[ OrderDiscount.q.orderID, OrderDiscount.q.percent_off ],
where=IN(OrderDiscount.q.id, q2))
conn = OrderDiscount._connection
for od in conn.queryAll(conn.sqlrepr(q3)):
discount[od[0]] = od[1]
return discount
- Original Message
From: Noam <[EMAIL PROTECTED]>
To: sqlalchemy
Sent: Wednesday, May 16, 2007 1:21:37 AM
Subject: [sqlalchemy] SQL newbie question: How to select the last message for
each user
Hello,
I'm pretty much an SQL newbie, so I don't know how to do this in SQL,
but I want to do it in SQLAlchemy, so I hope you'll agree to help.
I have a users table and a messages table. Each message belongs to a
user. Each message has a date. I want to select the last message for
each user, according to the date (actually I'm only interested in the
date). I know I can first select all users and then issue a select for
each user to get the last message. But is it possible to do it with
only one select, to save DB queries?
Thanks,
Noam
Looking
for a deal? Find great prices on flights and hotels with Yahoo! FareChase.
http://farechase.yahoo.com/
--~--~-~--~~~---~--~~
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: winpdb and sqlalchemy
Edin Salkovic ha scritto: > Currently, SQLAlchemy's setup(...) hasn't set this arg, meaning that > setuptools uses its own algorithm to determine if SQLAlchemy can be > installed as a zipped egg. > You can switch off zip for all newly installed eggs, in ~/.pydistutils.cfg [easy_install] zip_ok = false --~--~-~--~~~---~--~~ 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: winpdb and sqlalchemy
On 5/14/07, Michael Bayer <[EMAIL PROTECTED]> wrote: > id love if this issue were reported on the Distutils mailing list > (though im pretty sure theres a flag in setuptools to disable the egg > generation upon setup which might be the solution here)... since I'd > really like a lot more people to be there hammering setuptools into > proper shape (even if that includes just "documentation" of options > like these). >From http://peak.telecommunity.com/DevCenter/EasyInstall#command-line-options = Command-Line Options --zip-ok, -z Install all packages as zip files, even if they are marked as unsafe for running as a zipfile. This can be useful when EasyInstall's analysis of a non-setuptools package is too conservative, but keep in mind that the package may not work correctly. (Changed in 0.5a9; previously this option was required in order for zipped installation to happen at all.) --always-unzip, -Z Don't install any packages as zip files, even if the packages are marked as safe for running as a zipfile. This can be useful if a package does something unsafe, but not in a way that EasyInstall can easily detect. EasyInstall's default analysis is currently very conservative, however, so you should only use this option if you've had problems with a particular package, and after reporting the problem to the package's maintainer and to the EasyInstall maintainers. (Note: the -z/-Z options only affect the installation of newly-built or downloaded packages that are not already installed in the target directory; if you want to convert an existing installed version from zipped to unzipped or vice versa, you'll need to delete the existing version first, and re-run EasyInstall.) = So, the proper solution is to delete the easy_install-ed sqlalchemy egg (if there's any) from the site-packages dir and then run: easy_install -Z SQLAlchemy or easy_install -Z -U SQLAlchemy Alternatively, one can edit setup.py and add the zip_safe argument to the setup function, like: setup( # ... zip_safe = False # ... ) in order to always unzip the easy_install-ed egg. Currently, SQLAlchemy's setup(...) hasn't set this arg, meaning that setuptools uses its own algorithm to determine if SQLAlchemy can be installed as a zipped egg. See: http://peak.telecommunity.com/DevCenter/setuptools#setting-the-zip-safe-flag IMO the setuptools docs are very good. Edin --~--~-~--~~~---~--~~ 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: SQL newbie question: How to select the last message for each user
YourMessageMapper.select_by( tbl['users'].c.id == $$$, order_by= [desc(tbl[messages'].c.data)], limit=1) this is your last message for $$$ person Glauco --~--~-~--~~~---~--~~ 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] unsave instance on a cascade='all, delete-orphans'
in a mapper where i've a 1:n relation i've some problem with Tubo Gears in delete, after the cascade="delete-orphan, all" options, all times i add a new instance of child this error occours: instance is an unsaved, pending instance and is an orphan (is not attached to any parent 'Operatore' instance via that classes' 'groups' attribute) If i remove the cascade="delete-orphan, all" option, the insert run perfeclty. Thank you Glauco --~--~-~--~~~---~--~~ 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: retrieving columns from secondary in n:m relation
I think is not possible... :-( The association object pattern describe because But for this and other cases i've a proposal: The add_column function must add only column in the Result, and oly add_entity must add tables to from clause, in this manner people who use this feature can do anyway all they want. Otherwise in cases where generated select contain already the table of my add_column dont add it again retrievind erroneus records. Thank you Glauco --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
