D4mations.com

SQL – Lecture 3 – CS50's Web Programming with Python and JavaScript



00:00:00 – Introduction
00:00:51 – Databases
00:01:53 – SQL
00:02:35 – SQL Data Types
00:04:26 – create.sql
00:09:27 – Constraints
00:11:07 – INSERT
00:13:39 – insert.sql
00:14:06 – SELECT
00:25:37 – SQL Functions
00:26:21 – UPDATE
00:28:37 – DELETE
00:36:57 – Foreign Keys
00:42:48 – joins.sql
00:50:15 – Indexing
00:56:30 – SQL Injection
01:02:57 – Race Conditions
01:08:10 – SQL Transactions
01:09:08 – SQLAlchemy
01:09:45 – list.py
01:15:42 – import.py
01:22:05 – passengers.py
01:25:19 – airline0
01:38:59 – airline1

Original source

d4mer

, , , , , , , ,

33 responses to “SQL – Lecture 3 – CS50's Web Programming with Python and JavaScript”

  2. Struggling with import.py I got this: [SQL: INSERT INTO books (isbn, title, author, year) VALUES (%(isbn)s, %(title)s, %(author)s, %(year)s )]

    [parameters: {'isbn': '0380795272', 'title': 'Krondor: The Betrayal', 'author': 'Raymond E. Feist', 'year': '1998'}]

  9. db.execute("INSERT INTO BOOKS (isbn,title,author,year) VALUES (:isbn,:title,:author,:year)",{"isbn":isbn,"title":title,"author":author,"year":year})

    ERROR–invalid input syntax for integer: "isbn"

    LINE 1: …NSERT INTO BOOKS (isbn,title,author,year) VALUES ('isbn','ti…

  11. ## My Notes to this lecture
    – DataTypes

    * INTEGER

    * DECIMAL

    * SERIAL

    * VARCHAR

    * TIMESTAMP

    * BOOLEAN

    * ENUM

    – LOGIN CMD ` psql -U postgres postgres`

    – TO SHOW List of Relations `d`

    – TO SHOW List of Roles `du`

    – Constraints

    * NOT NULL

    * UNIQUE

    * PRIMARY KEY

    * DEFAULT

    * CHECK

    * …

    – Functions

    * SUM

    * COUNT

    * MIN

    * MAX

    * AVG

    * …

    – CREATE TABLE

    * `CREATE TABLE flights(

    id SERIAL PRIMARY KEY,

    origin VARCHAR NOT NULL,

    destination VARCHAR NOT NULL,

    duration INTEGER NOT NULL

    );`

    – INSERT ROW

    * `INSERT INTO flights

    (origin, destination, duration)

    VALUES ('New York', 'London', 415);`

    – SELECT

    * `SELECT * FROM flights;`

    * `SELECT origin, destination FROM flights;`

    * `SELECT * FROM flights WHERE duration > 400;`

    * `SELECT * FROM flights WHERE id = 3;`

    * `SELECT AVG(duration) FROM flights;`

    * `SELECT AVG(duration) FROM flights WHERE origin = 'New York';`

    * `SELECT COUNT(*) FROM flights;`

    * `SELECT COUNT(*) FROM flights WHERE origin = 'New York';`

    * `SELECT MIN(duration) FROM flights;`

    * `SELECT MAX(duration) FROM flights;`

    * `SELECT * FROM flights WHERE origin IN ('New York', 'Lima');`

    * `SELECT * FROM flights WHERE destination = 'Paris' AND duration > 500;`

    * `SELECT * FROM flights WHERE destination = 'Paris' OR duration > 500;`

    * `SELECT * FROM flights WHERE origin LIKE '%a%';` # contain a char

    * `SELECT * FROM flights ORDER BY duration ASC LIMIT 3;`

    * `SELECT * FROM flights ORDER BY duration DESC;`

    * `SELECT origin, COUNT(*) FROM flights GROUP BY origin;`

    * `SELECT origin, COUNT(*) FROM flights GROUP BY origin HAVING COUNT(*) > 1;`

    – DELETE

    * `DELETE FROM flights;` # Delete all rows

    * `DELETE FROM flights WHERE id=0;`

    – UPDATE

    * `UPDATE flights

    SET duration = 430

    WHERE origin = 'New York'

    AND destination = 'London';`

    – JOIN

    * `SELECT origin, destination, name FROM flights JOIN passengers ON passengers.flight_id = flights.id;`

    * `SELECT origin, destination, name FROM flights JOIN passengers ON passengers.flight_id = flights.id; WHERE name='Alice'`

    * `SELECT origin, destination, name FROM flights INNER JOIN passengers ON passengers.flight_id = flights.id;`

    * `SELECT origin, destination, name FROM flights JOIN passengers ON passengers.flight_id = flights.id;`

    * `SELECT origin, destination, name FROM flights LEFT OUTER JOIN passengers ON passengers.flight_id = flights.id;`

    * `SELECT origin, destination, name FROM flights RIGHT OUTER JOIN passengers ON passengers.flight_id = flights.id;`

    – INDEXING

    * Speed up the selection from table

    * `CREATE INDEX duration_index ON flights (duration);

    `

  14. Still ststuck on this error, please help, i'm losing motivation, tried so many things
    Error: flask.cli.NoAppException: Could not locate a Flask application. You did not provide the "FLASK_APP" environment variable, and a "wsgi.py" or "app.py" module was not found in the current directory.

  15. if the colon variable thing ( :variable ) is giving you errors like the following –

    db.execute("INSERT INTO flights(origin, destination, duration) VALUES (:origin, :destination, :duration)", {"origin" : origin, "destination" : destination, "duration" : duration})

    wrap the string part into a text() like the following –

    db.execute(text("INSERT INTO flights(origin, destination, duration) VALUES (:origin, :destination, :duration)"), {"origin" : origin, "destination" : destination, "duration" : duration})

    It works for me. For more info check out: https://docs.sqlalchemy.org/en/13/core/sqlelement.html#sqlalchemy.sql.expression.text

  17. If you're running into authentication issues on Windows, It's because PSQL by default use your windows user as user. First switch to the postgres with "psql -u postgres" then enter the password you created when you ran the PSQL installer.

  20. If you are stuck with this error

    engine = create_engine(os.getenv("DATABASE_URL"))

    AttributeError: 'NoneType' object has no attribute '_instantiate_plugins'

    I solved it by
    1. installing psycopg2 —– pip install psycopg2
    2. Edited engine to read like this engine=create_engine("postgres://username:password@localhost:5432/name_of_db")

  21. When I click on the "book ticket " button it does not redirect to the success or the error page instead shows "server error".
    And in the bash terminal it shows an error:
    "sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) column "flight_id" of relation "passengers" does not exist
    "
    Does anybody have any idea about what is wrong here..

  30. Set the environment variable DATABASE_URL to be the URI of your database, which you should be able to see from the credentials page on Heroku.

    In line 20 engine = create_engine(os.getenv, I have copy pasted the URI of my database but when I run, I get this error "DATABASE URL NOT SET"

  31. Traceback (most recent call last):
    File "application.py", line 9, in <module>
    engine = create_engine(os.getenv("DATABASE_URL"))
    File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/__init__.py", line 424, in create_engine
    return strategy.create(*args, **kwargs)
    File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/strategies.py", line 52, in create
    plugins = u._instantiate_plugins(kwargs)
    AttributeError: 'NoneType' object has no attribute '_instantiate_plugins'

Leave a Reply