gmane.comp.python.sqlalchemy.user

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 object is a the base class object

Line 133 does not work since the requested object is a child class object

How can I configure sqa to be able to get the whole child without having 
later refreshes ?

Thanks a lot for your help,

Pierre

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 receives all the data "eventually".


On May 23, 2012, at 4:56 PM, Jeff wrote:

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.

Thread local connection garbage collection problem

limodou — 2012-05-23T14:01:55

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 <function
<lambda> at 0x02E694B0> 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.

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

Michael Bayer — 2012-05-23T13:46:57

On May 23, 2012, at 5:04 AM, pr64 wrote:


this sounds like you're using a :memory: database, if the effects of CREATE TABLE are being lost on a subsequent checkout.   NullPool can't be used with a memory database, since the entire database lasts for the duration of a single connection.   You'd need to stick with SingletonThreadPool or even StaticPool for that.

If this is in fact a file based database, I don't see anything obviously wrong with the code I'm seeing so the issue probably lies amongst more of the total picture of the fixtures + tests + configuration.   If the file is left over, you can look into it with the sqlite3 commandline tool to see that it contains the expected state, perhaps interactively as you pause the tests with inline pdb.set_trace() statements.

NullPool for sqlite (Operational error: no such table)

pr64 — 2012-05-23T09:04:31

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 database and provides an abstraction layer for it
    """

    def  __init__(self,  database, metadata,  echo=False):
        self.database = database
        self.session_maker = sessionmaker()
        engine = create_engine('sqlite:///' + database, echo=echo,
                connect_args={'detect_types': sqlite3.PARSE_DECLTYPES|
                              sqlite3.PARSE_COLNAMES},
                native_datetime=True,
                poolclass=SingletonThreadPool,
                pool_size=50
                )

        metadata.create_all(engine)
        self.session_maker.configure(bind=engine)

    def get_session(self):
        """Gets ORM session"""
        return self.session_maker()


and here is the one which makes the unit-test fail but should avoid
the 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, StaticPool

import centralunit.core.models
import centralunit.core.models.meta

class OrmManager:
    """
    OrmManager class

    Handles the database and provides an abstraction layer for it
    """

    def  __init__(self,  database, metadata,  echo=False):
        self.database = database
        self.session_maker = sessionmaker()
        engine = create_engine('sqlite:///' + database, echo=echo,
                connect_args={'detect_types': sqlite3.PARSE_DECLTYPES|
                              sqlite3.PARSE_COLNAMES},
                native_datetime=True,
                poolclass=NullPool,
                )

        metadata.create_all(engine)
        self.session_maker.configure(bind=engine)

    def get_session(self):
        """Gets ORM session"""
        return self.session_maker()

We would like to switch to NullPool... Any idea to avoid the 'no such
table' error ?

Thanks a lot for your help,

Pierre

Re: Tutorial/Example Code

Michael Bayer — 2012-05-23T06:49:15

A better idea, still thinking about it, just using __mapper_cls__ to intercept when the mapping is about to be created.    This is probably the most appropriate place for this to happen - as the Table is being sent right to mapper(), we can respond to the Table and add more things to the properties dictionary.    Though I'm not crazy about the super call at the bottom.

class HasAddresses(object):
    """HasAddresses mixin, creates a new Address class
    for each parent.
    
    """

    < at >classmethod
    def __mapper_cls__(cls, klass, table, properties, **kw):
        remote_cols = dict(
            ("remote_%s" % c.name,
            Column(c.type))
            for c in table.primary_key
        )
        constraint = ForeignKeyConstraint(
            list(remote_cols),
            [c for c in table.primary_key]
        )
        clsdict = dict(
                __tablename__ = "%s_address" % 
                            cls.__tablename__,
                __table_args__ = (constraint,)
        )
        clsdict.update(remote_cols)
        cls.Address = type(
            "%sAddress" % cls.__name__,
            (Address, Base,),
            clsdict
        )
        properties['addresses'] = relationship(cls.Address, 
                                        backref="parent")
        return getattr(
                super(HasAddresses, cls), 
                '__mapper_cls__', 
                mapper)(klass, table, properties, **kw)