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)