0

I am testing around with SQL at the Moment. I noticed that getting values out of the DB and messing around in python is pretty slow. So i try to calculate inside my SQL update function. Way faster. Now i have the problem that i have a date, created with datetime.utcnow(), in my DB and i want to calculate/update the Age inside the update function.

This is the error message i get with my Code:

ValueError: parameters are of unsupported type

Date is as Text in my DB, maybe this is my mistake?

Here is my Code so far:

import sqlite3
from datetime import datetime

def database_create():
    conn = sqlite3.connect('Age_Calculation_in_SQL.db')
    c = conn.cursor()

    c.execute("INSERT into Test (ID, Date, Age) values (?, ?, ?)",
                ("1", datetime.utcnow(), 1))         
    conn.commit() 


def database_calculate():
    conn = sqlite3.connect('Age_Calculation_in_SQL.db')
    c = conn.cursor()

    c.execute("UPDATE Test SET Age = Date-? Where ID = 1", (datetime.utcnow()))    

    conn.commit()     

database_calculate()
mk1337
  • 105
  • 10
  • what type is the Date column in your DB? – ddor254 Feb 18 '19 at 13:14
  • Text, i think this is my mistake. But i cant say Date or anything like that. What should it be? – mk1337 Feb 18 '19 at 13:16
  • my suggestion : try and use `time.time()` which will give you a timestamp since the epoch, instead of ` datetime.utcnow()` , and that way your subtraction will work. then manipulate the `Date` attribute to represent Age or whatever you want – ddor254 Feb 18 '19 at 13:18
  • There's two issues. First, you don't pass a sequence to your UPDATE query, you'll need a trailing comma if you just pass in one value (e.g. `(datetime.utcnow(),)` to make it a tuple. Second, you can't just do date calculations like that in SQLite. See [this Q&A](https://stackoverflow.com/questions/289680/difference-between-2-dates-in-sqlite) for a refrerence. – shmee Feb 18 '19 at 13:21
  • I exchanged `datetime.utcnow()` with `time.time()`, same error. What do i have to Change in `c.execute("UPDATE Test SET Age = Date-? Where ID = 1", (time.time())) ` ? – mk1337 Feb 18 '19 at 13:28
  • [execute method](https://docs.python.org/3/library/sqlite3.html#sqlite3.Connection.execute) takes an array/list `[]` as the second parameter, you are sending a tuple `()`. – DinoCoderSaurus Feb 18 '19 at 14:43
  • @DinoCoderSaurus That is just partially true. `execute` absolutely accepts tuples as well; it's just that `(datetime.utcnow())` is **not** a tuple, it's a `datetime` object. See the difference: `print(type((datetime.utcnow())))` vs. `print(type((datetime.utcnow(),)))`. Yes, a list is also accpeted. A tuple should be preferred though, because it is immutable, i.e. cannot be modified by other parts of the code (e.g. multithreading) while the `execute` method is ... executing :) – shmee Feb 18 '19 at 15:01
  • @shmee how embarrassing! Thank you for correcting me (sorry I didn't see your earlier comment before opened my big trap). I always forget that (trailing ,) so I make it a habit to follow the doc to the letter..... – DinoCoderSaurus Feb 18 '19 at 15:07

0 Answers0