Python Forum
Unable to Update SQLite Table
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Unable to Update SQLite Table
#1
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.
Reply
#2
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)
Reply
#3
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))
Reply
#4
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?
Reply
#5
u are rockstar ! - thank You @Larz60+
I see the issue and hopefully can troubleshoot from here on ... appreciate the help here -
Cheers!
Reply
#6
Glad to help.
DB browser is a valuable sqlite tool.
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
Question Using SQLAlchemy, prevent SQLite3 table update by multiple program instances Calab 3 704 Aug-09-2023, 05:51 PM
Last Post: Calab
  UPDATE SQLITE TABLE - Copy a fields content to another field. andrewarles 14 4,247 May-08-2021, 04:58 PM
Last Post: ibreeden
  How to update values in a pyarrow table? xraphael75 1 3,625 Jan-25-2021, 02:14 PM
Last Post: xraphael75
Question Python + Google Sheet | Best way to update specific cells in a single Update()? Vokofe 1 2,627 Dec-16-2020, 05:26 AM
Last Post: Vokofe
  How to create db table with SQLite and SQLAlchemy?? marcello86 1 2,275 Sep-02-2020, 03:05 PM
Last Post: marcello86
  how to use items combobox in table name sqlite in python hampython 1 2,624 May-24-2020, 02:17 AM
Last Post: Larz60+
  unable to update packages russianponchik 0 1,615 Mar-29-2020, 04:18 PM
Last Post: russianponchik
  Unable to read update access DB saaketh 1 1,937 Sep-24-2019, 11:37 AM
Last Post: Malt
  Updating records 1 to n on an SQLite table KevinBrown 2 2,609 Mar-30-2019, 05:02 PM
Last Post: KevinBrown
  sqlite: INSERT using a variable for table name DT2000 3 6,520 Feb-23-2019, 06:28 AM
Last Post: DT2000

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020