Python sqlite3: Loop through rows from table_a stops when inserting into table_b

user_9247687 0
Viewed 401

Trying to to loop through rows from table_a, pass them into a function, and insert the outputs into table_b. I can manage to loop through rows from table_a, but as soon as i try to insert to table_b something, my script just stops.

with sqlite3.connect(db_path) as db:
    cursor = db.cursor()

cursor.execute("""SELECT * FROM cars""")

for row in cursor:
  car_price = get_car_price(row[0])
  cursor.execute("""INSERT INTO car_prices (price, car_id) VALUES (?,?);""",(car_price, row[0]))

output :

[Finished in 1.0s]

It is weird because the below works, but as soon as i try executing to car_prices table (as in the previous code), the code just ends at first iteration.

with sqlite3.connect(db_path) as db:
    cursor = db.cursor()

cursor.execute("""SELECT * FROM cars""")

for row in cursor:
  print(row)

output:

(1, "car_1")
(2, "car_2")
...
(n, "car_n")
[Finished in 0.7s]

As asked, here is the .schema:

sqlite> .schema
CREATE TABLE IF NOT EXISTS "cars" (
        "id"    integer NOT NULL,
        "name"  text NOT NULL,
        "website_url"   text NOT NULL,
        PRIMARY KEY("id")
);
CREATE TABLE IF NOT EXISTS "car_prices" (
        "price_id"      integer,
        "price" NUMERIC NOT NULL,
        "car_id"        integer NOT NULL,
        PRIMARY KEY("price_id"),
        FOREIGN KEY("car_id") REFERENCES "cars"("id")
python-3.x sqlite
1 Answers

The problem is that you're reusing your cursor, throwing away the results of your first query.

Here's a minimal runnable example (you may have gotten a faster answer if you had included this in your question, since it would've taken less effort for people to get involved):

#!/usr/bin/env python3

import sqlite3
import sys

def get_car_price(carid):
    return carid * 11 + 101

with sqlite3.connect(':memory:') as db:
    db.execute('''
        CREATE TABLE IF NOT EXISTS "cars" (
            "id"    integer NOT NULL,
            "name"  text NOT NULL,
            "website_url"   text NOT NULL,
            PRIMARY KEY("id")
        )
    ''')
    db.execute('''
        CREATE TABLE IF NOT EXISTS "car_prices" (
            "price_id"      integer,
            "price" NUMERIC NOT NULL,
            "car_id"        integer NOT NULL,
            PRIMARY KEY("price_id"),
            FOREIGN KEY("car_id") REFERENCES "cars"("id")
        )
    ''')
    for ix, name in enumerate(('Lolvo', 'Yotoya', 'Edison')):
        url = 'http://example.com/' + name.lower()
        db.execute('INSERT INTO cars VALUES(?,?,?)', (ix, name, url))

    cursor = db.cursor()
    if 'sep' in sys.argv:
        insertcursor = db.cursor()
    else:
        insertcursor = cursor


cursor.execute("""SELECT * FROM cars""")
for row in cursor:
    car_price = get_car_price(row[0])
    print(row, car_price)
    insertcursor.execute("""INSERT INTO car_prices (price, car_id) VALUES (?,?);""",(car_price, row[0]))

cursor.execute('''SELECT * FROM car_prices''')
for row in cursor:
    print('car_prices:', row)

Running with no args, it'll use the same cursor, reproducing your problem:

python-sqlite-stops$ ./stuff.py
(0, 'Lolvo', 'http://example.com/lolvo') 101
car_prices: (1, 101, 0)

Give it 'sep' as an arg, and it'll use a separate cursor for insertion, fixing your problem:

python-sqlite-stops$ ./stuff.py sep
(0, 'Lolvo', 'http://example.com/lolvo') 101
(1, 'Yotoya', 'http://example.com/yotoya') 112
(2, 'Edison', 'http://example.com/edison') 123
car_prices: (1, 101, 0)
car_prices: (2, 112, 1)
car_prices: (3, 123, 2)

You could make it even simpler by just using db.execute() for the inserts; no cursor() call necessary.

user_48118030
Related