SQLite queries with Python: different errors

Question

I'm really new to Python and I'm trying to query a SQLite database. Here is one part of the code which I have problems with:

def estadistiques(estacio='all',mes='all', graph=0):

if (len(mes)<2):
    mes_sql = "0%s" %mes
else:
    mes_sql = "%s" %mes
mesos = {"1":"Gener","2":"Febrer","3":"Març","4":"Abril","5":"Maig","6":"Juny","7":"Juliol","8":"Agost","9":"Setembre","10":"Octubre","11":"Novembre","12":"Decembre"}  
if (estacio!='all'):
    if (mes!='all'):
        nom_estacio = cursor.execute("SELECT DISTINCT nom FROM estacions WHERE codi==?",[estacio]).fetchall()   

        mitjana_recarrega = cursor.execute("SELECT avg(recarrega) FROM dades WHERE strftime('%m', data)==? AND maquina IN (SELECT maquina FROM estacions WHERE codi==?)",(mes_sql, estacio)).fetchall()
        mitjana_recuperat = cursor.execute("SELECT avg(recuperat) FROM dades WHERE strftime('%m', data)==? AND maquina IN (SELECT maquina FROM estacions WHERE codi==?)",(mes_sql, estacio)).fetchall()
        mitjana_recaptat = cursor.execute("SELECT avg(recaptat) FROM dades WHERE strftime('%m', data)==? AND maquina IN (SELECT maquina FROM estacions WHERE codi==?)",(mes_sql, estacio)).fetchall()

        print "-"*50            
        print "| Dades de l'estació %s durant el mes de %s" % (nom_estacio,mesos[mes])
        print '-'*50
        print '\n\n'
        print 'Mitjana de recàrregues:\t\t %.2f' % mitjana_recarrega[0]
        print 'Mitjana de recuperacions:\t %.2f' % mitjana_recuperat[0]
        print 'Mitjana de recaptacions:\t %.2f' % mitjana_recaptat[0]

I get those parameters using raw_input. The first query:

nom_estacio = cursor.execute("SELECT DISTINCT nom FROM estacions WHERE codi==?",[estacio]).fetchall()

Works well if I have [estacio] but if I write it without brackets estacio Python complains. I understood that is because SQLite takes that variable as a tuple.

But on the second query, I should write (mes_sql,estacio) with no brackets for it to work. If I put it between brackets I get

sqlite3.InterfaceError: Error binding parameter 1 - probably unsupported type.

Why is it happening? I solved this issue by try-error but I'd like to know why I should do it that way.

Ignacio Vazquez-Abrams · Accepted Answer · 2012-08-05 19:22:02Z

1

I understood that is because SQLite takes that variable as a tuple.

As a sequence, actually. (And DB-API, not SQLite.) It just so happens that a list is a sequence. In the second case it's already in a sequence, so adding brackets makes it wrong.

answered Aug 5, 2012 at 19:22

Ignacio Vazquez-Abrams

804k160 gold badges1.4k silver badges1.4k bronze badges

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

3 Comments

Lenna Over a year ago

It's worth noting that [estacio] is a list with length 1, whereas (estacio) is not a tuple. A length-one tuple must be written (estacio,).

Ignacio Vazquez-Abrams Over a year ago

Actually, tuple(estacio) won't help, since it will convert iterables to a tuple and crash on non-iterables.

Keizer Over a year ago

I tried Lenna's that way: Firstly I iniciated a tuple: estacio = (estacio,) and then passing it as estacio[0] to SQLite. But I found the approach I posted clearer and required less writing. Thank you so much!

Collectives™ on Stack Overflow

SQLite queries with Python: different errors

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related