PostgreSQL SELECT column names programmatically in python

Question

I would like to pass programmatically few columns from python(psycopg2) in a table.

  cursor.execute("SELECT col1,col2,col3...coln FROM %s ORDER BY datetime ASC" %mytable)

column name col1,col2,col3...coln can be of length 100's and change every time cursor.execute() is called.

I did a work around . columns = ['col1', 'col2', 'col3'] columnsstr = ','.join(map(str, columns )) querystring = 'SELECT ' + columnsstr + ' FROM %s ORDER BY timeentry ASC' cursor.execute(querystring %mytable) — rss
– rss, Commented Jun 14, 2018 at 8:21

kristaps · Accepted Answer · 2018-06-14 06:39:38Z

1

You're already using string interpolation, so you could do the same for the column names. Put your column names in an array and join them when formatting the query:

columns = ['col1', 'col2', 'col3']
cursor.execute("SELECT %s FROM %s ORDER BY datetime ASC" % (','.join(columns), mytable))

It is important that the columns are strictly controlled by you and not generated from user input, as that would enable SQL injection attacks.

answered Jun 14, 2018 at 6:39

kristaps

1,74311 silver badges16 bronze badges

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

1 Comment

rss Over a year ago

Very crisp and concise answer. Thank you.

Collectives™ on Stack Overflow

PostgreSQL SELECT column names programmatically in python

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related