python list in sql query as parameter

Suppose that we have a list [10, 20, 4, 6, 9], and we want to write a sql query to get the data for all elements of the list, like

select * from students where id in (10, 20, 4, 6, 9)

How to do that in Python? Actualy, it is very easy. Below are the list of techniques to do it:

Technique 1

id = [10, 20, 4, 6, 9]
sql = 'select * from studens where id in %s' % str(tuple(id))

Technique 2

id = [10, 20, 4, 6, 9]
sql = 'select * from studens where id in (%s)' % ','.join(map(str, id))

Technique 3

id = [10, 20, 4, 6, 9]
xx = ', '.join(id)
sql = 'select * from students where id in (%s)' % xx

Technique 4

id = [10, 20, 4, 6, 9]
sql = 'select * from studens where id in (' +
         ','.join(map(str, id)) + ')'

Technique 5

id = [10, 20, 4, 6, 9]
sql = 'select * from studens where id in (' +
         ','.join((str(n) for n in id)) + ')'

Technique 6

id = [10, 20, 4, 6, 9]
sql = 'select * from studens where ' +
         ' or '.join(('id = ' + str(n) for n in id))

I prefer the first technique. What about you? Do you have some other techniques to share? If you do, please leave a comment. Thank you.

Note: The above codes were tested using Sqlite3 and Python 2.5.

Python: Parse Apache log to sqlite database

This Python script was written for a friend in Australia, as part of his Ph.D project. The script will parse the Apache server log into sqlite3 database.

Apache server log like this:

23.13.171.152 - - [26/Sep/2007:21:20:36 +0800]
“GET /forum/Themes/BlueStory/images/bbc/ftp://ftp.gif HTTP/1.1? 200 191
“http://www.malaysiaindah.com/index.php?action=post;topic=587.0;
num_replies=3?
“Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.1.7)
Gecko/20070914 Firefox/2.0.0.7?

could be parse to an sqlite database, with the class below.

class Parser:
    def __init__(self, serverLog, db):
        if db.strip() == "":
        db = "log.db"
        try:
            conn = sqlite3.connect(db)
            cursor = conn.cursor()
            cursor.execute("create table if not exists log
            (ip, date, time, gmt, request,
            errorcode, bytes, referel, osa)" )
        except sqlite3.Error, error:
            wx.MessageBox(str(error), "Info")
            exit()

        numLog = len(open(serverLog).readlines())
        for line in open(serverLog, "r"):
            data = []
            a = line.split(‘"‘)
            line = line.split()
            data.append(line[0])

        #ip
        data.append(line[3][1:line[3].index(":")])

        #date
        data.append(line[3][line[3].index(":") + 1:])

        #time
        data.append(line[4][:-1])

        #gmt
        data.append(line[5][1:] + " " + line[6])

        #urlreq
        data.append(line[8])

        #statuscode
        data.append(line[9]) #bytestr data.append(line[10][1:-1])

        #referel
        data.append(a[-2])

        #osagent
        try:
            cursor.execute( "insert into log values(
             ?, ?, ?, ?, ?, ?, ?, ?, ?)",
            (data[0], data[1], data[2], data[3],
            data[4], data[5], data[6],
            data[7], data[8]) )
        except sqlite3.Error, error:
            wx.MessageBox(str(error), 'Info')
            exit()
        
        conn.commit()
        cursor.close()
        conn.close()