How to do update rows in SQLite table using SQLite3 and Python

Question

I am new to python and I don't really understand the sql thing that well. Currently on the 6th week of team treehouse so please bare with me here if these are noob questions.

Goal

Import CSV with stock_tickers and 5 other columns of data
Convert CSV into pandas dataframe
Import dataframe into database. If there is already the unique stock_ticker for it to not add a new row, but next to check if the data in the other 5 columns is different. If it is than update it.

Right now I can do steps #1 and #2 and half of #3. With the help on here was able to get the looping thing to work. If there is a new stock_ticker row in the csv it will add it to database. If the data changes for an existing stock_ticker it won't do any updates.

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

Current Code looks like this

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)

The error I am getting

Traceback (most recent call last):
  File "update_stock_tickers.py", line 71, in <module>
    c.execute(insert_statement, values)
sqlite3.OperationalError: incomplete input

Found these posts that talk about it, but still getting lost >.<

Any help is much appreciated.

Code after solution

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 = EXCLUDED.stock_status"""
    

for i in range(len(df)):
    values = tuple(df.iloc[i])
    c.execute(insert_statement, values)
    conn.commit()

score 2 · Accepted Answer · answered Sep 14 '20 at 21:19

This is the ON CONFLICT clause of your query:

ON CONFLICT (stock_ticker) DO UPDATE
    SET (stock_status)

This is not valid SQLite syntax. If you want to update stock_status when another row already exists with the same stock_ticker, you can use pseudo-table EXCLUDED like so:

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 = EXCLUDED.status

Thank you so much for the help! That solved the issue :) – Jakub Sep 14 '20 at 21:27 — Jakub, Sep 14 '20 at 21:27

How to do update rows in SQLite table using SQLite3 and Python

1 Answers1