Stack Exchange
SQLAlchemy error on left join
I am new to SQLAlchemy and I am trying to achieve this SQL:
SELECT 
    node.*,
    alarms.*
FROM
    node
        LEFT JOIN
            alarms ON alarms.nodeid = node.nodeid 
WHERE
    node.foreignid IN ('foreignid_1','foreignid_2','foreignid_3') 
ORDER BY node.nodeid;

I come up with this statement:
stmt = session.query(Node, Alarms).outerjoin(Alarms, Alarms.nodeid == Node.nodeid).filter(Node.foreignid.in_(['foreignid_1','foreignid_2','foreignid_3'])).order_by(asc(Node.nodeid))
results = stmt.all()

The generated SQL looks like:
SELECT 
    node.nodeid AS node_nodeid,
    node.dpname AS node_dpname,
    node.nodecreatetime AS node_nodecreatetime,
    node.nodeparentid AS node_nodeparentid,
    node.nodetype AS node_nodetype,
    node.nodesysoid AS node_nodesysoid,
    node.nodesysname AS node_nodesysname,
    node.nodesysdescription AS node_nodesysdescription,
    node.nodesyslocation AS node_nodesyslocation,
    node.nodesyscontact AS node_nodesyscontact,
    node.nodelabel AS node_nodelabel,
    node.nodelabelsource AS node_nodelabelsource,
    node.nodenetbiosname AS node_nodenetbiosname,
    node.nodedomainname AS node_nodedomainname,
    node.operatingsystem AS node_operatingsystem,
    node.lastcapsdpoll AS node_lastcapsdpoll,
    node.foreignsource AS node_foreignsource,
    node.foreignid AS node_foreignid,
    alarms.alarmid AS alarms_alarmid,
    alarms.eventuei AS alarms_eventuei,
    alarms.dpname AS alarms_dpname,
    alarms.nodeid AS alarms_nodeid,
    alarms.ipaddr AS alarms_ipaddr,
    alarms.serviceid AS alarms_serviceid,
    alarms.reductionkey AS alarms_reductionkey,
    alarms.alarmtype AS alarms_alarmtype,
    alarms.counter AS alarms_counter,
    alarms.severity AS alarms_severity,
    alarms.lasteventid AS alarms_lasteventid,
    alarms.firsteventtime AS alarms_firsteventtime,
    alarms.lasteventtime AS alarms_lasteventtime,
    alarms.firstautomationtime AS alarms_firstautomationtime,
    alarms.lastautomationtime AS alarms_lastautomationtime,
    alarms.description AS alarms_description,
    alarms.logmsg AS alarms_logmsg,
    alarms.operinstruct AS alarms_operinstruct,
    alarms.tticketid AS alarms_tticketid,
    alarms.tticketstate AS alarms_tticketstate,
    alarms.mouseovertext AS alarms_mouseovertext,
    alarms.suppresseduntil AS alarms_suppresseduntil,
    alarms.suppresseduser AS alarms_suppresseduser,
    alarms.suppressedtime AS alarms_suppressedtime,
    alarms.alarmackuser AS alarms_alarmackuser,
    alarms.alarmacktime AS alarms_alarmacktime,
    alarms.managedobjectinstance AS alarms_managedobjectinstance,
    alarms.managedobjecttype AS alarms_managedobjecttype,
    alarms.applicationdn AS alarms_applicationdn,
    alarms.ossprimarykey AS alarms_ossprimarykey,
    alarms.x733alarmtype AS alarms_x733alarmtype,
    alarms.x733probablecause AS alarms_x733probablecause,
    alarms.qosalarmstate AS alarms_qosalarmstate,
    alarms.clearkey AS alarms_clearkey,
    alarms.ifindex AS alarms_ifindex,
    alarms.eventparms AS alarms_eventparms
FROM
    node
        LEFT OUTER JOIN
            alarms ON alarms.nodeid = node.nodeid
WHERE
    node.foreignid IN ('foreignid_2' , 'foreignid_1', 'foreignid_3')
ORDER BY node.nodeid ASC  

But it also throws this error (from iPython):
In [86]: stmt = session.query(Node, Alarms).outerjoin(Alarms, Alarms.nodeid == Node.nodeid).filter(Node.foreignid.in_(['foreignid_1','foreignid_3','foreignid_2'])).order_by(asc(Node.nodeid))

In [87]: results = stmt.all()
---------------------------------------------------------------------------
InternalError                             Traceback (most recent call last)
<ipython-input-87-1110c354b407> in <module>()
----> 1 results = stmt.all()

/usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.7.8-py2.6-linux-x86_64.egg/sqlalchemy/orm/query.pyc in all(self)
   2113
   2114         """
-> 2115         return list(self)
   2116
   2117     @_generative(_no_clauseelement_condition)

/usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.7.8-py2.6-linux-x86_64.egg/sqlalchemy/orm/query.pyc in __iter__(self)
   2225         if self._autoflush and not self._populate_existing:
   2226             self.session._autoflush()
-> 2227         return self._execute_and_instances(context)
   2228
   2229     def _connection_from_session(self, **kw):

/usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.7.8-py2.6-linux-x86_64.egg/sqlalchemy/orm/query.pyc in _execute_and_instances(self, querycontext)
   2240                         close_with_result=True)
   2241
-> 2242         result = conn.execute(querycontext.statement, self._params)
   2243         return self.instances(result, querycontext)
   2244

/usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.7.8-py2.6-linux-x86_64.egg/sqlalchemy/engine/base.pyc in execute(self, object, *multiparams, **params)
   1447                                                 object,
   1448                                                 multiparams,
-> 1449                                                 params)
   1450         else:
   1451             raise exc.InvalidRequestError(

/usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.7.8-py2.6-linux-x86_64.egg/sqlalchemy/engine/base.pyc in _execute_clauseelement(self, elem, multiparams, params)
   1582             compiled_sql,
   1583             distilled_params,
-> 1584             compiled_sql, distilled_params
   1585         )
   1586         if self._has_events:

/usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.7.8-py2.6-linux-x86_64.egg/sqlalchemy/engine/base.pyc in _execute_context(self, dialect, constructor, statement, parameters, *args)
   1696                                 parameters,
   1697                                 cursor,
-> 1698                                 context)
   1699             raise
   1700

/usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.7.8-py2.6-linux-x86_64.egg/sqlalchemy/engine/base.pyc in _execute_context(self, dialect, constructor, statement, parameters, *args)
   1689                                     statement,
   1690                                     parameters,
-> 1691                                     context)
   1692         except Exception, e:
   1693             self._handle_dbapi_exception(

/usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.7.8-py2.6-linux-x86_64.egg/sqlalchemy/engine/default.pyc in do_execute(self, cursor, statement, parameters, context)
    329
    330     def do_execute(self, cursor, statement, parameters, context=None):
--> 331         cursor.execute(statement, parameters)
    332
    333     def do_execute_no_params(self, cursor, statement, context=None):

InternalError: (InternalError) current transaction is aborted, commands ignored until end of transaction block
 'SELECT node.nodeid AS node_nodeid, node.dpname AS node_dpname, node.nodecreatetime AS node_nodecreatetime, node.nodeparentid AS node_nodeparentid, node.nodetype AS node_nodetype, node.nodesysoid AS node_nodesysoid, node.nodesysname AS node_nodesysname, node.nodesysdescription AS node_nodesysdescription, node.nodesyslocation AS node_nodesyslocation, node.nodesyscontact AS node_nodesyscontact, node.nodelabel AS node_nodelabel, node.nodelabelsource AS node_nodelabelsource, node.nodenetbiosname AS node_nodenetbiosname, node.nodedomainname AS node_nodedomainname, node.operatingsystem AS node_operatingsystem, node.lastcapsdpoll AS node_lastcapsdpoll, node.foreignsource AS node_foreignsource, node.foreignid AS node_foreignid, alarms.alarmid AS alarms_alarmid, alarms.eventuei AS alarms_eventuei, alarms.dpname AS alarms_dpname, alarms.nodeid AS alarms_nodeid, alarms.ipaddr AS alarms_ipaddr, alarms.serviceid AS alarms_serviceid, alarms.reductionkey AS alarms_reductionkey, alarms.alarmtype AS alarms_alarmtype, alarms.counter AS alarms_counter, alarms.severity AS alarms_severity, alarms.lasteventid AS alarms_lasteventid, alarms.firsteventtime AS alarms_firsteventtime, alarms.lasteventtime AS alarms_lasteventtime, alarms.firstautomationtime AS alarms_firstautomationtime, alarms.lastautomationtime AS alarms_lastautomationtime, alarms.description AS alarms_description, alarms.logmsg AS alarms_logmsg, alarms.operinstruct AS alarms_operinstruct, alarms.tticketid AS alarms_tticketid, alarms.tticketstate AS alarms_tticketstate, alarms.mouseovertext AS alarms_mouseovertext, alarms.suppresseduntil AS alarms_suppresseduntil, alarms.suppresseduser AS alarms_suppresseduser, alarms.suppressedtime AS alarms_suppressedtime, alarms.alarmackuser AS alarms_alarmackuser, alarms.alarmacktime AS alarms_alarmacktime, alarms.managedobjectinstance AS alarms_managedobjectinstance, alarms.managedobjecttype AS alarms_managedobjecttype, alarms.applicationdn AS alarms_applicationdn, alarms.ossprimarykey AS alarms_ossprimarykey, alarms.x733alarmtype AS alarms_x733alarmtype, alarms.x733probablecause AS alarms_x733probablecause, alarms.qosalarmstate AS alarms_qosalarmstate, alarms.clearkey AS alarms_clearkey, alarms.ifindex AS alarms_ifindex, alarms.eventparms AS alarms_eventparms \nFROM node LEFT OUTER JOIN alarms ON alarms.nodeid = node.nodeid \nWHERE node.foreignid IN (%(foreignid_1)s, %(foreignid_2)s, %(foreignid_3)s) ORDER BY node.nodeid ASC' {'foreignid_1': 'foreignid_1', 'foreignid_3': 'foreignid_3', 'foreignid_2': 'foreignid_2'}

I just don't understand what is the error while the generated SQL is good.
Thank you.

Your exception has nothing to do with your generated SQL, which is exactly what you wanted.
Your database connection is in the wrong state; a transaction was aborted (probably due to an integrity error somewhere) and no new transaction was started, and your database didn't like that.
While experimenting with SQLAlchemy from an iPython prompt, it's probably better to turn auto-commit on, so you don't have to deal with the intricacies of transaction management.

You are right, I sent the command line session.commit() within the same iPython terminal and re-run that same statement, now it works. Thank you.

4 minutes to go, before I can do =)

On the other end, I recreated my session object using Session = sessionmaker(autocommit=True) session = Session() to prevent that kind of issue.

Do remember not to set autocommit in real-world applications though, unless that's what you really, really-I-mean-it want.

I am using MySQL Galera cluster in production and I will need the autocommit=true otherwise some times it locks the row for too long preventing the application to work properly. I was previously using PySQLPool and found out after a couple of locks that the autocommit was a necessity. So in my case it is a must.
