SQLAlchemy Demo

The following repo is a demo for how to create a manage a database using sqlalchemy.

Before you get started

You'll need a database configured to your local machine. I'd suggest using MySQL or Postgres. Both are free and are easy to manage. For the purpose of this demo, it does not matter which database you choose to use.

Getting started

The following is written for unix machines...

1. Launch a new (and empty) database from your local machine:

• Here's a video tutorial how to create a database on your machine: https://youtu.be/LXKTQWoQAj8
• I recommend using Postgres.app

2. Download this repository to your local machine:

git clone https://github.com/ybressler/sqlalchemy-demo.git

3. Create a virtual environment and activate it. Then, install the requirements:

python3 -m venv venv
source venv/bin/activate
pip install -r requirements.txt

You're now ready to begin!

4. Store your DB_URI as an environment variables or update the value in the code.
   For example, following default values:

export DB_URI='postgresql://postgres:@localhost:5432/postgres'

For more info as to what's going on here, give this a read: FOO

5. Export the full path of your current project the environment variable PROJECT_PATH:

export PROJECT_PATH=$(pwd)

This will allow you to execute nested files as executables, without dealing with issues like relative imports. In a production setting, your modules will likely be imported properly and executed from a central orchestrator (such as a web app).

Performing migrations

Your database is currently a blank slate. Let's make it reflect the models we've built:

Getting things ready for migrations

Note: I've taken the pleasure of making all the changes for you in this current repo.

1. Initialize alembic:

alembic init database/alembic

2. Edit the alembic.init file to your preferences

• On line 42, set comment out the following:

sqlalchemy.url = driver://user:pass@localhost/dbname

3. Edit alembic/env.py as follows:

• Import your db uri at the top of the module, add the following line of code to do so:

  from database import DB_URI
  

• After creating your config, set the db URI

# After the line where `config = context.config` appears...
# (should be about line 15)
config = context.config
config.set_main_option('sqlalchemy.url', DB_URI)

• That's it! You're done

Running the actual migration

1. Run your first migration, automatically detect changes:

alembic revision --autogenerate -m "first migration"

2. Review the autogenerated file in alembic/versions

• If it looks kosher, you good!
• If it doesn't look kosher, take the pleasure of fixing things manually. (Yes, you are supposed to finagle with these files.)

3. Once you're happy with the changes, run the upgrade command:

alembic upgrade head

4. Your database is now up to date!
5. Generate an ERD for the thrill of it!

python3 database/methods/generate_erd.py

---

Adding data to your new db

We'll add a few records with the following module: populate_db.py

Do a cool query

Querying your database can be done through a python interface. Pretty neat! Check out some of the querying behavior in the following module: some_query.py

---

Generate an ERD

Create a cool picture representation of your db: generate_erd.py