SqlInsert

class SqlInsert(table, alias=None)

Builds an INSERT query. Created via SQL.insert(table, alias). Inherits from SqlStatement (via SqlQuery).

Parameters:

• table – Target table name string.

• alias – Optional table alias. Renders as INSERT INTO table AS alias. Useful with ON CONFLICT DO UPDATE to reference the target table by alias.

In the examples below, qb refers to a SqlInsert instance:

qb = SQL.insert('users')

column(column, value=NOT_SPECIFIED)

Add a single column/value pair, or a column name without a value (when using separate values() calls).

Parameters:

• column – Column name string.

• value – The value — a Python object (parameterized via a placemarker), or a SqlObj which is rendered as literal SQL. If omitted, only the column name is recorded.

qb.column('name', 'Alice')
# sql:       INSERT INTO users (name) VALUES (%s)
# args:      ['Alice']
# mogrified: INSERT INTO users (name) VALUES ('Alice')

qb.column('created_at', SQL.cat('now()'))
# INSERT INTO users (created_at)
# VALUES (now())       -- no placemarker, literal SQL

columns(*columns)

Set column names (without values). Use with values() or values_list() to provide the values separately.

Parameters:

columns – One or more column name strings.

qb.columns('code', 'title', 'did')

columns_kwargs(**kwargs)

Add multiple column/value pairs. Columns are sorted alphabetically by name.

Parameters:

kwargs – Keyword arguments where keys are column names and values are the corresponding values.

qb.columns_kwargs(name='Alice', email='alice@example.com', age=30)
# INSERT INTO users (age, email, name)
# VALUES (%s, %s, %s)
# args: [30, 'alice@example.com', 'Alice']

values(*values)

Set values for a single row. Use with columns() to specify column names separately, or without columns() for a VALUES-only insert.

Parameters:

values – One or more values — Python objects (parameterized) or SqlObj instances (rendered as literal SQL).

(SQL.insert('films')
 .columns('code', 'title')
 .values('T_601', 'Yojimbo'))
# INSERT INTO films (code, title)
# VALUES (%s, %s)
# args: ['T_601', 'Yojimbo']

(SQL.insert('films')
 .values('UA502', 'Bananas', 105))
# INSERT INTO films
# VALUES (%s, %s, %s)
# args: ['UA502', 'Bananas', 105]

values_list(values_list)

Set multiple rows of values. Each element is a tuple of values.

Parameters:

values_list – A list of tuples, where each tuple contains values for one row.

(SQL.insert('films')
 .columns('code', 'title')
 .values_list([('B6717', 'Tampopo'),
               ('HG120', 'The Dinner Game')]))
# INSERT INTO films (code, title)
# VALUES
#     (%s, %s),
#     (%s, %s)
# args: ['B6717', 'Tampopo', 'HG120', 'The Dinner Game']

default_values()

Generate INSERT INTO table DEFAULT VALUES.

SQL.insert('films').default_values()
# INSERT INTO films DEFAULT VALUES

query(query)

Use a SELECT query as the value source (INSERT INTO … SELECT).

Parameters:

query – A SqlQuery (typically SQL.select()).

(SQL.insert('archive')
 .query(SQL.select()
        .from_table('staging')
        .column('*')))
# INSERT INTO archive
# SELECT *
# FROM staging

query_columns()

When using query(), derive the INSERT column list from the query’s columns. Without this, no column list is emitted.

(SQL.insert('archive')
 .query_columns()
 .query(SQL.select()
        .from_table('staging')
        .column('name')
        .column('email')))
# INSERT INTO archive (name, email)
# SELECT name, email
# FROM staging

on_conflict(condition, action=None)

Add ON CONFLICT clause.

Parameters:

• condition – Conflict target — a string (e.g. '(email)' or 'ON CONSTRAINT pk_name') or a SqlObj for complex targets (e.g. SQL.cat('(did)\nWHERE is_active')).

• action – A SqlUpdate (created via SQL.update()) for DO UPDATE, or None (default) for DO NOTHING.

# DO NOTHING
qb.on_conflict('(email)')
# ON CONFLICT (email) DO NOTHING

# DO UPDATE
action = SQL.update().set('name', SQL.cat('excluded.name'))
qb.on_conflict('(email)', action)
# ON CONFLICT (email) DO UPDATE
# SET name = excluded.name

# Named constraint
qb.on_conflict('ON CONSTRAINT distributors_pkey')
# ON CONFLICT ON CONSTRAINT distributors_pkey DO NOTHING

returning(*columns)

Add a RETURNING clause.

Parameters:

columns – One or more column name strings or SqlObj instances.

qb.returning('id', 'created_at')
# RETURNING id, created_at

recursive()

Mark the WITH clause as RECURSIVE.

with_cte(cte)

Add a SqlCte instance to the WITH clause.

Parameters:

cte – A SqlCte object (created via SQL.cte()).

qb.with_cte(SQL.cte('helper', helper_query))

with_cte_query(alias, query)

Shorthand for adding a CTE.

Parameters:

• alias – CTE name string.

• query – A SqlQuery for the CTE body.

qb.with_cte_query('upd', update_query)