Inserting date string into SQLite from Python giving unexpected results

Question

As the title states, I'm trying to insert a date (formatted as a string) into a SQLite database. This works, however the date is not showing up correctly in SQLite.

Here is a subset of my code:

print("Connecting to the database...")        
sqlite_file = './sqlite_db/cfr_changes.db'
conn = sqlite3.connect(sqlite_file)
c = conn.cursor()


today_date = datetime.now().strftime("%Y-%m-%d")
print(today_date)
print("Inserting tracker into database...")

c.execute("INSERT INTO DATE (`date_id`) VALUES(" + today_date + ")")
c.executemany("INSERT INTO TRACKER (`cfr_status`, `comment`, `mdu`, `iwb`, `obsolete`, `date_id`) VALUES(?,?,?,?,?, " + today_date + ")", list(tracker_df.to_records(index=False)))
#print(c.fetchall())

conn.commit()
conn.close()

Printing 'today_date' returns what I'd expect:

2018-10-24

However when I check the records in SQLite through the terminal, the date is shown as:

Note that 'date_id' is a VARCHAR(255), and date formatting should not be an issue. I would think that is simply a string being stored into a string (or close enough).

Can anyone inform me why this doesn't work as expected?

For reference, here is how the 'TRACKER' and 'DATE' tables were created:

CREATE TABLE `DATE` (
`date_id` VARCHAR(255) NOT NULL PRIMARY KEY);

CREATE TABLE `TRACKER` (
`tracker_id` INTEGER NOT NULL PRIMARY KEY,
 `cfr_status` VARCHAR(255) NOT NULL,
`mdu` BOOLEAN, `iwb` BOOLEAN,
`obsolete` BOOLEAN, `comment` VARCHAR(255), `date_id` VARCHAR(255) NOT NULL, FOREIGN KEY (`date_id`) REFERENCES DATE(`date_id`));

Any help is appreciated.

The problem is that you are inserting 2018-10-24 without single or double quotes around it. That doesn't make any sense to SQLite. I recommend using prepared statements here, and you may read about that here. — Tim Biegeleisen
– Tim Biegeleisen, Commented Oct 24, 2018 at 2:50

Madison Courto · Accepted Answer · 2018-10-24 03:20:35Z

1

The issue is that you are just concatenating variables into your insert statements without worrying about whether the format they are in makes any sense. For example, to insert a date literal into SQLite you should be using this:

'2018-10-24'

Here is an example of how you may use prepared statements to do a proper insert:

today_date = datetime.now().strftime("%Y-%m-%d")
c.execute("INSERT INTO DATE (date_id) VALUES (?)", ("'" + today_date + "'",))

edited Oct 24, 2018 at 3:20

Madison Courto

1,27713 silver badges26 bronze badges

answered Oct 24, 2018 at 2:56

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

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

4 Comments

Madison Courto Over a year ago

Do you mean ("'" + today_date + "'"))

Madison Courto Over a year ago

no... c.execute("INSERT INTO DATE (date_id) VALUES (?)", ("'" + today_date "'",)) vs c.execute("INSERT INTO DATE (date_id) VALUES (?)", ("'" + today_date + "'"))

Tim Biegeleisen Over a year ago

@MadisonCourto Read the documentation. Trailing commas at the end of the tuple are allowed.

PL200 Over a year ago

The 'executemany' actually works as is. The date part though worked perfectly, thanks for you help. I was thinking about it too hard!

Collectives™ on Stack Overflow

Inserting date string into SQLite from Python giving unexpected results

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related