8
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!
6
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" and a list of columns to use for the DISTINCT, but SQLite does not seem to support such thing.
I actually solved this by applying the DISTINCT to a sub-query ...
5
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 movie_id IN
(SELECT movie_id FROM stars Where person_id IN
(SELECT id FROM people WHERE name IS 'Kevin Bacon' and birth = 1958)))
ORDER BY name
5
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. To make your query correct all you have to do is change the = with IN, the IN command here checks a list of values.
e.g.
SELECT * FROM smth WHERE name IN ('...
4
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 individual query.
I have this in my transactions table:
`time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
4
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 documentation suggests.
The idea is fairly simple, but first, let's see what a prepared statement means.
As this article suggests,
Prepared statements, also ...
3
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 GROUP BY stock ORDER BY stock
I tested this so it should work.
I removed you " * " when making the SELECT statement because the 'shares' columns would ...
3
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.
3
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 other words, VALUES(col_name) in
the ON DUPLICATE KEY UPDATE clause refers to the value of col_name
that would be inserted, had no duplicate-key conflict ...
2
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 in avoiding concurrent modifications of the same data and possible deadlocks.
Let's take the following pseudocode as an example:
Process 1:
1 - Read the ...
2
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 manually — you would first retrieve (select) the data, update it, delete the original data from the table, and insert the updated data back.
I think this query ...
2
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.
2
'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 enough information so that you know who bought what at what price and when.
In order to keep the required level of detail, each buy transaction must be one row ...
2
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. It will return a lot of results. Now the more columns you search the more result you will get.
2: This question is linked to the first as in the more letters ...
2
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 == blank || password is blank)
{
// return apology;
}
if (password == again_password)
{
// insert finance row
}
printf("message");
...
2
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 at your queries, you see that this one says stock_total = :current_stock_total but in your list of arguments where you set the values, you haven't included that. ...
2
You need to concatenate the strings
'%' + sometext + '%'
2
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) FROM people WHERE id NOT IN ... AND id IN...
1
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 multiple nested loops passing all prices, holding values, etc as dictionaries? is partially true. I don't know as you need to use "multiple nested loops", or any ...
1
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 code as before, but if we have data to save, we can actually save it with db.execute("INSERT INTO registrants (name, dorm) VALUES(:name, :dorm)", name=request....
1
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 which are keys and values representing a table’s fields and cells, respectively.
Assuming the select query returns one row, you would want to pass klik[0]["...
1
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 for the keys for each stock (ex. NFLX, Netflix Inc., 150).
If you have further questions on the return types for different SQL commands, you can try the ...
1
This categories = [amounts[0]["category"]] looks suspicious, since categories is always updated with the first element of amounts. Ditto total_amount.
1
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 two columns, name which is table name and seq which is the last sequence assigned. After you delete all the records in the users table you could update ...
1
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 you gave for example a query that returns all users whose name starts with an S, you might want to use the whole array. Hope that helps.
Also from the doc on ...
1
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 transactions, but don't call it transaction, it's just a pain in the neck. (Generally speaking, in every language, avoid using reserved words to name things.) ...
1
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 injection. Review the last 30 seconds of the register walkthrough for sample syntax, or the login function in application.py.
1
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 a maximum of one stock.
You need to remove the existing primary key and create one that uses BOTH the ID column AND the SYMBOL. This will allow multiple rows ...
1
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 python program:
#!/usr/bin/env python3
somedict = {"a": 1, "i": 9, "r" : 18, "z": 26}
print(somedict)
executed 10 times in a row will not give the same ...
1
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 something like this:
<thead><th><td>STOCK</td><td>PRICE</td><td>SHARES</td><td>VALUE<td></th><...
Only top voted, non community-wiki answers of a minimum length are eligible