SyntaxError: invalid syntax on commit

Question

import sqlite3
import numpy


conn = sqlite3.connect('lotto.db')

cur = conn.cursor()

def fun(a,b,c,d,e,f):
    list = [a, b, c ,d, e, f]
    print(list)
    return numpy.mean(list)


numbers = cur.execute("SELECT * FROM combinations6")
numbers.fetchall()

for row in numbers:

    cur.execute("UPDATE combinations6 WHERE id = ? SET average = ?", (row, fun(row[0],row[1],row[2],row[3],row[4],row[5]))
    conn.commit()

conn.close()

having trouble getting this to iterate over each row getting syntax errors and when it does run it only calculates the average of the first row and inputs that to all the rows of the database

what am i doing wrong to get it to iterate over each row and calculate the average and input it into the database?

pretty new to python so thanks in advance for your help.

Don't change the question to remove the problem. If you want to show your new version of the code, add it as an addition. — Barmar
– Barmar, Commented Oct 4, 2014 at 2:04

Barmar · Accepted Answer · 2014-10-04 02:07:03Z

1

The problem isn't in Python, it's with your SQL syntax. The WHERE clause comes after SET:

cur.execute("UPDATE combinations6 SET average = ? WHERE id = ?", (fun(row[0],row[1],row[2],row[3],row[4],row[5]), row)

Don't forget to swap the order of the substitution parameters to match this.

Also, you're using row as the parameter for id = ?, which isn't right. You can't put a whole list into a parameter, it has to be a specific element of the list, e.g. something like row[6]. I don't know the actual position of the ID column in your table, so I don't know what the correct index is.

You can also do the entire thing with a single query:

UPDATE combinations6
SET average = (col1 + col2 + col3 + col4 + col5 + col6)/5

Replace col1, etc. with the actual names of the columns you're computing the average of.

edited Oct 4, 2014 at 2:07

answered Oct 4, 2014 at 1:49

Barmar

789k57 gold badges555 silver badges669 bronze badges

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

1 Comment

Barmar Over a year ago

Replace the row parameter with the element of the list that contains the ID. Or just use the new query that I added to my answer.

Tom Martin · Accepted Answer · 2014-10-04 02:33:27Z

1

import sqlite3
import numpy


conn = sqlite3.connect('lotto.db')

cur = conn.cursor()

def fun(a,b,c,d,e,f):
    list = [a, b, c ,d, e, f]
    print(list)
    return numpy.mean(list)


numbers = cur.execute("SELECT * FROM combinations6")
num = numbers.fetchall()

for row in num:

    cur.execute("UPDATE combinations6 SET average = ? WHERE id = ?", (fun(row[0],row[1],row[2],row[3],row[4],row[5]), row[7]))
    conn.commit()

conn.close()

strangely fixed with adding to the query and using a different pointer and the different query

num = numbers.fetchall()

Thanks for your help getting me there :)

answered Oct 4, 2014 at 2:33

Tom Martin

131 silver badge4 bronze badges

Collectives™ on Stack Overflow

SyntaxError: invalid syntax on commit

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related