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!
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" ...
user avatar
  • 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 ...
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. ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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.
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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.
user avatar
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 ...
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....
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 18.4k
2 votes
Accepted

Substring SQL Search

You need to concatenate the strings '%' + sometext + '%'
user avatar
  • 18.4k
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) ...
user avatar
  • 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
user avatar
  • 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 ...
user avatar
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 ...
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 ...
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 ...
user avatar
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.
user avatar
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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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 ...
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 ...
user avatar
  • 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 ...
user avatar

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