http://permalink.gmane.org/gmane.comp.python.sqlalchemy.user hourly 1 1901-01-01T00:00+00:00 http://gmane.org/img/gmane-25t.png http://gmane.org http://permalink.gmane.org/gmane.comp.python.sqlalchemy.user/15224 On Aug 31, 11:36 pm, Michael Bayer <mike...< at >zzzcomputing.com> wrote: What would be the equivalent way in 0.4 version of sqlalchemy --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy< at >googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscribe< at >googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~--- qvx 2008-09-06T19:46:48 http://permalink.gmane.org/gmane.comp.python.sqlalchemy.user/15223 Maps to explicit joins are in any case going to integrate poorly with the joins created by SQLA's inheritance functionality. But in this case, no imitation of SQLA's join is needed: orm.mapper(Lab, inherits=Group, polymorphic_identity=u"group_lab", ) On Sep 6, 2008, at 1:24 PM, Jason Yamada-Hanff wrote: --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy< at >googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscribe< at >googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~--- Michael Bayer 2008-09-06T19:04:00 http://permalink.gmane.org/gmane.comp.python.sqlalchemy.user/15222 Below is a simplified case of a problem (bug?) I am running into. I have a polymorphic schema using joined-table inheritance. In my OO layer, a Group is an Agent, and a Lab is a Group. In the DB layer, Lab doesn't actually need to hold any extra information so it doesn't need a separate table. Instead, I would like to map the Lab object to the 'groups' table and select only those types that are identified as a lab by the 'type' discriminator column in 'agents' table. I want,t hen, to map Lab to a selectable. However, when I try to create and flush a Lab instance with the setup below, it is obvious that the attributes that should map into the groups table aren't getting collected into the INSERT statement correctly. If I change `lab_select` to the simple join without the select statement, the Lab instance is persisted correctly. Of course, this does nothing for the need to filter out non-lab entries. Similarly, if I create a useless extra table called `labs` and map to that instead, the INSERT works Jason Yamada-Hanff 2008-09-06T17:24:58 http://permalink.gmane.org/gmane.comp.python.sqlalchemy.user/15221 On Sep 6, 2008, at 8:03 AM, gatto wrote: If this is executed "at the beginning of a request", do you mean "each" request ? If so, this code is creating a new engine on each request, since metadata.bind = "somestring" calls create_engine(). For a mod_python application, create_engine() should be called exactly once at the module level, so that one connection pool is created for the lifespan of the entire process. Though in theory, if you were creating new engines on each request they should be garbage collected if they became unreferenced. After ensuring that you have only one engine per process, for further research I'd advise enabling connection pool logging to see just where all the connections are getting opened. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy< at >googlegroups.com To unsubscribe from this group, send email to Michael Bayer 2008-09-06T16:27:29 http://permalink.gmane.org/gmane.comp.python.sqlalchemy.user/15220 On Sep 6, 2008, at 7:35 AM, Kyle Schaffrick wrote: To make this very specific, here is the method we're talking about: def __no_criterion_condition(self, meth): if self._criterion or self._statement or self._from_obj or \ self._limit is not None or self._offset is not None or \ self._group_by or self._order_by: raise sa_exc.InvalidRequestError("Query.%s() being called on a Query with existing criterion. " % meth) self._statement = self._criterion = self._from_obj = None self._order_by = self._group_by = self._distinct = False self.__joined_tables = {} This method is used by a few methods which do need to raise if order_by() is set, since order_by() would not normally be a noop in those cases but existing order_by's are incompatible with the generative method being called - these are with_polymorphic() and select_from(), both of which affect aliasing behavior which isnt retroactively applied to existi Michael Bayer 2008-09-06T16:15:46 http://permalink.gmane.org/gmane.comp.python.sqlalchemy.user/15215 On Sep 5, 12:19 pm, gatto <thegattoma...< at >gmail.com> wrote: does anyone have any advice on this? it's a real problem as you might imagine.. regularly getting "too many connection" errors just because the app isn't releasing the connections. the above code is executed at the start of the application, and at the end, i run session.remove(), which i was under the impression would release my connection. but i'm tracking my open connections to mysql by running "show processlist" and the connections never die, they stay alive until apache is rebooted. how can i close these connections? thank you for any pointers! i don't know where to turn to, i've been researching this for 2 days.. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy< at >googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscribe< at >googlegroups.com For more options, visit this g gatto 2008-09-06T12:03:18 http://permalink.gmane.org/gmane.comp.python.sqlalchemy.user/15214 On Thu, 4 Sep 2008 18:54:27 -0400 Michael Bayer <mike_mp< at >zzzcomputing.com> wrote: I was thinking about pointing out that distinction myself. get() basically turns order_by() into a no-op, but it isn't at all *ambiguous*. I'm going to agree with the OP in that throwing the order_by() away in this situation isn't really tantamount to guessing. I'm on the fence about an appropriate behavior. In a statically typed language, it's "nonsensical" to declare unused local variables since the compiler optimizes them away anyhow, but it's not "ambiguous" either. By this logic, perhaps the ORDER BY should be optimized away and a warning should be issued, just as a compiler would issue "warning: local variable 'foo' is unreferenced." On the other hand, a limit() followed by a get() *would* be ambiguous: Some might expect it to throw away the limit and always get the item: q = ses.query(A).order_by([A.rank]).limit(5) top5, item123 = q.all(), q.get(123) while others might expect it to apply the limit and return Kyle Schaffrick 2008-09-06T11:35:09 http://permalink.gmane.org/gmane.comp.python.sqlalchemy.user/15213 On Sep 5, 2:27 pm, Michael Bayer <mike...< at >zzzcomputing.com> wrote: Thanks Michael, and thanks for your help. It was your suggestion to do chained joins that was the key. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy< at >googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscribe< at >googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~--- Sam Magister 2008-09-05T23:51:18 http://permalink.gmane.org/gmane.comp.python.sqlalchemy.user/15212 On Sep 5, 2:14 pm, a...< at >svilendobrev.com wrote: That's right, SQLA is not handling the inheritance directly. In some sense this is nice because there is nothing going on 'under the hood' (at least nothing related to the inheritance structure). I don't think so. This issue did come up but we don't use it in our application so that's ok. I'm not sure how the table joins would work and how the discriminator setting would work. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy< at >googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscribe< at >googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~--- Sam Magister 2008-09-05T23:49:41 http://permalink.gmane.org/gmane.comp.python.sqlalchemy.user/15211 this looks pretty impressive ! I was poking around today to see how hard it would be to make "inherits" a list. It seems like some things would be straightforward, others not. Its interesting and I'm still thinking about it. But its good you could make it work without getting into all of that. On Sep 5, 2008, at 2:56 PM, Sam Magister wrote: --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy< at >googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscribe< at >googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~--- Michael Bayer 2008-09-05T21:27:55 http://permalink.gmane.org/gmane.comp.python.sqlalchemy.user/15210 u can always cut the verbosity in py: query( *[func.sum(getattr(TimeEntries, x)) for x in 'reg ot ce hol sklv vac ct whatever else'.split() ]) ... --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy< at >googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscribe< at >googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~--- az< at >svilendobrev.com 2008-09-05T21:33:48 http://permalink.gmane.org/gmane.comp.python.sqlalchemy.user/15209 On Sep 4, 2:32 pm, Michael Bayer <mike...< at >zzzcomputing.com> wrote: I ended up with the following: <code> query = self.session.query(func.sum(TimeEntries.reg), func.sum(TimeEntries.ot), func.sum(TimeEntries.ce), func.sum(TimeEntries.hol), func.sum(TimeEntries.sklv), func.sum(TimeEntries.vac), func.sum(TimeEntries.ct), func.sum(TimeEntries.conv), func.sum(TimeEntries.misc) ) query = query.filter(and_(TimeEntries.dateworked >= start_date, TimeEntries.dateworked <= end_date, TimeEntries.empid==self.emp_id) ) totals = query.first() </code> This works quite well, although it is kind of verbose. Thanks again for your advice. Mike --~--~---------~--~----~------------~-------~--~----~ You received Mike 2008-09-05T20:44:31 http://permalink.gmane.org/gmane.comp.python.sqlalchemy.user/15208 just a theoretical comment... so instead of relying on SA mapper logic to automaticaly put the discriminator/s, u are doing it on a level above, in a way - as SA does not know about the (multiple) inheritance that those imply. can be useful as an approach for other 'beyond SA' things... will it work for multilevel inheritance? btw i would not call it concrete-table inh, as Engineer has only foreignkeys to other tables (like joined-table) and not their data columns. but it isnt joined-table either. inbetween... On Friday 05 September 2008 21:56:28 Sam Magister wrote: --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy< at >googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscribe< at >googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~--- az< at >svilendobrev.com 2008-09-05T21:14:16 http://permalink.gmane.org/gmane.comp.python.sqlalchemy.user/15207 Hi, On Sep 3, 3:45 pm, Michael Bayer <mike...< at >zzzcomputing.com> wrote: Your tip about the Column() did the trick. I can now autoload my view and manipulate it as needed. I had to map it to an empty class as the other poster mentioned. Thanks again! Mike --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy< at >googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscribe< at >googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~--- Mike 2008-09-05T20:41:27 http://permalink.gmane.org/gmane.comp.python.sqlalchemy.user/15206 hi everyone. just started using sqlalchemy and elixir recently. i'm not having any issues with coding yet, just this one: OperationalError: (OperationalError) (1040, 'Too many connections') as a workaround for this problem, i created a cron job to restart apache once every hour and that is mostly working. but i really need to find out what is causing this. i'm using a custom built mvc framework that runs atop mod_python, and in the base application class i have the following code, which executes once at the beginning of the request: metadata.bind = 'mysql://' + app_config.database.user + ':' + app_config.database.password + '< at >' + app_config.database.host + ': 3306/' + app_config.database.schema metadata.bind.echo = True metadata.bind.recycle = 3600 i had thought setting recycle to 3600 in this way would cause the connections to be reused every 5 minutes if they had remained open. we're only getting a couple hundred unique visitors per day currently so i'm surprised that the error happens so quick gatto 2008-09-05T19:19:53 http://permalink.gmane.org/gmane.comp.python.sqlalchemy.user/15205 Michael! Thank you for such an excellent and prompt reply! I'm going to use method two. It looks very convenient. Thanks again--- Sam P.S. Perhaps an example of method two could be added to the tutorial for the next person in my predicament? :) On Sep 5, 7:19 am, Michael Bayer <mike...< at >zzzcomputing.com> wrote: --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy< at >googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscribe< at >googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~--- Sam 2008-09-05T19:02:01 http://permalink.gmane.org/gmane.comp.python.sqlalchemy.user/15204 I've come up with an implementation based on concrete table inheritance, without touching any SQLA internals like Michael suggested. I've also added a discriminator to both base classes, 'citizen_type' and 'employee_type'. The discriminator is set via the before_insert() method of a MapperExtension which extends the Engineer mapper. I realize that going this way forfeits the advantages of the polymorphic querying, but that's ok for my application. I can still get the same resuts, it just takes a few subqueries after I query one of the base classes. I've run a few tests with this setup and it seems to work - the crux is in the engineers.join(employees).join(citizens) table definition in the Engineer mapper and the ForeignKey constraints. I just wanted to put this out there to see if anyone notices any issues I am not anticipating or if this model might be helpful to someone in the future who wants to inherit from two independent base classes. I realize that the Engineers inheriting from Employees and Citi Sam Magister 2008-09-05T18:56:28 http://permalink.gmane.org/gmane.comp.python.sqlalchemy.user/15203 That's certainly the root cause, since each thread has it's own session in my config. Thanks! Alex On 5 сент, 21:59, Michael Bayer <mike...< at >zzzcomputing.com> wrote: --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy< at >googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscribe< at >googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~--- Alex K 2008-09-05T18:19:59 http://permalink.gmane.org/gmane.comp.python.sqlalchemy.user/15202 On Sep 5, 2008, at 1:48 PM, Alex K wrote: this mostly depends on how you've configured Session access. Your mappers, classes, and database engine should be configured at the application level only, not per request, and you should be using scoped_session() to manage one session per thread. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy< at >googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscribe< at >googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~--- Michael Bayer 2008-09-05T17:59:48 http://permalink.gmane.org/gmane.comp.python.sqlalchemy.user/15201 Hi All, I'm using sqlalchemy and mod_wsgi. mod_wsgi is set up to serve request in multiple threads of the same process. class looks like that: class WebSessionRoute(DeclarativeBase): __tablename__ = 'web_session_route' key = Column('key', Unicode(40), primary_key=True) user_id = Column('user_id',Integer, ForeignKey('user_route.id')), expires = Column('expires', DateTime) def __init__(self,key,user_id,expires): self.key = key self.user_id = user_id self.expires = expires I'm facing the following issue. The first request goes fine, but on the second attempt I get: query.py, line 1620, in __init__ raise sa_exc.InvalidRequestError("Invalid column expression '%r'" % column) on any attempt to execute any query. Instrumented attributes are in place, but they don't work. I've rewrote the code using regular mapper. If each thread maps the class to the table. everything is ok, Once I put the lock to make initialization only once per process, I get this error. Alex K 2008-09-05T17:48:50 http://permalink.gmane.org/gmane.comp.python.sqlalchemy.user/15200 the order_by set on the mapper() is stripped out when using get() with 0.5: import sqlalchemy as sa import sqlalchemy.orm as orm from sqlalchemy.ext.declarative import declarative_base import StringIO import logging buf = StringIO.StringIO() logging.basicConfig(stream=buf, format = '%(message)s') logging.getLogger('sqlalchemy.engine').setLevel(logging.INFO) Base = declarative_base(engine=sa.create_engine('sqlite://')) class Foo(Base): __tablename__ = 'foo' id = sa.Column(sa.Integer, primary_key=True) data = sa.Column(sa.String) __mapper_args__ = {'order_by':data} Base.metadata.create_all() sess = orm.create_session() buf.truncate(0) sess.query(Foo).filter(Foo.id==5).all() assert "ORDER BY foo.data" in buf.getvalue() buf.truncate(0) sess.query(Foo).get(5) assert "ORDER BY" not in buf.getvalue() --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email Michael Bayer 2008-09-05T15:23:01 Search the mailing list at Gmane query http://search.gmane.org/?group=$group=gmane.comp.python.sqlalchemy.user