public inbox for [email protected]  

help / color / mirror / Atom feed

From: Christophe Pettus <[email protected]>
To: Daniel Cohen <[email protected]>
Cc: [email protected]
Subject: Re: Creating dynamically-typed tables using psycopg2's built-in formatting
Date: Thu, 13 Jun 2019 13:00:20 -0700
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAHAzoYkzXi1HFE0PmY3z0qn4SRnSZLdhChPUePf-2VwTM91upg@mail.gmail.com>
References: <CAHAzoYkzXi1HFE0PmY3z0qn4SRnSZLdhChPUePf-2VwTM91upg@mail.gmail.com>

Hi, Daniel,

First, tbl and "tbl" aren't "totally different":

> xof=# create table tbl (i integer);
> CREATE TABLE
> xof=# create table "tbl" (i integer);
> ERROR:  relation "tbl" already exists

The difference is that putting double quotes around an SQL identifier makes the comparison type-sensitive, and allows for characters not otherwise allowed in identifiers:

> xof=# select * from Tbl;
>  i 
> ---
> (0 rows)
> 
> xof=# select * from "Tbl";
> ERROR:  relation "Tbl" does not exist
> LINE 1: select * from "Tbl";
>                       ^

You can use SQL.identifier, but you need to make sure you are getting the case right; in general, PostgreSQL types are all lower-case, and it's only the lack of double quotes that makes this work:

xof=# create table x (i VARCHAR);
CREATE TABLE
xof=# create table y (i "VARCHAR");
ERROR:  type "VARCHAR" does not exist
LINE 1: create table y (i "VARCHAR");
                          ^
xof=# create table y (i "varchar");
CREATE TABLE

> On Jun 13, 2019, at 12:28, Daniel Cohen <[email protected]> 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:
> 
> 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 
> 
> 

--
-- Christophe Pettus
   [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