Loop works fine but when sqlite execute called it stops after first line

Question

It's the first stage of a Python for Everyone data visualisation project. I can't work out why the loop stops working during the SQL commands in lines 90-91. I've tested it segment by segment and the loop works fine if you comment out the last MySQL commands, but it stops working after adding one successful row when you leave them in.

import urllib.request, urllib.parse, urllib.error
import sqlite3
import json
import ssl

api_key = "800a5c3b"
serviceurl = "http://www.omdbapi.com/?"

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

cur.execute('DROP TABLE IF EXISTS Omdbdump;')
cur.execute('''CREATE TABLE Omdbdump (
    id  INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
title TEXT, year TEXT, rated TEXT, released TEXT, runtime TEXT, genre TEXT, director TEXT, writer TEXT, actors TEXT, plotlong TEXT, language  TEXT, country TEXT, awards TEXT, poster URL, imdbrating REAL, rtrating REAL, mcrating REAL, imdbid TEXT, type TEXT, dvd TEXT, boxoffice TEXT, production TEXT, website URL)
''')

# Ignore SSL certificate errors
ctx = ssl.create_default_context()
ctx.check_hostname = False
ctx.verify_mode = ssl.CERT_NONE

fh = cur.execute('''SELECT id, title, year FROM top50''')

rty = list()
for row in fh:
    #row = cur.fetchone()
    qtitle = str(row[1])
    qyear = str(row[2])

    #print(rty)
    print(qtitle)
    print(qyear)

    #parms sets up the query url: url concatenated with address and api key
    #query format https://www.omdbapi.com/?t=blade+runner&y=2018&plot=full&apikey=800a5c3b

    parms = dict()
    parms["t"] = qtitle
    parms["y"] = qyear
    parms["plot"] = "full"
    parms["apikey"] = api_key
    url = serviceurl + urllib.parse.urlencode(parms)

    print('Retrieving', url)
    uh = urllib.request.urlopen(url, context=ctx)
    data = uh.read().decode()
    print('Retrieved', len(data), 'characters', data[:20].replace('\n', ' '))
    
    js = json.loads(data)
    if js['Response'] == 'False':
        print('==== Failure To Retrieve ====')
        print(data)
        continue
   
    title = js['Title']
    year = js['Year']
    rated = js['Rated']
    released = js['Released']
    runtime = js['Runtime']
    genre = js['Genre']
    director = js['Director']
    writer = js['Writer']
    actors = js['Actors']
    plotlong = js['Plot']
    language = js['Language']
    country = js['Country']
    awards = js['Awards']
    poster = js['Poster']
    imdbrating = js['imdbRating']
    mcrating = js['Metascore']
    imdbid = js['imdbID']
    type = js['Type']
    dvd = js['DVD']
    boxoffice = js['BoxOffice']
    production = js['Production']
    website = js['Website']
    try:
        rtrating = js['Ratings'][1]['Value']
    except:
        rtrating = 'N/A'
    
    print(title)
    print(imdbid)
    print(runtime)
    
    #the loop works until here -- with the following lines, it goes through once then stops...
    
    cur.execute('''INSERT INTO Omdbdump (title, year, rated, released, runtime, genre, director, writer, actors, plotlong, language, country, awards, poster, imdbrating, rtrating, mcrating, imdbid, type, dvd, boxoffice, production, website) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )''', (title, year, rated, released, runtime, genre, director, writer, actors, plotlong, language, country, awards, poster, imdbrating, rtrating, mcrating, imdbid, type, dvd, boxoffice, production, website) )
    conn.commit()

print("Done")

There is no such thing as mysqlite. There is mysql and there is sqlite. — Shadow, Oct 23 '21 at 12:21
"It stops working": **exactly** what do you mean here? Does the application hang (i.e. it appears not to respond any more). Do you get an exception? If so, please edit the question to include the full traceback of the exception, as the fix for your problem may depend on exactly what exception you are getting. Or is something else happening, and if so, exactly what? We can't help you yet because we don't have enough details. — Luke Woodward, Oct 23 '21 at 13:34
Thanks, @LukeWoodward. It correctly runs the script on first line of the CSV data, creating the URL, sending the query to the API, receiving the JSON, assigning the expected variables and inserting them into the table, but then doesn't run the rest of the lines from the CSV. When I comment out the final cur.execute line, it successfully iterates through all 50 lines of the CSV, and receives the expected JSON data for each query. I can't figure it out. — Louis, Oct 23 '21 at 14:07

score 1 · Accepted Answer · answered Oct 23 '21 at 14:19

1

Program changes the "value" of the cursor here

cur.execute('''INSERT INTO Omdbdump.......)

while iterating over the cursor here for row in fh:.

Possible solutions:

create another cursor for the insert
use the connection's execute method for the insert.

answered Oct 23 '21 at 14:19

DinoCoderSaurus

6,110
2
10
15

1

Thank you, DinoCoderSaurus, that's great: changing cur.execute to conn.execute in that line worked. – Louis Oct 23 '21 at 14:30

score 0 · Answer 2 · answered Oct 26 '21 at 06:32

The accepted answer is a great explanation from [DinoCoderSaurus][1] that helped me answer my own [question][2] but the connection.execute() suggestion does not work in apsw. I had to concatenate some sql and take some of the calls into a new loop after using a list of tuples to link the two loops.

The answer to the OP's case would be to change:

    cur.execute('''INSERT INTO Omdbdump (title, year, rated, released, runtime, genre, director, writer, actors, plotlong, language, country, awards, poster, imdbrating, rtrating, mcrating, imdbid, type, dvd, boxoffice, production, website) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )''', (title, year, rated, released, runtime, genre, director, writer, actors, plotlong, language, country, awards, poster, imdbrating, rtrating, mcrating, imdbid, type, dvd, boxoffice, production, website) )

to:

values=[] #outside the original loop

#then inside the loop    
    values.append((title, year, rated, released, runtime, genre, director, writer, actors, plotlong, language, country, awards, poster, imdbrating, rtrating, mcrating, imdbid, type, dvd, boxoffice, production, website))

#then after the values harvesting loop has closed a new loop:
for value in values:
    cur.execute('''INSERT INTO Omdbdump (title, year, rated, released, runtime, genre, director, writer, actors, plotlong, language, country, awards, poster, imdbrating, rtrating, mcrating, imdbid, type, dvd, boxoffice, production, website) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )''', value)


  [1]: https://stackoverflow.com/users/5577076/dinocodersaurus
  [2]: https://stackoverflow.com/questions/69706486/why-does-apsw-cursor-executesql-vars-break-outer-for-loop/69712751#69712751

Thanks, Andy, this sounds a more robust way of doing it, but when I try this, the same line (from the last record in values) gets inserted into every line of the table. I get 49 identical lines. I can see all the titles' data in the values list, but don't know why the insert loop is not iterating through the whole values list — Louis, Oct 29 '21 at 17:20
have you tried printing value before the cur.execute statement? it should be correct. need to pinpoint down the error — Andy Thompson, Nov 08 '21 at 12:43

Loop works fine but when sqlite execute called it stops after first line

2 Answers2

Linked

Related