In my budget tracking app using Python and sqlite3 I don’t need the user to come up with new types of transactions or accounts (card, cash and etc.). I need to make a fixed amount of transaction types (income, expense and transfer) and that’s it. My code:
def make_a_transaction(current_cursor, account: str, type_of_transaction: str, category: str,
amount: float, currency: str = 'USD',
date_of_transaction: str = str(datetime.now())[:19], description_of_transaction: str = ''):
current_cursor.execute('INSERT INTO Transactions (account, type_of_transaction, category, amount, currency, '
'date_of_transaction, description_of_transaction) VALUES (?, ?, ?, ?, ?, ?, ?)',
(account, type_of_transaction, category, amount, currency,
date_of_transaction, description_of_transaction))
I don’t want to let the user create a new account, type of transaction, categories nor currency. I could write:
cursor.execute('SELECT DISTINCT category FROM Transactions')
if category not in cursor.fetchall():
print('Choose category from the list of existing categories')
But is there a more flexible approach? Maybe a JSON file or another SQL table?
mad_code is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
2
It would be more efficient to let the database do the work of determining that some category be not present in the table. You could try something along these lines:
query = '''SELECT EXISTS (
SELECT 1 FROM category WHERE category = ?
)'''
cursor.execute(query)
result = cursor.fetchall()
if not result:
print('Choose category from the list of existing categories')
0