Working on Pset8, Finance. Created a Foreign Key relationship between my portfolio and users table but getting this error when running the application: foreign key mismatch - "portfolio" referencing "users" [SQL: INSERT INTO portfolio(user_id) VALUES(15)]
Yes, there is a user id of 15 in my uses table, just in case you were wondering.
I've attached my users and portfolio tables. I don't see my Foreign Key constraint, but maybe it's not possible in sqlite3? Although, I've read that one can do it.
Here's what my code looks like for the imports and buy function:
import os
from datetime import datetime
from cs50 import SQL
from flask import Flask, flash, jsonify, redirect, render_template, request, session
from flask_session import Session
from tempfile import mkdtemp
from werkzeug.exceptions import default_exceptions, HTTPException, InternalServerError
from werkzeug.security import check_password_hash, generate_password_hash
from helpers import apology, login_required, lookup, usd
# Configure application
app = Flask(__name__)
# Ensure templates are auto-reloaded
app.config["TEMPLATES_AUTO_RELOAD"] = True
# Ensure responses aren't cached
@app.after_request
def after_request(response):
response.headers["Cache-Control"] = "no-cache, no-store, must-revalidate"
response.headers["Expires"] = 0
response.headers["Pragma"] = "no-cache"
return response
# Custom filter
app.jinja_env.filters["usd"] = usd
# Configure session to use filesystem (instead of signed cookies)
app.config["SESSION_FILE_DIR"] = mkdtemp()
app.config["SESSION_PERMANENT"] = False
app.config["SESSION_TYPE"] = "filesystem"
Session(app)
# Configure CS50 Library to use SQLite database
db = SQL("sqlite:///finance.db", foreign_keys=True)
db.execute("PRAGMA foreign_keys = ON")
# Make sure API key is set
if not os.environ.get("API_KEY"):
raise RuntimeError("API_KEY not set")
@app.route("/")
@login_required
def index():
"""Show portfolio of stocks"""
return apology("This is Index Page")
@app.route("/buy", methods=["GET", "POST"])
@login_required
def buy():
"""Buy shares of stock"""
if request.method == "POST":
stock_symbol = request.form.get("symbol")
stock_shares = request.form.get("shares")
if not stock_symbol:
return apology("Missing Symbol", 400)
elif not stock_shares:
return apology("Missing Shares", 400)
if int(stock_shares) < 1:
return apology("Shares must be positive", 400)
rows = db.execute("SELECT cash FROM users WHERE id = :id", id=session['user_id'])
cash = rows[0]['cash']
quote = lookup(stock_symbol)
stock_name = quote['name']
stock_price = quote['price']
now = datetime.now()
total_value = int(stock_shares) * stock_price
if cash > total_value:
# buy the stock and insert into portfolio table
db.execute("INSERT INTO portfolio(stock) VALUES(:stock)",
stock=stock_name)
return redirect("/")
else:
return apology("Need more $ to buy!", 400)
else:
return render_template("buy.html")
Any help in the right direction would be great!