Using INSERT Statements

When using Core as well as when using the ORM for bulk operations, a SQL INSERT statement is generated directly using the insert() function - this function generates a new instance of Insert which represents an INSERT statement in SQL, that adds new data into a table.

ORM Readers -

This section details the Core means of generating an individual SQL INSERT statement in order to add new rows to a table. When using the ORM, we normally use another tool that rides on top of this called the unit of work, which will automate the production of many INSERT statements at once. However, understanding how the Core handles data creation and manipulation is very useful even when the ORM is running it for us. Additionally, the ORM supports direct use of INSERT using a feature called Bulk / Multi Row INSERT, upsert, UPDATE and DELETE.

To skip directly to how to INSERT rows with the ORM using normal unit of work patterns, see Inserting Rows using the ORM Unit of Work pattern.

The insert() SQL Expression Construct

A simple example of Insert illustrating the target table and the VALUES clause at once:

>>> from sqlalchemy import insert
>>> stmt = insert(user_table).values(name="spongebob", fullname="Spongebob Squarepants")

The above stmt variable is an instance of Insert. Most SQL expressions can be stringified in place as a means to see the general form of what’s being produced:

>>> print(stmt)

INSERT INTO user_account (name, fullname) VALUES (:name, :fullname)

The stringified form is created by producing a Compiled form of the object which includes a database-specific string SQL representation of the statement; we can acquire this object directly using the ClauseElement.compile() method:

>>> compiled = stmt.compile()

Our Insert construct is an example of a “parameterized” construct, illustrated previously at Sending Parameters; to view the name and fullname bound parameters, these are available from the Compiled construct as well:

>>> compiled.params
{'name': 'spongebob', 'fullname': 'Spongebob Squarepants'}

Executing the Statement

Invoking the statement we can INSERT a row into user_table. The INSERT SQL as well as the bundled parameters can be seen in the SQL logging:

>>> with engine.connect() as conn:
...     result = conn.execute(stmt)
...     conn.commit()

BEGIN (implicit)
INSERT INTO user_account (name, fullname) VALUES (?, ?)
[...] ('spongebob', 'Spongebob Squarepants')
COMMIT

In its simple form above, the INSERT statement does not return any rows, and if only a single row is inserted, it will usually include the ability to return information about column-level default values that were generated during the INSERT of that row, most commonly an integer primary key value. In the above case the first row in a SQLite database will normally return 1 for the first integer primary key value, which we can acquire using the