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.klaver@aklaver.com
