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
JavaScript
x
39
39
1
import psycopg2
2
from psycopg2 import sql
3
4
def create_table(table_name):
5
""" create tables in the PostgreSQL database"""
6
7
composed_str = sql.SQL(
8
"""CREATE TABLE {}
9
(
10
id SERIAL PRIMARY KEY,
11
col1 decimal,
12
col2 decimal,
13
)
14
""").format(sql.Identifier(table_name))
15
16
conn = None
17
18
try:
19
# read database configuration
20
params = setparams()
21
# connect to the PostgreSQL database
22
conn = psycopg2.connect(**params)
23
# create a new cursor
24
cur = conn.cursor()
25
# execute the INSERT statement
26
cur.execute(composed_str)
27
# commit the changes to the database
28
conn.commit()
29
# close communication with the database
30
cur.close()
31
except (Exception, psycopg2.DatabaseError) as error:
32
print(error)
33
finally:
34
if conn is not None:
35
conn.close()
36
37
if __name__ == '__main__':
38
create_table("test_table_1")
39
When I change to using this code it will work
JavaScript
1
5
1
#works when static
2
sql = (
3
"""
4
CREATE TABLE test_table_1
5
Here is the error
JavaScript
1
3
1
syntax error at or near "'test_node_3'"
2
LINE 2: CREATE TABLE 'test_node_3'
3
The only thing not in this is a method to manage connection parameters that is working
new error
JavaScript
1
2
1
[Previous line repeated 996 more times]
2
RecursionError: maximum recursion depth exceeded
Advertisement
Answer
Using sql module from psycopg2
to dynamically build a CREATE TABLE
statement safely.
JavaScript
1
26
26
1
import psycopg2
2
from psycopg2 import sql
3
4
def create_table(table_name):
5
""" create tables in the PostgreSQL database"""
6
7
composed_str = sql.SQL(
8
"""CREATE TABLE {}
9
(
10
id SERIAL PRIMARY KEY,
11
col1 decimal,
12
col2 decimal,
13
)
14
""").format(sql.Identifier(table_name))
15
return composed_str
16
17
out_sql = create_table('test_tbl')
18
19
print(out_sql.as_string(con))
20
CREATE TABLE "test_tbl"
21
(
22
id SERIAL PRIMARY KEY,
23
col1 decimal,
24
col2 decimal,
25
)
26
You can then use the built SQL to actually create the table:
JavaScript
1
2
1
cur.execute(out_sql)
2