On 6/13/19 12:28 PM, Daniel Cohen wrote:
Hi!
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:
|frompsycopg2 importconnect,sql connection
=connect(host="host",port="port",database="database",user="user",password="pw")defcreate_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
I'm not seeing it:
cursor.execute(sql.SQL("CREATE TABLE {} AS SELECT * FROM
t1").format(sql.Identifier("tbl")))
test_(aklaver)> \d
...
public | t1 | table | aklaver
public | tbl | table | aklaver
...
The question then becomes how is the variable tbl in your script being
assigned to?
|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
--
Adrian Klaver
adrian.kla...@aklaver.com
