时间戳

首页 > 解决方案 > Python 仅在 SQLite DB 上插入一行

python - Python 仅在 SQLite DB 上插入一行

问题描述

为什么我的代码只插入一行？

thewholeenchilada = ("SELECT SUBSTR(email, (SELECT INSTR(email,'@'))) AS org, SUM(count) as count FROM Em GROUP BY org ORDER BY count DESC")

for salida in cur.execute(thewholeenchilada):
    cur.execute('''INSERT INTO Counts (org, count)
                VALUES (?, ?)''', (salida[0],row[1]))

    print((str(salida[0]), salida[1]))
    conn.commit()

标签： pythonsqlsqlite

解决方案

避免循环并运行一个INSERT INTO ... SELECT查询。现在，您在循环内外重复使用相同的光标会导致处理问题。要么使用两个不同的游标，要么有效地组合并让数据库引擎运行操作查询：

sql = '''INSERT INTO Counts (org, [count])
         SELECT SUBSTR(email, INSTR(email, '@')+1) AS org, 
                SUM(count) as [count]
         FROM Em 
         GROUP BY org 
         ORDER BY count DESC
      '''

cur.execute(sql)
conn.commit()

推荐阅读