0
Follow
0
View

PostGres - Insert list of tuples using execute instead of executemany

echo_xiuxiu 注册会员
2023-01-24 19:54

No, this isn’t secure or even reliable – Python repr isn’t compatible with PostgreSQL string syntax (try some strings with single quotes, newlines, or backslashes).

  • Consider passing array parameters instead and using UNNEST:

    cursor.execute(
        "INSERT INTO types (name, created_at)"
        " SELECT name, created_at FROM UNNEST (%(names)s, %(created_ats)s) AS t",
        {
            'names': ['TIMER', 'Sequence1', ...],
            'created_ats': ['2022-04-09 03:19:49', ...],
        })
    

    This is the best solution, as the query doesn’t depend on the parameters (can be prepared and cached, statistics can be easily grouped, makes the absence of SQL injection vulnerability obvious, can easily log queries without data).

  • Failing that, build a query that’s only dynamic in the number of parameters, like VALUES ((%s, %s, ...), (%s, %s, ...), ...). Note that PostgreSQL has a parameter limit, so you might need to produce these in batches.

  • Failing that, use psycopg2.sql.Literal.

About the Author

Question Info

Publish Time
2023-01-24 19:54
Update Time
2023-01-24 19:54

Related Question