Python Forum
Unable to Update SQLite Table - Printable Version

+- Python Forum (https://python-forum.io)
+-- Forum: Python Coding (https://python-forum.io/forum-7.html)
+--- Forum: General Coding Help (https://python-forum.io/forum-8.html)
+--- Thread: Unable to Update SQLite Table (/thread-30006.html)



Unable to Update SQLite Table - sambanerjee - Sep-29-2020

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.


RE: Unable to Update SQLite Table - Larz60+ - Sep-29-2020

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)


RE: Unable to Update SQLite Table - sambanerjee - Sep-29-2020

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



RE: Unable to Update SQLite Table - Larz60+ - Sep-30-2020

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?



RE: Unable to Update SQLite Table - sambanerjee - Sep-30-2020

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


RE: Unable to Update SQLite Table - Larz60+ - Sep-30-2020

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