Passing table name as a parameter in psycopg2

by

According to the official documentation:

If you need to generate dynamically an SQL query (for instance
choosing dynamically a table name) you can use the facilities
provided by the psycopg2.sql module.

The sql module is new in psycopg2 version 2.7. It has the following syntax:

from psycopg2 import sql

cur.execute(
    sql.SQL("insert into {table} values (%s, %s)")
        .format(table=sql.Identifier('my_table')),
    [10, 20])

More on: https://www.psycopg.org/docs/sql.html#module-usage

[Update 2017-03-24: AsIs should NOT be used to represent table or fields names, the new sql module should be used instead: https://stackoverflow.com/a/42980069/5285608 ]

Also, according to psycopg2 documentation:

Warning: Never, never, NEVER use Python string concatenation (+) or string parameters interpolation (%) to pass variables to a SQL query string. Not even at gunpoint.

More Related Contents:

  1. psycopg2: insert multiple rows with one query
  2. Pandas read_sql with parameters
  3. Parameterized queries with psycopg2 / Python DB-API and PostgreSQL
  4. DatabaseError: current transaction is aborted, commands ignored until end of transaction block?
  5. error installing psycopg2, library not found for -lssl
  6. Error: pg_config executable not found when installing psycopg2 on Alpine in Docker
  7. How to set up a PostgreSQL database in Django
  8. Complex foreign key constraint in SQLAlchemy
  9. psycopg2.OperationalError: FATAL: unsupported frontend protocol 1234.5679: server supports 2.0 to 3.0
  10. Psycopg2 image not found
  11. Error Installing Psycopg2 on MacOS 10.9.5
  12. sqlalchemy.exc.NoSuchModuleError: Can’t load plugin: sqlalchemy.dialects:postgres
  13. Create a Postgres database using python
  14. Python SQLite3 SQL Injection Vulnerable Code
  15. Python/postgres/psycopg2: getting ID of row just inserted
  16. Escape SQL “LIKE” value for Postgres with psycopg2
  17. How to set connection timeout in SQLAlchemy
  18. How to quote a string value explicitly (Python DB API/Psycopg2)
  19. Mac + virtualenv + pip + postgresql = Error: pg_config executable not found
  20. “Failed building wheel for psycopg2” – MacOSX using virtualenv and pip
  21. Psycopg2, Postgresql, Python: Fastest way to bulk-insert
  22. Insert Python Dictionary using Psycopg2
  23. Use binary COPY table FROM with psycopg2
  24. How Postgresql COPY TO STDIN With CSV do on conflic do update?
  25. How do I get a raw, compiled SQL query from a SQLAlchemy expression?
  26. Filtering a Pyspark DataFrame with SQL-like IN clause
  27. Connection problems with SQLAlchemy and multiple processes
  28. Is this Python code vulnerable to SQL injection? (SQLite3)
  29. pysqlite: Placeholder substitution for column or table names?
  30. What is the best way to access stored procedures in Django’s ORM

Leave a Comment