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.8k
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
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.5k
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.6k
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.
- 27.5k
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
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 ...
- 27.5k
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 ...
- 27.5k
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.5k
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) ...
- 66.6k
2
votes
pset7/movies/13.sql: exclude Kevin Bacon from the resulting list
Try:
SELECT name FROM people WHERE id in (
SELECT person_id FROM stars WHERE movie_id in (
SELECT id FROM movies WHERE id in (
SELECT movie_id FROM stars WHERE person_id in (
SELECT id FROM people ...
- 21
2
votes
Accepted
PSET 7 Movies SQL Q9
The issue here was in the syntax of the SQL query. When looking for multiple elements from a subquery, it's necessary to use "IN" in the query. In other words look for all elements A that ...
- 66.6k
2
votes
Accepted
How do we delete existing flight in sqlite via Django?
I was having the same problem. What worked for me was going back into Django shell and entering the following command.
Flight.objects.filter(id=1).delete()
Of course if there is more than one flight ...
2
votes
Accepted
SQL QUERY MOVIES.DB
Using IN instead of = solves the problem cause the movie_id is not one but too many
- 86
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 ...
- 27.5k
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 ...
- 27.5k
1
vote
pset7 finance/index problem updating values in table
Welcome to the forum!
None of the update statements in the debugger view updated any rows. None of them found a matching symbol in the test table. Notice, test.symbol in the database is goog, but the ...
- 27.5k
1
vote
Accepted
pset7 finance/index problem updating values in table
"It did exactly what I said, but not what I wanted!" ;-)
db.execute("UPDATE test SET price=:price, total=:total WHERE symbol=:symbol", price=price, total=total, symbol=quote['symbol'])
This SQL ...
- 66.6k
1
vote
Accepted
pset7 "index" - using SQL SUM adds a parenthesis to returned dict strings
You can use AS to rename a field, this is especially useful if you join multiple tables and return fields from several of them, or when using aggregate functions as you do here. Like "SELECT buy....
- 20.8k
1
vote
Pset7 - Finance (register function) - creating internal server error
You are missing the ) for your db.execute statement. Count the opening and closing parentheses.
- 20.8k
1
vote
Accepted
Final project SQL Error: execute() takes 2 positional arguments but 3 were given
Turns out I had to us the semicolon. The below works.
row_1 = db.execute("SELECT bum FROM conversion WHERE unit = :id", id =
request.form.get("unit_1"))
row_2 = db.execute("SELECT bum FROM ...
- 35
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 ...
- 66.6k
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 ...
- 27.5k
1
vote
Accepted
PSet 7: Issues with flask table output - hint needed
Basic problem is <table> and <thead> tags inside the first loop. Ditto <tbody> tag inside the second loop. And missing <tr></tr> tags.
The "heading" HTML should look ...
- 27.5k
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 ...
- 27.5k
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
Only top scored, non community-wiki answers of a minimum length are eligible