How do I prevent sqlalchemy from creating a transaction on select?
<p>My problem:</p>

<p>I have a file with several rows of data in it. I want to <em>try</em> to insert every row into my database, but if <strong>any</strong> of the rows have problems I need to roll back the whole kit and kaboodle. But I want to track the actual errors so rather than just dying on the first record that has an error I can say something like this:</p>

<blockquote>
  <p>This file has 42 errors in it.</p>

<pre><code>Line 1 is missing a whirlygig.
Line 2 is a duplicate.
Line 5 is right out.
</code></pre>
</blockquote>

<p>The way I'm trying to do this is with transactions, but I have a problem where SQLAlchemy creates implicit transactions on select, and apparently I don't really understand how sqlalchemy is using transactions because nothing I do seems to work the way I want. Here's some code that demonstrates my problem:</p>

<pre><code>import sqlalchemy as sa
import logging
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
l = logging.getLogger('sqlalchemy.engine')
l.setLevel(logging.INFO)
l.addHandler(logging.StreamHandler())

engine = sa.create_engine('YOUR PG CONNECTION HERE')

Session = sessionmaker(bind=engine)
session = Session()
temp_metadata = sa.MetaData(schema='pg_temp')
TempBase = declarative_base(metadata=temp_metadata)


class Whatever(TempBase):
    __tablename__ = 'whatevs'
    id = sa.Column('id', sa.Integer, primary_key=True, autoincrement=True)
    fnord = sa.Column('fnord', sa.String, server_default=sa.schema.FetchedValue())
    quux = sa.Column('quux', sa.String)
    value = sa.Column('value', sa.String)


def insert_some_stuff(session, data):
    value = session.query(Whatever.value).limit(1).scalar()
    session.add(Whatever(quux=data, value='hi'))
    try:
        session.commit()
        errors = 0
    except sa.exc.IntegrityError:
        session.rollback()
        errors = 1
    return errors


with session.begin_nested():
    session.execute('''
        CREATE TABLE pg_temp.whatevs (
            id      serial
          , fnord   text not null default 'fnord'
          , quux    text not null
          , value   text not null
          , CONSTRAINT totally_unique UNIQUE (quux)
        );
        INSERT INTO pg_temp.whatevs (value, quux) VALUES ('something cool', 'fnord');
    ''')
    w = Whatever(value='something cool', quux='herp')
    session.add(w)


errors = 0
for q in ('foo', 'biz', 'bang', 'herp'):
    with session.begin_nested():
        errors += insert_some_stuff(session, q)
for row in session.query(Whatever).all():
    print(row.id, row.fnord, row.value)
</code></pre>

<p>I've tried a variety of combinations where I do <code>session.begin()</code> or <code>.begin(subtransactions=True)</code>, but they all either don't work, or just seem really weird because I'm committing transactions I never (explicitly) started.</p>

<p>Can I prevent sqlalchemy from creating a transaction on select? Or am I missing something here? Is there a better way to accomplish what I want?</p>

<p>It looks as if <code>begin_nested</code> and <code>with</code> blocks are the way to go.</p>

<blockquote>
  <p><code>begin_nested()</code>, in the same manner as the less often used <code>begin()</code> method...  - <a href="http://docs.sqlalchemy.org/en/latest/orm/session_transaction.html#using-savepoint" rel="nofollow">sqlalchemy docs</a></p>
</blockquote>

<p>That leads me to believe that <code>begin_nested</code> is the preferred option.</p>

<pre><code>def insert_some_stuff(session, data):
    try:
        with session.begin_nested():
            value = session.query(Whatever.value).limit(1).scalar()
            session.add(Whatever(quux=data, value='hi'))
        errors = 0
    except sa.exc.IntegrityError:
        errors = 1

    return errors
</code></pre>

<p>By using the <code>with</code> block, it Does The Right Thingâ¢ when it comes to committing/rolling back, and not rolling back too far.</p>

python transactions sqlalchemy