CODEWITHSUNDEEP
pythonsqlite
PL200
PL200

Reputation: 741

Inserting date string into SQLite from Python giving unexpected results

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:

1984

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.

Upvotes: 0

Views: 1511

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522741

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 + "'",))

Upvotes: 1

Related Questions