INSERT Queries

Builds INSERT queries using SqlInsert.

All SQL output below shows the result of get_sql(). Where queries have parameters, get_args() is shown alongside.

Basic Insert

qb = (SQL.insert('users')
      .column('name', 'Alice')
      .column('email', 'alice@example.com'))

INSERT INTO users (name, email)
VALUES (%s, %s)

-- get_args(): ['Alice', 'alice@example.com']

Multiple Columns (kwargs)

Use columns_kwargs() with keyword arguments (columns are sorted alphabetically):

qb = (SQL.insert('users')
      .columns_kwargs(name='Alice', email='alice@example.com', age=30))

INSERT INTO users (age, email, name)
VALUES (%s, %s, %s)

-- get_args(): [30, 'alice@example.com', 'Alice']

Columns and Values

Separate column names from values using columns() and values():

qb = (SQL.insert('films')
      .columns('code', 'title', 'did', 'date_prod', 'kind')
      .values('T_601', 'Yojimbo', 106, '1961-06-16', 'Drama'))

INSERT INTO films (code, title, did, date_prod, kind)
VALUES (%s, %s, %s, %s, %s)

-- get_args(): ['T_601', 'Yojimbo', 106, '1961-06-16', 'Drama']

Values Only

Omit column names and provide only values:

qb = (SQL.insert('films')
      .values('UA502', 'Bananas', 105, '1971-07-13', 'Comedy', '82 minutes'))

INSERT INTO films
VALUES (%s, %s, %s, %s, %s, %s)

-- get_args(): ['UA502', 'Bananas', 105, '1971-07-13', 'Comedy', '82 minutes']

DEFAULT VALUES

qb = (SQL.insert('films')
      .default_values())

INSERT INTO films DEFAULT VALUES

Multiple Rows

Use values_list() to insert multiple rows:

qb = (SQL.insert('films')
      .columns('code', 'title', 'did', 'date_prod', 'kind')
      .values_list([('B6717', 'Tampopo', 110, '1985-02-10', 'Comedy'),
                    ('HG120', 'The Dinner Game', 140, SQL.cat('DEFAULT'), 'Comedy')]))

INSERT INTO films (code, title, did, date_prod, kind)
VALUES
    (%s, %s, %s, %s, %s),
    (%s, %s, %s, DEFAULT, %s)

-- get_args(): ['B6717', 'Tampopo', 110, '1985-02-10', 'Comedy',
--              'HG120', 'The Dinner Game', 140, 'Comedy']

INSERT from SELECT

Without column list (columns not derived from query):

qb = (SQL.insert('films')
      .query(SQL.select()
             .from_table('tmp_films')
             .column('*')
             .where('date_prod', '<', '2004-05-07')))

INSERT INTO films
SELECT *
FROM tmp_films
WHERE date_prod < %s

-- get_args(): ['2004-05-07']

With column list derived from the query (use query_columns()):

qb = (SQL.insert('archive')
      .query_columns()
      .query(SQL.select()
             .from_table('orders')
             .column('id')
             .column('total')
             .where('status', '=', 'completed')))

INSERT INTO archive (id, total)
SELECT id, total
FROM orders
WHERE status = %s

-- get_args(): ['completed']

Table Alias

Use an alias for the target table (useful with ON CONFLICT DO UPDATE):

qb = (SQL.insert('distributors', 'd')
      .column('did', 8)
      .column('dname', 'Anvil Distribution')
      .on_conflict('(did)',
                   SQL.update()
                   .set('dname', SQL.cat("((excluded.dname || ' (formerly ') || d.dname) || ')'"))
                   .where('d.zipcode', '<>', '21201')))

INSERT INTO distributors AS d (did, dname)
VALUES (%s, %s)
ON CONFLICT (did) DO UPDATE
SET dname = ((excluded.dname || ' (formerly ') || d.dname) || ')'
WHERE d.zipcode <> %s

-- get_args(): [8, 'Anvil Distribution', '21201']

ON CONFLICT (Upsert)

PostgreSQL only. See also MySQL’s SQL.insert().ignore() and SQLite’s SQL.insert().or_ignore().

Do nothing on conflict:

qb = (SQL.insert('users')
      .column('email', 'alice@example.com')
      .on_conflict('(email)'))

INSERT INTO users (email)
VALUES (%s)
ON CONFLICT (email) DO NOTHING

-- get_args(): ['alice@example.com']

With update action:

action = (SQL.update()
          .set('name', SQL.cat('excluded.name'))
          .set('updated_at', SQL.cat('now()')))

qb = (SQL.insert('users')
      .column('email', 'alice@example.com')
      .column('name', 'Alice')
      .on_conflict('(email)', action))

INSERT INTO users (email, name)
VALUES (%s, %s)
ON CONFLICT (email) DO UPDATE
SET
    name = excluded.name,
    updated_at = now()

-- get_args(): ['alice@example.com', 'Alice']

With a named constraint:

qb = (SQL.insert('distributors')
      .column('did', 9)
      .column('dname', 'Antwerp Design')
      .on_conflict('ON CONSTRAINT distributors_pkey'))

INSERT INTO distributors (did, dname)
VALUES (%s, %s)
ON CONFLICT ON CONSTRAINT distributors_pkey DO NOTHING

-- get_args(): [9, 'Antwerp Design']

WITH Clause

PostgreSQL only (uses RETURNING in the CTE).

qb = (SQL.insert('employees_log')
      .with_cte_query('upd',
                      SQL.update('employees')
                      .set('sales_count = sales_count + 1')
                      .where('id', '=',
                             SQL.select()
                             .column('sales_person')
                             .from_table('accounts')
                             .where('name', '=', 'Acme Corporation'))
                      .returning('*'))
      .query(SQL.select()
             .columns('*', 'current_timestamp')
             .from_table('upd')))

WITH upd AS (
    UPDATE employees
    SET sales_count = sales_count + 1
    WHERE
        id = (
            SELECT sales_person
            FROM accounts
            WHERE name = %s
        )
    RETURNING *
)
INSERT INTO employees_log
SELECT *, current_timestamp
FROM upd

-- get_args(): ['Acme Corporation']

RETURNING

PostgreSQL only.

qb = (SQL.insert('users')
      .column('name', 'Alice')
      .returning('id', 'created_at'))

INSERT INTO users (name)
VALUES (%s)
RETURNING id, created_at

-- get_args(): ['Alice']