CODEWITHSUNDEEP
pythonsqlite
Abinash Tripathy
Abinash Tripathy

Reputation: 81

Python Sqllite - UPDATE command executes but doesn't update

import sqlite3 as sql    
v = (161.5, 164.5, 157.975, 158.5375, 159.3125, 160.325, 74052, 8)

try:
        connection = sql.connect("data.db")
        sql_update_query = """UPDATE RECORDS SET OPEN = ?,HIGH = ?,LOW = ?,CLOSE = ?,LAST = ?,PREVCLOSE = ?,TOTTRDQTY = ? WHERE ROWID = ?"""
        cursor = connection.cursor()
        cursor.execute(sql_update_query,v)
        connection.commit()
        print("Total", cursor.rowcount, "Records updated successfully")
        connection.close()
except Exception as e:
        print(e)

Here is the code that I am using to update the data on my table named "RECORDS".

I tried to check if my SQL statement was wrong on DBBrowser:

UPDATE RECORDS SET OPEN = 161.5,HIGH = 164.5,LOW = 157.975,CLOSE = 158.5375,LAST = 159.3125,PREVCLOSE = 160.325,TOTTRDQTY = 74052 WHERE ROWID = 8

Output was:

Execution finished without errors.
Result: query executed successfully. Took 2ms, 1 rows affected
At line 1:
UPDATE RECORDS SET OPEN = 161.5,HIGH = 164.5,LOW = 157.975,CLOSE = 158.5375,LAST = 159.3125,PREVCLOSE = 160.325,TOTTRDQTY = 74052 WHERE ROWID = 8

But when I run my code on python.. it just doesn't update. I get:

Total 0 Records updated successfully

My python code runs but nothing changes on the database. Please help.

Edit: 29-04-2022: Since my code is fine, maybe the way my database is created is causing this issue.

So I am adding the code that I use to create the DB file.

import os
import pandas as pd
import sqlite3 as sql
connection = sql.connect("data.db")
d = os.listdir("Bhavcopy/")
for f in d:
    fn = "Bhavcopy/" + f
    df = pd.read_excel(fn)
    df["TIMESTAMP"] = pd.to_datetime(df.TIMESTAMP)
    df["TIMESTAMP"] = df['TIMESTAMP'].dt.strftime("%d-%m-%Y")
    df.rename(columns={"TIMESTAMP":"DATE"},inplace=True)
    df.set_index("DATE",drop=True,inplace=True)
    df['CHANGE'] = df.CLOSE - df.PREVCLOSE
    df['PERCENT'] = round((df.CHANGE/df.PREVCLOSE) * 100, 2)
    df.to_sql('RECORDS', con=connection, if_exists='append')
connection.close()

Sample of data that is being added to the database:

       SYMBOL SERIES      OPEN  ...    TIMESTAMP  TOTALTRADES          ISIN
0   20MICRONS     EQ     58.95  ...  01-JAN-2018         1527  INE144J01027
1  3IINFOTECH     EQ      8.40  ...  01-JAN-2018         7133  INE748C01020
2     3MINDIA     EQ  18901.00  ...  01-JAN-2018          728  INE470A01017
3      5PAISA     EQ    383.00  ...  01-JAN-2018          975  INE618L01018
4     63MOONS     EQ    119.55  ...  01-JAN-2018         6628  INE111B01023

[5 rows x 13 columns]
         SYMBOL SERIES     OPEN  ...    TIMESTAMP  TOTALTRADES          ISIN
1412  ZODJRDMKJ     EQ    43.50  ...  01-JAN-2018           10  INE077B01018
1413      ZUARI     EQ   555.00  ...  01-JAN-2018         2097  INE840M01016
1414  ZUARIGLOB     EQ   254.15  ...  01-JAN-2018         1670  INE217A01012
1415  ZYDUSWELL     EQ  1051.00  ...  01-JAN-2018          688  INE768C01010
1416      ZYLOG     EQ     4.80  ...  01-JAN-2018          635  INE225I01026

[5 rows x 13 columns]

Shape of the excel files:

(1417, 13)

Also someone asked how I am creating the table:

import sqlite3 as sql
connection = sql.connect("data.db")

cursor = connection.cursor()

#create our table:
command1 = """
    CREATE TABLE IF NOT EXISTS
    RECORDS(
        DATE TEXT NOT NULL,
        SYMBOL TEXT NOT NULL,
        SERIES TEXT NOT NULL,
        OPEN REAL,
        HIGH REAL,
        LOW REAL,
        CLOSE REAL,
        LAST REAL,
        PREVCLOSE REAL,
        TOTTRDQTY INT,
        TOTTRDVAL REAL,
        TOTALTRADES INT,
        ISIN TEXT,
        CHANGE REAL,
        PERCENT REAL
        )
"""

cursor.execute(command1)
connection.commit()
connection.close()

Upvotes: 0

Views: 361

Answers (3)

gimix
gimix

Reputation: 3833

I created your table with only the numeric fields that needed to be updated, and run your code - it worked. So in the end it had to be a datatype mismatch, I'm glad you found it :)

Upvotes: 3

Abinash Tripathy
Abinash Tripathy

Reputation: 81

So I found the problem why the code even if correct was not working. Thanks to @gimix.

I was creating the variable v:

v = (161.5, 164.5, 157.975, 158.5375, 159.3125, 160.325, 74052, 8)

by read it from a dataframe, when everyone said that my code is correct and when gimix asked "how I created the table", I realized that it could have been a datatype mismatch. On checking I found that one of the values was string type.

so this change:

i = 0
o = float(adjdf['OPEN'].iloc[i])
h = float(adjdf['HIGH'].iloc[i])
l = float(adjdf['LOW'].iloc[i])
c = float(adjdf['CLOSE'].iloc[i])
last = float(adjdf['LAST'].iloc[i])
pc = float(adjdf['PREVCLOSE'].iloc[i])
tq = int(adjdf['TOTTRDQTY'].iloc[i])
did = int(adjdf['ID'].iloc[i])

v = (o,h,l,c,last,pc,tq,did)

This fixed the issue. Thank you very much for the help everyone.

I finally got:

Total 1 Records updated successfully

Upvotes: 0

Pepe N O
Pepe N O

Reputation: 2344

Your code works fine both in Windows and Linux, the only reason to see that kind of behavior is that you are modifying two files with same name in a different location. Check what file is being referenced in your DBBrowser.

And in doubt prefer absolute paths as in your comment above

connection = sql.connect("C:/Users/Abinash/Desktop/data.db")

Upvotes: 0

Related Questions