I'm working on a project in Python that interacts with a PostgreSQL data warehouse, and I'm using the psycopg2 API. I am looking to create dynamically-typed tables.
For example, I would like to be able to execute the following code:
from psycopg2 import connect, sql
connection = connect(host="host", port="port", database="database", user="user", password="pw")
def create_table(tbl_name, col_name, col_type): query = sql.SQL("CREATE TABLE {} ({} {})".format(sql.Identifier(tbl_name), sql.Identifier(col_name), sql.Identifier(column_type))) connection.execute(query)
create_table('animals', 'name', 'VARCHAR')
and end up with a table named "animals" that contains a column "name" of type VARCHAR. However, when I attempt to run this, I get an error: 'type "VARCHAR" does not exist'. I assume psycopg2's built-in formatter is putting double quotes around the VARCHAR type when there should not be any. Normally, I would just work around this myself, but the documentation is very clear that Python string concatenation should never be used for fear of SQL injection attacks. Security is a concern for this project, so I would like to know if it's possible to create dynamically-typed tables in this fashion using pyscopg2, and if not, whether there exists another third-party API that can do so securely.
A second issue I've had is that when creating tables with a similar methodology, the sql.Identifier() function does not perform as I expect it to. When I use it to dynamically feed in table names, for example, I get varying results. See below:
CREATE TABLE tbl AS SELECT * FROM other_tbl;
in raw SQL creates a table called tbl, whereas
cursor.execute(sql.SQL("CREATE TABLE {} AS SELECT * FROM other_tbl").format(sql.Identifier(tbl))
creates a table called "tbl". The two are different, and
SELECT * FROM tbl;
returns a totally different table than
SELECT * FROM "tbl";
Please let me know if I can fix either of these problems; I want to be able to dynamically feed types into SQL queries, and I want the tables created to be of the form tbl not "tbl". Thank you!
Danny