How can I insert a float into a real position in a SQLite database with python?-CodePudding

I'm trying to make a python program that can track the total amount of money spent over a period of time using an SQLite database to keep track of the running total.

Right now, I've got

def investement_update(a):
    conn = sqlite3.connect('trades.db')
    c = conn.cursor()
    c.execute("SELECT * FROM money_tracker")
    old = c.fetchone()[0]
    print(old)
    new = old   a
    print(new)
    print(type(new))
    str(new)
    c.execute("UPDATE money_tracker SET invested_money = ('?')", (new))
    print('asfdad')
    conn.commit()
    conn.close()

However, that keeps throwing back an error of

PS C:\Users\samue\Projects\cbpro\V2> & C:/Users/samue/AppData/Local/Microsoft/WindowsApps/python3.9.exe c:/Users/samue/Projects/cbpro/V2/main.py
2.0
4.2
<class 'float'>
Traceback (most recent call last):
  File "c:\Users\samue\Projects\cbpro\V2\main.py", line 5, in <module>
    database.investement_update(2.2)
  File "c:\Users\samue\Projects\cbpro\V2\database.py", line 65, in investement_update
    c.execute("UPDATE money_tracker SET invested_money = ('?')", (new))
ValueError: parameters are of unsupported type

I'm unsure as to what to do to fix this error, I've tried making it a string (That's whats listed right now) I've tried putting it in as an int, a float, and all of these return this error. However, if I hardcode an edit, it works perfectly.

EDIT

I've tried removing the parentheses and quotes around the placeholder and still get the same error.

def investement_update(a):
    conn = sqlite3.connect('trades.db')
    c = conn.cursor()
    c.execute("SELECT * FROM money_tracker")
    old = c.fetchone()[0]
    print(old)
    new = old   a
    print(new)
    print(type(new))
    str(new)
    c.execute("UPDATE money_tracker SET invested_money = ?", (new))
    print('asfdad')
    conn.commit()
    conn.close()

PS C:\Users\samue\Projects\cbpro\V2> & C:/Users/samue/AppData/Local/Microsoft/WindowsApps/python3.9.exe c:/Users/samue/Projects/cbpro/V2/main.py
2.0
4.2
<class 'float'>
Traceback (most recent call last):
  File "c:\Users\samue\Projects\cbpro\V2\main.py", line 5, in <module>
    database.investement_update(2.2)
  File "c:\Users\samue\Projects\cbpro\V2\database.py", line 65, in investement_update
    c.execute("UPDATE money_tracker SET invested_money = '?'", (new))
ValueError: parameters are of unsupported type
PS C:\Users\samue\Projects\cbpro\V2>

CodePudding user response：

Remove the parenthesis and quotes around the parameter placeholder, eg:

 ".. SET invested_money = ?"

The inclusion of the quotes turns it into a SQL string literal (with the value of '?', eg. the value supplied is not used) unwittingly and the parenthesis are not required.

Also, ensure to pass a tuple. For a single parameter, it must be written as (x,) as so:

c.execute(".. = ?", (new,))

See How to create a tuple with only one element for details.