select multiple columns using SQLite3 in Python

Question

I have a list that contains the name of columns I want to retrieve from a table in the database. My question is how to make the cursor select columns specified in the list. Do I have to convert nameList to a string variable before include it in the select statement? Thanks

nameList = ['A','B','C','D',...]

 with sqlite3.connect(db_fileName) as conn:
        cursor = conn.cursor()
        cursor.execute("""
        select * from table
        """)

mechanical_meat · Accepted Answer · 2015-04-16 23:16:30Z

2

As long as you can be sure your input is sanitized -- to avoid SQL injection attack -- you can do:

    ...
    qry = "select {} from table;"
    qry.format( ','.join(nameList) )
    cursor.execute(qry)

If you're on a really old version of Python do instead:

    ...
    qry = "select %s from table;"
    qry % ','.join(nameList) 
    cursor.execute(qry)

edited Apr 16, 2015 at 23:16

answered Apr 16, 2015 at 22:34

mechanical_meat

170k25 gold badges237 silver badges231 bronze badges

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

3 Comments

nivix zixer Over a year ago

Good warning against SQL injection. That still isn't second nature to me coming from an Access background.

Jin Over a year ago

qry = "select {} from table;" qry.format( ','.join(nameList) ), it seems Python does not accept since string is immutable and can't be changed? Could you double check? Error message is unrecognized token: "{" . Thanks

Jin Over a year ago

@bernie: There is still some problem with the updated solution. Nivix's solution seems to be working

nivix zixer · Accepted Answer · 2015-04-17 01:16:17Z

1

nameList = ["'A(pct)'",'B','C','D',...]

 with sqlite3.connect(db_fileName) as conn:
        cursor = conn.cursor()
        cursor.execute("""
        select {} from table
        """.format(", ".join(nameList)))

edited Apr 17, 2015 at 1:16

answered Apr 16, 2015 at 22:35

nivix zixer

1,6691 gold badge13 silver badges19 bronze badges

5 Comments

Jin Over a year ago

what if nameList contains column names like this, 'A(pct)', 'B(pct)'. This will break down your code since Python thought you might be calling some function with parameter pct. Thanks

nivix zixer Over a year ago

Python doesn't think you are calling a function, it just handles strings as strings. But the SQL might think A(pct) is a SQL function..so you might need to put single quotes around those column names. See this: stackoverflow.com/a/25210041/4774955

CL. Over a year ago

In SQL, single quotes are for strings, and double quotes are for table/column names.

nivix zixer Over a year ago

@CL Depends on which database software you are using. Single quotes are valid in MSSQL.

CL. Over a year ago

That's why I said "SQL", and did not mention a certain database that does not conform to the SQL standard.

Collectives™ on Stack Overflow

select multiple columns using SQLite3 in Python

2 Answers 2

3 Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related