Unable to Update SQLite Table

[sambanerjee]

Hi:

I am not able to get the update statement to work :( - any help on what I'm missing ?

c.execute("select DISTINCT Symbol from StockData")
tickers = c.fetchall()   
for row in tickers:
    c.execute("select [Adj Close] from StockData where Symbol = ? ", (row))
    AdjClose = c.fetchall()
    df_ta = pd.DataFrame(data = AdjClose, dtype=numpy.float64)
    df_ta = df_ta.apply(lambda c: talib.RSI(c, timeperiod = 14))
    for index, item in df_ta.iterrows():
        try:
            c.execute('''UPDATE StockData SET RSI = ? where Symbol = ? ''', (item.values, row))
        except Exception as e:
            print('Update Error: ' + str(e))
    print('Updated' + str(row))        
    conn.commit()
print ("Number of rows updated: {}".format(c.rowcount))

Error -- Update Error: Error binding parameter 1 - probably unsupported type.

[Larz60+]

you're not showing enough code, or if this is all of the code, you are missing connect, and other starting protocol.

In addition, please post entire unmodified error tracebacks (in error tags)

[sambanerjee]

Sorry missed the imports and the initializations -- here you go

import import_ipynb
import talib
import numpy
import yfinance as yf
import datetime as dt
import time
from datetime import datetime, timedelta 
import sqlite3
import pandas as pd
import numpy as np
conn = sqlite3.connect('Strategy_RSI_MACD_Data.db')
c = conn.cursor()
c.execute("select DISTINCT Symbol from StockData")
tickers = c.fetchall()   
for row in tickers:
    c.execute("select [Adj Close] from StockData where Symbol = ? ", (row))
    AdjClose = c.fetchall()
    df_ta = pd.DataFrame(data = AdjClose, dtype=numpy.float64)
    df_ta = df_ta.apply(lambda c: talib.RSI(c, timeperiod = 14))
    for index, item in df_ta.iterrows():
        try:
            c.execute('''UPDATE StockData SET RSI = ? where Symbol = ? ''', (item.values, row))
        except Exception as e:
            print('Update Error: ' + str(e))
    print('Updated' + str(row))        
    conn.commit()
print ("Number of rows updated: {}".format(c.rowcount))

[Larz60+]

Install a copy of db browser: https://sqlitebrowser.org/

• Temporarily add the following three lines of code before line 22
  query = f"UPDATE StockData SET RSI = {item.values} where Symbol = {row};"
  print(query)
  input()
  
• Copy the printed query
  
• ctrl-c out of your code and remove the three lines.
  
• Install db browser
  
• double click on your sqlite database, it should come up in db browser
  
• Click on the Execute SQL tab.
  
• Enter an update query copied above
  
• Highlight the query and click on right arrow in toolbar.
  
• Does the query work?
  

[sambanerjee]

u are rockstar ! - thank You @Larz60+
I see the issue and hopefully can troubleshoot from here on ... appreciate the help here -
Cheers!

[Larz60+]

Glad to help.
DB browser is a valuable sqlite tool.