Jython, Query multiple columns dynamically

Question

Hey all,

I am working with a oracle database and Jython.

I can get data pulled from the database no problem.

results = statement.executeQuery("select %s from %s where column_id = '%s'", % (column, table, id))

This works fine if I want to pull one column of data.

Say I wanted to loop threw a list like this:

columns = ['column1', 'column2', 'column3', 'column4', 'column5']

So the query ended up looking like this:

results = statement.executeQuery("select %s, %s, %s, %s, %s from %s where column_id = '%s'", % (column1, column2, column3, column4, column5, table, id))

How could I do this?

The reason I want to achive this is because I may want to pull 6 or 7 columns and I would like to store different queries in a external file.

I hope you understand what I mean. If not I will try to re word it as best as I can.

Cheers

Arthur

Answer 1

You could simply substitute all the columns into your query as a single string, like this:

columns = ['column1', 'column2', 'column3', 'column4', 'column5']
results = statement.executeQuery("select %s from %s where column_id = '%s'" % (",".join(columns), table, id))

By the way, this isn't protecting against SQL injection, so I'm assuming the columns, table, and id inputs are program-generated or sanitized.