DML with return

Although PySQLXEngine has a method for DML execute which returns the number of rows affected.

It is possible to use the query* methods to get an output/returning from the database.

execute always returns the number of rows affected. Maybe this is not useful, because if you make an insert and want the id as a return, the execute will limit you.

Although sql statements are atomic, one execution at a time, most modern databases bring sensational features like RETUNING or OUTPUT in the case of SQL Server that can return a value after the insert .

So since we need something to be returned, we can use the query* methods.

Examples

In this examples we use the user table, which has the structure below. Change the types to your database types. In this SQLite is used.

CREATE TABLE IF NOT EXISTS user (
    id          INTEGER PRIMARY KEY,
    first_name  TEXT,
    last_name   TEXT,
    created_at  TEXT,
    updated_at  TEXT
)

Warning

In this example, MySQL is not mentioned because it does not have reliable support for this type of execution.

You can use SELECT LAST_INSERT_ID(); after inserting the row, but it is not guaranteed to be the correct ID, given that in a concurrent DB, many inserts at the same time, and the LAST_INSERT_ID() function takes only the last one. If possible, start using MariaDB; in addition to being more reliable, it is also an up-to-date open source.

PostgreSQL, SQLite and MariaDB

Create a main.py file and add the code examples below.

AsyncSync

from pysqlx_engine import PySQLXEngine

uri = "sqlite:./db.db"
db = PySQLXEngine(uri=uri)

async def main():
    await db.connect()

    sql = """sql
        INSERT INTO user (
            first_name, 
            last_name, 
            created_at, 
            updated_at) 
        VALUES (
            'bob', 
            'test', 
            '2022-05-30 05:47:51', 
            '2022-05-30 05:47:51'
        )
        RETURNING id;
    """

    row = await db.query(sql=sql)
    print(row)

import asyncio
asyncio.run(main())

from pysqlx_engine import PySQLXEngineSync

uri = "sqlite:./db.db"
db = PySQLXEngineSync(uri=uri)

def main():
    db.connect()

    sql = """sql
        INSERT INTO user (
            first_name, 
            last_name, 
            created_at, 
            updated_at) 
        VALUES (
            'bob', 
            'test', 
            '2022-05-30 05:47:51', 
            '2022-05-30 05:47:51'
        )
        RETURNING id;
    """

    row = db.query(sql=sql)
    print(row)

# running the code
main()

Running the code using the terminal

$ python3 main.py

[BaseRow(id=1)]

Microsoft SQL Server

Create a main.py file and add the code examples below.

AsyncSync

from pysqlx_engine import PySQLXEngine

uri = "sqlite:./db.db"
db = PySQLXEngine(uri=uri)

async def main():
    await db.connect()

    sql = """sql
        INSERT INTO user (
            first_name, 
            last_name, 
            created_at, 
            updated_at)
        OUTPUT Inserted.id
        VALUES (
            'bob', 
            'test', 
            '2022-05-30 05:47:51', 
            '2022-05-30 05:47:51'
        );
    """

    row = await db.query(sql=sql)
    print(row)

import asyncio
asyncio.run(main())

from pysqlx_engine import PySQLXEngineSync

uri = "sqlite:./db.db"
db = PySQLXEngineSync(uri=uri)

def main():
    db.connect()

    sql = """sql
        INSERT INTO user (
            first_name, 
            last_name, 
            created_at, 
            updated_at)
        OUTPUT Inserted.id
        VALUES (
            'bob', 
            'test', 
            '2022-05-30 05:47:51', 
            '2022-05-30 05:47:51'
        );
    """

    row = db.query(sql=sql)
    print(row)

# running the code
main()

Running the code using the terminal

$ python3 main.py

[BaseRow(id=1)]