I need to dynamically create tables in python this statement works when the variable is statically identified but when I use a variable I get the following error
 import psycopg2
 from psycopg2 import sql
 def create_table(table_name):
""" create tables in the PostgreSQL database"""
    composed_str = sql.SQL(
    """CREATE TABLE {}
    (
    id SERIAL PRIMARY KEY,
    col1 decimal,
    col2 decimal,
    )
    """).format(sql.Identifier(table_name))
    conn = None
    try:
    # read database configuration
        params = setparams()
    # connect to the PostgreSQL database
        conn = psycopg2.connect(**params)
    # create a new cursor
        cur = conn.cursor()
    # execute the INSERT statement
        cur.execute(composed_str)
    # commit the changes to the database
        conn.commit()
    # close communication with the database
    cur.close()
        except (Exception, psycopg2.DatabaseError) as error:
    print(error)
    finally:
    if conn is not None:
        conn.close()
 if __name__ == '__main__':
     create_table("test_table_1")
When I change to using this code it will work
     #works when static 
     sql = (
         """ 
         CREATE TABLE test_table_1
Here is the error
syntax error at or near "'test_node_3'" LINE 2: CREATE TABLE 'test_node_3'
The only thing not in this is a method to manage connection parameters that is working
new error
[Previous line repeated 996 more times]
RecursionError: maximum recursion depth exceeded
Advertisement
Answer
Using sql module from psycopg2 to dynamically build a CREATE TABLE statement safely.
import psycopg2
from psycopg2 import sql
def create_table(table_name):
     """ create tables in the PostgreSQL database"""
     composed_str = sql.SQL(
         """CREATE TABLE {}
         (
         id SERIAL PRIMARY KEY,
         col1 decimal,
         col2 decimal,
         )
         """).format(sql.Identifier(table_name))
     return composed_str
out_sql = create_table('test_tbl')
print(out_sql.as_string(con))                                                                                                                                                 
CREATE TABLE "test_tbl" 
         (
         id SERIAL PRIMARY KEY,
         col1 decimal,
         col2 decimal,
         )
You can then use the built SQL to actually create the table:
cur.execute(out_sql)