Learn how to connect a Flask Python app with a Postgres database using the
You should first complete this lab in your individual repository to complete your submission for the individual back-end deliverable. You’ll then want to coordinate with your team since you’ll need to apply what you learn to your team project.
You should create most of these files locally and push them to your individual repository before then connecting to perlman and testing them out. Remember that if you’re on perlman and want to push, you’ll probably need to use a GitHub Personal Access Token to do so. It’ll generally be easier to just create files locally and not need to push from perlman.
The module we’ll be using will allow for connection between your database and your Flask app. However, for it to make that connection, it needs to know the relevant information about your database, including your database password. In general, you should never push passwords to a git repository, so we’re going to first ensure that you don’t accidentally push this new file with that information to the remote repository.
- Create a file
.gitignore(or open the existing one if there is already one in your repo)
- Add the line
psqlConfig.py(this tells git to not add this file even when you do
git add *)
- You won’t make the
psqlConfig.pyyet, since you’ll need to just make it directly on perlman. Add your changes to
.gitignoreand commit and push to your individual repository.
You should create a file named
datasource.py in your individual deliverables repository.
This file will be responsible for connecting your database to your Flask app.
- In that file, you should first import the module we’ll be using and the configuration file that you will make on perlman:
import psycopg2 import psqlConfig as config
It’s best to structure this file in an object-oriented style, so you should first make a class called
class DataSource: def __init__(self): pass def connect(self): pass
- A common thing that you will need to do is connect to your database, hence why there is a
psycopg2module makes that pretty easy, add the following to the
try: connection = psycopg2.connect(database=config.database, user=config.user, password=config.password, host="localhost") except Exception as e: print("Connection error: ", e) exit() return connection
Your constructor should probably call the
connectmethod and save the resulting connection object as an instance variable; write that code now.
- Finally, you’ll need to write a method to execute one of your queries on your database and print the resulting information. To execute a query on the database, you need to use the
cursor()method of the connection. Here is an example adapted from the psycopg2 documentation
def getExample(self): #Open a cursor to perform database operations cursor = self.connection.cursor() #Execute a query cursor.execute("SELECT * FROM my_data") #Retrieve query results records = cursor.fetchall() print(records)
- You might want to be able to use arguments in your queries, which you can do with a particular syntax (more details are in the psycopg2 documentation). Here is an example method for your reference (that uses the earthquake data):
def getQuakesAboveMagnitude(self, magnitude): try: #set up a cursor cursor = self.connection.cursor() #make the query using %s as a placeholder for the variable query = "SELECT * FROM earthquakes WHERE mag>%s ORDER BY mag DESC" #executing the query and saying that the magnitude variable # should be placed where %s was, the trailing comma is important! cursor.execute(query, (magnitude,)) print(cursor.fetchall()) except Exception as e: print ("Something went wrong when executing the query: ", e) return None
- For testing purposes, create a
DataSourceobject and call one or more of your methods at the bottom of your
if __name__ == '__main__': my_source = DataSource() #call your method
- Add your
datasource.pyfile, commit, and push.
Now you’re ready to test out your code with your database.
Connect to perlman and pull your individual repository to get the changes that you made. Make sure that your
.gitignorefile is there so that you don’t accidentally push your database information.
- Add the following three lines with your individual database information:
database = 'YOUR_DATABASE_NAME' user = 'YOUR_CARLETON_USERNAME' password = 'YOUR_DATABASE_PASSWORD'
That should be your Carleton username for the first two and the password that I provided to you for the last one.
Try running your
datasource.pyand see if it is able to connect to your database!
- If that works, great job, you’re done with the individual deliverable! Back on your local setup, add the correct tag and push it.
Once you finish with the individual deliverable, start working on the changes to your team project that will be needed to connect your Flask app to your new database.