Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hbX0G-0001I2-9l for psycopg@arkaria.postgresql.org; Thu, 13 Jun 2019 21:11:24 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1hbWzG-0004Rg-Tj for psycopg@arkaria.postgresql.org; Thu, 13 Jun 2019 21:10:22 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hbWkV-0000FJ-Pm for psycopg@lists.postgresql.org; Thu, 13 Jun 2019 20:55:08 +0000 Received: from mail-yw1-xc29.google.com ([2607:f8b0:4864:20::c29]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hbWkS-0008Gc-T6 for psycopg@postgresql.org; Thu, 13 Jun 2019 20:55:06 +0000 Received: by mail-yw1-xc29.google.com with SMTP id m16so120859ywh.12 for ; Thu, 13 Jun 2019 13:55:04 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=berkeley-edu.20150623.gappssmtp.com; s=20150623; h=mime-version:references:in-reply-to:from:date:message-id:subject:to :cc; bh=6Okl5R6pfSjzfIoXa3QtYQTkonenzCY3oYOspD2oJMc=; b=dwRBGgoGBKGoxVMx60GgSuYjN+5P7sHd+wEWI6cRgkYcMjOnoXUVMxomEJIxRkjZcU 4FEt38fr07VVsp9eowLRjhkC6YTFzNWedyBrjkzgyy/43oY5euxzDteMMwRPlSUCdxBx No0r/6rU5oSyZzpsj+6qIa4YbVEOXv5Z77HZQJlOVxucdF4C6OfLqdru5QYWirYs4cvU DqiKHmyT6HGGiwAFpHQA/lb5NuRhYcYYYnkNjzL9s1g5GyLlVlRGkgi/URN7+XmAtKOx X3QLKvcUT3v75/5F3Ejfl2sir7BPpqrBDIfMDnB5wFk+EU5/lvp9vaOsT1tyWPTgXGMI eveQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:references:in-reply-to:from:date :message-id:subject:to:cc; bh=6Okl5R6pfSjzfIoXa3QtYQTkonenzCY3oYOspD2oJMc=; b=C2WTU1VZF29Z10ja84N9mEAFUEBgvdDeiiHbjAJjxw1y/LFMvDc5nptkX9nTzfH83h p5oXbu5OqOEz6JULeoPWP0J8L5bQCawiuK9mvjnli1n+KH7JfP2jEcAXmbtVr3uAQl0Q L2kA+n+sUVUzmnD2W8bsjKUnNd0TGa2v57SDOvFlHgaEHYQQiDd+flhuNMH6GIWyM0ri A0eTZipkDIlOTwpkJdpaWH+b1lXRmI67YLvvpVXYEx06X9Y6LNiV4ARyBJ2p4yxc7hI6 z4X2KOO+jXPnJ7CEn4dqvXqpldePAZwIwQvYF69cb7H/PtQlWMtH1vIKrtxZmpTLijfn W74w== X-Gm-Message-State: APjAAAWBhO/tIDC26DSfgjds8PXGGpiyere2r9RIlFK/egPFzvH8um7W uUhmzRDK64dlSs8hwuu0uWS4ffY4xO9q2ti2TjXxVg== X-Google-Smtp-Source: APXvYqzsGDfmTZx0tueWLDIzzGH/GK0+rCkT5betlhHBlmxgG1b/65hKNjMJ6oFowQqY2zNWYFqAmLue7Ns3JZ6B9Zs= X-Received: by 2002:a81:3a82:: with SMTP id h124mr44091411ywa.375.1560459303956; Thu, 13 Jun 2019 13:55:03 -0700 (PDT) MIME-Version: 1.0 References: <33071EF2-86DE-4698-BFAA-6F084024A826@thebuild.com> In-Reply-To: <33071EF2-86DE-4698-BFAA-6F084024A826@thebuild.com> From: Daniel Cohen Date: Thu, 13 Jun 2019 13:54:53 -0700 Message-ID: Subject: Re: Creating dynamically-typed tables using psycopg2's built-in formatting To: Christophe Pettus Cc: psycopg@postgresql.org Content-Type: multipart/alternative; boundary="0000000000007fd321058b3abe21" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --0000000000007fd321058b3abe21 Content-Type: text/plain; charset="UTF-8" Hi Christophe, Thanks so much for your response. The uppercase --> lowercase fix worked for the types, but I'm still only getting tables that can be searched by double-quotations (i.e. > SELECT * FROM tbl; returns nothing, but > SELECT * FROM "tbl"; returns the table I uploaded). I can't tell from your message what the script should say to fix this. Thank you again! Best, Danny On Thu, Jun 13, 2019 at 1:00 PM Christophe Pettus wrote: > 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 > 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 > xof@thebuild.com > > --0000000000007fd321058b3abe21 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi Christophe,

Thanks so muc= h for your response. The uppercase --> lowercase fix worked for the type= s, but I'm still only getting tables that can be searched by double-quo= tations (i.e. > SELECT * FROM tbl; returns nothing, but > SELECT * FR= OM "tbl"; returns the table I uploaded). I can't tell from yo= ur message what the script should say to fix this. Thank you again!

Best,

Danny

=
On Thu, Ju= n 13, 2019 at 1:00 PM Christophe Pettus <xof@thebuild.com> wrote:
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:=C2=A0 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 all= owed in identifiers:

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

You can use SQL.identifier, but you need to make sure you are getting the c= ase right; in general, PostgreSQL types are all lower-case, and it's on= ly 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:=C2=A0 type "VARCHAR" does not exist
LINE 1: create table y (i "VARCHAR");
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 ^
xof=3D# create table y (i "varchar");
CREATE TABLE

> On Jun 13, 2019, at 12:28, Daniel Cohen <daniel.m.cohen@berkeley.edu> = wrote:
>
> Hi!
>
> I'm working on a project in Python that interacts with a PostgreSQ= L data warehouse, and I'm using the psycopg2 API. I am looking to creat= e dynamically-typed tables.
>
> For example, I would like to be able to execute the following code: >
> from psycopg2 import connect,
>=C2=A0 sql
>
> connection
> =3D connect(host=3D"host", port=3D"port", database= =3D"database", user=3D"user", password=3D"pw"= )
>
>
>
> def create_table(tbl_name, col_name, col_type):
>
>=C2=A0 =C2=A0 =C2=A0query
> =3D sql.SQL("CREATE TABLE {} ({} {})".format(sql.Identifier(= tbl_name), sql.Identifier(col_name), sql.Identifier(column_type)))
>
>=C2=A0 =C2=A0 =C2=A0connection
> .execute(query)
>
>
> create_table
> ('animals', 'name', 'VARCHAR')
> and end up with a table named "animals" that contains a colu= mn "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 VARCH= AR type when there should not be any. Normally, I would just work around th= is myself, but the documentation is very clear that Python string concatena= tion 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 c= reate 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 simila= r methodology, the sql.Identifier() function does not perform as I expect i= t 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_tb= l").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 cre= ated to be of the form tbl not "tbl". Thank you!
>
> Danny
>
>

--
-- Christophe Pettus
=C2=A0 =C2=A0xof@theb= uild.com

--0000000000007fd321058b3abe21--