Content-Type: text/plain;
	charset=us-ascii
Mime-Version: 1.0 (Mac OS X Mail 11.5 \(3445.9.1\))
Subject: Re: Creating dynamically-typed tables using psycopg2's built-in
 formatting
From: Christophe Pettus <xof@thebuild.com>
In-Reply-To: 
 <CAHAzoYkzXi1HFE0PmY3z0qn4SRnSZLdhChPUePf-2VwTM91upg@mail.gmail.com>
Date: Thu, 13 Jun 2019 13:00:20 -0700
Cc: psycopg@postgresql.org
Content-Transfer-Encoding: quoted-printable
Message-Id: <33071EF2-86DE-4698-BFAA-6F084024A826@thebuild.com>
References: 
 <CAHAzoYkzXi1HFE0PmY3z0qn4SRnSZLdhChPUePf-2VwTM91upg@mail.gmail.com>
To: Daniel Cohen <daniel.m.cohen@berkeley.edu>
Precedence: bulk

Hi, Daniel,

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

> xof=3D# create table tbl (i integer);
> CREATE TABLE
> xof=3D# 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=3D# select * from Tbl;
>  i=20
> ---
> (0 rows)
>=20
> xof=3D# 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=3D# create table x (i VARCHAR);
CREATE TABLE
xof=3D# create table y (i "VARCHAR");
ERROR:  type "VARCHAR" does not exist
LINE 1: create table y (i "VARCHAR");
                          ^
xof=3D# create table y (i "varchar");
CREATE TABLE

> On Jun 13, 2019, at 12:28, Daniel Cohen <daniel.m.cohen@berkeley.edu> =
wrote:
>=20
> Hi!
>=20
> 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.
>=20
> For example, I would like to be able to execute the following code:
>=20
> from psycopg2 import connect,
>  sql
>=20
> connection=20
> =3D connect(host=3D"host", port=3D"port", database=3D"database", =
user=3D"user", password=3D"pw")
>=20
>=20
>=20
> def create_table(tbl_name, col_name, col_type):
>=20
>     query=20
> =3D sql.SQL("CREATE TABLE {} ({} {})".format(sql.Identifier(tbl_name), =
sql.Identifier(col_name), sql.Identifier(column_type)))
>=20
>     connection
> .execute(query)
>=20
>=20
> 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.=20
>=20
> 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:
>=20
> CREATE TABLE tbl AS SELECT * FROM other_tbl;
> in raw SQL creates a table called tbl, whereas
>=20
> 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=20
>=20
> SELECT * FROM tbl;
>=20
> returns a totally different table than
>=20
> 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!
>=20
> Danny=20
>=20
>=20

--
-- Christophe Pettus
   xof@thebuild.com