Question 115153 in PostgreSQL Question posted in
The official documentation can be found here.

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

Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    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)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search