Python3 Trying to execute sql query with dynamic column names

Question

I am using sqlite3 and I am trying to dynamically return specific columns from database using SELECT query, problem is I keep getting the column names back instead of the actual rows. Here is an example code

import sqlite3
conn = sqlite3.connect('db_name.db')
c = conn.cursor()

query = 'SELECT ?, ? FROM devices'
columns = ('name','network-id')
c.execute(query, columns)

print(c.fetchall())

This is the result I get:

[('name', 'network_id'), ('name', 'network_id'), ('name',    'network_id'), ('name', 'network_id'), ('name', 'network_id'), ('name', 'network_id'), ('name', 'network_id'), ('name', 'network_id'), ('name', 'network_id')]

It is very annoying, I am only trying to get back specific columns from my results, but I get the column names instead. Any help will be much appreciated

in line 6 of the code it is actually 'network_id'

prototypus
– prototypus

2016-01-22 10:00:15 +00:00
Commented Jan 22, 2016 at 10:00 — prototypus
– prototypus, Commented Jan 22, 2016 at 10:00

CL. · Accepted Answer · 2016-01-22 10:04:20Z

2

You cannot use SQL parameters for table or column names, only for literal values.

Your query is the equivalent of:

SELECT 'name', 'network-id' from devices

Just put the column names directly into the query:

columns = ('name','network-id')
query = 'SELECT %s from devices' % ','.join(columns)

answered Jan 22, 2016 at 10:04

CL.

182k18 gold badges241 silver badges282 bronze badges

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

1 Comment

prototypus Over a year ago

Much appreciated. I had no idea SQL parameters can only be used for literal values.

Rohan Kushwaha · Accepted Answer · 2016-01-22 10:07:09Z

0

Use columns name, network-id in the SELECT query itself as follows:

query = 'SELECT name, network-id FROM devices'

? is used to relpace value in the query for example

query = 'SELECT name, network-id FROM devices where name = ?'
columns = ('my-name')
c.execute(query, columns)

answered Jan 22, 2016 at 10:07

Rohan Kushwaha

7436 silver badges18 bronze badges

Collectives™ on Stack Overflow

Python3 Trying to execute sql query with dynamic column names

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related