0

I am writing a basic program in python and using sqlite3 as my database to store information.

I am trying to select some data from my database so that I can use it in my program, I have done this before in the program several times however this statement isn't working.

db_cursor.execute("SELECT startDate, renewalPeriod, description, income, expense"
                  "FROM IncomeExpense "
                  "WHERE accountID = {}".format(account_id))

The error I am getting is:

sqlite3.OperationalError: no such column: startDate

This is the sql for table design:

CREATE TABLE IncomeExpense (
    rowID INTEGER PRIMARY KEY AUTOINCREMENT,
    startDate TEXT NOT NULL,
    renewalPeriod INTEGER NOT NULL,
    description TEXT NOT NULL,
    type TEXT NOT NULL,
    income FLOAT NOT NULL,
    expense FLOAT NOT NULL,
    accountID INTEGER,
    FOREIGN KEY (accountID) REFERENCES Account(accountID));

The kicker is that when I manually type that select statement into the sqlite3 terminal it gives me the data no problem. I have included an image of the output I get when I enter it manually however I have blocked out the description, income and expense columns as they contain personal information.

Image of output in sqlite3 terminal

I am not sure if it is something simple I am missing in this statement however any assistance would be greatly appreciated.

Thanks :)

Nathan Winspear
  • 119
  • 10
  • 2
    Do not use string formatting to add parameters to queries. There is a fully-developed, perfectly simple system to create parameterized queries that avoid the risks of SQL injection. – roganjosh Jan 16 '19 at 20:56
  • A likely issue is that you've created multiple databases by accident while developing, and this cursor is connected to a legacy mistake. You need only to make a connection to a database that doesn't exist in SQLite and it will spring into life. – roganjosh Jan 16 '19 at 20:58
  • Are you sure, that this string construction is ok? No blanks will be added this way between the individual parts! – ZorgoZ Jan 16 '19 at 20:59
  • You need a space before `FROM` clause but I assume it is a typo here and not in actual code. – Parfait Jan 16 '19 at 21:05
  • @roganjosh - I have checked the code that connects to the database and it is linked to the correct database file of which only one is there, could you tell me more about the name of the system for creating parameterised queries it may be worth changing to this as I use parameterised queries often in this program? – Nathan Winspear Jan 16 '19 at 21:08
  • That _is_ the name of the system. https://stackoverflow.com/questions/22776756/parameterized-queries-in-sqlite3-using-question-marks etc. – roganjosh Jan 16 '19 at 21:09
  • @Parfait *facepalms* I feel like an idiot, all it was missing was a space after "expense", Thanks for your help guys. – Nathan Winspear Jan 16 '19 at 21:13

0 Answers0