时间戳

首页 > 解决方案 > Python sqlite3：插入table_b时，从table_a中的行循环停止

python-3.x - Python sqlite3：插入table_b时，从table_a中的行循环停止

问题描述

尝试遍历 table_a 中的行，将它们传递给函数，然后将输出插入 table_b。我可以设法遍历 table_a 中的行，但是一旦我尝试向 table_b 插入一些东西，我的脚本就会停止。

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

输出 ：

[Finished in 1.0s]

这很奇怪，因为下面的工作，但是一旦我尝试执行 car_prices 表（如前面的代码），代码就在第一次迭代时结束。

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

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

for row in cursor:
  print(row)

输出：

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

如所问，这是.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.xsqlite

解决方案

问题是您正在重用光标，丢弃了第一个查询的结果。

这是一个最小的可运行示例（如果您将其包含在您的问题中，您可能会得到更快的答案，因为人们参与其中会花费更少的精力）：

#!/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.

推荐阅读