[sqlalchemy] ForeignKey and onupdate/ondelete
Why ForeignKey does not allow the onupdate/ondelete keywords arguments? Thanks and regards Manlio Perillo --~--~-~--~~~---~--~~ 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 Has Gone Away
I'm still having this problem with MySQL (both 4.1 and 5.0), SA (tested with both 0.2.8 and 0.3.1) and TurboGears 1.0b1. I've gone over the TG connection code a number of times and I can't see anything wrong with it. This isn't after a long idle time either. Sometimes I can start the app and have the MySQL server has gone away exception after 2 or 3 requests. To reproduce this is simple. Quickstart a new turbogears project with SA and identity: tg-admin quickstart -i --sqlalchemy Change the dburi in dev.cfg to a mysql db. Then use ab to pound on the server. The magic numbers that *always* causes the app to fail at some point during the process is as follows: ab -n 500 -c 10 http://localhost:8080/ I'm pretty sure that this isn't a TG problem (although I don't have an issue with being proven wrong :) so is this an SA issue or a MySQLdb issue? Thanks, Lee -- Lee McFadden blog: http://www.splee.co.uk work: http://fireflisystems.com skype: fireflisystems --~--~-~--~~~---~--~~ 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 Has Gone Away
I'm still having this problem with MySQL (both 4.1 and 5.0), SA (tested with both 0.2.8 and 0.3.1) and TurboGears 1.0b1. Almost the same for me (MySQL 5.0.22, SA 0.2.8 and 0.3.1, using CherryPy). Using pool_recycle works nice on a non-high load environments. But while I benchmark my app (using siege or ab), this always produces Lost connection during MySQL query..., and sometimes MySQL server has gone away Note the version of MySQLdb is important: using 1.2.2b1 (didn't tested the last 1.2.2b2) can reduce those errors, but not completely though. Also, MySQLdb is not thread-safe, so using it in a multithreaded environment can be painful... (but I'm sure that's transparent using TG). Since SA 0.2.4 (pool_recycle did not exist at this time), I use a specific pool, derived from the QueuePool class, which *always* check the current checked-out connection is valid, using connection.ping() (MySQL specific, I think not all DBs support this). I'm still using my pool as of 0.3.1, to prevent those kind of errors. I didn't find out why this occurs: having a test-case is very difficult. I'd also investigated the way the app access MySQL. IIRC, while using a socket, if a connection is timed-out, reaccessing the server automatically regenerate the connection (well, actually, I've not observed this behavior for all the mysql servers I use), without producing those errors. I have this kind of lines in MySQL logs when this occurs: Connect [EMAIL PROTECTED] on dev blablabla Well, that's a lot of obscure observations... without a clean solution ! But maybe someone will have some ideas... Cheers, -- Sébastien LELONG sebastien.lelong[at]sirloon.net --~--~-~--~~~---~--~~ 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] table relationship with direct attributes access
Hi.
Consider this example:
articles = Table('articles',
Column('article_id', Integer, primary_key=True),
...
)
comments = Table('comments',
Column('comment_id', Integer, primary_key=True),
Column('user_id', Integer, ForeignKey('articles.article_id'),
Column('comment', String)
)
class Article(object):
pass
class Comment(object):
def __init__(self, comment):
self.comment = comment
With these definitions I can build a mapper that attach a comments
attribute to the Article class.
However the comments attribute is a list of Comment instance.
Is it possible to have it as a list of strings (that is, mapped to
Comment.comment instead that Comment)?
Thanks and regards Manlio Perillo
--~--~-~--~~~---~--~~
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] problem with mapper relationship
Hi again.
I have the following definitions:
tests = Table('tests',
Column('id', Integer, primary_key=True),
...
)
tests_state = Table('tests_state',
Column('id', Integer, ForeignKey('tests.id'), primary_key=True).
Column('count', Integer, default=0)
)
class TestState(object):
pass
class Test(object):
pass
testStateMapper = mapper(TestState, tests_state)
testMapper = mapper(
Test, tests,
properties={'state': relation(TestState, uselist=False)}
)
The problem is that when I do:
test = sess.get(Test, 1)
test.state.count = test.state.count + 1
the tests_state table is not updated.
Thanks Manlio Perillo
--~--~-~--~~~---~--~~
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: remote nondirect access to DB
um, web interface ? SQL console ? it would help to know what kind of client youre talking about. --~--~-~--~~~---~--~~ 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: loosing selected database with mysql
some commands that you type into your SQL console are specific to the console application, and dont work as SQL statements sent over DBAPI. the \d command in the Postgres client console is such an example, or .show in sqlite. use might fall into this category in some circumstances as well. --~--~-~--~~~---~--~~ 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: table relationship with direct attributes access
SA doesnt have this feature built in at the moment. you can use a property to acheive the desired effect: class MyClass(object): someprop = property(lamba self:[x.someattribute for x in self.comments]) it also might be possible to use the AssociationProxy extension (see sqlalchemy.ext.associationproxy) to achieve the same result with reading/writing capability. --~--~-~--~~~---~--~~ 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: problem with mapper relationship
cant reproduce. heres a test case of the above which passes:
from sqlalchemy import *
meta = BoundMetaData('sqlite://', echo=True)
tests = Table('tests',meta,
Column('id', Integer, primary_key=True),
Column('foo', String(30))
)
tests_state = Table('tests_state',meta,
Column('id', Integer, ForeignKey('tests.id'), primary_key=True),
Column('count', Integer, default=0)
)
meta.create_all()
class TestState(object):
pass
class Test(object):
pass
testStateMapper = mapper(TestState, tests_state)
testMapper = mapper(
Test, tests,
properties={'state': relation(TestState, uselist=False)}
)
sess = create_session()
test = Test()
test.state = TestState()
sess.save(test)
sess.flush()
assert test.state.count == 0
sess.clear()
test = sess.get(Test, 1)
test.state.count = test.state.count + 1
sess.flush()
sess.clear()
test = sess.get(Test,1)
assert test.state.count == 1
--~--~-~--~~~---~--~~
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 Has Gone Away
yes it appears that MySQLDB, among its many limitations, is also
completely not threadsafe. Running a barebones web test to a MySQL
produces the same error. its a little amazing SA has made it for over
a year and nobody has really had this problem before.
So, for non-threadsafe DBAPIs we use the SingletonThreadPool, like
this:
meta = BoundMetaData('mysql://scott:[EMAIL PROTECTED]/test',
poolclass=pool.SingletonThreadPool)
It appears that I will have to make SingletonThreadPool the defualt
pool for the mysql module the same way it is for sqlite although Im
going to check MySQLDB now to see if thread-safety has been worked into
newer versions.
--~--~-~--~~~---~--~~
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] rebind method for BoundMetaData
Hey Mike, any objection to me adding a 'rebind' method to BoundMetaData which would allow a swap of the engine at runtime? Rick --~--~-~--~~~---~--~~ 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] func.max()
Hi all, How would I build the following query using SA? select max(numero) from bolletta where anno=2006; this is my table tbl=[] tbl[name] = Table(name, database.metadata, autoload=True) class Bolletta(DomainObject): def __str__(self): return self.numero or repr(self) assign_mapper(context, Bolletta, tbl['bolletta']) jo --~--~-~--~~~---~--~~ 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: func.max()
Michael, Can you explain why this is the preferred method? Wouldn't it just be simpler and cleaner to do the following? conn.execute(select max(numero) from bolletta where anno=:anno,anno=2006) John Michael Bayer wrote: select([func.max(tbl['bolletta'].c.numero)], tbl['bolletta'].c.anno==2006) --~--~-~--~~~---~--~~ 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] PickleType with custom pickler
I just looked at SA's PickleType and came up with a couple of issues. 1. dumps/loads It looks like you can provide a custom pickler to PickleType, but the Python Pickler class does not have dumps()/loads() methods. Those methods are only available at the module level. This is a minor issue since it's not that hard to implement a wrapper for pickler/unpickler that supports dumps/loads. However, it may be a good idea to note this issue in the documentation. 2. persistent_id/persistent_load I need to supply a custom pickler that will use persistent_id() and persistent_load(). These pickle extensions are natural requirements in a database environment. They allow objects that will be pickled to hold references to persistent objects and have those links automatically preserved across pickle/unpickle without actually pickling the persistent objects. However, there is no easy way to use these methods with SQLAlchemy--I'm referring specifically to the orm package here. Here's a bit of (untested) code to illustrate: from cStringIO import StringIO from cPickle import Pickle, Unpickle class MyPickler(object): def __init__(self, session, typeMap): self.session = session self.typeMap = typeMap # map class names to types def persistent_id(self, obj): if hasattr(obj, id): # only mapped objects have an id return %s:%s % (type(obj).__name__, obj.id) return None def persistent_load(self, key): name, ident = key.split(:) class_ = self.typeMap[name] return self.session.query(class_).get(ident) def dumps(self, graph): src = StringIO() pickler = Pickler(src) pickler.persistent_id = self.persistent_id pickler.dump(graph) return src.getvalue() def loads(self, data): dst = StringIO(data) unpickler = Unpickler(dst) unpickler.persistent_load = self.persistent_load return unpickler.load() ... t = Table(... Column(pdata, PickleType(pickler=MyPickler(.?.))) Now the obvious flaw here is that MyPickler needs a session at instantiation time, and it uses the same session for every unpickle throughout the entire application. From what I can tell PickleType has no way of getting at the session of the current load/save taking place when the data is selected from/written to the database. I'm not using thread-local sessions, so that won't work, however there are multiple concurrent sessions within my application. My other thought was to use a mapper extension to unpickle on populate_instance and pickle on before_insert/before_update. The session is easier to get there, and I might have been able to hack it somehow, but I had no way to tell the mapper to perform an update if the only thing that changed was the pickle data. Am I missing something? Is there any way to do what I'm trying to do? ~ Daniel --~--~-~--~~~---~--~~ 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: loosing selected database with mysql
what I meant is that I use use XYZ from python code .. robert --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~--- begin:vcard fn:robert rottermann n:rottermann;robert email;internet:[EMAIL PROTECTED] tel;work:031 333 10 20 tel;fax:031 333 10 23 tel;home:031 333 36 03 x-mozilla-html:FALSE version:2.1 end:vcard
[sqlalchemy] Re: func.max()
I have wondered this myself several times. I've tried to build an SA query for a few of my database views but have always fallen back to just a straight SQL query. Is the SA method used to be database agnostic? I think that an area on the wiki set up for these sorts of examples would be valuable. The examples in the documentation were not complicated enough to figure out some of my SA translated queries. --~--~-~--~~~---~--~~ 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: func.max()
John Lavoie wrote: Michael, Can you explain why this is the preferred method? Wouldn't it just be simpler and cleaner to do the following? conn.execute(select max(numero) from bolletta where anno=:anno,anno=2006) This is a good question, Michael. I hope somebody can answer this question. I'm trying to write my queries using only the mappers but sometimes I have difficult to do that. I have many queries in my programs written in pure sql but I feel myself guilt :-[ and I'm trying to convert all of them. SQLAlchemy allows you to do the same thing in too many ways. At this point I would like to know, what is the best way (or the right way to do things). jo John Michael Bayer wrote: select([func.max(tbl['bolletta'].c.numero)], tbl['bolletta'].c.anno==2006) --~--~-~--~~~---~--~~ 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: func.max()
jose wrote: John Lavoie wrote: Michael, Can you explain why this is the preferred method? Wouldn't it just be simpler and cleaner to do the following? conn.execute(select max(numero) from bolletta where anno=:anno,anno=2006) This is a good question, Michael. I hope somebody can answer this question. I'm trying to write my queries using only the mappers but sometimes I have difficult to do that. I have many queries in my programs written in pure sql but I feel myself guilt :-[ and I'm trying to convert all of them. SQLAlchemy allows you to do the same thing in too many ways. At this point I would like to know, what is the best way (or the right way to do things). jo sometimes i merged sql pure with mappers like this: sql=select([count(*)], from_obj=[azienda]) -- because func.count(*) doesn't work sql=select([azienda.id as pk], from_obj=[azienda]) -- alias... I confess, I'm new to SA, thus I beleave these things can be done in a best way :-\ Sometimes, I have to do mortal jumps to write sample sql commands. jo John Michael Bayer wrote: select([func.max(tbl['bolletta'].c.numero)], tbl['bolletta'].c.anno==2006) --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
