SQL Alchemy Parametrized Query , binding table name as parameter gives error

Question

I am using parametrized query utilizing Text object in SQL alchemy and are getting different result.

Working example:

import sqlalchemy as sqlal
from sqlalchemy.sql import text

    db_table = 'Cars'
    id_cars = 8
    query = text("""SELECT * 
                    FROM Cars 
                    WHERE idCars = :p2
                 """)
    self.engine.execute(query, {'p2': id_cars})

Example that produces sqlalchemy.exc.ProgrammingError: (pymysql.err.ProgrammingError) (1064, "You have an error in your SQL syntax)

import sqlalchemy as sqlal
from sqlalchemy.sql import text

    db_table = 'Cars'
    id_cars = 8
    query = text("""SELECT * 
                    FROM :p1 
                    WHERE idCars = :p2
                 """)
    self.engine.execute(query, {'p1': db_table, 'p2': id_cars})

Any idea on how I can run the query with a dynamic table name that are also protected from sql injection?

Answer 1

I use PostgreSQL and psycopg2 backend. I was able to do it using:

from psycopg2 import sql
from sqlalchemy import engine

connection: sqlalchemy.engine.Connection
connection.connection.cursor().execute(
    sql.SQL('SELECT * FROM {} where idCars = %s').format(sql.Identifier(db_table)),
    (id_cars, )
)