python - Issue with parameters of SQLAlchemy execute() - Stack Overflow

admin管理员组

文章数量:1296454

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

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!

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

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!

• python
• sql
• sqlalchemy
• parameters

Share Improve this question Follow edited Feb 12 at 4:04 Parfait 108k1919 gold badges100100 silver badges134134 bronze badges asked Feb 11 at 22:47 DDigitsDDigits 15777 bronze badges 1

• 1 Related (though I wouldn't necessarily recommend it). – snakecharmerb Commented Feb 12 at 6:57

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 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./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 sessionmit()


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


asyncio.run(main())

本文标签： pythonIssue with parameters of SQLAlchemy execute()Stack Overflow