I’m trying to dynamically create a table in postgres with psycopg2. It seems when the arguments are passed, pyscopg2 is not escaping quotations and understanding my query. Code looks like this:
input_string_tup = ('col1 int', 'col2 varchar(2)', ...) create_table_str = sql.SQL("CREATE TABLE {} ({data})").format( sql.SQL(table_name), data=sql.SQL(", ").join(sql.Composed(sql.Identifier(i) for i in input_string_tup) )) execute_batch(cur, create_table_str, [input_string_tup])
The error I’m getting:
psycopg2.errors.SyntaxError: syntax error at end of input LINE 1: ...", "shape_area numeric(8, 6)", "shape_length numeric(8, 6)") ^
What print(create_table_str.as_string(conn)) outputs:
CREATE TABLE my_table ("col1 int", "col2 varchar(2)", "col3 int", ... )
Edit to show modified answer that works with no string concatenation
input_string_tup = (('col1', 'int'), ('col2, varchar(2)'), ...) create_table_str = sql.SQL("CREATE TABLE {} ({data})").format( sql.SQL(table_name), data=sql.SQL(", ").join(sql.Composed([sql.Identifier(i[0]), sql.SQL(' '), sql.SQL(i[1])]) for i in input_string_tup))
Thanks to Adrian for the help
Advertisement
Answer
sql.Identifier is for SQL objects only e.g. table/column names not the types: https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS
Try:
input_string_tup = (('col1', 'int'), ('col2', 'varchar(2)')) table_name = 'test_table' create_table_str = sql.SQL("CREATE TABLE {} ({data})").format( sql.SQL(table_name), data=sql.SQL(", ").join(sql.Composed(sql.Identifier(i[0]) + sql.SQL(' ' + i[1]) for i in input_string_tup))) print(create_table_str.as_string(con)) CREATE TABLE test_table ("col1" int, "col2" varchar(2)) cur.execute(create_table_str)