datetime objects in SQlite3 and python

Question

Trying to store datetimes in my database project using python and sqlite3. Here's what I have so far...

import sqlite3

def connect():
    conn = sqlite3.connect("road_works.db")
    cur = conn.cursor()
    cur.execute("""CREATE TABLE IF NOT EXISTS road_works(id INTEGER PRIMARY 
        KEY, location TEXT, client TEXT, start_date TEXT, end_date TEXT)""")
    conn.commit()
    conn.close()


def insert(location, client, start_date, end_date):
    conn = sqlite3.connect("road_works.db")
    cur = conn.cursor()
    cur.execute("INSERT INTO road_works VALUES (NULL, ?, ?, ?, ?)",
               (location, client, start_date, end_date))
    conn.commit()
    conn.close()

insert("M25", "Lindor", "2019-03-16 20:00", "2019-07-16 06:00")

At the minute the database takes start_date and end_date as text. I want to change that to dates that I can then start doing calculations with like counting down the days/nights until the job is finished etc. The user will also be inputing the dates and times so I cant use datetime.datetime.now()/today() I have been reading into datetime but its more advanced than I'm used to if anyone could explain it better I would appreciate it.

I have been searching for the solution for a few days this is my first project it may be a little out of my league but I appreciate your attention.

Thanks :)

fixed! Thanks for the help!

def insert(location, client, start_date, end_date):
    conn = sqlite3.connect("road_works.db")
    cur = conn.cursor()
    cur.execute("INSERT INTO road_works VALUES (NULL, ?, ?, ?, ?)",
                (location, client, start_date, end_date))
    start_date = datetime.strptime(start_date, "%Y-%m-%d %H:%M")
    end_date = datetime.strptime(end_date, "%Y-%m-%d %H:%M")
    conn.commit()
    conn.close()

Roland Smith · Accepted Answer · 2019-01-03T14:03:48.650

There is no native datatype for dates in sqlite. The manual that I link to advises (see 2.2) three possible ways to store a date:

TEXT (ISO 8601 string)
REAL (Julian day numbers)
INTEGER (Unix Time)

My suggestion would be to use ISO 8601 format, since it is standardized and unambiguous. Additionally, as opposed to the alternatives it is human-readable without conversion. And datetime objects have standard conversion methods to and from a string for it.

See examples below:

In [1]: from datetime import datetime                                                                    

In [2]: now = datetime.now()                                                                             

In [3]: now                                                                                              
Out[3]: datetime.datetime(2019, 1, 3, 14, 53, 38, 596477)

In [4]: string = now.isoformat()                                                                         

In [5]: string                                                                                           
Out[5]: '2019-01-03T14:53:38.596477'

In [6]: datetime.fromisoformat(string)                                                                   
Out[6]: datetime.datetime(2019, 1, 3, 14, 53, 38, 596477)

Thanks for the response! I didnt know that I have been trying cur.execute('start_date DATETIME') :D. I will give your solution a try soon and let you know how I get on. Thanks again really appreciate the help. — Michael.Hughes1, Jan 03 '19 at 14:16
Hi I have put this in my code now. I got a 'ValueError: time data does not match format' when I tested if a normal string would work so it works. — Michael.Hughes1, Jan 03 '19 at 17:08

score 0 · Answer 2 · answered Jan 03 '19 at 13:45

0

You can use datetime.strtime to convert a string to datetime object

e.g.

from datetime import datetime
datetime_object = datetime.strptime('2019-03-16 20:00', '%Y-%m-%d %H:%M')

SO

answered Jan 03 '19 at 13:45

Talha Junaid

2,351
20
29

Thanks for the response I will do more reading on that topic. Much appreciated. – Michael.Hughes1 Jan 03 '19 at 13:59

datetime objects in SQlite3 and python

2 Answers2