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 1hbVlV-0006wY-Ck for psycopg@arkaria.postgresql.org; Thu, 13 Jun 2019 19:52:05 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1hbVlT-0008GK-1E for psycopg@arkaria.postgresql.org; Thu, 13 Jun 2019 19:52:03 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hbVOd-0002Tm-MT for psycopg@lists.postgresql.org; Thu, 13 Jun 2019 19:28:27 +0000 Received: from mail-yw1-xc2e.google.com ([2607:f8b0:4864:20::c2e]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hbVOZ-00033U-DP for psycopg@postgresql.org; Thu, 13 Jun 2019 19:28:26 +0000 Received: by mail-yw1-xc2e.google.com with SMTP id u134so38794ywf.6 for ; Thu, 13 Jun 2019 12:28:22 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=berkeley-edu.20150623.gappssmtp.com; s=20150623; h=mime-version:from:date:message-id:subject:to; bh=t8CJB6y8jRrRJWh2VpT4x1lIViEjpBJzrnt+umZ5BJ4=; b=cgy7F+CBB6vLQEpQTcm7x44n+hMs/+HBan5Bln9wwvpGejfMiIfi1xH2oSLOV7+HAd 37g48ra00n/gH4JBtNPKknSQubnZdcs+qY4JscSbA5pn/zugIHuqd0e40ZTrLBbtRf+X 7RGfcl1GT7R3kJmNpGEa7cF51pfYNGobMeRu1V+cwNym2xW4/Jfd3R0j61EHC2qLnZsc RnFWMQNzXgHNfc8Qwmw7XDuF2orxTz6Ho/9GzbUaT7wK44rlh2m19wlUefKh4XwcwUFs chNFnNFvDaQJRmS4wBKc64Cr5lY2AQP2O7/AIOiQtMLHDpbLLtXVQYLeT7CDrkdIetUz Q8yg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:from:date:message-id:subject:to; bh=t8CJB6y8jRrRJWh2VpT4x1lIViEjpBJzrnt+umZ5BJ4=; b=RpQq0SykPaUByn80cG5ZTdIUrtxQnjMTumn5XJmJjy04obOvuT6wbNTrvfGv7ACdIU FDIVlLBB3c6cuxMoE9NgYkHbz4yStUOYW4y39BTIrne+yajPKofpuTAEjV5Mp2+TsFrw lcWKb+A6JL9NoFA5LdIdHY4UMJGV1vw9LDUGMX++bb1RQFzDLO2YIqDgBfYyoJU1QZ77 gfbZO9Xf+OYscWomzXC8jti9nk7Cl+nnfmJoBUNl0JpEqm2koX79Pb2cUegW6YZ0HXwS VGNTCHMV3x9Ekt9TxynV1G3oRXaOqdzS2OzLOUPb2RwBuKNdQ439rDWqED39eqYzklVk ZXLw== X-Gm-Message-State: APjAAAW3/U2awqUhI8aRtYEwZPWTOQmECD0UCBYGZMQrp62eNtqJ9BVa wIyBu6wqE+F1uP4bp1MeAYUGlY5O/2LgiDi2UB67MooT9nE= X-Google-Smtp-Source: APXvYqyMhPxpFRQ7/nT1XfCY2xyO8vU1trcogjyZ59WE5ACNA0+12OUQz9llZoO21sc7BvibSGACf0Ub7tYsSEkcZkA= X-Received: by 2002:a81:a491:: with SMTP id b139mr25605819ywh.148.1560454099373; Thu, 13 Jun 2019 12:28:19 -0700 (PDT) MIME-Version: 1.0 From: Daniel Cohen Date: Thu, 13 Jun 2019 12:28:08 -0700 Message-ID: Subject: Creating dynamically-typed tables using psycopg2's built-in formatting To: psycopg@postgresql.org Content-Type: multipart/alternative; boundary="00000000000048283d058b3988cb" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --00000000000048283d058b3988cb Content-Type: text/plain; charset="UTF-8" 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 --00000000000048283d058b3988cb Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
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 =3D=
 connect(host=3D"host", port=3D"=
port", database<=
span class=3D"gmail-pun" style=3D"margin:0px;padding:0px;border:0px none;fo=
nt-family:inherit;font-style:inherit;font-variant-caps:inherit;font-weight:=
inherit;font-stretch:inherit;line-height:inherit;font-size:13px;vertical-al=
ign:baseline;box-sizing:inherit;color:rgb(48,51,54)">=3D"database", user=3D"user", password"pw=
")=


def create_table(tbl_name, col_name,=
 col_type):
    query =3D<=
/span> sql.SQL("CREATE TABLE {} ({} {})".format(.<=
span class=3D"gmail-typ" style=3D"margin:0px;padding:0px;border:0px none;fo=
nt-family:inherit;font-style:inherit;font-variant-caps:inherit;font-weight:=
inherit;font-stretch:inherit;line-height:inherit;font-size:13px;vertical-al=
ign:baseline;box-sizing:inherit;color:rgb(43,145,175)">Identifier(tbl_name), sql.I=
dentifier(col_name), sql.Identifier(column_type))=
)
    connection=
.execute(query)

create_table(<=
/span>'animals&#=
39;, 'name', 'VARCHAR')

and end= up with a table named "animals" that contains a column "nam= e" of type VARCHAR. However, when I attempt to run this, I get an erro= r: 'type "VARCHAR" does not exist'. I assume psyco= pg2's built-in formatter is putting double quotes around the VARCHAR ty= pe when there should not be any. Normally, I would just work around this my= self, but the documentation is= =C2=A0very=C2=A0clear 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 ta= bles in this fashion using pyscopg2, and if not, whether there exists anoth= er third-party API that can do so securely.

A second issue I've had is that when creating tables with a simi= lar 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 ge= t varying results. See below:

CR=
EATE TABLE tbl AS SELECT * FROM other_tbl;

in raw SQL creates a table called tbl, whereas

cursor.execute(sql.SQL("CRE=
ATE TABLE {} AS SELECT * FROM other_tbl").format(sql.Identifier=
(tbl))<=
/code>

creates a table called "tbl&= quot;. The two are different, and 

SELECT * =
FROM tbl;

returns a totally different table than

SELECT * FROM "tbl";

Plea= se 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 b= e of the form tbl not "tbl". Thank you!

Danny=C2=A0

<= /div>
--00000000000048283d058b3988cb--