Skip to content

Parameters

The PySQLX-Engine supports the parameters. The parameters are passed as a dictionary to the functions below.

  • query
  • query_first
  • query_as_dict
  • query_first_as_dict
  • execute

These functions are described in the Documentation for methods section.

Parameters are has prepared statements, where the values are converted to rust types and then to SQL types.

Python types > Rust types > SQL types

Note

In development mode, the SQL statements are printed in the console with the parameters builded and raw SQL. But the SQL builded with the parameters is not sent to the database.

The PySQLXEngine supports many Python types with automatic conversion to SQL.

Parameters types

Key: dict key must be a valid string.

Value: dict value can be one of the types bellow:

  • bool
  • bytes
  • date
  • datetime
  • Decimal
  • dict
  • float
  • int
  • list
  • str
  • time
  • tuple
  • UUID
  • enum.Enum
  • None

These types are converted to the corresponding SQL type. This applies to parameters chained in a list or dict that are converted to json.

Example

For this example, the table users will be used. The table users has the following structure:

CREATE TABLE IF NOT EXISTS users (
    id INTEGER, 
    name TEXT, 
    age INTEGER,
    active BOOLEAN
);

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

main.py
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
from pysqlx_engine import PySQLXEngine

async def main():
    db = PySQLXEngine(uri="sqlite:./db.db")
    await db.connect()

    param = {
        "i": 1,
        "n": "John",
        "ag": 20,
        "ac": True
    }
    sql = """
        INSERT INTO users (id, name, age, active) 
        VALUES (:i, :n, :ag, :ac);
    """

    resp = await db.execute(sql=sql, parameters=param)

    print("inserted: ", resp == 1)

import asyncio
asyncio.run(main())
main.py
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
from pysqlx_engine import PySQLXEngineSync

def main():
    db = PySQLXEngineSync(uri="postgresql://user:pass@host:port/db")
    db.connect()

    param = {
        "i": 1,
        "n": "John",
        "ag": 20,
        "ac": True
    }
    sql = """
        INSERT INTO users (id, name, age, active) 
        VALUES (:i, :n, :ag, :ac);
    """

    resp = db.execute(sql=sql, parameters=param)

    print("inserted: ", resp == 1)

# runnig the code
main()

Running the code using the terminal

$ python3 main.py

inserted: True