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 Python + Google Sheet | Best way to update specific cells in a single Update()? Vokofe 1 151 Dec-16-2020, 05:26 AM
Last Post: Vokofe
  Fetch Oracle DB rows & print it in HTML file with table's col headers in table format tssr_2001 1 494 Sep-04-2020, 01:39 PM
Last Post: ibreeden
  How to create db table with SQLite and SQLAlchemy?? marcello86 1 498 Sep-02-2020, 03:05 PM
Last Post: marcello86
  how to use items combobox in table name sqlite in python hampython 1 679 May-24-2020, 02:17 AM
Last Post: Larz60+
  Mysql CREATE TABLE IF NOT EXISTS dynamic table name nisusavi 0 401 Apr-29-2020, 06:45 PM
Last Post: nisusavi
  unable to update packages russianponchik 0 473 Mar-29-2020, 04:18 PM
Last Post: russianponchik
  MYSQL how to assign a table for each user in my username table database? YoshikageKira 1 853 Dec-26-2019, 05:57 AM
Last Post: buran
  Unable to read update access DB saaketh 1 450 Sep-24-2019, 11:37 AM
Last Post: Malt
  Updating records 1 to n on an SQLite table KevinBrown 2 680 Mar-30-2019, 05:02 PM
Last Post: KevinBrown
  sqlite: INSERT using a variable for table name DT2000 3 2,151 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