Working with normlite CRUD statements

Intro

In this guide, you will learn what CRUD constructs normlite provides.

You will apply CRUD statements on the following table:

metadata = MetaData()
students = Table(
    'students',
    metadata,
    Column('id', Integer()),
    Column('name', String(is_title=True)),
    Column('grade', String()),
    Column('is_active', Boolean()),
    Column('started_on', Date()),
)

The insert() constructor

To add new rows into a table, use the insert() constructor:

stmt = insert(students)

The insert() constructor procures a Insert object and it connects it to the table students. Now, you can add rows in several ways.

See also

• normlite.sql.dml.Insert for a comprehensive class documentation

• normlite.sql.dml.insert() for the constructor

Add rows with the values() clause.

The Insert class provides the API values() to specify rows. The most simple form is to provide keyword arguments list specifying the value for each columns, as follows:

stmt = stmt.values(
    id=123456, 
    name='Galileo Galilei', 
    grade='A', 
    is_active=False, 
    started_on=date(1581, 9, 1)
)

You can also pass in a tuple with all values:

stmt = stmt.values((123456, 'Galileo Galilei', 'A', False, date(1581, 9, 1)))

Or you can pass a mapping, like this:

stmt = stmt.values({
    "id": 123456, 
    "name": "Galileo Galilei", 
    "grade": "A", 
    "is_active": False, 
    "started_on": date(1581, 9, 1)
})

You can also insert multiple rows using .values(). Just provide a list of row values (e.g. a list of mappings).

stmt = stmt.values([
    {
        "id": 123456, 
        "name": "Galileo Galilei", 
        "grade": "A", 
        "is_active": False, 
        "started_on": date(1581, 9, 1)
    },
    {
        "id": 123457, 
        "name": "Isaac Newton", 
        "grade": "B", 
        "is_active": False, 
        "started_on": date(1705, 9, 1)
    },
    {
        "id": 123458, 
        "name": "Ada Lovelace", 
        "grade": "C", 
        "is_active": False, 
        "started_on": date(1867, 9, 1)
    },
])

Add rows with the parameters keyword argument in .execute()

This is likely the most common way to add rows.

result = connection.execute(
    stmt,
    parameters={
        "id": 123456, 
        "name": "Galileo Galilei", 
        "grade": "A", 
        "is_active": False, 
        "started_on": date(1581, 9, 1)
    }
)

The parameters argument accepts both sigle parameters (e.g., a mapping) or multiple parameters (e.g., a sequence of mappings):

result = connection.execute(
    stmt,
    parameters=[
    {
        "id": 123456, 
        "name": "Galileo Galilei", 
        "grade": "A", 
        "is_active": False, 
        "started_on": date(1581, 9, 1)
    },
    {
        "id": 123457, 
        "name": "Isaac Newton", 
        "grade": "B", 
        "is_active": False, 
        "started_on": date(1705, 9, 1)
    },
    {
        "id": 123458, 
        "name": "Ada Lovelace", 
        "grade": "C", 
        "is_active": False, 
        "started_on": date(1867, 9, 1)
    },
])

Return behavior for INSERT statements.

You can specify which columns the Insert object should return in the result rows by using the .returning() method. The following example lets you return all columns, including the system columns, in the result rows.

stmt = insert(students).returning(*students.c)
result = connection.execute(
    stmt,
    parameters=[
        {"id": 123458, "name": "John", "grade": "A", "is_active": True, "started_on": "2026-01-01"},
        {"id": 123459, "name": "Mark", "grade": "A", "is_active": True, "started_on": "2026-01-01"},
        {"id": 123460, "name": "Paula", "grade": "B", "is_active": True, "started_on": "2026-01-01"},
    ]
)

When you use the .returning() method, .returned_primary_keys_rows returns None and the implicit_returning execution option is ignored.

The delete() constructor

To delete rows from a table, use the delete() constructor:

stmt = (
    delete(students)
    .where(students.c.started_on.after("2020-02-01") & students.c.started_on.before("2022-01-01"))
)

The delete() constructor procures a Delete object and it connects it to the table students. Now, you can add rows in several ways.

See also

• normlite.sql.dml.Delete for a comprehensive class documentation

• normlite.sql.dml.delete() for the constructor

Summary of returning behavior for INSERT/DELETE

Feature	Implicit Returning	Explicit .returning()
Requires API post-fetch	No	Yes
Returns full rows	No	Yes
Returns primary keys	Yes	No
Uses returned_primary_keys_rows	Yes	No
Performance	Fast	Slower (extra API call)

If performance is important, set the execution option implicit_returning to True and store the _returned_primary_keys_rows for later use.

Note

normlite.engine.cursor.CursorResult.returned_primary_keys_rows provides a sequence of one-value tuples containing the object ids of the deleted rows.