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 · Answer 1 · 2015-04-16T23:16:30.670

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 '15 at 23:16

answered Apr 16 '15 at 22:34

mechanical_meat

163,903
24
228
223

Good warning against SQL injection. That still isn't second nature to me coming from an Access background. – nivix zixer Apr 16 '15 at 22:36
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 Apr 16 '15 at 23:09
@bernie: There is still some problem with the updated solution. Nivix's solution seems to be working – Jin Apr 16 '15 at 23:21

nivix zixer · Accepted Answer · 2015-04-17T01:16:17.790

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 '15 at 01:16

answered Apr 16 '15 at 22:35

nivix zixer

1,611
1
13
19

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 – Jin Apr 16 '15 at 23:46
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: http://stackoverflow.com/a/25210041/4774955 – nivix zixer Apr 17 '15 at 01:15
In SQL, single quotes are for strings, and double quotes are for table/column names. – CL. Apr 17 '15 at 07:55
@CL Depends on which database software you are using. Single quotes are valid in MSSQL. – nivix zixer Apr 17 '15 at 13:15
That's why I said "SQL", and did not mention a certain database that does not conform to the SQL standard. – CL. Apr 17 '15 at 13:25

select multiple columns using SQLite3 in Python

2 Answers2