Python + SQLAlchemy + MySQL: IntegrityError and Duplicateent

When I updated the database with SQLAlchemy today, I encountered an Error: Firebug: WebErrorTraceback :? IntegrityError :( IntegrityError) (1062, Duplicateentry14-0forkeyidx_sid_view) INSERTINTOtemplate (schema_id, view, template, update_time) VALUES (% s

When I updated the database with SQLAlchemy today, I encountered an Error: Firebug: WebError Traceback :? IntegrityError: (IntegrityError) (1062, Duplicate entry '14-0' for key 'idx _ sid_view ') 'insert INTO template (schema_id, view, template, update_time) VALUES (% s

Today, when I updated the database with SQLAlchemy, an Error occurs:

Firebug:

WebError Traceback:
? IntegrityError: (IntegrityError) (1062, "Duplicate entry '14-0' for key 'idx _ sid_view '") 'insert INTO template (schema_id, view, template, update_time) VALUES (% s, % s) '(14, 0, 'This is a new view message. ', None)

Log:

17:21:55, 461 INFO [sqlalchemy. engine. base. Engine] [worker 5] BEGIN (implicit)
17:21:55, 461 INFO [sqlalchemy. engine. base. engine] [worker 5] insert into template (schema_id, view, template, update_time) VALUES (% s, % s)
17:21:55, 461 INFO [sqlalchemy. engine. base. Engine] [worker 5] (14, 0, 'This is a new view message. ', None)
17:21:55, 463 INFO [sqlalchemy. engine. base. Engine] [worker 5] ROLLBACK

Because the database is updated, it is written in Python as follows:

Session. merge (newtpl)

Session. commit ()

I don't know what went wrong...

Later, I thought about the structure of the table. This is the case.

The table structure is like this,

During the update, I directly inserted the three fields schema_id, view, and template, but the id field as the primary key was not updated, so the above error occurs.

Then it is easy to correct.

Read the id of the data in the original table, and put the id in the field to be updated during update. This makes it okay.

Similar to this:

Now we have succeeded. Let's look at the log:

17:52:36, 729 INFO [sqlalchemy. engine. base. Engine] [worker 2] BEGIN (implicit)
17:52:36, 730 INFO [sqlalchemy. engine. base. engine] [worker 2] SELECT template. id AS template_id, template. schema_id AS template_schema_id, template. view AS template_view, template. template AS template_template, template. update_time AS template_update_time
FROM template
WHERE template. schema_id = % s AND template. view = % s
LIMIT % s
17:52:36, 730 INFO [sqlalchemy. engine. base. Engine] [worker 2] (14, 0, 1)
17:52:36, 732 INFO [sqlalchemy. engine. base. Engine] [worker 2] UPDATE template SET template = % s WHERE template. id = % s
17:52:36, 732 INFO [sqlalchemy. engine. base. Engine] [worker 2] ('ffffffff, this is a merge test. again ', 14949L)
17:52:36, 734 INFO [sqlalchemy. engine. base. Engine] [worker 2] COMMIT

Some useful things:

1. Use of merge: This record exists in the Database Table, mainly used to update a record in the data table. Compare the data in the table with that in the source table. If a record exists, the data in the new target table is updated based on the value in the source table. If no record exists, the data is added to the target table. The MERGE statement is used to query another table based on the connection conditions of one table or subquery. The join condition matching is updated, and the INSERT statement cannot be executed. This syntax only requires one full table scan to complete all the work, and the execution efficiency is higher than INSERT + UPDATE.

2. Several statements commonly used for database operations in SQLAlchemy:

Session.query(table)Session.add(record)Session.merge(record)Session.delete(record)Session.commit()