CODEWITHSUNDEEP
pythonsqlite
Tom Martin
Tom Martin

Reputation: 13

SyntaxError: invalid syntax on commit

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.

Upvotes: 0

Views: 7993

Answers (2)

Tom Martin
Tom Martin

Reputation: 13

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 :)

Upvotes: 1

Barmar
Barmar

Reputation: 781761

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.

Upvotes: 1

Related Questions