Questions tagged [sql]
The sql tag has no usage guidance.
131 questions
4
votes
2
answers
2k
views
How to use current timestamp or now()
In CS50 pset 7, there is a part where you have to use SQL to insert the date and time of a transaction into a table.
According to the web, there is a function to do that called CURRENT_TIMESTAMP or ...
- 85
2
votes
1
answer
288
views
Problem Set 7 (fighting of SQL injection attack)
Halfway the PS-text it says that using the "query'-function will also ensure "that any such placeholders' values are properly escaped so that your code is not vulnerable to SQL injection attacks.", ...
2
votes
1
answer
395
views
2017 pset8 mashup: Slow search - how to improve search performance?
I have successfully implemented search in application.py. Though ss I was testing through the app, I noticed that every search was really slow.
It's turns out that every search request was from 63KB ...
- 23
1
vote
2
answers
4k
views
PSET7 Movies Q9: Why is my number of rows generated different from what the assignment requires?
Here are the parameters for running a SQL query in the file 9.sql:
In 9.sql, write a SQL query to list the names of all people who starred in a movie released in 2004, ordered by birth year.
...
1
vote
2
answers
98
views
How is Week 9 SQL Atomocity solved?
David said:
So in problem set seven, we give you this one line of code, which long
story short, solves this problem in MySQL.
This very long instruction that doesn't even fit onto one line on ...
- 309
1
vote
1
answer
1k
views
PSET 7 Movies SQL Q9
In 9.sql, write a SQL query to list the names of all people who starred in a movie released in 2004, ordered by birth year.
...Your query should output a table with a single column for the name of ...
1
vote
1
answer
191
views
How to use SQL SUM command in this specific situation
I am stuck here, I have to display sum of each share of specific share group by symbol I have tried a lot but could not get it how can I display them in the html.This is my index.py code right now, I ...
- 93
1
vote
1
answer
27
views
How do we delete existing flight in sqlite via Django?
In the notes for the SQLITE section, week 3,
https://cs50.harvard.edu/web/2020/notes/4/#:~:text=delete%20your%20existing%20flight%20from%20New%20York%20to%20London
It states:
Every time we make ...
- 135
1
vote
1
answer
84
views
line 541, in sell user_id = current_id coming back as an error, Why? Is it caused by global variables? Thanks
I know my variable names suck.
sell application.py
@app.route("/sell", methods=["GET", "POST"])
@login_required
def sell():
"""Sell shares of stock"""
current_id = session["user_id_2"]
...
- 43
1
vote
1
answer
382
views
pset7 buy - ON DUPLICATE KEY syntax question
I'm on pset7 buy, and I just need some explanation on how the ON DUPLICATE KEY syntax is supposed to look (at least I assume that's the issue). Btw, id and symbol are both unique keys in my portfolio ...
- 147
1
vote
1
answer
84
views
cs50x Ps7 Movies 10.sql
On the Pset page it says that the solution must have 3392 rows. I wrote two different solutions, both accepted by check50, with different number of rows.
The first one gives 3389 lines:
SELECT ...
1
vote
1
answer
308
views
Finance: Ensuring no duplicate usernames
I've implemented the following code in my "/register" route:
def register():
"""Register user"""
if request.method == "GET":
return render_template("register.html")
else:
#...
- 13
1
vote
2
answers
5k
views
Final project SQL Error: execute() takes 2 positional arguments but 3 were given
EDITED
I am working on a conversion table website as my final project. I am using a base unit multiplier to convert one unit to the other. So for example centimeter to yard would look like this.
...
- 35
1
vote
1
answer
37
views
Passing SQL values into arrays
I'm designing a budgeting website for the final project. One of the features that I want to implement is to be able to track how much money you've spent for each item category.
The SQL command that I'...
- 13
1
vote
1
answer
235
views
pset7 why doesn't my SQL table update?
Can anyone tell me what's wrong with my INSERT INTO? The table is not updating. I get the first purchase and nothing after that.
https://gist.github.com/0bc6c50a5f399ed1a672a8a345cbc395
Here is the ...
- 105
1
vote
1
answer
509
views
SQL search multiple columns with priority
I'm trying to implement the search in pset8 and want to find the most elegant and fast solution.
The way I have implemented it is to first of all check whether the search term is an int, and if so, ...
- 1,122
1
vote
1
answer
74
views
C$50 Finance Pset7 - Buy.php not INSERTing shares correctly
My code for Buy.php.
For some reason whenever I fill out both fields of shares and symbol correctly I get the apologize error that the data wasn't inserted into the table correctly. (I don't think I ...
- 15
1
vote
2
answers
178
views
How do I use SQL transactions?
How do I use SQL transaction with the query function provided by CS50 staff?
I tried:
query("BEGIN TRANSACTION");
and even:
query("START TRANSACTION");
but these didn't work.
1
vote
0
answers
319
views
parse error: no such table
I'm currently on week 1 of cs50's SQL course, running into some issues with the exercise.
Running the code from both the file and the terminal results in a no such table, despite me already ...
- 11
1
vote
1
answer
69
views
PSET7/SQL Any clues as to why my logic isn't working? (13.sql)
This very nested query is to return names of all the actors that performed on a Kevin Bacon film. Instead it's returning nothing, any ideas are appreciated!
Note: I'm yet to implement the part that ...
- 53
1
vote
0
answers
20
views
Why doesn't IDE50 highlight the "IN" keyword in SQL?
Highlighting works well with all other keywords I tried, this is confusing to people who might think (like me, originally) that the keyword is wrong/inexistent...
- 240
0
votes
3
answers
5k
views
PSET7 Movies - Q13.SQL How to NOT include 'Kevin Bacon' in List of Actors that have starred in movies with 'Kevin Bacon
I was able to determine the list of actors that have been in movies with 'Kevin Bacon' and get rid of the duplicates with DISTINCT; however, I cannot figure out how to exclude 'Kevin Bacon' from the ...
0
votes
1
answer
391
views
PSET7 Movies Q8 - Why these queries return different results?
I'm working on pset7 movies problem of the CS50 course and I have a question regarding 8.sql, which asks to write a SQL query to list the names of all people who starred in Toy Story.
To my ...
- 3
0
votes
1
answer
167
views
What does VALUES(shares) refer to in the ON DUPLICATE KEY UPDATE clause?
This is the working snippet:
query("INSERT INTO portfolio (id, symbol, shares) VALUES(?, ?, ?)
ON DUPLICATE KEY UPDATE shares = shares + VALUES(shares)",
$_SESSION['id'], $_POST["...
- 55
0
votes
1
answer
292
views
What is wrong with this SQL command?
query("INSERT INTO stocks (id, symbol, shares) VALUES($_SESSION["id"], $_POST["symbol"], $_POST["number"]) ON DUPLICATE KEY UPDATE shares = shares + VALUES(shares)");
I am getting this error
Parse ...
- 169
0
votes
1
answer
318
views
SQL QUERY MOVIES.DB
I have written the query for 6.sql but it does not work.
SELECT AVG(rating) FROM ratings
WHERE movie_id = (SELECT id FROM movies
WHERE year = 2012);
I can do this by using JOIN
SELECT AVG(rating) ...
- 86
0
votes
1
answer
725
views
pset7/movies/13.sql: exclude Kevin Bacon from the resulting list
In pset7/movies, I want to dynamically exclude Kevin Bacon from the resulting list (e.g. not specifically mentioning his name with NOT or EXCEPT or copying and pasting code that calculates his ID, so ...
0
votes
1
answer
38
views
Substring SQL Search
I am trying to query the Database for a sub-string, I am substituting a string variable from a form for the query. It is giving me a syntax error.
This is the error:
LINE 1: SELECT * FROM books ...
- 107
0
votes
1
answer
1k
views
Access just the text from a db.execute return
Evening all.
I am working on my final project and have a question about db.execute.
When I run my SQL search the return always has the format {"key": value}. Is there any way to then extract the ...
0
votes
2
answers
690
views
pset7 finance/index problem updating values in table
First time asking question, sorry if formatted badly.
I am trying to set up to update my price and total column in my sql table.
I seem to have it mostly correct, as values are being updated properly, ...
- 3
0
votes
1
answer
426
views
For register in pset 7 where I am doing wrong?
Here is my code for register user in database but it is not working, What is my mistake
@app.route("/register", methods=["GET","POST"])
def register():
if request.form["username"] == "" or request....
- 93
0
votes
1
answer
99
views
pset8, import. Only even row numbers from US.txt are being written to db
I managed to import file from US.txt to places table and all seems ok, except that only rows from even numbers in file are being imported. Instead of 40975 rows I have 20487.
I tried with while loop(...
0
votes
1
answer
74
views
Finance pset, cs50's SQL INSERT statements results in runtime error
I'm trying to write a buy() function which would update user's stocks and his balance.
I've added two new tables to my finance.db: stocks table for storing user's stocks and transactions table for ...
0
votes
1
answer
82
views
CS50 pset9: Why does 'SELECT last_insert_rowid()' return 0 instead of the expected value when using CS50 SQL library in Flask?
symbol_id = db.execute("SELECT symbol_id FROM symbols WHERE symbol = ?", quote["symbol"])
if not symbol_id:
db.execute("INSERT INTO symbols (symbol) VALUES ...
0
votes
1
answer
406
views
ValueError: UNIQUE constraint failed: on finance pset9, could someone point me in the right direction on which values should be UNIQUE from my table?
Could someone help me figure out which values should I define as UNIQUE? I'm getting the ValueError: UNIQUE constraint failed: compras.id beacuase of the way I created my table for keeping tab of who ...
0
votes
1
answer
284
views
CS50 PSET7 Movies "query did not return results" 12.sql
First timer asking on Stack Exchange. I have a SQL query that returns 6 results in the terminal but does not work with check50 resulting in
"Query did not return any results"
I read in a ...
- 11
0
votes
1
answer
715
views
pset7 Fifty Ville // not sure how to get the intersect of cases to find the thief
I am currently doing pset7 and I have found most of the clues of who it is but the problem is that I need to use the intersect sql function to find the common name of the thief but I am not sure how.
...
- 27
0
votes
1
answer
405
views
CS50 finance SELL - Cannot get the code to subtract the stock from the database or index
I have tried a number of versions of this code, except I cannot get it to subtract shares from my ("/") index page it does nothing and shows nothing in the shares column in the transaction ...
0
votes
1
answer
75
views
pset 7 houses passes check 50 but shows the names 4 times
I have a really strange problem here: both import and rooster passes every check in check 50, but if I run Rooster the names on every house appear 4 times. I have no clue about why this is happening, ...
0
votes
1
answer
196
views
FINANCE: GROUP BY doesn't add up the numbers of shares of the same stock
So in index i'm trying to display a table with all the stocks that the user has bought, one row for each stock, displaying the total number of shares in the user's portfolio along with the total value ...
- 35
0
votes
1
answer
171
views
PSET8 Finance Buy. SQL query not executing
My buy function is crashing at the SQL query where I'm trying to insert data from the transaction into a transactions table. The types of each column are transaction_id(integer, primary key), user_id(...
0
votes
1
answer
206
views
Problem taking a SUM with SQL to python
I am doing Finance and I am fighting with this code... could anyone help me?
I want to "select" some data from a sql table and put this value to a variable in python (a specific FLOAT number)...
0
votes
1
answer
71
views
PSET 7 HOUSES - Syntax error on case function
I'm trying to select the first name and last name and ONLY select the middle name when there is one. The logic of my query makes sense. Every time I am extracting the first name, last name, and birth. ...
- 5
0
votes
1
answer
413
views
pset7 Q10 Weird Result
The challenge is apparently straightforward: write an SQL query to list the names of all people who have directed a movie that received a rating of at least 9.0.
To get from names (which lives in the '...
0
votes
1
answer
332
views
Pset7 (Movies) 12.sql: Table outputs 96 rows rather than 5 rows
I've been stuck on this for quite some time and did a lot of research on the AND operator in SQL, but no matter how many variations I try I cannot get my table to output just 6 rows, with each row ...
- 23
0
votes
1
answer
272
views
PSET 7 - 6.sql, terminal just hangs
PSET 7 for 6.sql, Terminal just hangs when I execute it. Can anyone tell me why this wouldn't work? I've tried every variation I could think of (and several I found online) for this and none of them ...
0
votes
1
answer
23
views
Difference between { var } vs :var as placeholder - SQL, Postgre
I was wondering if it's a good idea to use { var } in place of :var as placeholder?
This is what I see in CS50 notes:
flight = db.execute("SELECT * FROM flights WHERE id = :id", {"id": flight_id})....
- 103
0
votes
1
answer
149
views
CS50w Project1: Trouble posting reviews (Row Proxy error - how to ensure a tuple)
The last bit I am struggling with on Project1 is posting a rating and review.
I continually get
sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) can't adapt type 'RowProxy'
[SQL: INSERT ...
- 63
0
votes
1
answer
278
views
pset7 houses ISNULL function
I want to replace all NULL value by nothing('') in an output query.
Note: I don't want to replace the NULL by nothing in the table.
SELECT row1, row2, row3
ISNULL(row1,'') AS row1
FROM table
output ...
- 153
0
votes
1
answer
515
views
pset7 houses: dict to SQL
I need to pass a python dict to a database with SQL.
one of my dicts is:
print(student[1])
{'first': 'Adrian', 'house': 'Slytherin', 'birth': '1977', 'middle': 'NULL', 'last': 'Pucey'}
I want to ...
- 153