problem with python and sql query for select entire month-CodePudding

I have no problem with

query = 'INSERT OR REPLACE INTO totalbtc5mbydate(total) SELECT SUM(amount) FROM btc5m where transactionDate like "01-04%"'
c.execute(query)

I got the sum in total

but doesn't work with

last_month = (datetime.date.today().replace(day=1) - datetime.timedelta(days=1)).strftime("%m-%Y")

query = 'INSERT OR REPLACE INTO totalbtc5mbydate(total) SELECT SUM(amount) FROM btc5m where transactionDate like "last_month"'
c.execute(query)

I got the error

sqlite3.IntegrityError: NOT NULL constraint failed: totalbtc5mbydate.total

CodePudding user response：

you are not use the variable last_update.

Switch to prepared statements

last_month = (datetime.date.today().replace(day=1) - datetime.timedelta(days=1)).strftime("%m-%Y")

query = 'INSERT OR REPLACE INTO totalbtc5mbydate(total) SELECT SUM(amount) FROM btc5m where transactionDate like %s'
c.execute(query,(last_month ,))

CodePudding user response：

The query that you are running is

INSERT OR REPLACE INTO totalbtc5mbydate(total) SELECT SUM(amount) FROM btc5m where transactionDate like "last_month"

Try with

last_month = (datetime.date.today().replace(day=1) - datetime.timedelta(days=1)).strftime("%m-%Y")
query = 'INSERT OR REPLACE INTO totalbtc5mbydate(total) SELECT SUM(amount) FROM btc5m where transactionDate like "'   last_month   '"'
c.execute(query)