public inbox for [email protected]  

help / color / mirror / Atom feed

From: Adrian Klaver <[email protected]>
To: Daniel Cohen <[email protected]>
To: [email protected]
Subject: Re: Creating dynamically-typed tables using psycopg2's built-in formatting
Date: Fri, 14 Jun 2019 09:07:09 -0700
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAHAzoYkzXi1HFE0PmY3z0qn4SRnSZLdhChPUePf-2VwTM91upg@mail.gmail.com>
References: <CAHAzoYkzXi1HFE0PmY3z0qn4SRnSZLdhChPUePf-2VwTM91upg@mail.gmail.com>

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
[email protected]

view thread (13+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: Creating dynamically-typed tables using psycopg2's built-in formatting
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox