Re: Creating dynamically-typed tables using psycopg2's built-in formatting

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 <xof(at)thebuild(dot)com> 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 <daniel(dot)m(dot)cohen(at)berkeley(dot)edu>
> 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(at)thebuild(dot)com
>
>