gmane.comp.python.sqlalchemy.user

Re: Combine attributes from two objects in parent object

Michael Bayer — 2012-05-25T23:36:54

On May 25, 2012, at 4:53 PM, Stephan Hügel wrote:


Assuming corr_1 and corr_2 are two separate tables, if you have control over the schema of this application and you wish to have "email_address" be unique across the union of these two tables, the best practice here would be to have only one table of email addresses.   Otherwise you have to make due with what you're given.



the general idea is fine though I think you mean something more like this (note I'm using the exclusive or operator here though the all/any() approach would work too):

class Correspondent(Base):
    def __init__(self, corr_1, corr2):
        assert (corr_1 is None) ^ (coor_2 is None)

the important part is that when you create objects, the primary key/foreign key attributes like "id" and such aren't populated until a flush occurs, so we deal with references to the objects themselves.



usually < at >property achieves this:

class Correspondent(Base):
    < at >property
    def email_address(self):
        return self.corr_1.email_address if

Re: Session management with mod_wsgi and webapp2

Michael Bayer — 2012-05-25T23:31:55

On May 24, 2012, at 11:28 PM, Bradley Mclain wrote:


scoped_session produces a session factory/proxy object which maintains a single Session per thread, and as the guide indicates you can allow this factory to automatically create a Session on first use, then at request end time you do an unconditional remove() so that the scoped_session closes the Session it may or may not be handling, and disposes of it.



db.Session.remove() should be enough here, it calls close() for you.



The Session uses an Engine as a source of connections.   The Engine is introduced at http://docs.sqlalchemy.org/en/rel_0_7/core/engines.html .    Intrinsic to its behavior is that it uses a small connection pool which by default holds onto five connections out of the total accumulated.  Full information on how this pooling is configured or disabled is at http://docs.sqlalchemy.org/en/rel_0_7/core/pooling.html.   


one of the many options here is the "pool_recycle" option which places a time limit on the age of connections.



I'm

Combine attributes from two objects in parent object

Stephan Hügel — 2012-05-25T20:53:11

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.

Session management with mod_wsgi and webapp2

Bradley Mclain — 2012-05-25T03:28:39

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<http://docs.sqlalchemy.org/en/rel_0_7/orm/session.html#contextual-thread-local-sessions> 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

Re: Get 'full' child when querying inherited tables

pr64 — 2012-05-24T15:34:50

Worked perfectly. Thanks a lot for your quick answer and so great software.

Le jeudi 24 mai 2012 16:45:50 UTC+2, Michael Bayer a écrit :

Re: Oracle CLOB: empty field

FrancescaL — 2012-05-24T15:07:23

Belatedly... Upgrade successfully completed, thanks to your recipe!
Francesca


On 16 Mag, 15:36, Michael Bayer <mike...< at >zzzcomputing.com> wrote:

Re: Get 'full' child when querying inherited tables

Michael Bayer — 2012-05-24T14:45:50

On May 24, 2012, at 10:02 AM, pr64 wrote:



use the with_polymorphic() method of Query described at http://docs.sqlalchemy.org/en/rel_0_7/orm/inheritance.html#basic-control-of-which-tables-are-queried .   0.8 will have a more capable and flexible version of this function.

Get 'full' child when querying inherited tables

pr64 — 2012-05-24T14:02:08

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

Re: Re: NullPool for sqlite (Operational error: no such table)

pr64 — 2012-05-24T13:48:33


Ok thanks Michael. We found a work around. We are now closing sessions once 
the object is retriewed from database since we don't care if it is not 
synchronized with fresh data.
I will post another topic since it does not seem to work with inherited 
tables.

Re: Re: Efficient Inserting to Same Table Across 100s of Processes

Michael Bayer — 2012-05-24T12:51:31

im not sure about the autoincrement, though MySQL is known for pretty high performance so I'm sure whatever technique it uses there is very efficient, if it isn't maintaining an internal sequence-like-object, it's at the very least just consulting the index and shouldn't be detectable as a "lock" on the table.   I'm just guessing though.

I don't really have familiarity with the concurrent_inserts setting, I just found it on their docs.   If I were you I'd prepare test cases that illustrate how you are seeing that inserts are being serialized (not using SQLAlchemy, since we'd get blamed for things, make it use mysql-python only, if even that) and then take it to a MySQL-specific forum.


On May 23, 2012, at 6:17 PM, Jeff wrote:

Re: Error "object has no attribute '_sa_instance_state'" - one to many (with fixtures)

Vinz — 2012-05-24T08:39:31

OK, the "_sa_instance_state" message was kind of cryptic, but it
actually contained accurate information (that the problem came from my
list, badly configured).
Thanks !

On May 18, 11:03 pm, Michael Bayer <mike...< at >zzzcomputing.com> wrote:

Re: Efficient Inserting to Same Table Across 100s of Processes

Jeff — 2012-05-23T22:17:58

Thanks for the help and links! One additional data point: The table
has an id field that autoincrements. A friend thought that might be a
barrier to non-locking inserts, but wasn't sure. I'm having difficulty
finding any resource explicitly saying that, though, and simply trying
it would be costly.

The described behavior for MySIAM's concurrent_inserts setting appears
to be the behavior I'm describing (queued inserts). Am I missing some
detail? I actually found that documentation earlier today, and had
assumed that the inserts that are "queued and performed in sequence"
would result in the inserting process (the cluster's jobs) waiting
until they're done in the queue. Which, of course, is what's happening
in my case.

Now I'll attempt to parse the rest of your comments :-) Please let me
know if the above paragraphs are accurate, though.

Thanks!

On May 23, 5:20 pm, Michael Bayer <mike...< at >zzzcomputing.com> wrote:

Re: Re: Efficient Inserting to Same Table Across 100s of Processes

Michael Bayer — 2012-05-23T21:20:13

My initial thought is that INSERTs shouldn't be locking the whole table, at least not throughout a whole transaction.      There's some MySQL hints that can help with this, if you're on MyISAM take a look at http://dev.mysql.com/doc/refman/5.0/en/concurrent-inserts.html , possibly using the "concurrent_inserts" setting, as well as some of the INSERT hints specific to MySQL:  http://dev.mysql.com/doc/refman/5.5/en/insert.html .    On InnoDB it doesn't lock the whole table unless you do so explicitly.

SQLAlchemy is just a tool to help emit messages to the database.  You'd need to figure out what scheme you want to use to improve your INSERT throughput first, then apply that strategy through SQLAlchemy's API.   It might be as easy as switching to InnoDB, not sure.

If you're looking to serialize inserts after the fact, you can use a message queue for that, such as a Celery process.    This might be the way you want to go if you get large bursts of data with quiet periods, and it's OK that the MySQL database re

Re: Efficient Inserting to Same Table Across 100s of Processes

Jeff — 2012-05-23T20:56:49

More data:
A typical not-quite-worst-but-in-the-class-of-worst case scenario is a
half a million rows per insert. Absolute worst case scenarios could be
10 times that. So that insert will take awhile.

Would there be any logic to breaking up all the inserts into one row
per insert? Would that allow independent jobs to meaningfully
interleave their inserts? Does the overhead of sqlalchemy's session
adding and committing not really work that way?

One uglier route that has been suggested to me is to first write all
the rows to independent local files, then later merge and insert them.
Does SQLAlchemy have functionality that might help in this direction?

On May 23, 4:19 pm, Jeff <jeffalst...< at >gmail.com> wrote:

Efficient Inserting to Same Table Across 100s of Processes

Jeff — 2012-05-23T20:19:11

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

Re: Re: NullPool for sqlite (Operational error: no such table)

Michael Bayer — 2012-05-23T16:26:26

On May 23, 2012, at 11:27 AM, pr64 wrote:


The tables not existing points to an extremely basic issue like one bit of code talking to one database, and another bit of code talking to another, mistakenly assuming they are the same file.   There's nothing in the snippet of code that has any bearing on an issue like this.    Only if you can provide a short test case that reproduces the error, or if you can identify the problem by carefully debugging your program by watching the rendered SQL and stepping through, ensuring the correct database is in use at all times, can this problem be solved.

Re: Re: NullPool for sqlite (Operational error: no such table)

pr64 — 2012-05-23T15:27:59

Operational error: no such table. I'm not familiar with low level 
implementation of db layers (that's why i found sqlalchemy so useful in 
that it encapsulates the details). Our code just creates one instance of 
the OrmManager and then everytime a db access is needed, we ask it a 
session for that.

Example code is:

orm = OrmManager(....)
session = orm.get_session()
my_obj = session.query(....)
my_obj.attribute = 'new value'
session.commit()


I can see an issue here, talking to you. We used the SingleThreadedPool 
with pool_size=50. But looking at our code, it seems that we never free the 
created sessions (session.close() ). Are they garbage collected ? Do we 
need to explicitly call session.close() ? 
The problem occurs when multiple access are done, we may use all the 
sessions available before the garbage collector runs...

Re: Re: NullPool for sqlite (Operational error: no such table)

Michael Bayer — 2012-05-23T14:39:22

On May 23, 2012, at 10:34 AM, pr64 wrote:


StaticPool can't be used for a multithreaded application, a connection can only be used in one thread at a time.   pysqlite specifically should be even preventing that connection from being used in any thread in which it wasn't created.   If you're using a file based database, you should be on NullPool - pooling for SQLite isn't really appropriate.

Re: Thread local connection garbage collection problem

limodou — 2012-05-23T14:36:58

On Wed, May 23, 2012 at 10:35 PM, Michael Bayer
<mike_mp< at >zzzcomputing.com> wrote:

Oh, I see. Thanks.

Re: Thread local connection garbage collection problem

Michael Bayer — 2012-05-23T14:35:47

There's some cleanup code inside of weakref callbacks which has been improved in 0.7 to not complain about unavoidable exceptions during Python's teardown of object state.    


On May 23, 2012, at 10:01 AM, limodou wrote:

Re: NullPool for sqlite (Operational error: no such table)

pr64 — 2012-05-23T14:34:15


On May 23, 3:46 pm, Michael Bayer <mike...< at >zzzcomputing.com> wrote:
Running on a linux sever, the unit test use the code above
(create_engine('sqlite:///' + database, ...) ) but the database dir
storage is /dev/shm which is a ram directory, but i wonder sqlite does
not even know that it is a ram disk....

Anyway, the '(Programming Error): Cannot operate on a closed database'
occurs on our real app (same code: create_engine('sqlite:///' +
database, ...) ) which runs on a normal dir (hard disk).

We tried to switch from SingletonThreadPool (which raises errors,
sometimes) to StaticPool... we'll see what happens. The problem is
that this error is not systematically raised and it seems that it is
raised when multiple threads are accessing the db.