How to compile an INSERT ... ON DUPLICATE KEY UPDATE with SQL Alchemy with support for a bulk insert.

100%

How to compile an INSERT ... ON DUPLICATE KEY UPDATE with SQL Alchemy with support for a bulk insert.

✓ Published0🌍 Public

Last edited Dec 5, 2013

Created on Jul 20, 2016

This example demonstrates a custom SQLAlchemy construct that compiles an `INSERT ... ON DUPLICATE KEY UPDATE` statement for MySQL, enabling efficient bulk upserts. The `Upsert` class extends `sqlalchemy.sql.expression.Insert`, and a `@compiles` decorator registers a MySQL-specific compiler function that transforms the insert into an upsert by appending the `ON DUPLICATE KEY UPDATE` clause with `VALUES()` references. The code detects auto-increment primary keys to use `LAST_INSERT_ID()` for correct ID handling, and it supports both single-row and multi-row parameter sets.

AI-generated description

Similar vizzes

Loading thumbnail…

Efficient bulk update and insert

This example demonstrates two efficient SQL patterns for bulk loading election results into a CartoDB table named `counties_results`. The `insert.sql` file shows how to add new rows using a single `INSERT INTO ... VALUES` statement with multiple tuples, while `update.sql` performs a bulk update by joining the table against a `FROM (VALUES ...)` clause, matching records on `st_usps` and `fips`. Both files contain identical county-level presidential vote percentage data, including Democratic, Republican, and other party shares, ready to be applied to a spatial database.

Aandrewxhill

53% match

Loading thumbnail…

SQL builder sketches for node.js and node-postgres

Three variations of a JavaScript SQL query builder sketch generate `UPDATE` statements with parameterized placeholders for the node-postgres driver. The code demonstrates both a single-call function with positional arguments and a fluent, chainable interface named `UPDATE` that supports `.SET()`, `.WHERE()`, and `.RETURNING()` methods. Common utilities like `Object.keys`, `Array.map`, and `assert` manage column-value pairs, while the chained version tracks placeholder numbering against accumulated `values`. A third, prototype-free variant shows an alternative closure-based approach. The examples illustrate different API design patterns for safely composing dynamic SQL.

RRandomEtc

48% match

Loading thumbnail…

SQL Alchemy Database Introspection through MetaData

This example demonstrates how to introspect a MySQL database schema and its tables using SQLAlchemy’s reflection API. It connects to a database via an engine, retrieves table names through `Inspector.get_table_names()`, and filters them by substring. It then loads a specific table into a pandas DataFrame with `read_sql_table`, and prints the table’s metadata, columns, and data types. The code also creates a `MetaData` instance, accesses a table object like `metadata.tables['stark_master']`, and calls `sa.inspect(engine)` to list all tables.

WWilliamQLiu

46% match

Loading thumbnail…

Add a primary key to a sqlite table

This example demonstrates a SQLite migration technique that adds a primary key to an existing table without losing its data. The code defines three functions: `get_create_table_string` retrieves the original `CREATE TABLE` statement from the `sqlite_master` system table, `add_pk_to_create_table_string` uses the `re` module’s `re.sub` to insert the `PRIMARY KEY` constraint into that string for a specified column, and `add_pk_to_sqlite_table` orchestrates the migration. It executes an SQL `BEGIN TRANSACTION` block that renames the original table, creates a new table using the modified schema, copies rows via `INSERT INTO ... SELECT *`, and drops the old table. The example uses Python’s `sqlite3` connection API and `executescript` to run the multi-statement transaction.

RRobinL

46% match

Loading thumbnail…

Concept for inserting data into SQL Server

This example demonstrates a concept for inserting data into SQL Server by using Power Query’s M language. It shows a table creation script for a demo table with an identity column, a key, and a value, followed by an M query that iterates over a list of key-value pairs. The code uses `List.Accumulate` to loop through the pairs and calls `OleDb.DataSource` with a SQL `INSERT` statement for each iteration, referencing the SQLOLEDB provider to connect to a local database. The result is the total number of affected records from the inserts, though the actual inserted rows are not visualized or returned in the output.

HHugoberry

45% match

Loading thumbnail…

SQL to upload the two big MOT data files to a MySQL database, requires the tables to by using the MyIASM engine.

This example demonstrates a SQL script that loads two large MOT test data files into a MySQL database. It first disables keys on the `testitem` and `testresult` tables, then uses `LOAD DATA LOCAL INFILE` to bulk-import tab-delimited text files, and finally re-enables keys to speed up the insertion. The code relies on MySQL’s `ALTER TABLE ... DISABLE KEYS` and `ENABLE KEYS` directives to optimize index maintenance for the MyISAM storage engine. The data source consists of two plain-text files, `UAT_test_item_2011.txt` and `UAT_test_result_2011.txt`, with fields separated by pipes, and the path is specified by the user.

IIanHopkinson

45% match

Loading thumbnail…

MySQL Cheat Sheet

This example presents a plain-text MySQL cheat sheet as a structured reference for database management and SQL querying. It demonstrates how to organize commands for user administration, database and table creation, data manipulation, and complex queries such as joins and foreign key relationships. The text is formatted with markdown headers and code blocks, showing SQL statements for operations like `CREATE TABLE`, `SELECT`, `INSERT`, and `INNER JOIN`. It also includes configuration tips for adding MySQL to the system PATH via bash commands, while the underlying source is a GitHub Gist authored by Lyndoman.

LLyndoman

45% match

Loading thumbnail…

A schema for uploading MOT test data, as suggested in the user guide with the exception of the `SET storage_engine=MyISAM` which is there to allow the `ENABLE KEYS\DISABLE KEYS` mechanism used in the large table uploads. Also the field `RFRINSPMANDESC TEXT(500) #Variation from suggested schema due to 1074 error` is suggested as `char` in the user guide but for my MySQL setup this exceeds the allowed size for char.

This schema defines the relational structure for importing UK MOT test data, showing the primary tables for test results, test items, item details, item groups, and failure locations. It demonstrates the use of MySQL DDL with explicit primary keys, composite indexes, and the MyISAM storage engine to enable bulk-loading optimizations. The code highlights practical deviations from the official user guide, such as using `TEXT(500)` for inspection descriptions instead of an oversized `CHAR`, accommodating MySQL’s row-size limits while preserving data integrity.

IIanHopkinson

44% match