CODEWITHSUNDEEP
pythonsqlitebinding
Keizer
Keizer

Reputation: 23

SQLite queries with Python: different errors

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.

Upvotes: 0

Views: 135

Answers (1)

Ignacio Vazquez-Abrams
Ignacio Vazquez-Abrams

Reputation: 799300

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.

Upvotes: 1

Related Questions