Python, SQL: Set the columns read as parameter

Question

I have a SQL query, mad in Python using Psycopg2. The query reads some columns from the arches table:

rows = archesDB.read_all("""SELECT "+str(columns)[1:-1].replace("'","")+" 
                           FROM arches 
                           WHERE lower(arch) like '%%%s%%'""" % (arch.lower()))

I want to parametrize this query, so that it will not specify the columns needed using string concatenation, but as parameters - a far more elegant way.

The naïve way is to SELECT *, and filter out the columns I need. But this burdens the DB and network with unneeded data, so I rather avoid it.

Any ideas?

Adam

edgars · Accepted Answer · 2010-12-09 12:40:56Z

Column names cannot be passed in the SQL string as parameters within the DB API. And it would not make sense to it as well.

If you need to check the column name input, sanitize it beforehand.

The like string should go in as a parameter though

The following code sample would be an improvement if columns variable contains a list of strings for the column names:

rows = archesDB.read_all("""SELECT %s 
                       FROM arches 
                       WHERE lower(arch) like %%s""" % (",".join(columns),),
                       ("%%%s%%" % (arch.lower(),),))

First, the column names are inserted within first substitution (%s) and the last %%s is converted to %s. Then the ("%%%s%%" % (arch.lower(),),) makes a string with %string_content%. And at last, db api escapes the %string_content% and adds quotes to it that finally makes the query.

Ignacio Vazquez-Abrams · Accepted Answer · 2010-12-09 10:27:59Z

0

You cannot parametrize metadata. Create a mapping of some identifier to the desired field list, and use that.

answered Dec 9, 2010 at 10:27

Ignacio Vazquez-Abrams

804k160 gold badges1.4k silver badges1.4k bronze badges

2 Comments

Adam Matan Over a year ago

That is, use SELECT * and filter the desired columns?

Ignacio Vazquez-Abrams Over a year ago

A mapping. Like a dict. And then get the field list to put in the query from that.

Collectives™ on Stack Overflow

Python, SQL: Set the columns read as parameter

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related