gmane.comp.python.sqlalchemy.user

SQLite synchronous on engine

pr64 — 2013-06-19T14:14:25

Hi,

In order to improve the underlying SQLite performance, I've changed some 
low level settings with PRAGMA commands:

PRAGMA synchronous=NORMAL; /* instead of default FULL value, see: 
http://www.sqlite.org/pragma.html#pragma_synchronous */
PRAGMA journal_mode=WAL;       /* 
http://www.sqlite.org/pragma.html#pragma_journal_mode and 
http://www.sqlite.org/wal.html */

From an implementation point of view, I did as explained in this thread: 
https://groups.google.com/forum/?fromgroups#!topic/sqlalchemy/IY5PlUf4VwE. I've 
got an OrmManager class (which is a singleton) which is used to get new 
sessions. *The bold lines are the ones I added to improve performance*.


class OrmManager:
    """
    OrmManager class

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

    def  __init__(self, database, metadata, db_type, echo=False):
        self.database = database
        self.session_maker = sessionmaker()

        if db_type == 'file':
            engine = create_engine('sqlite:///' + d

The problem with Oldest transaction (Firebird)

graf — 2013-06-18T22:09:40

I use SQLAlchemy inside Tornado, and I use a singleton to make the Firebird 
connection inside application.

class FirebirdDatabase(object):

    def __init__(self, firebird_params, echo=False):
        engine = 
create_engine('firebird+fdb://%(user)s:%(password)s< at >%(host)s:%(port)s/%(path)s' 
% firebird_params,
                               echo=echo,
                               echo_pool=echo,
                               pool_recycle=3600,
                               convert_unicode=True,
                               encoding='cp1251')
        self._session = sessionmaker(bind=engine)

    _instance = None

    def __new__(cls, *args, **kwargs):
        if not cls._instance:
            cls._instance = super(FirebirdDatabase, cls).__new__(cls, 
*args, **kwargs)
        return cls._instance

    def get_session(self):
        return self._session()


After a while the Oldest transaction begin blocking and this slow down the 
database speed.

To commit changes I use:

try:
    self.Session.commit(

Re: The problem with the update

Witold Greń — 2013-06-18T21:55:54

This is an interesting question :)
Description: Looking for a project in which students (ManyToMany) to 
address (OneToOne) with the street dying text "Jana" and delete this row.

subq = 
session.query(Addressess.id).filter(Addressess.street.like('%Jana%')).subquery()
session.query(Projects).filter(Projects.students.any(Person.addressess_id.in_(subq))).delete(synchronize_session='fetch')
session.commit()

Re: The problem with the update

Witold Greń — 2013-06-18T21:14:51

Resolverd no. 5:

session.query(Projects).filter(Projects.students.any(and_(Person.gender == 
False, Person.name.like('M%') )))\
    .update({Projects.name: u'New project name', Projects.supervisor_id: 
1}, synchronize_session=False)



Again many thanks Michael :)

Re: The problem with the update

Michael Bayer — 2013-06-18T21:08:23

On Jun 18, 2013, at 4:46 PM, Witold Greń <witold.gren< at >gmail.com> wrote:



any() does the subquery correlated to the related table for you:

s.query(Projects).filter(Projects.students.any(and_(Person.gender == False, Person.name.like('x')))).count()

Re: The problem with the update

Witold Greń — 2013-06-18T20:46:29

Thanks for your help Michael :)

Resolved:
No.2 

subq = session.query(Person.group_leader_id).filter(or_(Person.name == 
'Jan', Person.surname == 'Nowak')).subquery()
        
session.query(Person).filter(Person.id.in_(subq)).update({Person.name: 
u'Łukasz'}, synchronize_session=False)

# All support but not SQLite:
# session.query(Person).join(Person.group_leader, aliased=True).\
#     filter(or_(Person.name == 'Jan', Person.surname == 'Nowak')).
#     update({Person.name: u'Łukasz'}, synchronize_session=False)


No. 3
subq = 
session.query(Person.price_id).filter(Person.name.like('J%')).subquery()
        
session.query(Price).filter(Price.id.in_(subq)).update({Price.extra: 
20000}, synchronize_session=False)

# All support but not SQLite:
# 
session.query(Price).join(Person).filter(Person.name.like('J%')).update({Price.extra: 
20000}, synchronize_session=False)


No.4
subq = 
session.query(Person.addressess_id).filter(Person.surname.like('%k'))\
            .filter(sqlalchemy.not_(Person.surname.like('

Re: The problem with the update

Michael Bayer — 2013-06-18T19:13:50

if the database you're using doesn't support UPDATE..FROM (which is likely), the normal way is to UPDATE with a subquery, which may be correlated or in a simple case like this just using IN:

UPDATE price SET extra=20000 WHERE price.id in (select price_id from person where name like 'J%')


subq = session.query(Person.price_id).where(Person.name.like('J%')).subquery()
session.query(Price).filter(Price.id.in_(subq)).update({Price.extra: 20000}, synchronize_session=False)



On Jun 18, 2013, at 2:01 PM, Witold Greń <witold.gren< at >gmail.com> wrote:

Re: between .one() and .first()

ian marcinkowski — 2013-06-18T18:30:04

From the docs:
"one()

Return exactly one result or raise an exception.

Raises sqlalchemy.orm.exc.NoResultFound if the query selects no rows.

Raises sqlalchemy.orm.exc.MultipleResultsFound if multiple object
identities are returned, or if multiple rows are returned for a query
that does not return object identities."

Thus, you could:

try:
  return query.one()
  print ('Yay! One result!')
except NoResultFound:
  # Deal with case of zero results
  print('Zero results is also good!')
except MultipleResultsFound:
  # Deal with case of >1 results
  print('This should not happen. :(')



On Mon, Jun 17, 2013 at 1:57 PM, Petr Viktorin <encukou< at >gmail.com> wrote:

Re: The problem with the update

Witold Greń — 2013-06-18T18:01:22

I try resolved problem used this code:

No 5:

sql = session.query(Projects).join(Person, 
Projects.students).filter(Person.gender==False, Person.name.like('M%'))

for projects in sql:
    projects.supervisor_id = 1

session.commit()

but it is probably not a very efficient way?





W dniu poniedziałek, 17 czerwca 2013 21:09:18 UTC+2 użytkownik Witold Greń 
napisał:

Re: sqlite in memory

Richard Gerd Kuesters — 2013-06-17T19:29:50

Petr, just found something intesting that may work for anyone:

http://blog.marcus-brinkmann.de/2010/08/27/how-to-use-sqlites-backup-in-python/

I was able do create a database totally in memory and them just "dump" 
it to a file. The process now takes about 5 minutes, against one hour+ 
that it took earlier.

Cheers :)
Richard.

On 06/17/2013 01:37 PM, Richard Gerd Kuesters wrote:

The problem with the update

Witold Greń — 2013-06-17T19:09:18

Hi, I'm Witold and learn Sqlalchemy :)
I have a little problem, here it is:

This is my DB:

class Addressess(Base):

    __tablename__ = 'addressess'

    id = Column('id', Integer, Sequence('address_id_seq'), 
primary_key=True, index=True, unique=True, nullable=False)
    street = Column('street', String(200))
    house_no = Column('house_no', Integer())
    place = Column('place', String(200))
    postal_code = Column('postal_code', String(200))
    post = Column('post', String(200))

    def __init__(self, street, house_no):
        self.street = street
        self.house_no = house_no

    def __repr__(self):
       return u'%s, %s, %s' % (self.place, self.street, self.house_no)


class Price(Base):

    __tablename__ = 'prices'

    id = Column('id', Integer, Sequence('price_id_seq'), primary_key=True, 
index=True, unique=True, nullable=False)
    price = Column('price', String(200))
    extra = Column('extra', String(200), nullable=True)

    def __init__(self, price, extra=None):
        self.price =

Re: between .one() and .first()

Petr Viktorin — 2013-06-17T17:57:26

Simply handling NoResultFound should work just fine...

def zero_or_one(query):
    try:
        return query.one()
    except NoResultFound:
        return None

On Mon, Jun 17, 2013 at 6:36 PM, Michael Bayer <mike_mp< at >zzzcomputing.com> wrote:

Re: between .one() and .first()

Charlie Clark — 2013-06-17T17:47:56

Am 17.06.2013, 08:58 Uhr, schrieb Chris Withers <chris< at >simplistix.co.uk>:



.count() would seem to be your friend here, at least in case that a lot of  
rows might be returned.


Only if you can express that need as a query.

Charlie

Re: Unique constraints disappearing

Michael Bayer — 2013-06-17T16:39:25

On Jun 15, 2013, at 4:32 PM, Rj Ewing <ewing.rj< at >gmail.com> wrote:


unique constraints are not currently reflected.    This feature was just added in master and is currently set for release in 0.9.0.

Re: SQLAlchemy 0.8.1 + fdb, row size

Michael Bayer — 2013-06-17T16:38:24

On Jun 17, 2013, at 9:44 AM, graf <a.zhabotinskiy< at >gmail.com> wrote:



this would be a difference in how they handle unicode.   I've run tests for fdb here against unicode values and haven't observed that issue.

its possible fdb is doing the right thing here.   You should carefully look at how non-ASCII characters are being placed in that column both with kinterbasdb and with fdb.

Re: sqlite in memory

Richard Gerd Kuesters — 2013-06-17T16:37:01

Hmmm, never knew of that. All my "constraints" are already predefined 
and inserts are in order so I won't get a broken FK. I'll try that too 
see if it increases the speed (decreasing my headaches per build, lol).

Thanks a lot, Petr!


On 06/17/2013 11:18 AM, Petr Viktorin wrote:

Re: between .one() and .first()

Michael Bayer — 2013-06-17T16:36:16

On Jun 17, 2013, at 5:33 AM, Wichert Akkerman <wichert< at >wiggy.net> wrote:


you could even drop the limit(2) so that the query renders more simply if eagerloads are present.

Re: mapping to a dictionary-based collection using a select

Michael Bayer — 2013-06-17T16:35:37

On Jun 17, 2013, at 2:36 AM, Chris Withers <chris< at >simplistix.co.uk> wrote:



well you're going to have relationship with "primaryjoin=and_(Symbol.instrument_id==Instrument.id, Symbol.period.contains(func.now()))"  (what does contains(func.now()) do ?   havent seen that one before).    then collection_class=attribute_mapped_collection(<attribute you want to key on>).

Re: SA0.7.9 - poor query performance (full scan instead of index scan because of joined table inheritance)

Ladislav Lenart — 2013-06-17T09:35:27

Hello.

I ended up with the following query:

    < at >classmethod
    def _find_contacts_fetch_window(cls, contact_cls, win):
        """Special data-fetching query for contacts and all their related info
        including tags, partner, client,...

        NOTE: We build the FROM part entirely by hand, because SA generates bad
        SQL for postgres. It does a FULL SCAN of client and personal_client /
        corporate_client even though it reads at most window_size rows from
        them. All this because SA inheritance creates a subselect which leads
        to the full scan.
        """
        # win .. list of ids
        # contact_cls .. PersonalContact / CorporateContact
        client_cls = contact_cls.client_cls() # PersonalClient / CorporateClient
        data_cls = client_cls.data_cls() # PersonalData / CorporateData

        # We need TABLEs to build the FROM part by hand.
        # We reference PersonalData/CorporateData and Address twice, hence we
        # need to alias them.
        # We also

Re: SA0.7.9 - poor query performance (full scan instead of index scan because of joined table inheritance)

Ladislav Lenart — 2013-06-17T09:54:14

Hello.

I ended up with the following query:

    < at >classmethod
    def _find_contacts_fetch_window(cls, contact_cls, win):
        """Special data-fetching query for contacts and all their related info
        including tags, partner, client,...

        NOTE: We build the FROM part entirely by hand, because SA generates bad
        SQL for postgres. It does a FULL SCAN of client and personal_client /
        corporate_client even though it reads at most window_size rows from
        them. All this because SA inheritance creates a subselect which leads
        to the full scan.
        """
        # win .. list of ids
        # contact_cls .. PersonalContact / CorporateContact
        client_cls = contact_cls.client_cls() # PersonalClient / CorporateClient
        data_cls = client_cls.data_cls() # PersonalData / CorporateData

        # We need TABLEs to build the FROM part by hand.
        # We reference PersonalData/CorporateData and Address twice, hence we
        # need to alias them.
        # We also

Unique constraints disappearing

Rj Ewing — 2013-06-15T20:32:49

I am trying to do a migration and am running into a problem where my 
unique constraints are being delete with sqlite, but works fine with 
postgres.

It seems that unique constraints are not present in the table, even before 
the migration.

Ex.

engine = create_engine("sqlite:///my.db")
Session = sessionmaker(bind=engine)
s = Session()
metadata = MetaData(s.bind)
t = Table('core__users', metadata, autoload=True, 
autoload_with=metadata.bind)

t.constraints shows the pk constraint, but not the unique constraints.

Is this a limitation with sqlite? Or am I doing something wrong? 

Any help is appreciated.

Thanks