Working with SQL databases in Flask

Activate your virtualenv in the folder where you’ve been doing Flask projects.

Install the Flask-SQLAlchemy extension like this (use uppercase as shown):

$ pip3 install Flask-SQLAlchemy

That installs SQLAlchemy as well as Flask-SQLAlchemy. No need for a separate install.

Using a database with Flask

You’ll have to connect your app to an existing SQL database, whether the app reads from the database, writes to the database, or both. Connecting will require your own database username and database password. (You are the owner of the database.)

You can create the SQL database using Python, but that is not required. You might already have a database. Then all you need to worry about is how to connect it.

If you do use Python to create a SQL database (and that’s an “if,” not a necessity), you will only do it once. You don’t create the same database again and again.

Your database may have one table, or more than one table. That depends on what you need, or the structure of the existing SQL database.

Your app might only read from your SQL database. You can write SQL queries to accomplish this — use Flask-SQLAlchemy commands to do so.

Your app might allow people to write to your SQL database. In that case, it is possible you will want people to log in securely.

You might write a Python script to populate your database from the contents of a CSV file. This would be fairly simple if you only need to run it once. If you need to add records repeatedly (say, once per month) to an existing database, you might need to check whether you are duplicating records that are already there. If you need to check for existing records and update them, that’s more challenging.

If people are writing into your database, you will want to give them a form, or forms, for doing so. Remember that Bootstrap and Flask-WTF are your very best friends for that!

Make a checklist for your database project

Figure out what your own app will need to do:

  1. Create a database (optional; only once)
  2. Connect to the database (required)
  3. Read from the database: Query
    • Display all records?
    • Display a subset of records?
    • Search for multiple records?
    • Search for one record at a time?
  4. Write to the database
  5. Allow users to write to the database
    • Insert a new record?
    • Update an existing record?
    • Delete a record?

For all Python and SQL commands, refer to the links listed under “User’s Guide” in the Flask-SQLAlchemy documentation.

Advertisements

3 thoughts on “Working with SQL databases in Flask

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s