kstullich kstullich - 26 days ago 9
Python Question

Inserting a Date in SQLite

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.

Answer

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.

Comments