How to parameterize create table function for PostgreSQL in python-CodePudding

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_sql(table_name):
     """ generates SQL for 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



 def create_table(table_name):
     """ create tables in the PostgreSQL database"""

     vals = {"table_name": table_name}

     #works when static 
     sql = create_table(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(sql, vals)
    # 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

CodePudding user response：

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)

CodePudding user response：

Can't be sure since you didn't put how you are executing the function, but it's probably because you are doing create_table("'test_node_3'") instead of create_table("test_node_3") (you are including quotes). It should be noted that using string substitution inside SQL queries is not considered good practice for security reasons, read this for more