I want to DELETE every sqlite3 record with a datetime (e.g. 2022-11-10T??????????) using a date (e.g. 2022-11-10) from a variable.
for example:
last_Date = datetime.strftime(datetime.now() - timedelta(1), '%Y-%m-%d')
and use sqlite code to something like this;
cursor.execute("""
DELETE FROM alpaca_stock_prices_4H WHERE datetime = (?)
""", (last_Date))
CodePudding user response:
The value of the variable last_Date
is a formatted as YYYY-MM-DD
date string but the column datetime
in the table contains timestamps in the format YYYY-MM-DDT??????????
so you can't compare them with the =
operator.
Use SQLite's strftime()
or date()
function to format the datetime
s also to YYYY-MM-DD
:
cursor.execute("""
DELETE FROM alpaca_stock_prices_4H WHERE strftime('%Y-%m-%d', datetime) = ?
""", (last_Date,))
or:
cursor.execute("""
DELETE FROM alpaca_stock_prices_4H WHERE date(datetime) = ?
""", (last_Date,))
If what you actually want is delete all yesterday's rows you can do it without passing a parameter, by using only SQL code:
cursor.execute("""
DELETE FROM alpaca_stock_prices_4H WHERE date(datetime) = date('now', '-1 day'))
""")