public inbox for [email protected]
help / color / mirror / Atom feedCreating dynamically-typed tables using psycopg2's built-in formatting
13+ messages / 7 participants
[nested] [flat]
* Creating dynamically-typed tables using psycopg2's built-in formatting
@ 2019-06-13 19:28 Daniel Cohen <[email protected]>
2019-06-13 20:00 ` Re: Creating dynamically-typed tables using psycopg2's built-in formatting Christophe Pettus <[email protected]>
2019-06-13 20:29 ` RE: Creating dynamically-typed tables using psycopg2's built-in formatting David Raymond <[email protected]>
2019-06-14 16:07 ` Re: Creating dynamically-typed tables using psycopg2's built-in formatting Adrian Klaver <[email protected]>
0 siblings, 3 replies; 13+ messages in thread
From: Daniel Cohen @ 2019-06-13 19:28 UTC (permalink / raw)
To: psycopg
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Creating dynamically-typed tables using psycopg2's built-in formatting
2019-06-13 19:28 Creating dynamically-typed tables using psycopg2's built-in formatting Daniel Cohen <[email protected]>
@ 2019-06-13 20:00 ` Christophe Pettus <[email protected]>
2019-06-13 20:54 ` Re: Creating dynamically-typed tables using psycopg2's built-in formatting Daniel Cohen <[email protected]>
2 siblings, 1 reply; 13+ messages in thread
From: Christophe Pettus @ 2019-06-13 20:00 UTC (permalink / raw)
To: Daniel Cohen <[email protected]>; +Cc: psycopg
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 <[email protected]> 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
[email protected]
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Creating dynamically-typed tables using psycopg2's built-in formatting
2019-06-13 19:28 Creating dynamically-typed tables using psycopg2's built-in formatting Daniel Cohen <[email protected]>
2019-06-13 20:00 ` Re: Creating dynamically-typed tables using psycopg2's built-in formatting Christophe Pettus <[email protected]>
@ 2019-06-13 20:54 ` Daniel Cohen <[email protected]>
2019-06-13 21:17 ` RE: Creating dynamically-typed tables using psycopg2's built-in formatting David Raymond <[email protected]>
2019-06-14 12:40 ` Re: Creating dynamically-typed tables using psycopg2's built-in formatting Federico Di Gregorio <[email protected]>
2019-06-14 16:12 ` Re: Creating dynamically-typed tables using psycopg2's built-in formatting Christophe Pettus <[email protected]>
0 siblings, 3 replies; 13+ messages in thread
From: Daniel Cohen @ 2019-06-13 20:54 UTC (permalink / raw)
To: Christophe Pettus <[email protected]>; +Cc: psycopg
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 <[email protected]> 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 <[email protected]>
> 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
> [email protected]
>
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* RE: Creating dynamically-typed tables using psycopg2's built-in formatting
2019-06-13 19:28 Creating dynamically-typed tables using psycopg2's built-in formatting Daniel Cohen <[email protected]>
2019-06-13 20:00 ` Re: Creating dynamically-typed tables using psycopg2's built-in formatting Christophe Pettus <[email protected]>
2019-06-13 20:54 ` Re: Creating dynamically-typed tables using psycopg2's built-in formatting Daniel Cohen <[email protected]>
@ 2019-06-13 21:17 ` David Raymond <[email protected]>
2 siblings, 0 replies; 13+ messages in thread
From: David Raymond @ 2019-06-13 21:17 UTC (permalink / raw)
To: psycopg
Could you let us know what version the server is, and also show a table listing? (Connect with psql and do a \d for example)
From: Daniel Cohen <[email protected]>
Sent: Thursday, June 13, 2019 4:55 PM
To: Christophe Pettus <[email protected]>
Cc: [email protected]
Subject: 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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Creating dynamically-typed tables using psycopg2's built-in formatting
2019-06-13 19:28 Creating dynamically-typed tables using psycopg2's built-in formatting Daniel Cohen <[email protected]>
2019-06-13 20:00 ` Re: Creating dynamically-typed tables using psycopg2's built-in formatting Christophe Pettus <[email protected]>
2019-06-13 20:54 ` Re: Creating dynamically-typed tables using psycopg2's built-in formatting Daniel Cohen <[email protected]>
@ 2019-06-14 12:40 ` Federico Di Gregorio <[email protected]>
2 siblings, 0 replies; 13+ messages in thread
From: Federico Di Gregorio @ 2019-06-14 12:40 UTC (permalink / raw)
To: [email protected]
On 6/13/19 10:54 PM, Daniel Cohen wrote:
> 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!
From psql use the "\d" command to list all tables and check their
names. Send the output here so that we can help you understand why your
qury doesn't do what you expect.
federico
--
Federico Di Gregorio [email protected]
DNDG srl http://dndg.it
The number of the beast: vi vi vi. -- Delexa Jones
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Creating dynamically-typed tables using psycopg2's built-in formatting
2019-06-13 19:28 Creating dynamically-typed tables using psycopg2's built-in formatting Daniel Cohen <[email protected]>
2019-06-13 20:00 ` Re: Creating dynamically-typed tables using psycopg2's built-in formatting Christophe Pettus <[email protected]>
2019-06-13 20:54 ` Re: Creating dynamically-typed tables using psycopg2's built-in formatting Daniel Cohen <[email protected]>
@ 2019-06-14 16:12 ` Christophe Pettus <[email protected]>
2019-06-14 16:41 ` Re: Creating dynamically-typed tables using psycopg2's built-in formatting Daniel Cohen <[email protected]>
2 siblings, 1 reply; 13+ messages in thread
From: Christophe Pettus @ 2019-06-14 16:12 UTC (permalink / raw)
To: Daniel Cohen <[email protected]>; +Cc: psycopg
> On Jun 13, 2019, at 13:54, Daniel Cohen <[email protected]> wrote:
>
> 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).
By "returns nothing," do you mean you get an error, or that you get zero rows?
I'd connect to the database using psql and use \d to see what tables actually exist.
--
-- Christophe Pettus
[email protected]
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Creating dynamically-typed tables using psycopg2's built-in formatting
2019-06-13 19:28 Creating dynamically-typed tables using psycopg2's built-in formatting Daniel Cohen <[email protected]>
2019-06-13 20:00 ` Re: Creating dynamically-typed tables using psycopg2's built-in formatting Christophe Pettus <[email protected]>
2019-06-13 20:54 ` Re: Creating dynamically-typed tables using psycopg2's built-in formatting Daniel Cohen <[email protected]>
2019-06-14 16:12 ` Re: Creating dynamically-typed tables using psycopg2's built-in formatting Christophe Pettus <[email protected]>
@ 2019-06-14 16:41 ` Daniel Cohen <[email protected]>
2019-06-14 17:44 ` Re: Creating dynamically-typed tables using psycopg2's built-in formatting Sebastiaan Mannem <[email protected]>
0 siblings, 1 reply; 13+ messages in thread
From: Daniel Cohen @ 2019-06-14 16:41 UTC (permalink / raw)
To: Christophe Pettus <[email protected]>; +Cc: psycopg
Hi again,
I realize the error was that I specified the name as "schema.tbl", and I
think because there was a period in the name, it converted to double-quote.
I tried again as just "tbl" and it worked perfectly. Thanks so much for all
your help.
Best,
Danny
On Fri, Jun 14, 2019 at 9:12 AM Christophe Pettus <[email protected]> wrote:
>
>
> > On Jun 13, 2019, at 13:54, Daniel Cohen <[email protected]>
> wrote:
> >
> > 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).
>
> By "returns nothing," do you mean you get an error, or that you get zero
> rows?
>
> I'd connect to the database using psql and use \d to see what tables
> actually exist.
> --
> -- Christophe Pettus
> [email protected]
>
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Creating dynamically-typed tables using psycopg2's built-in formatting
2019-06-13 19:28 Creating dynamically-typed tables using psycopg2's built-in formatting Daniel Cohen <[email protected]>
2019-06-13 20:00 ` Re: Creating dynamically-typed tables using psycopg2's built-in formatting Christophe Pettus <[email protected]>
2019-06-13 20:54 ` Re: Creating dynamically-typed tables using psycopg2's built-in formatting Daniel Cohen <[email protected]>
2019-06-14 16:12 ` Re: Creating dynamically-typed tables using psycopg2's built-in formatting Christophe Pettus <[email protected]>
2019-06-14 16:41 ` Re: Creating dynamically-typed tables using psycopg2's built-in formatting Daniel Cohen <[email protected]>
@ 2019-06-14 17:44 ` Sebastiaan Mannem <[email protected]>
2019-06-14 19:12 ` Re: Creating dynamically-typed tables using psycopg2's built-in formatting Daniele Varrazzo <[email protected]>
0 siblings, 1 reply; 13+ messages in thread
From: Sebastiaan Mannem @ 2019-06-14 17:44 UTC (permalink / raw)
To: Daniel Cohen <[email protected]>; +Cc: Christophe Pettus <[email protected]>; psycopg
Yeah,
"schema.tbl" will search all schemas in search path for a table explicitly called "schema.tbl".
So that will never find a table called "tbl", and most probably never find a table at all.
And without quotes, it will search for a table called "tbl" in a schema called "schema".
So your issue makes perfect sense now.
FWIW, if you where to query "schema"."tbl", it would look for a table (or view) called "tbl" in a schema called "schema",
Which seems to be what you want.
Sebastiaan Alexander Mannem
Senior Consultant
Anthony Fokkerweg 1
1059 CM Amsterdam, The Netherlands
T: +31 6 82521560
www.edbpostgres.com
> On 14 Jun 2019, at 18:41, Daniel Cohen <[email protected]> wrote:
>
> Hi again,
>
> I realize the error was that I specified the name as "schema.tbl", and I think because there was a period in the name, it converted to double-quote. I tried again as just "tbl" and it worked perfectly. Thanks so much for all your help.
>
> Best,
>
> Danny
>
> On Fri, Jun 14, 2019 at 9:12 AM Christophe Pettus <[email protected] <mailto:[email protected]>> wrote:
>
>
> > On Jun 13, 2019, at 13:54, Daniel Cohen <[email protected] <mailto:[email protected]>> wrote:
> >
> > 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).
>
> By "returns nothing," do you mean you get an error, or that you get zero rows?
>
> I'd connect to the database using psql and use \d to see what tables actually exist.
> --
> -- Christophe Pettus
> [email protected] <mailto:[email protected]>
>
Attachments:
[image/png] email_logo.png (3.4K, ../../[email protected]/3-email_logo.png)
download | view image
[image/png] social_rss.png (549B, ../../[email protected]/4-social_rss.png)
download | view image
[image/png] social_fb.png (295B, ../../[email protected]/5-social_fb.png)
download | view image
[image/png] social_tw.png (403B, ../../[email protected]/6-social_tw.png)
download | view image
[image/png] social_li.png (415B, ../../[email protected]/7-social_li.png)
download | view image
[image/png] social_gp.png (462B, ../../[email protected]/8-social_gp.png)
download | view image
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Creating dynamically-typed tables using psycopg2's built-in formatting
2019-06-13 19:28 Creating dynamically-typed tables using psycopg2's built-in formatting Daniel Cohen <[email protected]>
2019-06-13 20:00 ` Re: Creating dynamically-typed tables using psycopg2's built-in formatting Christophe Pettus <[email protected]>
2019-06-13 20:54 ` Re: Creating dynamically-typed tables using psycopg2's built-in formatting Daniel Cohen <[email protected]>
2019-06-14 16:12 ` Re: Creating dynamically-typed tables using psycopg2's built-in formatting Christophe Pettus <[email protected]>
2019-06-14 16:41 ` Re: Creating dynamically-typed tables using psycopg2's built-in formatting Daniel Cohen <[email protected]>
2019-06-14 17:44 ` Re: Creating dynamically-typed tables using psycopg2's built-in formatting Sebastiaan Mannem <[email protected]>
@ 2019-06-14 19:12 ` Daniele Varrazzo <[email protected]>
2019-06-14 20:41 ` Re: Creating dynamically-typed tables using psycopg2's built-in formatting Daniel Cohen <[email protected]>
0 siblings, 1 reply; 13+ messages in thread
From: Daniele Varrazzo @ 2019-06-14 19:12 UTC (permalink / raw)
To: Sebastiaan Mannem <[email protected]>; +Cc: Daniel Cohen <[email protected]>; Christophe Pettus <[email protected]>; psycopg
On Fri, Jun 14, 2019 at 6:44 PM Sebastiaan Mannem <
[email protected]> wrote:
> Yeah,
> "schema.tbl" will search all schemas in search path for a table explicitly
> called "schema.tbl".
> So that will never find a table called "tbl", and most probably never find
> a table at all.
>
Also note that from psycopg 2.8 you can use Identifier("schema", "tbl") to
represent a dot-separated sequence of identifiers.
http://initd.org/psycopg/docs/sql.html#psycopg2.sql.Identifier
-- Daniele
Attachments:
[image/png] email_logo.png (3.4K, ../../CA+mi_8aMcR3uhZHjdDkWfx_zJ9NgnHL9U_injEjg62wA5+7YUg@mail.gmail.com/3-email_logo.png)
download | view image
[image/png] social_rss.png (549B, ../../CA+mi_8aMcR3uhZHjdDkWfx_zJ9NgnHL9U_injEjg62wA5+7YUg@mail.gmail.com/4-social_rss.png)
download | view image
[image/png] social_fb.png (295B, ../../CA+mi_8aMcR3uhZHjdDkWfx_zJ9NgnHL9U_injEjg62wA5+7YUg@mail.gmail.com/5-social_fb.png)
download | view image
[image/png] social_tw.png (403B, ../../CA+mi_8aMcR3uhZHjdDkWfx_zJ9NgnHL9U_injEjg62wA5+7YUg@mail.gmail.com/6-social_tw.png)
download | view image
[image/png] social_li.png (415B, ../../CA+mi_8aMcR3uhZHjdDkWfx_zJ9NgnHL9U_injEjg62wA5+7YUg@mail.gmail.com/7-social_li.png)
download | view image
[image/png] social_gp.png (462B, ../../CA+mi_8aMcR3uhZHjdDkWfx_zJ9NgnHL9U_injEjg62wA5+7YUg@mail.gmail.com/8-social_gp.png)
download | view image
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Creating dynamically-typed tables using psycopg2's built-in formatting
2019-06-13 19:28 Creating dynamically-typed tables using psycopg2's built-in formatting Daniel Cohen <[email protected]>
2019-06-13 20:00 ` Re: Creating dynamically-typed tables using psycopg2's built-in formatting Christophe Pettus <[email protected]>
2019-06-13 20:54 ` Re: Creating dynamically-typed tables using psycopg2's built-in formatting Daniel Cohen <[email protected]>
2019-06-14 16:12 ` Re: Creating dynamically-typed tables using psycopg2's built-in formatting Christophe Pettus <[email protected]>
2019-06-14 16:41 ` Re: Creating dynamically-typed tables using psycopg2's built-in formatting Daniel Cohen <[email protected]>
2019-06-14 17:44 ` Re: Creating dynamically-typed tables using psycopg2's built-in formatting Sebastiaan Mannem <[email protected]>
2019-06-14 19:12 ` Re: Creating dynamically-typed tables using psycopg2's built-in formatting Daniele Varrazzo <[email protected]>
@ 2019-06-14 20:41 ` Daniel Cohen <[email protected]>
0 siblings, 0 replies; 13+ messages in thread
From: Daniel Cohen @ 2019-06-14 20:41 UTC (permalink / raw)
To: Daniele Varrazzo <[email protected]>; +Cc: Christophe Pettus <[email protected]>; Sebastiaan Mannem <[email protected]>; psycopg
Awesome, thanks!
On Fri, Jun 14, 2019 at 12:13 PM Daniele Varrazzo <
[email protected]> wrote:
> On Fri, Jun 14, 2019 at 6:44 PM Sebastiaan Mannem <
> [email protected]> wrote:
>
>> Yeah,
>> "schema.tbl" will search all schemas in search path for a table
>> explicitly called "schema.tbl".
>> So that will never find a table called "tbl", and most probably never
>> find a table at all.
>>
>
> Also note that from psycopg 2.8 you can use Identifier("schema", "tbl") to
> represent a dot-separated sequence of identifiers.
>
> http://initd.org/psycopg/docs/sql.html#psycopg2.sql.Identifier
>
>
> -- Daniele
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* RE: Creating dynamically-typed tables using psycopg2's built-in formatting
2019-06-13 19:28 Creating dynamically-typed tables using psycopg2's built-in formatting Daniel Cohen <[email protected]>
@ 2019-06-13 20:29 ` David Raymond <[email protected]>
2019-06-13 20:35 ` Re: Creating dynamically-typed tables using psycopg2's built-in formatting Christophe Pettus <[email protected]>
2 siblings, 1 reply; 13+ messages in thread
From: David Raymond @ 2019-06-13 20:29 UTC (permalink / raw)
To: psycopg
SELECT * FROM tbl;
returns a totally different table than
SELECT * FROM "tbl";
That's a little disconcerting.
For the capitalization thing basically what's going on is two things. (The way I understand it)
1) Postgres IS case sensitive.
2) When any query gets sent to the server, the server converts everything not inside quotes to lower case as step 1, _before_ trying to match names for tables, columns, functions, etc.
So if you send
SELECT * FROM TBL;
One of the first things the server does is change it to
select * from tbl;
at which point it will do a case-sensitive match for tbl
And if you run
SELECT * FROM "TBL";
it gets turned into
select * from "TBL";
at which point it will do a case-sensitive match for TBL
But in your case what is in quotes is the same as its lowercase version. So it "should" match up to the same thing. So if you are indeed getting different results from different tables then either my understanding is way off, or something weird is going on.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Creating dynamically-typed tables using psycopg2's built-in formatting
2019-06-13 19:28 Creating dynamically-typed tables using psycopg2's built-in formatting Daniel Cohen <[email protected]>
2019-06-13 20:29 ` RE: Creating dynamically-typed tables using psycopg2's built-in formatting David Raymond <[email protected]>
@ 2019-06-13 20:35 ` Christophe Pettus <[email protected]>
0 siblings, 0 replies; 13+ messages in thread
From: Christophe Pettus @ 2019-06-13 20:35 UTC (permalink / raw)
To: David Raymond <[email protected]>; +Cc: psycopg
> On Jun 13, 2019, at 13:29, David Raymond <[email protected]> wrote:
> For the capitalization thing basically what's going on is two things. (The way I understand it)
> 1) Postgres IS case sensitive.
> 2) When any query gets sent to the server, the server converts everything not inside quotes to lower case as step 1, _before_ trying to match names for tables, columns, functions, etc.
It's a bit more elaborate than that, although that's not a bad summary of the perceived result. The details are here:
https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS\
--
-- Christophe Pettus
[email protected]
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Creating dynamically-typed tables using psycopg2's built-in formatting
2019-06-13 19:28 Creating dynamically-typed tables using psycopg2's built-in formatting Daniel Cohen <[email protected]>
@ 2019-06-14 16:07 ` Adrian Klaver <[email protected]>
2 siblings, 0 replies; 13+ messages in thread
From: Adrian Klaver @ 2019-06-14 16:07 UTC (permalink / raw)
To: Daniel Cohen <[email protected]>; psycopg
On 6/13/19 12:28 PM, 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:
>
> |frompsycopg2 importconnect,sql connection
> =connect(host="host",port="port",database="database",user="user",password="pw")defcreate_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
I'm not seeing it:
cursor.execute(sql.SQL("CREATE TABLE {} AS SELECT * FROM
t1").format(sql.Identifier("tbl")))
test_(aklaver)> \d
...
public | t1 | table | aklaver
public | tbl | table | aklaver
...
The question then becomes how is the variable tbl in your script being
assigned to?
>
> |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
>
>
--
Adrian Klaver
[email protected]
^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2019-06-14 20:41 UTC | newest]
Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-06-13 19:28 Creating dynamically-typed tables using psycopg2's built-in formatting Daniel Cohen <[email protected]>
2019-06-13 20:00 ` Christophe Pettus <[email protected]>
2019-06-13 20:54 ` Daniel Cohen <[email protected]>
2019-06-13 21:17 ` David Raymond <[email protected]>
2019-06-14 12:40 ` Federico Di Gregorio <[email protected]>
2019-06-14 16:12 ` Christophe Pettus <[email protected]>
2019-06-14 16:41 ` Daniel Cohen <[email protected]>
2019-06-14 17:44 ` Sebastiaan Mannem <[email protected]>
2019-06-14 19:12 ` Daniele Varrazzo <[email protected]>
2019-06-14 20:41 ` Daniel Cohen <[email protected]>
2019-06-13 20:29 ` David Raymond <[email protected]>
2019-06-13 20:35 ` Christophe Pettus <[email protected]>
2019-06-14 16:07 ` Adrian Klaver <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox