CS50 Web Programming: Lecture 3 (SQL)- import.py won't work for me

Question

I'm trying to run the same code for import.py from the lecture, but am running into trouble. import.py:

import csv
import os

from sqlalchemy import create_engine
from sqlalchemy.orm import scoped_session, sessionmaker

engine = create_engine("postgresql://myusername:mypassword@localhost:5432/mydbname")
db = scoped_session(sessionmaker(bind=engine))

def main():
    f = open("flights.csv")
    reader = csv.reader(f)
    for o, dest, dur in reader: # these are shortened just to make it easier to understand for learning purposes
        db.execute("INSERT INTO flights (origin, destination, duration) VALUES (:origin, :destination, :duration)",
            {"origin": o, "destination": dest, "duration": dur})
        print(f"Added flight from {o} to {dest} lasting {dur} minutes")
    db.commit()

if __name__ == "__main__":
    main()

When I run the program I get this error message:

Traceback (most recent call last):
  File "/Users/username/Documents/Projects/Coding/Practice/Flask/sql0/venv/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1248, in _execute_context
    cursor, statement, parameters, context
  File "/Users/username/Documents/Projects/Coding/Practice/Flask/sql0/venv/lib/python3.7/site-packages/sqlalchemy/engine/default.py", line 588, in do_execute
    cursor.execute(statement, parameters)
psycopg2.errors.UndefinedTable: relation "flights" does not exist
LINE 1: INSERT INTO flights (origin, destination, duration) VALUES (...
                    ^


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "import.py", line 20, in <module>
    main()
  File "import.py", line 15, in main
    {"origin": o, "destination": dest, "duration": dur})
  File "/Users/username/Documents/Projects/Coding/Practice/Flask/sql0/venv/lib/python3.7/site-packages/sqlalchemy/orm/scoping.py", line 162, in do
    return getattr(self.registry(), name)(*args, **kwargs)
  File "/Users/username/Documents/Projects/Coding/Practice/Flask/sql0/venv/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 1278, in execute
    clause, params or {}
  File "/Users/username/Documents/Projects/Coding/Practice/Flask/sql0/venv/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 984, in execute
    return meth(self, multiparams, params)
  File "/Users/username/Documents/Projects/Coding/Practice/Flask/sql0/venv/lib/python3.7/site-packages/sqlalchemy/sql/elements.py", line 293, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/Users/username/Documents/Projects/Coding/Practice/Flask/sql0/venv/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1103, in _execute_clauseelement
    distilled_params,
  File "/Users/username/Documents/Projects/Coding/Practice/Flask/sql0/venv/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1288, in _execute_context
    e, statement, parameters, cursor, context
  File "/Users/username/Documents/Projects/Coding/Practice/Flask/sql0/venv/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1482, in _handle_dbapi_exception
    sqlalchemy_exception, with_traceback=exc_info[2], from_=e
  File "/Users/username/Documents/Projects/Coding/Practice/Flask/sql0/venv/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 178, in raise_
    raise exception
  File "/Users/username/Documents/Projects/Coding/Practice/Flask/sql0/venv/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1248, in _execute_context
    cursor, statement, parameters, context
  File "/Users/username/Documents/Projects/Coding/Practice/Flask/sql0/venv/lib/python3.7/site-packages/sqlalchemy/engine/default.py", line 588, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedTable) relation "flights" does not exist
LINE 1: INSERT INTO flights (origin, destination, duration) VALUES (...
                    ^

[SQL: INSERT INTO flights (origin, destination, duration) VALUES (%(origin)s, %(destination)s, %(duration)s)]
[parameters: {'origin': 'Paris', 'destination': 'New York', 'duration': '540'}]
(Background on this error at: http://sqlalche.me/e/f405)

I have made sure to create a database under the myname user named flights. When I remove the

if __name__ == "__main__":
    main()

the program will run, but it won't add anything to the flights table. My flights.csv is in the same folder as my import.py file is. Any help would be greatly appreciated.

Answer 1

I have had same problems. Could it be that your CSV has a column header, but you send that first row as data as well? You might change CSV data like that:

and program will run: