Checking the handling of unique keys/indexes

[SF Markus Elfring]

Hello,

I am using source code like the following in one of my scripts
where I am trying the software packages "SQLAlchemy 0.9.7-77.1"
and "SQLite 3.8.7.2-1.1" out on my openSUSE system.

...
engine = create_engine("sqlite:///:memory:", echo = False)
base = declarative_base()

class position(base):
__tablename__ = "positions"
function = Column(String, primary_key = True)
source_file = Column(String, primary_key = True)
line = Column(Integer, primary_key = True)
column = Column(Integer, primary_key = True)
void = Column(Integer, default = 0)
static = Column(Integer, default = 0)
data_type = Column(String)
parameter = Column(String)
...
def store_positions(fun, type, point, places):
"""Add source code positions to an internal table."""
...



Now I stumble on an error message like the following.
...
cursor.executemany(statement, parameters)
sqlalchemy.exc.IntegrityError: (IntegrityError) UNIQUE constraint failed: positions.function,
...


The message might be appropriate in principle for my concrete use case.
But I observe that the constraint violation is reported a bit
too late because I got the impression from corresponding debug
output that three rows were added to the shown table here
with unique attributes.

I would appreciate your explanations and further advices.

Regards,
Markus

[Michael Bayer]

SF Markus Elfring <elf...@users.sourceforge.net> wrote:

> Now I stumble on an error message like the following.
> ...
> cursor.executemany(statement, parameters)
> sqlalchemy.exc.IntegrityError: (IntegrityError) UNIQUE constraint failed: positions.function,
> ...
>
>
> The message might be appropriate in principle for my concrete use case.
> But I observe that the constraint violation is reported a bit
> too late because I got the impression from corresponding debug
> output that three rows were added to the shown table here
> with unique attributes.
>
> I would appreciate your explanations and further advices.

SQLAlchemy sends to the log the statement and parameters it is to send to the DBAPI cursor.execute() method, *before* it actually does so. This so that if the DBAPI throws an exception, as is the case here, one can see what instructions were sent to it which were the immediate cause of this error.

The mechanism of a UNIQUE constraint is that this is a database-level construct, so the backend database is tasked with checking this condition and reporting on it at statement execution time.

[SF Markus Elfring]

> SQLAlchemy sends to the log the statement and parameters it is to send
> to the DBAPI cursor.execute() method, *before* it actually does so.
> This so that if the DBAPI throws an exception, as is the case here,
> one can see what instructions were sent to it which were the immediate
> cause of this error.

Thanks for your explanation.

Do I need to consider any more fine-tuning for my database session?

> The mechanism of a UNIQUE constraint is that this is a database-level
> construct, so the backend database is tasked with checking this
> this condition and reporting on it at statement execution time.

Should I get the exception "sqlalchemy.exc.IntegrityError" directly
after I attempted to insert a second record set with unique attributes
into a SQLite table?

Regards,
Markus

[Michael Bayer]

SF Markus Elfring <elf...@users.sourceforge.net> wrote:

I don’t have a stack trace here to see what the nature of the issue is but it is likely that the INSERT is proceeding using DBAPI executemany(), which receives the full set of records in one batch before any communication with the database is established. SQLAlchemy doesn’t have access to at what point each individual series of parameters are invoked as the interface is too coarse-grained.

The two general techniques for dealing with unique constraints are to either SELECT ahead of time the rows that you know to be dealing with into a collection, such that you can check within this collection ahead of time for the existing row before proceeding, or invoking the INSERT of rows one at a time, catching each IntegrityError inside of a SAVEPOINT (http://docs.sqlalchemy.org/en/latest/orm/session_transaction.html#using-savepoint describes the Session’s API for SAVEPOINT). Note that the SQLite driver has a bug with SAVEPOINT which you need to apply the technique at http://docs.sqlalchemy.org/en/latest/dialects/sqlite.html#pysqlite-serializable in order to overcome.


>
> Regards,
> Markus

[SF Markus Elfring]

>> Should I get the exception "sqlalchemy.exc.IntegrityError" directly
>> after I attempted to insert a second record set with unique attributes
>> into a SQLite table?
> I don’t have a stack trace here to see what the nature of the issue is
> but it is likely that the INSERT is proceeding using DBAPI executemany(),

Yes. - It seems that this method was used in my use case.

> which receives the full set of records in one batch before any
> communication with the database is established.

Can it happen then that an error will be reported for a single SQL statement
which was submitted within an unit of more database commands?

> SQLAlchemy doesn’t have access to at what point each individual series
> of parameters are invoked as the interface is too coarse-grained.

Do you know any attempts to make the affected error reporting more precise?

Regards,
Markus

[Michael Bayer]

SF Markus Elfring <elf...@users.sourceforge.net> wrote:

Here are the two options we have for invoking a statement:

try:
cursor.execute(stmt, params) # single row
except IntegrityError:
# …

try:
cursor.executemany(stmt, [params, params, params, params, …]) # many rows
except IntegrityError:
# …

There is no way to use the second form while being able to record the moment each parameter set is used, unless the DBAPI itself provides additional hooks for logging at this level. However, this logging would defeat some of the purpose of executemany(), which is that of processing many parameter sets at maximum speed.

The SQLAlchemy Session tries to use executemany() as often as it can within a flush() procedure; it can be used any time there are more than one row to be INSERTED where we already have the primary key value available.

If you’d like to operate on individual rows, I guess I wasn’t specific enough from my instruction to use SAVEPOINT, you should flush individually:

for obj in all_my_objects:
session.add(obj)
try:
with session.begin_nested():
session.flush()
except IntegrityError:
# deal with this error, don’t add obj, or however it is you intend to deal with existing rows






>
> Regards,
> Markus
>
> --
> You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.
> To post to this group, send email to sqlal...@googlegroups.com.
> Visit this group at http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

[SF Markus Elfring]

> There is no way to use the second form while being able to record the moment
> each parameter set is used, unless the DBAPI itself provides additional hooks
> for logging at this level. However, this logging would defeat some of the
> purpose of executemany(), which is that of processing many parameter sets
> at maximum speed.

Thanks for your helpful advice.

Regards,
Markus