unused variable error when trying to create separate table for each user's transactions in finance problem

Question

here is my code

215 db.execute("CREATE TABLE ':username' [finance.db]('symbol' 
             varchar(16),'price' float, 'shares' int,'transaction_date' date , 
             'transaction_time' time" ,username=request.form.get("username"))

here is my error when i run flask

File "/home/ubuntu/finance/application.py", line 215, in register
    ,username=request.form.get("username"))
  File "/usr/local/lib/python3.7/site-packages/cs50/flask.py", line 40, in _execute_after
    return _execute_before(*args, **kwargs)
  File "/usr/local/lib/python3.7/site-packages/cs50/sql.py", line 183, in execute
    raise RuntimeError("unused values ({})".format(", ".join(keys)))
RuntimeError: unused values (username)

could anyone explain me this runtime error. and also the reason why should a single table be made for every users transaction rather than separate table for each?

Answer 1

Can't speak to the user error because I'm not well versed in flask, other than to say that an unused variable is pretty much self-explanatory. But, hopefully, this explanation will make the whole thing moot.

As a general security rule, table manipulation at an app level should be completely avoided!

Creating a new table for each user transaction, or even each user would be a logistical, practical, and operational nightmare! It could produce countless tables in the database and would consume vast resources needlessly. It would be like putting a library in storage, but renting a self-storage unit for each book! Along with having potentially countless tables, you'd then have to write more complex code to find and manipulate those tables and the data within them.

There are also security concerns. An app that adds/deletes/alters data in a table is a necessary risk and well handled. An app that can create/alter/delete TABLES is a major security risk. Table manipulation (as opposed to data manipulation) should ONLY be done at an administrative level.

No, the realistic way to handle it is to create a single table for ALL transactions. The table would have all the fields/columns necessary to describe the transaction, most importantly including the userid, plus any other columns that you'd feel are needed or useful

Hopefully, this answers your question. If it does, please click on the check mark to accept. Let's keep up on forum maintenance. ;-)