Issue with parameters of SQLAlchemy execute()

Question

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!

Related (though I wouldn't necessarily recommend it).

snakecharmerb
– snakecharmerb

2025-02-12 06:57:12 +00:00
Commented Feb 12 at 6:57 — snakecharmerb
– snakecharmerb, Commented Feb 12 at 6:57

Zhenhir · Accepted Answer · 2025-02-11 23:41:25Z

1

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())

answered Feb 11 at 23:41

Zhenhir

1,2389 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

DDigits Feb 11 at 23:54

So I cant use (?,?,?) anymore?

Zhenhir Feb 12 at 0:28

Not while using text() afaik. You might be able to pass the query in without using text, but it's not database agnostic, personally i'd still recommend switching to method 1 above.

Parfait Feb 12 at 4:02

For future readers who should follow best practice, consider removing second half of this answer especially since it detracts from OP's use of parameters. Also, please explain the use of asynchio.

Collectives™ on Stack Overflow

Issue with parameters of SQLAlchemy execute()

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related