How to parameterize create table function for PostgreSQL in python

Question

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

share the code part where you call the `create_table` function. — Deepan, Aug 13 '22 at 16:09
Show the code where you format the sql string to fill in the table name. — John Gordon, Aug 13 '22 at 16:13
The answer is here [sql](https://www.psycopg.org/docs/sql.html). — Adrian Klaver, Aug 13 '22 at 16:17
Your method, `create_table`, does not execute SQL in database. — Parfait, Aug 13 '22 at 16:32

Adrian Klaver · Accepted Answer · 2022-08-13T16:40:45.053

3

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)

edited Aug 13 '22 at 16:40

answered Aug 13 '22 at 16:35

Adrian Klaver

15,886
2
17
28

I got a new error, if you could take a look at it thanks so much! – Kyle Sponable Aug 13 '22 at 17:03
1) Why split the function into `create_sql` and `create_table`? 2) I am not seeing where `sql` is being returned from `create_table` and used? 3) It is a recursion error so somewhere you have loop you are not breaking out of. 4) What does the error show for the 'Previous line ...'? Add answers as update to question. – Adrian Klaver Aug 13 '22 at 17:10
thanks your return threw me I thought it was supposed to be a second function for some reason I edited it to show the working version. As an aside can you edit to show how to update the column names? Thanks again! – Kyle Sponable Aug 13 '22 at 18:58
Take a look at my answer [here](https://stackoverflow.com/questions/73135956/when-create-table-psycopg2-errors-syntaxerror-syntax-error-at-end-of-input/73156525#73156525) and see if that is what you want. – Adrian Klaver Aug 13 '22 at 19:37

score 0 · Answer 2 · answered Aug 13 '22 at 16:19

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

answered Aug 13 '22 at 16:19

zaki98

1,048
8
13

This is just for some deployment scripts is there a better way to do this? – Kyle Sponable Aug 13 '22 at 16:32
The quoting is not really the issue. `"test_node_3"` will create an error also. The issue is you cannot do parameter substitution(`%(table_name)s`) on identifiers. You have to do some form of string composition. See here [sql](https://www.psycopg.org/docs/sql.html) for why and solutions. – Adrian Klaver Aug 13 '22 at 16:46
The better way is to use something like [Sqitch](https://sqitch.org/) where you deploy SQL scripts under version control and with the ability to migrate and revert. – Adrian Klaver Aug 13 '22 at 16:51

How to parameterize create table function for PostgreSQL in python

2 Answers2