Storing date value into sqlite table in python3 stores integer value

I’m stuck with this bug and on how to solve it.

Background: I have built a ‘Paycheck’ table in SQLite that has a date attribute, net pay attribute, and a gross pay attribute. I have set the date attribute to a date datatype in SQLite, and set the net and gross attribute to an integer datatype.

The problem: I’m experiencing a problem where when I try to insert a paycheck record, the date attribute will be stored as “1994”. The thing is, while I was making the function to store a paycheck record, I read online that the date format for the date datatype in SQLite was yyyy-mm-dd, and I made sure that I’d insert the date in that format, but the code would run fine but in the database, every time I try to add a record it would state “1994” for the date.

Here’s all the associated code:

import sqlite3
import datetime


def date_sql_format():
    """return date in sql DATE format"""
    return str(datetime.datetime.now().strftime("%Y-%m-%d"))


def execute(command: str):
    """Take a sql query and execute it on the database."""
    connect = sqlite3.connect("db.sqlite")
    cursor = connect.cursor()
    cursor.execute(command)
    connect.commit()
    connect.close()


def add_paycheck(date: str, net: float, gross: float):
    """Add a paycheck record to the database."""
    template = f"{date}, {net}, {gross}"
    execute(f"INSERT INTO Paycheck (Date, Net, Gross) VALUES ({template})")

​x
 
import sqlite3import datetime​​def date_sql_format():    """return date in sql DATE format"""    return str(datetime.datetime.now().strftime("%Y-%m-%d"))​​def execute(command: str):    """Take a sql query and execute it on the database."""    connect = sqlite3.connect("db.sqlite")    cursor = connect.cursor()    cursor.execute(command)    connect.commit()    connect.close()​​def add_paycheck(date: str, net: float, gross: float):    """Add a paycheck record to the database."""    template = f"{date}, {net}, {gross}"    execute(f"INSERT INTO Paycheck (Date, Net, Gross) VALUES ({template})")​

Answer

Evaluate this 2020-04-22 as an arithmetic expression. The answer is 1994. That’s where that number is coming from.

sqlite doesn’t have a datatype date internally. If you look at the Affinity Name Examples section of the sqlite datatypes doc, that typename DATE from a create table statement results in a NUMERIC affinity. So it does the math.

Change the datatype of the date column to TEXT. And you might want to keep the sqlite date and time functions doc handy.

Advertisement

Answer