http://blog.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://comments.gmane.org/gmane.comp.python.sqlalchemy.user/31820 <pre>I'd like to represent a Correspondent object, which can refer to two separate objects Corr_1 - id - email_address Corr_2 - id - email_address Correspondent - id - corr_1.id - corr_2.id But I have two problems: I'm using MySQL, so I can't use a check constraint to ensure that only one of the corr_*.id fields can be populated. Is it it a bad idea to enforce the constraint at the SQLAlchemy level, by doing if all(corr1.id, corr2.id) or not any(corr_1.id, corr2.id): raise … in my __init__? My second problem relates to then being able to retrieve the related .email_address attribute. My naïve relationship above obviously won't work for that. Any pointers would be greatly appreciated. </pre> Stephan Hügel 2012-05-25T20:53:11 http://comments.gmane.org/gmane.comp.python.sqlalchemy.user/31819 <pre>Hi, Currently working on a project that use SQL alchemy with mod_wsgi, webapp2 and prestans (python REST framework). The backend is Oracle XE using cx_Oracle. What is the appropriate way to manage sessions in a multithreading app such as this? Currently I have been following the guide found here&lt;http://docs.sqlalchemy.org/en/rel_0_7/orm/session.html#contextual-thread-local-sessions&gt; to implement a contextual/thread-local session. I have then made a session that is automatically cleaned up by the request handler. The code for this looks something like: def dispatch(self): self.db_session = db.Session() super(BaseHandler, self).dispatch() self.db_session.close() db.Session.remove() However this doesn't seem to clean up all database connections as viewed in netstat and I get a connections exhausted error from Oracle XE after a certain time of using the application. The same thing also happens when I use MySQL as well. If I add a call to db.engine.dispose() after the handler has re</pre> Bradley Mclain 2012-05-25T03:28:39 http://comments.gmane.org/gmane.comp.python.sqlalchemy.user/31815 <pre>Hi, For some reason, I need to access my sqlite database through an API which encapsulates the sqlalchemy stuff. In my api methods, the typical sequence is: 1. get a session from the session maker 2. query/commit the db using this session 3. return the queried/commited object and close the session From the caller side, the returned object may have expired but i don't care (I configure my session maker with expire_on_commit=False) This works fine, ie the object is detached from the db but immediate attributes are available from reading without any call to a hidden sqlalchemy refresh. If I want to access linked tables, I just implement it with joinedloads within my api and again the caller can read related attributes. The problem comes with inherited tables. I would like to be able to tell sqlalchemy to automatically load the child instead of loading the base class (and after issueing a refresh). Here's a sample code:http://pastebin.com/SyKmh9Ac Line 131 works fine since the requested ob</pre> pr64 2012-05-24T14:02:08 http://comments.gmane.org/gmane.comp.python.sqlalchemy.user/31808 <pre>Hello, I have hundreds of independent jobs on a cluster all writing entries to the same MySQL database table. Every time one job INSERTs, it locks the table, and the other jobs have to queue up for their turn. So at that point, the massively parallel cluster has turned into a massively serial cluster :-( What are routes available in SQLAlchemy to improve this situation? Thanks! Jeff </pre> Jeff 2012-05-23T20:19:11 http://comments.gmane.org/gmane.comp.python.sqlalchemy.user/31801 <pre>Did anyone encounterred this problem? In my framework, I'll cache the first connection in the thread local variable, so that I can result them later. There are some command line tools can do the sql process. But if I use the version 0.6.7 of SQLA I'll get an Exception after the command finished, the Exception is just like: Exception TypeError: "'NoneType' object is not callable" in &lt;function &lt;lambda&gt; at 0x02E694B0&gt; ignored So I tried to close the thread local cached connection object and set it to None, there is no Exception any more after the command finished. And I've also upgrade my SQLA version to 0.7.7, and even I didn't clean up the thread local cached connection, the is no Exception at all. I want to know are there some differences between the two versions about the connection process at the end of program. Thanks. </pre> limodou 2012-05-23T14:01:55 http://comments.gmane.org/gmane.comp.python.sqlalchemy.user/31799 <pre>Hi, Using SQLAlchemy 0.7.7 with an underlying sqlite databse, we configured the engine with poolclass=SingletonThreadPool and pool_size=50 Our unit-tests were working fine. But when running the app, some (ProgrammingError) Cannot work on a closed database occured. Reading the docs and mailist archives, we tried to switch to NullPool which is the advised poolclass for sqlite. It broke all our unit-test raising '(OperationalError) no such table: ...blabla...' Here after you will find our code. An OrmManager is instancied and we call the get_session() method on it when we want to make some db stuff. Here is the one which makes the unit-test pass but raises an occasional '(Programming Error): Cannot operate on a closed database' when running our app: import sqlite3 from sqlalchemy import * from sqlalchemy.orm import * from sqlalchemy.pool import SingletonThreadPool, NullPool import centralunit.core.models import centralunit.core.models.meta class OrmManager: """ OrmManager class Handles the d</pre> pr64 2012-05-23T09:04:31 http://comments.gmane.org/gmane.comp.python.sqlalchemy.user/31794 <pre>Hi All, I have this relation: # Users user_table = Table('user', self.metadata, Column('id', Integer, primary_key=True), Column('place_id', Integer), mysql_engine='InnoDB' ) # Places places_table = Table('places', self.metadata, Column('id', Integer, primary_key=True), mysql_engine='InnoDB' ) mapper(User, user_table,properties={ 'user' : relation(Place, primaryjoin=(user_table.c.place_id==places_table.c.id), foreign_keys=[places_table.c.id]), } ) mapper(Place, places_table, properties={ 'id':places_table.c.id, } ) A /Place/ can be associated with multiple users. I fetch a user as so: query = session.query(User).options( eagerload('place')).\ filter_by(id=id) user = query.one() If there's a place associated with the user I end up with : user.place[], where user.place[0] is the first place, etc. Now, I want to remove the relation be</pre> Michael Wilson 2012-05-23T02:35:50 http://comments.gmane.org/gmane.comp.python.sqlalchemy.user/31793 <pre>I have this relation: # Users user_table = Table('user', self.metadata, Column('id', Integer, primary_key=True), Column('place_id', Integer), mysql_engine='InnoDB' ) # Places places_table = Table('places', self.metadata, Column('id', Integer, primary_key=True), mysql_engine='InnoDB' ) mapper(User, user_table,properties={ 'user' : relation(Place, primaryjoin=(user_table.c.place_id==places_table.c.id), foreign_keys=[places_table.c.id]), } ) mapper(Place, places_table, properties={ 'id':places_table.c.id, } ) A *Place* can be associated with multiple users. I fetch a user as so: query = session.query(User).options( eagerload('place')).\ filter_by(id=id) user = query.one() If there's a place associated with the user I end up with : user.place[], where user.place[0] is the first place, etc. Now, I want to rem</pre> ThereMichael 2012-05-23T04:10:33 http://comments.gmane.org/gmane.comp.python.sqlalchemy.user/31786 <pre>Hi, I had a lot of trouble finding a decent set of examples on how to write a mixin that creates a class/table derived from a class. I've finally come up with a pattern that I'm pretty happy with an seems to fit most use cases that I saw when I was googling. I extracted, simplified, and commented an example, but I didn't see how to submit it for consideration for use the in the documentation. Please see attached. ~David Bowser </pre> David Bowser 2012-05-22T18:28:18 http://comments.gmane.org/gmane.comp.python.sqlalchemy.user/31781 <pre>Hi, Has anyone used sqlalchemy and azure at any point? I may need to work with azure shortly... Thanks! Damian </pre> Damian Dimmich 2012-05-21T13:09:12 http://comments.gmane.org/gmane.comp.python.sqlalchemy.user/31771 <pre>Hi all, I'm writing some tests for my SqlAlchemy DB using the fixtures module, and I'm currently trying to populate a one to many relationship. http://farmdev.com/projects/fixture/using-dataset.html#referencing-foreign-dataset-classes We have a relationship between Author and Address : (very much like the SA doc) ------------ model.py --------------- class Address(Base): author_id = Column(Integer, ForeignKey('author.id')) author = relationship("Author") ------------------------------------ Then I defined some data (fixture specific) : ------------ modelData.py -------------- class AuthorDate(DataSet): class foo: name = "foo" class AddressData(DataSet): class my_address: name = "my address" author_id = AuthorData.foo.ref('id') author = [AuthorData.foo,] # this is handy with the fixtures ------------- Then we have a file with the tests : -------- testAuthor.py import model from modelData import * # call to __init__.py and Base.metadata.bind = engine, session</pre> Vinz 2012-05-18T16:08:03 http://comments.gmane.org/gmane.comp.python.sqlalchemy.user/31762 <pre>Suppose I am using single table polymorphic inheritance like the docs Employee/Manager/Engineer example. I have a relationship that I want to have a different cascade or loading strategy for, depending on the mapper. Can I inherit from the base mapper and override the property, like this: employee_mapper = mapper(Employee, employees_table, \ polymorphic_on=employees_table.c.type, polymorphic_identity='employee', properties={'customer':relationship(Customer, lazy=False, cascade='save-update,refresh-expire,merge')}) manager_mapper = mapper(Manager, inherits=employee_mapper, polymorphic_identity='manager', properties={'customer':relationship(Customer, lazy=True, cascade='save-update,refresh-expire')}) engineer_mapper = mapper(Engineer, inherits=employee_mapper, polymorphic_identity='engineer', properties={'customer':relationship(Customer, lazy=True, cascade='save-update,refresh-expire')}) Here, the 'customer' rela</pre> Kent 2012-05-17T21:00:20 http://comments.gmane.org/gmane.comp.python.sqlalchemy.user/31758 <pre>Hey guys! Which way you take to map some custom data (not in database) with sqlalchemy? In django it will be: *GENDER_CHOICES = ( ('M', 'Male'), ('F', 'Female'), ) class Foo(models.Model): gender = models.CharField(max_length=1, choices=GENDER_CHOICES**)* I saw an implamantion of it using types in http://stackoverflow.com/questions/6262943/sqlalchemy-how-to-make-django-choices-using-sqlalchemy: *import** sqlalchemy.types as types class ChoiceType(types.TypeDecorator): impl = types.String def __init__(self, choices, **kw): self.choices = dict(choices) super(ChoiceType, self).__init__(**kw) def process_bind_param(self, value, dialect): return [k for k, v in self.choices.iteritems() if v == value][0] def process_result_value(self, value, dialect): return self.choices[value]* Is it alright? Or you guys do it another way? Thanks a lot! </pre> Artur Felipe Sousa 2012-05-17T16:07:40 http://comments.gmane.org/gmane.comp.python.sqlalchemy.user/31743 <pre></pre> Vlad K. 2012-05-17T11:33:34 http://comments.gmane.org/gmane.comp.python.sqlalchemy.user/31739 <pre>(using sqlalchemy 0.8.0b1) Using the new 8.0 inspection mechanism, I can perform the following on one of my model classes in an ipython or bpython shell and obtain a boolean result: True But it seems I can only do this successfully in a shell. When the same line of code is encountered at runtime, I get the following error: AttributeError: '_Label' object has no attribute 'nullable' Further info: this is in a shell initiated with respect to my Pyramid project by invoking "pshell -p bpython development.ini". What I'm actually trying to do: got some mako templates generating HTML forms from my model classes, and just want to have non-nullable fields generate the "required" keyword in my input elements. Perhaps there is another better way to reach the 'nullable' property of a column? Fishing for an easy answer here, but if none is forthcoming I'll knuckle down and build a testcase. </pre> Eric Ongerth 2012-05-17T10:08:21 http://comments.gmane.org/gmane.comp.python.sqlalchemy.user/31732 <pre>Hello everybody! I would like to know if it's possible to create an optimized query that gives all the elements of a class A that have a relationship to an specific class B Here's the thing: I have a pretty simple class "Region" that defines an area in the screen: class Region(declarativeBase): __tablename__ = "regions" id = Column("id", Integer, primary_key=True, key="id") x = Column("x", Integer) y = Column("y", Integer) width = Column("width", Integer(unsigned=True)) height = Column("height", Integer(unsigned=True)) And then a class Layout that can contain (or point to?) a number of these regions depending on what they're used for: class Layout(declarativeBase): __tablename__ = "layouts" id = Column("id", Integer, primary_key=True, key="id") width = Column("width", Integer) height = Column("height", Integer) logoRegionId = Column("logo_region_id", Integer, ForeignKey("regions.id"), key="logoRegionId") logoRegion = relationship(Region.Region, uselist=False, primaryjoi</pre> Hector Blanco 2012-05-16T21:50:08 http://comments.gmane.org/gmane.comp.python.sqlalchemy.user/31717 <pre> Hello, I have got a query of the following type: rows = session.query(*[func.count().over().label("count")]+map(lambda column: MyClass.__dict__[columns],columns)).filter(...).limit(n).offset(m).all() it returns the number of results together with values of selected columns. The problem is when I try to apply the distinct query I see that func.count().over().label("count") does not return correct results, that is, it returns the number of results for the query by which the distinct function is omitted. Is there any workaround for this problem? The query contains no joins. Thank you ED </pre> Eduardo 2012-05-16T10:07:26 http://comments.gmane.org/gmane.comp.python.sqlalchemy.user/31715 <pre>Recently I found a wired problem in my application, something like this: I have a long time deamon program, it'll be an infinite loop, just like: engine = create_engine('...') conn = engine.connect() while True: for row in select: update sleep(xxx) In the loop, I create conn at first, and then reuse it in the loop. If there is data in database, it'll fetch all matched records and update the flag, then do the next loop. So if there is no matched records, it'll just sleep and do nothing. The problem is, because I used the shared connection object, so if there is no matched records for a while, and even I changed the database to make some records, it'll not fetch the records at all. And if I restart the program it'll get them at the first time, then if I changed the database, it'll not fetch the records again. But If I put the conn creation in the loop, it seems everything is ok. And if I tried to use strategy='threadlocal', and get the conn like: conn = engine.contextual_connect(), even I p</pre> limodou 2012-05-16T00:13:15 http://comments.gmane.org/gmane.comp.python.sqlalchemy.user/31708 <pre>Shouldn't the select() method be included in the Table class documentation? The following exception shows where I expected it and can be seen online here: http://docs.sqlalchemy.org/en/latest/core/schema.html#sqlalchemy.schema.Table.get_children. =========================== get_children(column_collections=True, schema_visitor=False, **kw) key tometadata(metadata, schema=&lt;symbol 'retain_schema&gt;) =========================== Rodney </pre> Rodney Barnett 2012-05-15T12:56:27 http://comments.gmane.org/gmane.comp.python.sqlalchemy.user/31707 <pre>Background: I'm recently new to SQLA but not new to data access layers/object relational mapping. Problem: I'm trying to alias a subquery in sqlachemy in Oracle but the AS keyword gets added to the alias, which is not Oracle compatible. Oracle 10.02.03 cx_Oracle 5.0.4 sqlalchemy: 0.7.5 Given a simple table: foo = Table('foo', metadata, Column('id', types.Integer, nullable=False, primary_key=True), Column('name_txt', types.String(32), nullable=True, primary_key=False), ) class Foo(CustomBase): __table__ = foo __fields__ = ["id", "name"] id = foo.c.id name = foo.c.name_txt from sqlalchemy import create_engine from sqlalchemy.orm import sessionmaker engine = create_engine('oracle+cx_oracle://tc:tc&lt; at &gt;opc',echo=True,use_ansi=False) # tried both True and False Session = sessionmaker(bind=engine) session = Session() f = session.query(Foo).subquery().alias('f') print session.query(f) SELECT f.name_txt AS f_name_txt, f.id AS f_id FROM (SELECT foo.name_txt AS name_txt, foo.id AS id FROM foo) *AS* f What </pre> Karl 2012-05-14T21:33:56 http://comments.gmane.org/gmane.comp.python.sqlalchemy.user/31705 <pre>I'm use the Informix dialog with SQLalchemy 0.7.7 (the latest 0.7.x release). It works. But now I'm getting this error - sqlalchemy.exc.ProgrammingError: (ProgrammingError) SQLCODE -201 in PREPARE: 42000: Syntax error or access violation 'SELECT xrefr.xr_serial_no AS xrefr_xr_serial_no, xrefr.xr_stock_no AS xrefr_xr_stock_no, xrefr.xr_desc AS xrefr_xr_desc, xrefr.xr_oem_code AS xrefr_xr_oem_code, xrefr.xr_oem_partcode AS xrefr_xr_oem_partcode, xrefr.xr_vend_code AS xrefr_xr_vend_code, xrefr.xr_vendr_part_code AS xrefr_xr_vendr_part_code, xrefr.xr_vendr_stock_no AS xrefr_xr_vendr_stock_no, xrefr.xr_min_qty AS xrefr_xr_min_qty, xrefr.xr_weight AS xrefr_xr_weight, xrefr.xr_quality_code AS xrefr_xr_quality_code, xrefr.xr_net_price AS xrefr_xr_net_price, xrefr.xr_list_price AS xrefr_xr_list_price, xrefr.xr_supersede AS xrefr_xr_supersede, CASE WHEN (xrefr.xr_supersede = :1) THEN :2 ELSE :3 END AS anon_1 \nFROM xrefr \nWHERE xrefr.xr_oem_code = :4 AND xrefr.xr_vend_code = :5 AND CASE WHEN (xrefr.xr_supersede =</pre> Adam Tauno Williams 2012-05-15T15:36:18 Search the mailing list at Gmane query http://search.gmane.org/?group=$group=gmane.comp.python.sqlalchemy.user