Python, SQL: Set the columns read as parameter

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

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.

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