Skip to content
Advertisement

How to parameterize create table function for PostgreSQL in python

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)
User contributions licensed under: CC BY-SA
3 People found this is helpful
Advertisement