Setting up Flask/SQLAlchemy Step by Step

flask-sqla-step-by-step.rst

Setting up Flask <-> SQLAlchemy Step-by-Step

1. Check app.py for postgresql:///sample_db <— the name of your new database is the thing after the 3 slashes. Use this in step 2.

2. In terminal (and maybe not necessarily in the dir of your file, just anywhere?), type: createdb sample_db

   NOTE: (Yes, it doesn't matter what directory you're in when you run createdb. The files PostgreSQL uses to actually store your database are not in your project directory)

3. Check to see that it was created. The command: psql -l should list all your databases

4. To create the tables defined in models.py, type: psql -f sample_db.sql sample_db (I think we’ve seen other variations on this command where the -f is in a different order?) (also see note/question in step 14)

   NOTE: There isn't a different order with -f, but you can use "shell redirection in" operator like this psql sample_db < sample_db.sql. That right-pointing angle bracket tells the shell "read the contents of this file into this program". This accomplishes the same goal as -f, but -f is a feature built in to psql, and you get slightly more informative error messages with that, so I'd remember the -f way.

5. To open psql and work with the database, type: psql sample_db
6. Now, your lines should start with sample_db=#
7. To see all tables, type: \dt
8. To see everything, type: \d
9. To see the schema of one table, type: \d table_name_goes_here
10. Use the usual sql commands (SELECT * …, etc) to find your info. Keep this window open.

11. To use Python to work with your database, open a new terminal window and create a venv:

    $ cd my-project-directory
    $ python3 -m venv venv
    $ source venv/bin/activate
    (venv) $ pip3 install -r requirements.txt

12. Open IPython (type: ipython)
13. Load your app variables into scope with In [1]: %run app.py

14. If you skipped step 4, I think you can also create your tables here by typing: db.create_all()?

    NOTE: Step #4 assumes someone has given you a database dump (we often do this so you can get some sample data from us. If you have a database dump, you won't have any effect by using db.create_all(), since you already have the tables. However, if you aren't given a database dump, db.create_all() will have SQLAlchemy make the tables for you, using your model class as a guide.

15. You can check the connection by adding a record to one of your tables: for example, in the sqla-associations-demo file, add a new department to the departments table:

    In [2]: dept = Department(dept_code=“hr”, dept_name=“Human Resources”, phone=“867-5309”)

    check that dept exists:

    In [3]: dept
    Out [3]: <Department hr Human Resources>

    check for some column info:

    In [4]: dept.dept_code
    Out [4]: ‘hr’

    Add this new record to the session:

    In [5]: db.session.add(dept)

    Commit:

    In [6]: db.session.commit()

16. Back in your psql terminal window, verify that it’s there: SELECT * FROM departments; --- and the new hr department record should appear in the table

17. To start your Flask server: open a new terminal window (3 total open windows now - psql, ipython, flask server), navigate to the directory that holds your project, start your venv, and run flask:

    $ source venv/bin/activate
    (venv) flask run

    Open a browser window, go to localhost:5000/some_route_in_your_app.py_file and you should see your website connected to the database

(thanks to Sarah Stockton of r22 for putting this together)