Been messing with this for a few hours today. I'm not a SQLAlchemy expert but I do want to be as careful as I can be to prevent SQLInjections. Goal: Generate this SQL Statement

CREATE TEMP TABLE "temp_12345_etc" AS
     SELECT * FROM "sometable" LIMIT 0

Nieve appraoch would be something like

def create_temp_table_from_table(self, from_table_name, temp_table_name):
        self.connection.execute(f"CREATE TEMP TABLE \"{temp_table_name}\" AS SELECT * FROM \"{from_table_name}\" LIMIT 0")

Trying to be good about this is

def create_temp_table_from_table(self, from_table_name, temp_table_name):
        create_temp_table_sql = sqlalchemy.text("""
        CREATE TEMP TABLE :temp_table_name AS
        SELECT * FROM :from_table_name LIMIT 0
        """)
        self.connection.execute(create_temp_table_sql,
        temp_table_name=temp_table_name,
        from_table_name=from_table_name)

Seems right from a distance, but this generates sql like

CREATE TEMP TABLE 'temp_table_name' AS 
SELECT * FROM 'from_thistable' LIMIT 0

Issue with that is table names need to be double quotes. So we could do something like this to be cheeky

def create_temp_table_from_table(self, from_table_name, temp_table_name):
        create_temp_table_sql = sqlalchemy.text("""
        CREATE TEMP TABLE ":temp_table_name" AS
        SELECT * FROM ":from_table_name" LIMIT 0
        """)
        self.connection.execute(create_temp_table_sql,
        temp_table_name=temp_table_name,
        from_table_name=from_table_name)

Which gives you

CREATE TEMP TABLE "'temp_table_name'" AS 
SELECT * FROM "'from_thistable'" LIMIT 0

Which kind of works except for the fact that

"'from_thistable'"

points to an absolute table name while

"from_thistable"

points to a relative table (ie in your current schema) Which leads me to diving into how we go from

sqlalchemy.text

to SQL. Which gets you into the internals of SQLAlchemy with Bind Parameters re https://docs.sqlalchemy.org/en/14/core/sqlelement.html?highlight=bindparams#sqlalchemy.sql.expression.bindparam Maybe we could specify a certain Type https://docs.sqlalchemy.org/en/14/core/type_api.html#sqlalchemy.types.TypeEngine and makeup a column https://docs.sqlalchemy.org/en/14/core/metadata.html?highlight=quote#sqlalchemy.schema.Column.params.quote

But before I start learning about how Columns specify a Type Engine and how that all boils down to BindParameters I thought I'd just punt as someone here may know more! 😄

Maybe being worried about SQL Injections in this context is a bit absurd? But I'd rather be safe if we can be, maybe that's a bad assumption 🤷