Database Interactions

Database Interactions

This doc was written before Docker was implemented in. Check out the Docker doc before looking through this. We use Flask-SQLAlchemy as an Object Relation Mapping(ORM) to Postgres. Look into the Hack4Impact UIUC wiki for more database basics.

Caveats

• If you are using Docker, always remember to be in the correct container before running these commands
• If you are in the regular setup, always remember to be in your virtual environment

SQLAlchemy Examples

Here are some ways to use SQLAlchemy to query, add, and delete from your database.
Note: this example is specific to what is initially described in models.py

– A Table called Person with an attribute called "name"(String) and one-to-many relationship to the table Emails. Another Table called Email has an attribute called "email"(String) and a Foreign Key to Person.

# import Person and db
>>> from api import create_app
>>> from api.models import db, Person, Email

# create the app and push the app context for db to use in
# for more info on app context, look here http://flask-sqlalchemy.pocoo.org/2.3/contexts/
>>> app = create_app()
>>> app.app_context().push()

# add a Person with name "Tim" and another with name "Tim2"– yes, I'm that narcissistic haha
>>> person1 = Person(name="Tim")
>>> person2 = Person(name="Tim2")
>>> db.session.add(person1)
>>> db.session.add(person2)
>>> db.session.commit()

# get Person with id 1
>>> person1 = Person.query.get(1)
>>> person1.name # person1 is a Python object so you can access its attributes like python class variables!
Tim

# get all Persons
>>> Person.query.all()
[<Person Tim>,<Person Tim2>] # this depends on how __repr__() is defined for Person, but it will be a list of Person

# filter based on certain attributes
>>> Person.query.filter(Person.name=="Tim") # returns list of Person that has a name "Tim"
[<Person Tim>]

# deleting a Person
>>> p = Person.query.get(1) # get the object you want to delete
>>> db.session.delete(obj   # delete it
>>> db.session.commit()        
>>> Person.query.all()
[<Person Tim2>]
# If you had any Foreign Keys linked to Person, you must set make sure to define whether you want
# it to cascade or SET NULL when you define your model

# Relationships
# In our current schema, we see that a Person has an attribute "emails".
# This is represented by a python list, which would be initially empty
>>> email1 = Email(email="tim@gmail.com") # create an Email
>>> email2 = Email(email="tim.ko@gmail.com")
>>> p = Person.query.get(2) # query the person who has the email
>>> p.emails
[]
>>> p.emails.append(email1) # add the email to that person 
# Note: You must add an Email object
>>> p.emails.append(email2)
>>> p.emails
[<email tim@gmail.com>,<email tim.ko@gmail.com>]

For the SQLAlchemy documentation, look here

Adding Dummy data

Eventually, you would want to make POST requests to certain endpoints that would add entries to the database. You can add dummy data through the python CLI. Make sure you're in the right virtualenv.

Note: For Docker, go into the app container with docker-compose exec app bash beforehand.

$ python

You will be at the head directory. Import the Objects you need from models.py and your database

>>> from api import create_app
>>> from api.models import db, Person, Emai
>>> app = create_app()
>>> app.app_context().push()

Then, make a new Person Object and add it to the database.

>>> p = Person(name="Tim")
>>> db.session.add(p)

Once you add it, you need save(commit) the change

>>> db.session.commit()

You can also write scripts to do this. Or you can also write scripts in the frontend, where you will make POST requests to your "adding resources" endpoint

Database Schema Changes

The Database Schema is described in models.py. For any changes you make, you MUST let everyone know about it. First, create migration files for your changes:

Note: For Docker, be sure to be inside the app container with docker-compose exec app bash beforehand.
For regular setup, make sure you're in the right virtualenv.

$ python manage.py db migrate 

This will be reflected in /migrations. Then, upgrade the database and let everyone know to do to.

$ python manage.py db upgrade

Everyone will have to follow this same process whenever someone pushes new changes to models.py. Migration files will not be pushed into the main repo due to versioning complaints.