Contents

Inserting Rows with Core

When using Core, a SQL INSERT statement is generated 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 - The way that rows are INSERTed into the database from an ORM perspective makes use of object-centric APIs on the Session object known as the unit of work process, and is fairly different from the Core-only approach described here. The more ORM-focused sections later starting at Inserting Rows with the ORM subsequent to the Expression Language sections introduce this.

from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String, ForeignKey

metadata = MetaData()
user_table = Table(
    "user_account",
    metadata,
    Column('id', Integer, primary_key=True),
    Column('name', String(30)),
    Column('fullname', String)
)
address_table = Table(
    "address",
    metadata,
    Column('id', Integer, primary_key=True),
    Column('user_id', ForeignKey('user_account.id'), nullable=False),
    Column('email_address', String, nullable=False)
)

engine = create_engine("sqlite+pysqlite:///:memory:", echo=True, future=True)
metadata.create_all(engine)

2021-10-04 01:45:35,017 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2021-10-04 01:45:35,018 INFO sqlalchemy.engine.Engine PRAGMA main.table_info("user_account")
2021-10-04 01:45:35,019 INFO sqlalchemy.engine.Engine [raw sql] ()
2021-10-04 01:45:35,020 INFO sqlalchemy.engine.Engine PRAGMA temp.table_info("user_account")
2021-10-04 01:45:35,020 INFO sqlalchemy.engine.Engine [raw sql] ()
2021-10-04 01:45:35,021 INFO sqlalchemy.engine.Engine PRAGMA main.table_info("address")
2021-10-04 01:45:35,021 INFO sqlalchemy.engine.Engine [raw sql] ()
2021-10-04 01:45:35,022 INFO sqlalchemy.engine.Engine PRAGMA temp.table_info("address")
2021-10-04 01:45:35,023 INFO sqlalchemy.engine.Engine [raw sql] ()
2021-10-04 01:45:35,024 INFO sqlalchemy.engine.Engine 
CREATE TABLE user_account (
	id INTEGER NOT NULL, 
	name VARCHAR(30), 
	fullname VARCHAR, 
	PRIMARY KEY (id)
)


2021-10-04 01:45:35,025 INFO sqlalchemy.engine.Engine [no key 0.00069s] ()
2021-10-04 01:45:35,026 INFO sqlalchemy.engine.Engine 
CREATE TABLE address (
	id INTEGER NOT NULL, 
	user_id INTEGER NOT NULL, 
	email_address VARCHAR NOT NULL, 
	PRIMARY KEY (id), 
	FOREIGN KEY(user_id) REFERENCES user_account (id)
)


2021-10-04 01:45:35,026 INFO sqlalchemy.engine.Engine [no key 0.00044s] ()
2021-10-04 01:45:35,027 INFO sqlalchemy.engine.Engine COMMIT

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 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()

2021-10-04 01:45:35,071 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2021-10-04 01:45:35,072 INFO sqlalchemy.engine.Engine INSERT INTO user_account (name, fullname) VALUES (?, ?)
2021-10-04 01:45:35,072 INFO sqlalchemy.engine.Engine [generated in 0.00108s] ('spongebob', 'Spongebob Squarepants')
2021-10-04 01:45:35,073 INFO sqlalchemy.engine.Engine 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 inserted_primary_key accessor:

result.inserted_primary_key

(1,)

Tip

inserted_primary_key returns a tuple because a primary key may contain multiple columns. This is known as a composite primary key. The inserted_primary_key is intended to always contain the complete primary key of the record just inserted, not just a “cursor.lastrowid” kind of value, and is also intended to be populated regardless of whether or not “autoincrement” were used, hence to express a complete primary key it’s a tuple.

Changed in version 1.4.8: the tuple returned by :attr:_engine.CursorResult.inserted_primary_key is now a named tuple fullfilled by returning it as a :class:_result.Row object.

INSERT usually generates the “values” clause automatically

The example above made use of the values() method to explicitly create the VALUES clause of the SQL INSERT statement. This method in fact has some variants that allow for special forms such as multiple rows in one statement and insertion of SQL expressions. However the usual way that Insert is used is such that the VALUES clause is generated automatically from the parameters passed to the execute() method; below we INSERT two more rows to illustrate this:

with engine.connect() as conn:
    result = conn.execute(
        insert(user_table),
        [
            {"name": "sandy", "fullname": "Sandy Cheeks"},
            {"name": "patrick", "fullname": "Patrick Star"}
        ]
    )
    conn.commit()

2021-10-04 01:45:35,087 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2021-10-04 01:45:35,088 INFO sqlalchemy.engine.Engine INSERT INTO user_account (name, fullname) VALUES (?, ?)
2021-10-04 01:45:35,089 INFO sqlalchemy.engine.Engine [generated in 0.00124s] (('sandy', 'Sandy Cheeks'), ('patrick', 'Patrick Star'))
2021-10-04 01:45:35,090 INFO sqlalchemy.engine.Engine COMMIT

The execution above features “executemany” form first illustrated at Sending Multiple Parameters, however unlike when using the text() construct, we didn’t have to spell out any SQL. By passing a dictionary or list of dictionaries to the execute() method in conjunction with the Insert construct, the Connection ensures that the column names which are passed will be expressed in the VALUES clause of the Insert construct automatically.

Deep Alchemy

Hi, welcome to the first edition of Deep Alchemy. The person on the left is known as The Alchemist, and you’ll note they are not a wizard, as the pointy hat is not sticking upwards. The Alchemist comes around to describe things that are generally more advanced and/or tricky and additionally not usually needed, but for whatever reason they feel you should know about this thing that SQLAlchemy can do.

In this edition, towards the goal of having some interesting data in the address_table as well, below is a more advanced example illustrating how the values() method may be used explicitly while at the same time including for additional VALUES generated from the parameters. A scalar subquery is constructed, making use of the select() construct introduced in the next section, and the parameters used in the subquery are set up using an explicit bound parameter name, established using the bindparam() construct.

This is some slightly deeper alchemy just so that we can add related rows without fetching the primary key identifiers from the user_table operation into the application. Most Alchemists will simply use the ORM which takes care of things like this for us.

from sqlalchemy import select, bindparam
scalar_subquery = (
    select(user_table.c.id).
    where(user_table.c.name==bindparam('username')).
    scalar_subquery()
)

with engine.connect() as conn:
    result = conn.execute(
        insert(address_table).values(user_id=scalar_subquery),
        [
            {"username": 'spongebob', "email_address": "spongebob@sqlalchemy.org"},
            {"username": 'sandy', "email_address": "sandy@sqlalchemy.org"},
            {"username": 'sandy', "email_address": "sandy@squirrelpower.org"},
        ]
    )
    conn.commit()

{opensql}BEGIN (implicit)
INSERT INTO address (user_id, email_address) VALUES ((SELECT user_account.id
FROM user_account
WHERE user_account.name = ?), ?)
[...] (('spongebob', 'spongebob@sqlalchemy.org'), ('sandy', 'sandy@sqlalchemy.org'),
('sandy', 'sandy@squirrelpower.org'))
COMMIT{stop}

INSERT…FROM SELECT

The Insert construct can compose an INSERT that gets rows directly from a SELECT using the from_select() method:

from sqlalchemy import select

select_stmt = select(user_table.c.id, user_table.c.name + "@aol.com")
insert_stmt = insert(address_table).from_select(
    ["user_id", "email_address"], select_stmt
)
print(insert_stmt)

INSERT INTO address (user_id, email_address) SELECT user_account.id, user_account.name || :name_1 AS anon_1 
FROM user_account

INSERT…RETURNING

The RETURNING clause for supported backends is used automatically in order to retrieve the last inserted primary key value as well as the values for server defaults. However the RETURNING clause may also be specified explicitly using the returning() method; in this case, the Result object that’s returned when the statement is executed has rows which can be fetched:

insert_stmt = insert(address_table).returning(address_table.c.id, address_table.c.email_address)
print(insert_stmt)

INSERT INTO address (id, user_id, email_address) VALUES (:id, :user_id, :email_address) RETURNING address.id, address.email_address

It can also be combined with from_select(), as in the example below that builds upon the example stated in INSERT…FROM SELECT:

select_stmt = select(user_table.c.id, user_table.c.name + "@aol.com")
insert_stmt = insert(address_table).from_select(
    ["user_id", "email_address"], select_stmt
)
print(insert_stmt.returning(address_table.c.id, address_table.c.email_address))

INSERT INTO address (user_id, email_address) SELECT user_account.id, user_account.name || :name_1 AS anon_1 
FROM user_account RETURNING address.id, address.email_address

Tip

The RETURNING feature is also supported by UPDATE and DELETE statements, which will be introduced later in this tutorial. The RETURNING feature is generally 1 only supported for statement executions that use a single set of bound parameters; that is, it wont work with the “executemany” form introduced at Sending Multiple Parameters. Additionally, some dialects such as the Oracle dialect only allow RETURNING to return a single row overall, meaning it won’t work with “INSERT..FROM SELECT” nor will it work with multiple row Update or Delete forms.

See also

Insert - in the SQL Expression API documentation

1

There is internal support for the psycopg2 dialect to INSERT many rows at once and also support RETURNING, which is leveraged by the SQLAlchemy ORM. However this feature has not been generalized to all dialects and is not yet part of SQLAlchemy’s regular API.