public inbox for [email protected]  

help / color / mirror / Atom feed

From: Daniel Cohen <[email protected]>
To: [email protected]
Subject: Creating dynamically-typed tables using psycopg2's built-in formatting
Date: Thu, 13 Jun 2019 12:28:08 -0700
Message-ID: <CAHAzoYkzXi1HFE0PmY3z0qn4SRnSZLdhChPUePf-2VwTM91upg@mail.gmail.com> (raw)

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

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]
  Subject: Re: Creating dynamically-typed tables using psycopg2's built-in formatting
  In-Reply-To: <CAHAzoYkzXi1HFE0PmY3z0qn4SRnSZLdhChPUePf-2VwTM91upg@mail.gmail.com>

* 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