How to Add rows using subqueries in sqlalchemy?

Question

I'm using Postgresql with SQLAlchemy but it seems sqlalchemy is having trouble adding rows when using subqueries.

In my example, I want to update a counter for a specific tag in a table.

In SqlAlchemy a test run class would look like the following:

class TestRun( base ):
    __tablename__   = 'test_runs'

    id              = sqlalchemy.Column( 'id', sqlalchemy.Integer, sqlalchemy.Sequence('user_id_seq'), primary_key=True )
    tag             = sqlalchemy.Column( 'tag', sqlalchemy.String )
    counter         = sqlalchemy.Column( 'counter', sqlalchemy.Integer )

The insertion code should then look like the following:

tag = 'sampletag'
counterquery = session.query(sqlalchemy.func.coalesce(sqlalchemy.func.max(TestRun.counter),0) + 1).\
                            filter(TestRun.tag == tag).\
                            subquery()

testrun = TestRun()
testrun.tag = tag
testrun.counter = counterquery

session.add( testrun )
session.commit()

The problem with this, is it gives a very interesting error when running this code, it's trying to run the following SQL Query:

'INSERT INTO test_runs (id, tag, counter) 
    VALUES (%(id)s, 
            %(tag)s, 
            SELECT coalesce(max(test_runs.counter), %(param_1)s) + %(coalesce_1)s AS anon_1 
               FROM test_runs 
               WHERE test_runs.tag = %(tag_1)s)' 
 {'coalesce_1': 1, 'param_1': 0, 'tag_1': 'mytag', 'tag': 'mytag', 'id': 267L}

Which looks reasonable, except it's missing parenthesis around the SELECT call. When I run the SQL query manually it gives me the same exact error that sqlalchemy gives me until I type in the parenthesis manually which fixes everything up. Seems like an unlikely bug that sqlalchemy would forget to put parenthesis when it needs to, so my question is am I missing a function to use subqueries correctly when adding rows using sqlalchemy?

Denis Otkidach · Accepted Answer · 2011-11-17 11:03:27Z

7

Instead of using subquery() call as_scalar() method:

Return the full SELECT statement represented by this Query, converted to a scalar subquery.

Example:

Models with classing parent-child relationship:

class Parent(Base):
    __tablename__ = 'parents'
    id = Column(Integer, primary_key=True)
    counter = Column(Integer, nullable=False, default=0)

class Child(Base):
    __tablename__ = 'children'
    id = Column(Integer, primary_key=True)
    parent_id = Column(ForeignKey(Parent.id), nullable=False)
    parent = relationship(Parent)

Code to update counter field:

parent.counter = session.query(func.count(Child.id))\
                    .filter_by(parent=parent).as_scalar()

Produced SQL (copied from the log):

UPDATE parents SET counter=(SELECT count(children.id) AS count_1 
FROM children
WHERE ? = children.parent_id) WHERE parents.id = ?

edited Nov 17, 2011 at 11:03

answered Nov 16, 2011 at 10:01

Denis Otkidach

33.5k8 gold badges86 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

David Yen Over a year ago

That's not the point, the question was how to get sqlalchemy to output the correct sql command. Using as_scalar() will separate it out into 2 sql queries which makes it not safe since multiple people can increment the value at the same time.

Denis Otkidach Over a year ago

@DavidYen, have you tried or at least read documentation I refer to? I've added an example illustrating that you are wrong. Probably you confuse it with scalar() method that returns result, not subquery.

Collectives™ on Stack Overflow

How to Add rows using subqueries in sqlalchemy?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related