gmane.comp.python.sqlalchemy.user

Re: order_by hybrid property fails when specifed as string in a relationship

Michael Bayer — 2013-06-19T20:47:08

On Jun 19, 2013, at 4:19 PM, George Sakkis <george.sakkis< at >gmail.com> wrote:


It's kind of a missing feature; here's a patch to make that work which will be for 0.8:  http://www.sqlalchemy.org/trac/ticket/2761

Re: flask-sqlalchemy pysybase connections

Michael Bayer — 2013-06-19T20:35:51

On Jun 19, 2013, at 3:07 PM, Kevin S <kevinrstone< at >gmail.com> wrote:


you can use FreeTDS with Pyodbc, and you might have much better results with that.   


There's a C macro Py_BEGIN_ALLOW_THREADS/Py_END_ALLOW_THREADS that must bridge areas where the C extension should release the GIL.  The main documentation on this is at http://docs.python.org/2/c-api/init.html#releasing-the-gil-from-extension-code.

order_by hybrid property fails when specifed as string in a relationship

George Sakkis — 2013-06-19T20:19:28

It seems that hybrid properties are not allowed to be specified as strings 
for the order_by parameter of a relationship; attempting it fails with 
"InvalidRequestError: Class <...> does not have a mapped column named 
'...'". Is this a known limitation or a bug? Sample test case below.

Thanks,
George

#============================================================ 

from sqlalchemy import Column, Integer, String, ForeignKey, case
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.ext.hybrid import hybrid_property
from sqlalchemy.orm import relationship

Base = declarative_base()


class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)
    firstname = Column(String(50))
    lastname = Column(String(50))
    game_id = Column(Integer, ForeignKey('game.id'))

    < at >hybrid_property
    def fullname(self):
        if self.firstname is not None:
            return self.firstname + " " + self.lastname
        else:
            return self.lastname

    < at >fullname.expression
    def fullname(cls):
        return case([
            (cls.firstname != None, cls.firstname + " " + cls.lastname),
        ], else_=cls.lastname)


class Game(Base):
    __tablename__ = 'game'
    id = Column(Integer, primary_key=True)
    name = Column(String(50))
    if 0: # this works
        users = relationship("User", order_by=User.fullname)
    else: # this fails
        users = relationship("User", order_by="User.fullname")

if __name__ == '__main__':
    game = Game(name="tetris")

Re: flask-sqlalchemy pysybase connections

Kevin S — 2013-06-19T19:07:36

Unfortunately, we cannot switch off of Sybase. It is a future project, but 
we cannot go there right now (I would love to). I also have not been able 
to find any other sybase dbapis that work with sqlalchemy and are free.

I did set up some tests against pysybase directly, omitting the sqlalchemy 
and cherrypy pieces. I'm having two threads each create a connection and 
execute a query. After littering debug statements in the test code, and 
throughout the pysybase library, I can see that as soon as the first thread 
executes its query, which is done through a c extension, everything else 
halts, the main function, as well as the second thread. 

Here is the test code (minus debugging lines):

def sy_query(query):
        db = Sybase.connect(dbServer,dbUser,dbPass,dbName)
        cur = db.cursor()
        cur.execute(query)
t1 = Thread(target=sy_query,args=("select * from blah",))
t2 = Thread(target=sy_query,args=("select something from blah2",))
t1.start()
t2.start()
t1.join()
t2.join()

It gets as far as one print statement after "t1.start()". I have a print at 
the beginning of "sy_query()" that does not execute for the second thread 
until the first has finished its query. Now, I'm not very familiar with 
threads in python, or the GIL in general. Inside pysybase's library, there 
is essentially this call to the c extension:

status, result = self._cmd.ct_results()

where ct_results() is defined in the c file.
Is there some easy or brute force way to force that call to NOT "grab the 
GIL"? From my brief reading it sounded like c extensions are supposed to 
get around GIL issues, but again I am naive on the subject.

On Sunday, June 16, 2013 1:10:03 PM UTC-4, Michael Bayer wrote:

Re: SQLite synchronous on engine

Michael Bayer — 2013-06-19T17:46:02

On Jun 19, 2013, at 10:14 AM, pr64 <pierreroth64< at >gmail.com> wrote:





you want to use a connect event for that:

from sqlalchemy import event

< at >event.listens_for(my_engine, "connect")
def on_connect(dbapi_conn, conn_rec, conn_proxy):
    cursor = dbapi_conn.cursor()
    cursor.execute("your pragma here")
    cursor.close()



I'm not familiar with the nature of these performance settings, but the sqlite3 DBAPI and SQLite itself is extremely fast, way way faster by itself than if you have any kind of Python code wrapping it and also faster than any other DBAPI I've worked with.   So if you do profiling you will see that the vast majority of time with a SQLite app is taken up by SQLAlchemy Core and ORM.   Like if you look at this profile diagram from 0.7:  http://techspot.zzzeek.org/files/2010/sqla_070b1_large.png , the proportion of time actually spent within SQLite is that dark blue box in the center-left, where you can see "<method 'execute' of sqlite3>" , and theres below it a little maroon box that says "<method>", that's likely the sqlite3 cursor.fetchall() method.    So all of the performance gains these pragmas get you will at most make that one blue box a little smaller.  The rest of the screen represents time spent outside of sqlite3.

Some techniques on profiling can be seen in my stackoverflow answer here: http://stackoverflow.com/questions/1171166/how-can-i-profile-a-sqlalchemy-powered-application/1175677#1175677

SQLAlchemy-ImageAttach: an extension for attaching images to entity objects

Hong Minhee — 2013-06-19T17:16:25

Hi,

I am glad to introduce SQLAlchemy-ImageAttach, a new extension for attaching images to entity objects. It provides the following features:

- Storage backend interface: Yu can use file system backend on your local development box, and switch it to AWS S3 when it’s deployed to the production box. Or you can add a new backend implementation by yourself.

- Maintaining multiple image sizes: Any size of thumbnails can be generated from the original size without assuming the fixed set of sizes. You can generate a thumbnail of a particular size if it doesn't exist yet when the size is requested. Use RRS (Reduced Redundancy Storage) for reproducible thumbnails on S3.

- Every image has its URL: Attached images can be exposed as a URL.

- SQLAlchemy transaction aware: Saved file are removed when the ongoing transaction has been rolled back.

- Tested on various environments: Python 2.6, 2.7, 3.2, 3.3, PyPy / PostgreSQL, MySQL, SQLite / SQLAlchemy 0.8 or higher

On the other hand, it does not provide:

- General file attachment: It only supports images.
- BLOB storage: It doesn’t use BLOB data type at all.
- Image preprocessing: It simply does resizing, but any other image processings like crop aren’t provided. (Although you can do such things using Wand, SQLAlchemy-ImageAttach depends on.)

Today I released SQLAlchemy-ImageAttach 0.8.0 which is its first stable version. You can install it using pip or easy_install:

$ pip install SQLAlchemy-ImageAttach

Docs and repository can be found at the following urls:

https://sqlalchemy-imageattach.readthedocs.org/
https://github.com/crosspop/sqlalchemy-imageattach

Please try it out and give me feedback!

Thanks,
Hong Minhee

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:///' + database, echo=echo,
                    connect_args={'detect_types': 
sqlite3.PARSE_DECLTYPES|sqlite3.PARSE_COLNAMES},
                    native_datetime=True,
                    poolclass=NullPool
                    )
        elif db_type == 'memory':
            engine = create_engine('sqlite:///' + database, echo=echo,
                    connect_args={'detect_types': 
sqlite3.PARSE_DECLTYPES|sqlite3.PARSE_COLNAMES},
                    native_datetime=True,
                    poolclass=SingletonThreadPool,
                    pool_size=5
                    )
        else:
            raise Exception("Unknown db_type: %s" % str(db_type))

        metadata.create_all(engine)
        self.session_maker.configure(bind=engine, expire_on_commit=False)
*        session = self.session_maker()*
*        session.connection().execute("PRAGMA journal_mode=WAL")*
*        session.commit()*
*        session.close()*

    def get_session(self):
        """Gets ORM session"""

        session = self.session_maker()
*        session.connection().execute("PRAGMA synchronous=NORMAL")*
        return session

I have two questions:

1- journal_mode pragma is persistent (according to sqlite doc) and should 
be done once but is there a way to pass the synchronous configuration to 
the  engine and make it global instead of setting it every time my 
application gets a new session ?
2- Are there any performance settings I can tune at sqlalchemy and/or 
sqlite level to improve my db access speed ?

Thanks a lot for your feedback,

Pierre

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()
except InvalidRequestError:
    self.Session.rollback()
    self.Session.commit()
self.Session.close()

What I'm doing wrong? Is it wrong way to close Firebird transaction?

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('%o%'))).subquery()
        
session.query(Addressess).filter(Addressess.id.in_(subq)).update({Addressess.house_no: 
10}, synchronize_session=False)

# All support but not SQLite:
# 
session.query(Addressess).join(Person.addressess).filter(Person.surname.like('%k')).\
#     
filter(sqlalchemy.not_(Person.surname.like('%o%'))).update({Addressess.house_no: 
10}, synchronize_session=False)


But how resolved no 5?
I try:

subq = session.query(Person).filter(Person.gender == False, 
Person.name.like('M%')).subquery()
y = session.query(Projects).filter(Projects.students.any(subq)).count()

ERROR:
OperationalError at /sqlalchemy/test_alchemy/
(OperationalError) near "SELECT": syntax error u'SELECT count(*) AS count_1 
\nFROM (SELECT projects.id AS projects_id, projects.name AS projects_name, 
projects.subject AS projects_subject, projects.supervisor_id AS 
projects_supervisor_id \nFROM projects \nWHERE EXISTS (SELECT 1 \nFROM 
association, person \nWHERE projects.id = association.projects_id AND 
person.id = association.person_id AND SELECT person.id, person.name, 
person.surname, person.gender, person.pesel, person.phone, person.email, 
person.no_card, person.status, person.student, person.group_leader_id, 
person.addressess_id, person.price_id \nFROM person \nWHERE person.gender = 
0 AND person.name LIKE ?)) AS anon_1' ('M%',)


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

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 = price
        self.extra = extra

    def __repr__(self):
       return u'%s (%s)' % (self.price, self.extra)


class Person(Base):

    __tablename__ = 'person'

    id = Column('id', Integer, Sequence('person_id_seq'), primary_key=True, 
index=True, unique=True, nullable=False)
    name = Column('name', String(200))
    surname = Column('surname', String(200))
    gender = Column('gender', Boolean, unique=False)
    pesel = Column('pesel', Numeric(11, 0))
    phone = Column('phone', Numeric(9, 0))
    email = Column('email', String(200))
    no_card = Column('no_card', String(10))
    status = Column('status', Integer)
    student = Column('student', Boolean, unique=False)
    group_leader_id = Column(Integer, ForeignKey('person.id'), 
nullable=True)
    group_leader = relationship("Person", remote_side=[id], 
primaryjoin="Person.id==Person.group_leader_id")
    addressess_id = Column(Integer, ForeignKey('addressess.id'), 
nullable=True)
    addressess = relationship("Addressess", backref=backref("person", 
uselist=False))
    price_id = Column(Integer, ForeignKey('prices.id'), nullable=True)
    price = relationship("Price", backref=backref("person", uselist=False))

    < at >validates('email')
    def validate_email(self, key, address):
        assert '< at >' in address
        return address

    def __init__(self, name, surname):
        self.name = name
        self.surname = surname

    def __repr__(self):
        return u'%s %s, %s' % (self.name, self.surname, self.pesel)


class Group(Base):

    __tablename__ = 'group'

    id = Column('id', Integer, Sequence('person_id_seq'), primary_key=True, 
index=True, unique=True, nullable=False)
    name = Column('email', String(200))
    leader_id = Column(Integer, ForeignKey('person.id'))
    leader = relationship("Person")
    addressess_id = Column(Integer, ForeignKey('addressess.id'))
    addressess = relationship("Addressess")

    def __init__(self, name):
        self.name = name

    def __repr__(self):
        return u'%s' % self.name


association_table = Table('association', Base.metadata,
    Column('person_id', Integer, ForeignKey('person.id')),
    Column('projects_id', Integer, ForeignKey('projects.id'))
)

class Projects(Base):

    __tablename__ = 'projects'

    id = Column('id', Integer, Sequence('person_id_seq'), primary_key=True, 
index=True, unique=True, nullable=False)
    name = Column('name', String(200))
    subject = Column('subject', String(200))
    supervisor_id = Column(Integer, ForeignKey('person.id'))
    supervisor = relationship("Person")
    students = relationship("Person", secondary=association_table, 
backref="projects")

    def __init__(self, name, subject):
        self.name = name
        self.subject = subject

    def __repr__(self):
        return u'%s' % self.subject


I have a problem with update record:
This is work:
1. 
session.query(Person).filter(Person.phone.like('%89%')).update({Person.no_card: 
u'Usunięto'}, synchronize_session=False)
2. 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)

But this is not work:
3. 
session.query(Price).join(Person).filter(Person.name.like('J%')).update({Price.extra: 
20000}, synchronize_session=False)
ERROR:
OperationalError at /sqlalchemy/test_alchemy/
(OperationalError) near "FROM": syntax error u'UPDATE prices SET extra=? 
FROM person WHERE person.name LIKE ?' (20000, 'J%')

4. session.query(Addressess).join(Person.addressess).filter(Person.surname.like('%k')).filter(sqlalchemy.not_(Person.surname.like('%o%'))).update({Addressess.house_no: 
10}, synchronize_session=False)
ERROR:
OperationalError at /sqlalchemy/test_alchemy/
(OperationalError) near "FROM": syntax error u'UPDATE addressess SET 
house_no=? FROM person WHERE person.surname LIKE ? AND person.surname NOT 
LIKE ?' (10, '%k', '%o%')

5. session.query(Projects).join(Person, 
Projects.students).filter(Person.gender==False).filter(Person.name.like('M%')).update({Projects.supervisor_id: 
1}, synchronize_session=False)
ERROR:
OperationalError at /sqlalchemy/test_alchemy/
(OperationalError) near "FROM": syntax error u'UPDATE projects SET 
supervisor_id=? FROM person WHERE person.gender = 0 AND person.name LIKE ?' 
(1, 'M%')

When i use only select and count this is work:
3. session.query(Price).join(Person).filter(Person.name.like('J%')).count()
4. session.query(Addressess).join(Person.addressess).filter(Person.surname.like('%k')).filter(sqlalchemy.not_(Person.surname.like('%o%'))).count()
5. session.query(Projects).join(Person, 
Projects.students).filter(Person.gender==False).filter(Person.name.like('M%')).count()

How to update this field?

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.