I am working on my buy module. In my database I have 'users' table, a 'history' table, where I keep track of each stock purchase, and a 'portfolio' table which contains a user_id, stock, and shares.
What is working:
1) I can successfully purchase a stock, enter the user, stock, number of shares purchased and date of transaction in the 'history' table.
2) I can successfully deduct purchase price from 'cash' in 'users' table.
What I want to do:
1) Check the 'portfolio' table to see if the logged in user has ever purchased stocks - If not add user_id to portfolio.id.
2) Check the 'portfolio' table to see if the logged in user has purchased this particular stock - If not add stock to portfolio.stock.
3) If logged in user has purchased this particular stock before - Update portfolio.shares.
What I have tried so far:
# query portfolio to see if stock exists
exists = db.execute("SELECT stock FROM portfolio WHERE id= :id", id=session['user_id'])
While portfolio is empty, the above code returns an empty (but not NULL or not None) list.
Question:
1) How do I iterate over 'exists' and check to see if the logged in user has an entry, and (if they do have an entry or entries) see if their stocks in the portfolio match the stock they are buying.