如何使用 SQLite3 和 Python 更新 SQLite 表中的行

Question

我是 python 新手，不太了解 sql 的东西。目前是团队树屋的第六周，所以如果这些是菜鸟问题，请在这里与我交流。

目标

1. 导入包含 stock_tickers 和其他 5 列数据的 CSV

2. 将 CSV 转换为 pandas 数据框

3. 将数据框导入数据库。如果已经有唯一的stock_ticker，则不添加新行，而是接下来检查其他5列中的数据是否不同。如果是的话就更新它。

现在我可以执行步骤 #1 和 #2 以及步骤 #3 的一半。在此处的帮助下，能够使循环工作正常工作。如果 csv 中有新的 stock_ticker 行，它会将其添加到数据库中。如果现有 stock_ticker 的数据发生变化，它不会进行任何更新。

for i in range(len(df)):

    try:

        df[df.index == i].to_sql(name='stocks', con=conn, if_exists='append', index=False)

        conn.commit()

    except sqlite3.IntegrityError:

        pass

当前代码如下所示

import pandas as pd

from pandas import DataFrame

from pandas import ExcelWriter

import csv

import sqlite3

### IMPORT CSV ###

stock_csv_file = pd.read_csv (r'C:\Users\home\Desktop\code_projects\FIRE_Dashboard\db\alpha_vantage_active_stocks.csv')

### CHANGING INDEX NAMES FROM CSV TO TABLE NAMES ###

df = pd.DataFrame(stock_csv_file)

df = df.rename(columns = {"symbol":"stock_ticker", "name":"stock_name", "exchange":"stock_exchange", "ipoDate":"stock_ipoDate", "delistingDate":"stock_delistingDate", "status":"stock_status"})

### UPDATING DATABSE WITH SQLITE3###

conn = sqlite3.connect('stockmarket.db')

c = conn.cursor()

insert_statement = """

INSERT INTO stocks (stock_ticker,

                    stock_name,

                    stock_exchange,

                    stock_ipoDate,

                    stock_delistingDate,

                    stock_status

                    )

VALUES (?, ?, ?, ?, ?, ?)

ON CONFLICT (stock_ticker) DO UPDATE

    SET (stock_status)"""

    

for i in range(len(df)):

    values = tuple(df.iloc[i])

    c.execute(insert_statement, values)

我收到的错误

Traceback (most recent call last):

  File "update_stock_tickers.py", line 71, in <module>

    c.execute(insert_statement, values)

sqlite3.OperationalError: incomplete input

Answer 1

这是ON CONFLICT您的查询的子句：ON CONFLICT (stock_ticker) DO UPDATE&nbsp; &nbsp; SET (stock_status)这不是有效的 SQLite 语法。stock_status如果您想在另一行已存在且具有相同的行时进行更新stock_ticker，您可以使用伪表，EXCLUDED如下所示：INSERT INTO stocks (stock_ticker,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; stock_name,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; stock_exchange,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; stock_ipoDate,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; stock_delistingDate,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; stock_status&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )VALUES (?, ?, ?, ?, ?, ?)ON CONFLICT (stock_ticker) DO UPDATE&nbsp; &nbsp; SET stock_status = EXCLUDED.status