passing variables to sqlite

[csergec]

Hi

I am writing a function which gets values. These ones will be used in my sqlite query

def updateDB(tel, month, price):
    mytel = (tel,)
    myprice = (price,)
    
    connection = sqlite.connect("mydb.db")
    cursor = connection.cursor()
    
    sql = 'UPDATE mydatabase SET %s = ? WHERE number = ?'
    cursor.execute( sql % (month), (myprice, mytel))
    connection.commit()

    ...and so on....

I think probably I tried all the possibilities to make it work except the right one...I miss the right syntax.

It works well if I give the name of the column instead of %s so it will:

sql = 'UPDATE mydatabase SET "jan" = ? WHERE number = ?'
    cursor.execute( sql , (myprice, mytel))

It works well if i keep the column and remove the "WHERE number = ?":

sql = 'UPDATE mydatabase SET %s = ? '
    cursor.execute( sql %(month), (myprice))

Can someone help me with it?
It will be useful instead of writing a fuction for every month...

[woooee]

Your existing code looks like it wants to find records where the field "month" equals myprice. The field "sql" is just a string so you format it like any other string. Post back if this isn't enough to get it to work.

##   assuming month, myprice and mytel are integers
    sql = 'UPDATE mydatabase SET %d = %d WHERE number = %d' % \
                                  (month, myprice, mytel)
    print sql
    cursor.execute( sql )
    connection.commit()

[csergec]

Hi Woooee

Thank you for your answer.
This works but in some places I read it is better to use the question point (?) as placeholders to prevent sql injections

After having tried a lot of possibilities I finally found this one:

sql = 'UPDATE mydatabase SET %s = :myprice WHERE number = :mytel ' % (mois) , \
                                  {"price"=myprice, "tel"= mytel)

Actually I found the second part of the code:

{"price"=myprice, "tel"= mytel)}

and I added it to the first part where the placeholder is "%s"