Python, SQL syntax error but I don´t see it

Python
#1

I´m working on an SQL/Python exercise from one of the supplemental resources and I´m running into a syntax error:

Traceback (most recent call last):
  File "c:\Users\Admin\Desktop\PY4E\fcc\seq_practice.py", line 19, in <module>
    cur.execute('''INSERT INTO Counts (email, count) 
sqlite3.OperationalError: near ")": syntax error

BUT I don´t see the error. Everything seems fine. However, VSCode doesn´t lie, so I need some help in figuring this out. I´m pasting my code below as well as a link to the YouTube video of the supplement resource exercise I´m working from.

Thank you for any help you can provide.

Exercise: Email

import sqlite3

conn = sqlite3.connect('emaildb.sqlite')
cur = conn.cursor()

cur.execute('DROP TABLE IF EXISTS Counts')
cur.execute('CREATE TABLE Counts (email TEXT, count INTEGER)')

fname = input('Enter file name: ')
if len(fname) < 1: fname='mbox.txt'
fh = open(fname)
for line in fh:
    if not line.startswith('From '):
        pieces = line.split()
        email = pieces[1]
        cur.execute('SELECT count FROM Counts WHERE email = ?', (email,))
        row = cur.fetchone()
        if row is None:
            cur.execute('''INSERT INTO Counts (email, count) 
            VALUES(?,)''', (email,))
        else:
            cur.execute('UPDATE Counts SET count = count + 1 WHERE email = ?'), conn.commit()
        
        #http://www.sqlite.org/lang_select.html
        sqlstr = 'SELECT email, count FROM Counts ORDER BY count DESC LIMIT 10'
        for row in cur.execute(sqlstr):
            print(row[0], row[1])
        
cur.close()
#2

INSERT INTO Counts (email, count) VALUES(?,)
What’s the value inserted into the count field?

#3

This does not look right.

#4

I agree. I’m following the lead of Charles Severance in the video resource for the course. His explanation was that this (email,)) was a tuple; yet, because the search was only for emails it has a blank after the comma. This I don´t understand and that is what my Python keeps returning a traceback on. I’m not sure where to go from here. The video example returns all the email addresses in the .txt file with a count beside of them.

I don’t know why I’m not getting the same result.

#5

After looking at the code a bit more and comparing to the video’s code, I do notice you have:

for line in fh:
    if not line.startswith('From '):
        pieces = line.split()

when the video shows:

for line in fh:
    if not line.startswith('From: '): continue
    pieces = line.split()

Notice your first line is missing a keyword and your indentation is different. This will cause issues. That missing continue would allow lines that do not start with "From: " execute the code below that if statement.

Why is your code not the same as the video’s code. The file emaildb.py already contains the correct code.

#6

continue has been added but the console still returns a traceback. This time, it is the following:

 File "c:\Users\Admin\Desktop\PY4E\fcc\seq_practice.py", line 22, in <module>
    cur.execute('UPDATE Counts SET count = count + 1 WHERE email = ?'), conn.commit()
sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 1, and there are 0 supplied.

I think it´s referring to the UPDATE Counts section but I’m not sure where to go from here.

#7

Post your latest full code.

#8 
import sqlite3

conn = sqlite3.connect('emaildb.sqlite')
cur = conn.cursor()

cur.execute('DROP TABLE IF EXISTS Counts')
cur.execute('CREATE TABLE Counts (email TEXT, count INTEGER)')

fname = input('Enter file name: ')
if len(fname) < 1: fname='mbox.txt'
fh = open(fname)
for line in fh:
    if not line.startswith('From '): continue
    pieces = line.split()
    email = pieces[1]
    cur.execute('SELECT count FROM Counts WHERE email = ? ', (email,))
    row = cur.fetchone()
    if row is None:
        cur.execute('''INSERT INTO Counts (email, count) 
            VALUES(?,1)''', (email,))
    else:
        cur.execute('UPDATE Counts SET count = count + 1 WHERE email = ?'), conn.commit()
        
        #http://www.sqlite.org/lang_select.html
sqlstr = 'SELECT email, count FROM Counts ORDER BY count DESC LIMIT 10'
for row in cur.execute(sqlstr):
    print(str(row[0], row[1]))

        
cur.close()
#9

This line is the problem.

It should not bet ?'), commit().

The commit() is a separate line.

It should be something like:

        cur.execute('UPDATE Counts SET count = count + 1 WHERE email = ?', a-tuple-with-one-item-goes-here)
#10

I keep getting this traceback:

  File "c:\Users\Admin\Desktop\PY4E\fcc\seq_practice.py", line 22, in <module>
    cur.execute('UPDATE Counts SET count = count + 1 WHERE email = ?', email,)
sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 1, and there are 15 supplied.

What is it referring to when it says “bindings”?

import sqlite3

conn = sqlite3.connect('emaildb.sqlite')
cur = conn.cursor()

cur.execute('DROP TABLE IF EXISTS Counts')
cur.execute('CREATE TABLE Counts (email TEXT, count INTEGER)')

fname = input('Enter file name: ')
if len(fname) < 1: fname='mbox.txt'
fh = open(fname)
for line in fh:
    if not line.startswith('From '): continue
    pieces = line.split()
    email = pieces[1]
    cur.execute('SELECT count FROM Counts WHERE email = ? ', (email,))
    row = cur.fetchone()
    if row is None:
        cur.execute('''INSERT INTO Counts (email, count) 
            VALUES(?,1)''', (email,))
    else:
        cur.execute('UPDATE Counts SET count = count + 1 WHERE email = ?', email,)
        conn.commit()
        
        #http://www.sqlite.org/lang_select.html
sqlstr = 'SELECT email, count FROM Counts ORDER BY count DESC LIMIT 10'
for row in cur.execute(sqlstr):
    print(str(row[0], row[1]))

        
cur.close()
#11

This still is not correct.

#12

Okay. It was an insufficient amount of parantheses.

It’s working. I just wasn’t paying close enough attention. Thanks for your help.