Issue with parameters of SQLAlchemy execute()

I have the following:

def execute_query(sql_query, engine, params_tuple=None):
    params_tuple = params_tuple if params_tuple else {}
    
    with engine.connect() as connection:
        result = connection.execute(text(sql_query), params_tuple)
        connection.commit()

This method seems to run sucessfully when params_tuple is None, but it fails otherwise. Here is an example:

INSERT INTO x.aggregation_dates (position_date, account_id, horizon)
VALUES (?, ?, ?)

where params_tuple = ('2025-01-31', 12, 22)

Why is this not working? What am I missing?

The error I get is:

List argument must consist only of tuples or dictionaries

Thank you!

Solution

Passing a tuple directly to the parameters via sqlalchemy will not work. You have two options:

Pass the params individually

Change the tuple to a dictionary, naming each column. And use this in the params.

I'd recommend this approach, though it involves more changes than the approach below it is closer to how sqlalchemy intends you to do this. See their guide here for an example: https://docs.sqlalchemy.org/en/14/core/tutorial.html#using-textual-sql

import asyncio

from sqlalchemy import text, tuple_

from . import DB


async def main():
    async with DB() as session:
        params_dict = {"position_data": "2025-01-31", "account_id": 12, "horizon": 22}

        sql_query = f"""
        INSERT INTO aggregation_dates VALUES (:position_data, :account_id, :horizon);
        """

        await session.execute(text(sql_query), params_dict)
        await session.commit()


asyncio.run(main())

Use string formatting

If you substitute the tuple into the raw sql directly, the insert command will work. This is less of a good idea because directly formatting like this does not sanitize the input, so I wouldn't recommend this approach. Although it is quick and easy, only do this if this is just personal code imo.

import asyncio

from sqlalchemy import text, tuple_

from . import DB


async def main():
    async with DB() as session:
        params_tuple = ("2025-01-31", 12, 22)

        sql_query = f"""
        INSERT INTO aggregation_dates VALUES {params_tuple};
        """

        await session.execute(text(sql_query))
        await session.commit()


asyncio.run(main())