Pset8 Finance problems with SQLite inserting into a table

Question

Inserting into a table in finance.db fails. I'm able to run the sqlite3 command via cli without errors and populate the table with the debugging snippet command:

INSERT INTO history (date, symbol, name ,price, shares, action, users_id) VALUES('date', 'symbol', 'name', 2, 2, 'but', 3); 

Actual code snippet:

db.execute("INSERT INTO history (date, symbol, name, price, shares, action, users_id) VALUES (datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'), :symbol, :name, :price, :shares, 'Buy', :users_id)",
                    symbol = symbol, name = quote['name'], price = quote['price'], shares = shares, users_id = session["user_id"]) 

Actual error:

RuntimeError: near "(": syntax error [SQL: "INSERT INTO history (date, symbol, name, price, shares, action, users_id) VALUES (datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'), 'NFLX', 'Netflix, Inc.', 365.21, 1, 'Buy', 2"] (Background on this error at: http://sqlalche.me/e/e3q8)

Debugging snippet:

db.execute(" INSERT INTO history(date, symbol, name ,price, shares, action, users_id) VALUES('date', 'symbol', 'name', 1, 2, 'sell', 3 ")

Debugging error:

`RuntimeError: near " ": syntax error [SQL: " INSERT INTO history(date, symbol, name ,price, shares, action, users_id) VALUES('date', 'symbol', 'name', 1, 2, 'sell', 3 "] (Background on this error at: http://sqlalche.me/e/e3q8)`

.schema history returns:

CREATE TABLE 'history' ( 'id' INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, 'date' TEXT NOT NULL, 'symbol' TEXT NOT NULL, 'name' TEXT NOT NULL, 'price' INTEGER NOT NULL,'shares' INTEGER NOT NULL,'action' TEXT NOT NULL,'users_id' INTEGER NOT NULL);

I believe it's a syntactical error or something regarding the structure of my table but I just can't see my mistake. Any help is welcome!

Thanks

Answer 1

This datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S') is not valid sqlite syntax. You can simply use datetime('now'). It will return the current datetime in YYYY-MM-DD HH:MM:SS format. Try SELECT datetime('now') in command-line sqlite3 or phpliteadmin to see the result (no FROM is required).

The problem with the Debugging snippet is at the end 'sell', 3 "). The " and ) are transposed.