Hot answers tagged

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!
Neri's user avatar
  • 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" ...
Blauelf's user avatar
  • 20.9k
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 ...
Pushpit Bhardwaj's user avatar
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. ...
Ojou Nii's user avatar
  • 721
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.
curiouskiwi's user avatar
  • 18.6k
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 ...
Matt's user avatar
  • 58
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 ...
kzidane's user avatar
  • 17.7k
3 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 ...
Cliff B's user avatar
  • 68.3k
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.
DinoCoderSaurus's user avatar
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 ...
DinoCoderSaurus's user avatar
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....
Shauny's user avatar
  • 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 ...
DinoCoderSaurus's user avatar
2 votes
Accepted

Access just the text from a db.execute return

Much like you would have seen in the CS50 Finance, db.execute returns a list of dict objects. So just as you accessed the cash value for your index page using rows[0]['cash'] you need to access your ...
curiouskiwi's user avatar
  • 18.6k
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 ...
curiouskiwi's user avatar
  • 18.6k
2 votes
Accepted

Substring SQL Search

You need to concatenate the strings '%' + sometext + '%'
curiouskiwi's user avatar
  • 18.6k
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) ...
Cliff B's user avatar
  • 68.3k
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 ...
njxue's user avatar
  • 21
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 ...
Damian Danner's user avatar
2 votes
Accepted

SQL QUERY MOVIES.DB

Using IN instead of = solves the problem cause the movie_id is not one but too many
MWD's user avatar
  • 86
1 vote
Accepted

PSET8 invalid sw.

A list of a list. Program expects a list of places. execute returns a list of rows. This return jsonify([placesMatches]) (specifically the []) nests placesMatches into another list.
DinoCoderSaurus's user avatar
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 ...
DinoCoderSaurus's user avatar
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 ...
Cliff B's user avatar
  • 68.3k
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....
Blauelf's user avatar
  • 20.9k
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.
Blauelf's user avatar
  • 20.9k
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 ...
SozDaneron's user avatar
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 ...
Cliff B's user avatar
  • 68.3k
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 ...
DinoCoderSaurus's user avatar
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 ...
DinoCoderSaurus's user avatar
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 ...
DinoCoderSaurus's user avatar
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 ...
tishihar's user avatar

Only top scored, non community-wiki answers of a minimum length are eligible