Inserting a Date in SQLite

Question

I am wanting to insert a Date (as data type of TEXT) in SQLite. This is what I have setup:

def create_table():
    c.execute('CREATE TABLE IF NOT EXISTS practice_data(date TEXT, distance REAL, duration REAL, avg_pace REAL)')

date = str(input('Enter DATE (i.e. 2016-01-10): '))
distance = float(input('Enter TOTAL DISTANCE RAN (i.e 2.11): '))
duration = float(input('Enter TOTAL DURATION (i.e. 20.34): '))
avg_pace = float(input('Enter AVERAGE PACE (i.e. 10.44): '))

def data_entry():
    c.execute("INSERT INTO practice_data(date,distance,duration,avg_pace) VALUES ({}, {}, {}, {})".format(date, distance, duration, avg_pace))
    conn.commit()
    c.close()
    conn.close()


create_table()
data_entry()

When I run the program it works fine. But when I open the SQLite Browser, the Date Column will not be correct. For example, if I input "2016-11-06" it will enter in "1999". For some reason it is assumming that the ' - ' are subtraction. Even though I have specified that date has a data type of TEXT.

I have seen that SQLite has a datetime option, but I am not sure how to implement it.

mhawke · Accepted Answer · 2016-11-06 23:09:03Z

1

There are 2 problems, neither of which is due to SQLite.

In Python 2 this line:

date = str(input('Enter DATE (i.e. 2016-01-10): '))

results in date being assigned 1999 if you enter 2016-11-06, and this is because Python 2 evaluates the input.

There are 2 solutions:

Use raw_input() instead of input(). raw_input() will return a string without evaluation:

>>> n = input('? ')
? 1+2+3+4
>>> n
10
>>> n = raw_input('? ')
? 1+2+3+4
>>> n
'1+2+3+4'

Use Python 3

EDIT

Since you are using Python 3 the above does not apply.

The other problem is due to the way that you construct the query using string formatting:

c.execute("INSERT INTO practice_data(date,distance,duration,avg_pace) VALUES ({}, {}, {}, {})".format(date, distance, duration, avg_pace))

This executes the query:

INSERT INTO practice_data(date,distance,duration,avg_pace) VALUES (2016-11-06, 1, 2, 3)

and SQLite will evaluate 2016-11-06 and then insert the result.

You could solve this by quoting the text fields like this:

c.execute("INSERT INTO practice_data(date,distance,duration,avg_pace) VALUES ('{}', {}, {}, {})".format(date, distance, duration, avg_pace))

but you shouldn't as it can lead to SQL injection vulnerabilities. Instead of string formatting use parameterised queries:

c.execute("INSERT INTO practice_data(date,distance,duration,avg_pace) VALUES (?, ?, ?, ?)", (date, distance, duration, avg_pace))

This uses place holders (?) for the fields, and the db engine will perform safe substitutions, properly quoting and escaping values as required.

edited Nov 6, 2016 at 23:09

answered Nov 6, 2016 at 22:42

mhawke

87.5k10 gold badges122 silver badges142 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

kstullich Over a year ago

I am using Python3.5 currently. I went ahead and changed the input() to raw_input() and ran it in Python 2.7. But I still get the same result of it interpreting the ' - ' as subtraction.

cco · Accepted Answer · 2016-11-06 22:40:32Z

1

It looks like you are using Python 2.x, because in 2.x input evaluates the input string (2016-11-6 == 1999).

You want to use raw_input instead (for 3.x you should use input)

answered Nov 6, 2016 at 22:40

cco

6,3712 gold badges20 silver badges22 bronze badges

1 Comment

kstullich Over a year ago

I am using Python3.5 currently. I went ahead and changed the input() to raw_input() and ran it in Python2. But I still get the same result of it interpreting the ' - ' as subtraction.

Collectives™ on Stack Overflow

Inserting a Date in SQLite

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related