How to escape reserved words in Python and Postgres SQL at the same time?

Question

Here is the code I have:

for x in range(len(un)):
   query = 'CREATE TABLE "'+un[x]+'" AS SELECT * FROM public.sample WHERE ticker = +un[x]+'
   cursor.execute(query)

The error I am getting:

LINE 1: ...E "AA" AS SELECT * FROM public.sample WHERE ticker = +un[x]+

The query I want to run in PostgreSQL

CREATE TABLE "AA" AS SELECT * FROM public.sample WHERE ticker = 'AA'

fpietka · Accepted Answer · 2020-08-17 08:43:26Z

1

You just miss a quote in the query string. Try this:

for x in range(len(un)):
       query = 'CREATE TABLE "'+un[x]+'" AS SELECT * FROM public.sample WHERE ticker = \''+un[x]+'\''
       cursor.execute(query)

Also consider using parameters, as this query is prone to SQL injection (meaning you could leak or loose your database):

for x in range(len(un)):
       query = 'CREATE TABLE %(ticker)s AS SELECT * FROM public.sample WHERE ticker = %(ticker)s'
       cursor.execute(query, {"ticker": un[x]})

answered Aug 17, 2020 at 8:43

fpietka

1,0411 gold badge10 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to escape reserved words in Python and Postgres SQL at the same time?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related