How to pass multiple parameters to INSERT INTO ... VALUES using SQLAlchemy Connection.execute()?

Question

The following is valid SQL for PostgreSQL.

INSERT INTO schema.table (id, letter)
VALUES 
    (1, 'a'),
    (2, 'b'),
    ...

I would like to execute a similar, parameterized statement using SQLAlchemy. I am using SQLAlchemy 1.4* Connection.execute(), but I do not have access to the table mapper class (ORM model).

The following does not work, but does demonstrate what I am trying to achieve.

statement: str = """
    INSERT INTO schema.table (id, letter)
    VALUES :values
"""

values: Tuple[Tuple[int, str], ...] = (
    (1, "a"),
    (2, "b"),
)

with engine.connect() as connection:
    connection.execute(
        sqlalchemy.text(statement),
        {"values": values},
    )

In this exact example, values is a tuple; it gets bound as a tuple of tuples, which does not fit what I am trying to achieve ("INSERT has more expressions than target columns"). Likewise, using a list of tuples creates a SQL ARRAY.

Q: Why don't you just use an f-string or "...".format(...)?

A: I've read that this is poor practice.

Question: How can I properly "unpack" the values parameters? Alternatively, what is the best way to achieve what I am seeking (without using the Table ORM class)?

---

* Yes, I am aware that SQLAlchemy 1.4 is deprecated at the time of this question's posting.

Answer 1

You can use the following, you were almost there, you just had to change the insert query and make the binding as a list of dicts.

As seen in the docs and slightly modified for your use-case.

statement: str = "INSERT INTO schema.table (id, letter) VALUES (:id, :letter)"

values = (
    (1, "a"),
    (2, "b"),
)

values = [{'id': id, 'letter': letter} for id, letter in values]

with engine.connect() as connection:
    connection.execute(text(statement), values)