Question 207507 in PostgreSQL Question posted in
The official documentation can be found here.

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

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.

Tags:

2

Answers


  1. 0

    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)
    Login or Signup to reply.
  2. 0

    Using dictionaries seems to work but I think the more correct version uses insert().values() and a Table object. Are you not able to generate the Table object?

    
with engine.connect() as conn, conn.begin():
    stmt = text("INSERT INTO users (id, name) VALUES(:id, :name)")
    conn.execute(stmt, [dict(id=1, name="test"), dict(id=2, name="testagain")])
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search