8
votes
PSET7 Movies Q9: Why is my number of rows generated different from what the assignment requires?
I had the same problem. In the end I've solved it by getting rid of DISTINCT and using both ORDER BY and GROUP BY.
Hope that helps!
- 101
6
votes
Accepted
PSET7 Movies Q9: Why is my number of rows generated different from what the assignment requires?
Your DISTINCT would make the result records appear only once. But that means the name, and multiple stars might share a name. In other SQL dialects you could use DISTINCT ON (col1, col2) with an "ON" ...
- 20.7k
5
votes
PSET7 Movies - Q13.SQL How to NOT include 'Kevin Bacon' in List of Actors that have starred in movies with 'Kevin Bacon
This is a shorter code that I wrote based on your code snippets. Have a look at it.
SELECT DISTINCT(name) FROM people WHERE name IS NOT 'Kevin Bacon' and id IN
(SELECT person_id FROM stars Where ...
5
votes
Accepted
PSET7 Movies Q8 - Why these queries return different results?
It's because in your first query you said where id = (.... this query here is expecting only 1 value so in the query inside the parentheses the first value it gets is the only value that is checked. ...
- 711
4
votes
Accepted
How to use current timestamp or now()
You can set the automatic value in the table itself to use CURRENT_TIMESTAMP so that anytime you add to the table, the time value is set to that. Then you don't need to insert that field in your ...
- 18.4k
4
votes
Accepted
Problem Set 7 (fighting of SQL injection attack)
Not really. The function query uses another function from the standard library namely prepare that prevents SQL injection attacks by eliminating the need to manually quote the parameters as the ...
- 17.4k
3
votes
Accepted
How to use SQL SUM command in this specific situation
I think I know what you are after. You could try your SQL as something like this:
SELECT stocks, price, SUM(shares) as total_shares, SUM(shares) * price AS total
FROM porfolio WHERE user_id = ii ...
- 58
3
votes
Accepted
What is wrong with this SQL command?
As I told you on Slack:
This is the syntax that will work:
"INSERT INTO table(field1,field2,field3) VALUES (?,?,?)", $var1, $var2, $var3);
and replacing the fields and variables as appropriate.
- 18.4k
3
votes
Accepted
What does VALUES(shares) refer to in the ON DUPLICATE KEY UPDATE clause?
According to the docs:
You can use the VALUES(col_name) function in the UPDATE clause to
refer to column values from the INSERT portion of the INSERT ... ON DUPLICATE KEY UPDATE statement. In ...
- 17.4k
2
votes
How is Week 9 SQL Atomocity solved?
Kareem correctly explained how the query works.
I only would like to elaborate the concept of "ATOMICITY".
Since actual computers run in a multi-tasking, multi-user environment, care should be taken ...
- 1,156
2
votes
Accepted
How is Week 9 SQL Atomocity solved?
I think the professor was referring to the process of updating when he analogized this to a SELECT query and an INSERT query at the same time, because that's probably how you'd probably do an update ...
- 17.4k
2
votes
Accepted
pset8, import. Only even row numbers from US.txt are being written to db
This while(fgetcsv($file, 500, "\t")) is reading the odd numbered lines. This $csvarray = fgetcsv($file, 500, "\t"); is reading the even numbered lines, which are then inserted into the db.
- 26.8k
2
votes
Accepted
pset7 buy - ON DUPLICATE KEY syntax question
'ON DUPLICATE KEY UPDATE' is MySQL syntax. But is this really what you need to do? Remember from the spec:
Add one or more new tables to finance.db via which to keep track of the purchase. Store ...
- 26.8k
2
votes
Accepted
2017 pset8 mashup: Slow search - how to improve search performance?
1: The more columns you are searching the more results you return. For example as you start to type the word "Boston" the search query takes 'B' and looks for every word in the column with a 'B' in it....
- 96
2
votes
Accepted
For register in pset 7 where I am doing wrong?
It looks like this function will always print "Both passwords don't match" and redirect to register.html (unless username or password is blank). In pseudo-C this routine would look like:
if (username ...
- 26.8k
2
votes
Accepted
line 541, in sell user_id = current_id coming back as an error, Why? Is it caused by global variables? Thanks
The error is pointing you to the problem.
missing value for placeholder (:current_stock_total)
That means that execute is looking for that value to fill in to one of your sql queries. If you look ...
- 18.4k
2
votes
Accepted
2
votes
PSET7 Movies - Q13.SQL How to NOT include 'Kevin Bacon' in List of Actors that have starred in movies with 'Kevin Bacon
Have you tried this. First do a select to get the ID or IDs of the one and only Kevin Bacon, then do the main select while excluding the very short list of IDs that are KB.
SELECT DISTINCT(name) ...
- 63.4k
2
votes
Accepted
SQL QUERY MOVIES.DB
Using IN instead of = solves the problem cause the movie_id is not one but too many
- 72
1
vote
PS7: Please give me a hint on flask loop usage
If I understand this correctly, you are seeing the list of stocks but symbol, share_count, price_current and holding all display the values from the first stock. I think this "Am I meant to be using ...
- 26.8k
1
vote
Accepted
Pset 7 Variable in SQL statement
Review the Notes section of week9, or week 9 lecture starting around 1:10:30 to see how parameter substitution (placeholders) is handled with the execute method.
Now in register we’ll have similar ...
- 26.8k
1
vote
Accepted
pset7-getting one value from database into htmk
The output looks like a list of dictionary objects, and in fact it is. From the Hints section of the spec:
If str is a SELECT, then execute returns a list of zero or more dict objects, inside of ...
- 26.8k
1
vote
SQL SELECT query
It will return a list of dicts. Each dict item contains a key:value pair, where the key should be the same as the keys in your portfolio table (ex. symbol, stockname, price) and the value being values ...
- 13
1
vote
Accepted
Passing SQL values into arrays
This categories = [amounts[0]["category"]] looks suspicious, since categories is always updated with the first element of amounts. Ditto total_amount.
- 26.8k
1
vote
Accepted
How to start id count from 1 after deleting all records in the table? pset7-finance
The autoincrement keys are in a table called sqlite_sequence. I do not see a way to access that table through the phpliteadmin GUI, but it can be accessed from the SQL tab (or with sqlite3). It has ...
- 26.8k
1
vote
Accepted
pset7: why do we need [0] in from of list? (e.g. someData[0]['dic']?)
It's because when you run an SQL query with execute(), all the affected records in the database are returned to you, in an array of dictionaries. So you want the first dictionary in that list. But if ...
- 7,326
1
vote
Accepted
Problem Set 7 - Buy - RuntimeError
The error from this sql INSERT INTO users ('transaction') VALUES(:cash) (quotes or not) is because there is no column named transaction in the users table.
Now is the perfect time to add a table for ...
- 26.8k
1
vote
Accepted
Finance: Update SQL Data: db.execute
If you don't use bound variables with the CS50 sql module, sqlite will read num_sell as a column name, thus the "no such column" error. You need to use bound variables ostensibly to prevent sql ...
- 26.8k
1
vote
Accepted
pset7 why doesn't my SQL table update?
Exactly as predicted. There is a primary key on the ID column and it is ONLY on the ID column. This means that there can be only one row in the table with that ID. The result is each ID can only own ...
- 63.4k
1
vote
Accepted
Flask column order randomly changes whenever I refresh local host
It's not flask. And opening/closing/saving is not involved. It is python. A dictionary is an "unordered list". This for key,value in somedict iterates over the key,value pair arbitrarily.
This simple ...
- 26.8k
Only top scored, non community-wiki answers of a minimum length are eligible