SQLAlchemy: successful insertion but then raises an exception

Question

I am running SQLAlchemy against FirebirdSQL, and when I execute an insert command in my project, SQLAlchemy is raising an exception on returning from executing against the connection. However, the insert query is being constructed and executed successfully. Querying the database shows that the items are actually being inserted correctly.

Edit: I'm digging down into the fbcore.py module now, and checking the value of value and vartype indicates that the issue is probably how the SEQUENCE item used to generate the primary key ID is returning its data is at issue. The vartype is SQL_LONG, but the actual value is [<an integer>] where <an integer> is the value returned by a sequence generator I created to auto-increment the primary key (e.g. [14]). This suggests to me that the problem should be resolved by fixing that, though I'm not sure how to do it. The generator appears to be working correctly within the database itself, but causing problems when returned to SQLAlchemy.

See below for my existing implementation and the stack trace for details.

My code:

class Project:
    # (I've snipped project instantiation, where engine connection, table, etc. are configured)
    def save_project(self, id_=None, title=None, file_name=None, file_location=None):

        # Build the dictionary of values to store
        values = {}
        if title is not None:
            values['title'] = title

        if file_name is not None:
            values['file_name'] = file_name

        if file_location is not None:
            values['file_location'] = file_location

        # Simplification: I account for the case that there *is* data---skipping that here

        # Execute the correct kind of statement: insert or settings_update.
        if id_ is None:
            statement = self.table.insert()

        else:
            statement = self.table.update().where(self.table.c.id == id_)

        result = self.connection.execute(statement, values)

        # If we inserted a row, get the new primary key. Otherwise, return
        # the one specified by the user; it does not change on settings_update.
        project_id = result.inserted_primary_key if result.is_insert else id_

The traceback:

  File "/Users/chris/development/quest/workspace/my_project/data/tables.py", line 350, in save_project
    result = self.connection.execute(statement, values)
  File "/Users/chris/.virtualenvs/my_project/lib/python3.3/site-packages/sqlalchemy/engine/base.py", line 720, in execute
    return meth(self, multiparams, params)
  File "/Users/chris/.virtualenvs/my_project/lib/python3.3/site-packages/sqlalchemy/sql/elements.py", line 317, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/Users/chris/.virtualenvs/my_project/lib/python3.3/site-packages/sqlalchemy/engine/base.py", line 817, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/Users/chris/.virtualenvs/my_project/lib/python3.3/site-packages/sqlalchemy/engine/base.py", line 947, in _execute_context
    context)
  File "/Users/chris/.virtualenvs/my_project/lib/python3.3/site-packages/sqlalchemy/engine/base.py", line 1111, in _handle_dbapi_exception
    util.reraise(*exc_info)
  File "/Users/chris/.virtualenvs/my_project/lib/python3.3/site-packages/sqlalchemy/util/compat.py", line 168, in reraise
    raise value
  File "/Users/chris/.virtualenvs/my_project/lib/python3.3/site-packages/sqlalchemy/engine/base.py", line 940, in _execute_context
    context)
  File "/Users/chris/.virtualenvs/my_project/lib/python3.3/site-packages/sqlalchemy/dialects/firebird/kinterbasdb.py", line 106, in do_execute
    cursor.execute(statement, parameters or [])
  File "/Users/chris/.virtualenvs/my_project/lib/python3.3/site-packages/fdb/fbcore.py", line 3323, in execute
    self._ps._execute(parameters)
  File "/Users/chris/.virtualenvs/my_project/lib/python3.3/site-packages/fdb/fbcore.py", line 2991, in _execute
    self.__Tuple2XSQLDA(self._in_sqlda, parameters)
  File "/Users/chris/.virtualenvs/my_project/lib/python3.3/site-packages/fdb/fbcore.py", line 2782, in __Tuple2XSQLDA
    sqlvar.sqlscale)
  File "/Users/chris/.virtualenvs/my_project/lib/python3.3/site-packages/fdb/fbcore.py", line 2266, in _check_integer_range
    if (value < vmin) or (value > vmax):
TypeError: unorderable types: list() < int()

I'm not yet sufficiently familiar with SQLAlchemy's to see why this is an issue; the style of my statement is pretty much identical to that in the tutorial. This appears to be an issue with how the parameters are being passed – possibly something about using a dict rather than keyword arguments? But there's nothing in the docs on how to handle parameters that suggests I have anything amiss here – it seems right from what I'm seeing there.

I've also tried this with self.table.insert().values(values) rather than passing the values term to the execute method, with the same results (as I would expect).

Edit: I note from reading the docstring on execute in fbcore.py that it raises a TypeError when the parameters passed to the method are not given either as a list or a tuple. Is this a change that is not yet reflected in the documentation?

Edit 2: As a comment notes, the stack trace indicates that it's running against the kinterbasdb driver, though I have explicitly configured the engine to run using fdb. This is also confusing to me.

What's the content of `values` variable, before this line `result = self.connection.execute(statement, values) `? — Omid Raha, Apr 04 '14 at 14:50
It's just the simple dict I initialized at the top of the `save_project` method, so e.g. `{'title': 'some_title_string', 'file_location': '/Users/chris/Desktop', 'file_name': 'some_name.project'}` (depending on the parameters I pass it). — Chris Krycho, Apr 04 '14 at 14:52
I deleted my answer as you probably are using the _fdb_ dialect; it inherits from the `kinterbasdb` dialect, so that is why it shows up in the stacktrace. Only other thing I can think of is that as Firebird doesn't support named parameters, that the named parameters are not correctly transformed to positional parameters. — Mark Rotteveel, Apr 04 '14 at 15:38
@MarkRotteveel, that's an interesting point, but (1) as noted, the query itself is being executed correctly—the items are ending up in the database as they should—and (2) at least as of 2.5, it *does* support named parameters (see e.g. the bit on the `EXECUTE` statement [here](http://www.firebirdsql.org/refdocs/langrefupd25-psql-execstat.html)). — Chris Krycho, Apr 04 '14 at 15:43
@ChrisKrycho Firebird itself only supports positional parameters (named parameters are only available in PSQL (stored procedures) and ESQL (embedded with preproccesor); some drivers do a translation though. `EXECUTE STATEMENT` is PSQL. — Mark Rotteveel, Apr 04 '14 at 15:48
Ah, I see, good point and clarification. I had missed that distinction in my reading of the docs so far. — Chris Krycho, Apr 04 '14 at 15:52
As far as I can tell the update should never reach the database, the error is thrown when checking the validity of the input parameters. — Mark Rotteveel, Apr 04 '14 at 15:58
@MarkRotteveel, thanks for all the help. The conversation got me asking the right questions and pointed me to the [answer](http://stackoverflow.com/a/22872598/564181), as well as gave me an opportunity to find [PostgreSQL Portable](http://sourceforge.net/projects/postgresqlportable/), which will I think prove to be a much better fit for our project than FirebirdSQL. — Chris Krycho, Apr 04 '14 at 20:56
As a fan of firebird, I am sorry to hear that ;) But happy that I could be of some help in finding the problem. — Mark Rotteveel, Apr 05 '14 at 08:02

Chris Krycho · Accepted Answer · 2014-04-05T16:53:31.990

As I might have expected, especially once I discovered that the issue was that the row was being inserted as expected but then called with an UPDATE function shortly after, the problem was some related code. I was returning the result as project_id (as you can see in the code above), and for an entirely unrelated reason (having to do with Blinker signals) the method was getting called again, with the returned value of project_id, which I had set thus:

project_id = result.inserted_primary_key if result.is_insert else id_

The correct version of this line is only slightly different:

project_id = result.inserted_primary_key[0] if result.is_insert else id_

From the SQLAlchemy docs (emphasis mine):

Return the primary key for the row just inserted.

The return value is a list of scalar values corresponding to the list of primary key columns in the target table.

The return value here has to be a list because primary keys can be a combination of more than one field in the database. (This should have been obvious to me; it's obvious I haven't done serious database work in over a year.) Since the primary key in this case is a single value, I just chose that value and returned it, and the problem is resolved.

Of course, now I have to go hunt down that Blinker signal issue—this method shouldn't be getting called twice—but c'est la vie...

score 1 · Answer 2 · answered Apr 04 '14 at 16:04

1

I have been going over the SQL Alchemy documentation, and I am wondering if you should be doing:

if id_ is None:
    statement = self.table.insert()

else:
    statement = self.table.update().where(self.table.c.id == id_)

statement = statement.values(title=title, file_name=file_name, file_location=file_location)

result = self.connection.execute(statement)

That is: instead of passing the dictionary to the execute, make it part of the statement (as shown by the Insert Expressions).

answered Apr 04 '14 at 16:04

Mark Rotteveel

100,966
191
140
197

I've actually done this both ways (as noted in the last statement of the question before the first edit), with the same results. I'm debugging from within the Firebird core, currently, and I think it may have to do with how the `SEQUENCE` is generating/returning its results here. I'm going to edit the question to that effect. – Chris Krycho Apr 04 '14 at 16:12
Maybe it expects an id value, so it checks the wrong parameter value, could you print the generated query? – Mark Rotteveel Apr 04 '14 at 16:30
Yeah, what's going on is that SQLAlchemy is attempting an insertion, failing and raising an exception, that it then handles and gets back the new PK from the DB, but storing that in a list rather than as an individual item (presumably with cause) so you end up with e.g. `[14]` instead of `14` which fails when doing a comparison between it and maximum integer length. – Chris Krycho Apr 04 '14 at 16:32
The generated SQL: `INSERT INTO projects (id, title, file_name, file_location) VALUES (gen_id(project_id_sequence, 1), ?, ?, ?) RETURNING projects.id`, then after a newline, `UPDATE projects SET title=?, file_name=?, file_location=? WHERE projects.id = ?` – Chris Krycho Apr 04 '14 at 16:50

SQLAlchemy: successful insertion but then raises an exception

2 Answers2