Python Web Apps:
Databases & ORMs

Meet Pamela

Python Cloud Advocate at Microsoft

Formerly: UC Berkeley, Coursera, Khan Academy, Google

Find Pamela online at:

Today's topics

• Databases
  • 👩🏾‍💻 Exercise: PostgreSQL Playground
• Using databases from Python
  • 👩🏼‍💻 Exercise: PostgreSQL Playground Part 2
• Using Flask with databases
  • 👩🏻‍💻 Exercise: Example Flask + DB app
• Deploying web apps and databases
  • 🧑🏿‍💻 Exercise: Deploying PostgreSQL
  • 👨🏻‍💻 Exercise: Deploying Flask+PG app

Exercise tools

To follow along with the exercises, you can do either:

1. Online development with Codespaces:
   • Github account
2. Local development with VS Code:
   • Visual Studio Code
   • Dev Containers extension
   • Docker Desktop
3. Local development with any editor:
   • Code editor
   • Python 3
   • PostgreSQL

Why databases?

Web apps use databases to store data that needs to be shared across multiple users or computers.

Database types

Non-relational databases

A non-relational database stores data in a very flexible way, often as JSON documents or as key-value stores.

Example document in a MongoDB collection:

                {
                    "username": "Pamela Fox",
                    "email": "pamelafox@microsoft.com"
                }
                

Example key-value pair in a Redis store:

                usersession1234: { "username": "Pamela Fox", "email": "pamelafox@microsoft.com" }
                

Relational databases

A relational database contains tables.
Each table has columns and rows.

Example table called speakers:

SQL

SQL (Structured Query Language) is a language for querying and modifying relational databases.

SQL commands:

• SELECT - get data
• INSERT - add data
• UPDATE - modify data
• DELETE - remove data

Learn SQL for free with my course on Khan Academy: khanacademy.org/computing/computer-programming/sql

PostgreSQL

PostgreSQL is a popular open-source relational database that supports JSON, XML, and other data types.

                CREATE TABLE cities (
                    name            varchar(80),
                    location        point
                );
                INSERT INTO cities VALUES ('San Francisco', '(-194.0, 53.0)');
                SELECT name FROM cities WHERE location <@ circle '((0,0), 300)';
                

There are many popular extensions for PostgreSQL like PostGIS for geospatial data and pgvector for vector similarity search.

Python libraries for PostgreSQL: psycopg (driver), SQLAlchemy (ORM)

Playgrounds: PostgreSQL playground, pgvector playground

Exercise: Play with PostgreSQL

Using this repo:
github.com/pamelafox/postgresql-playground
aka.ms/postgres-playground

1. Open in GitHub Codespaces or VS Code with Dev Containers extension
2. Copy .env.devcontainer into a .env file
3. Open the SQLTools extension from the sidebar and select 'Local database'
4. Enter these SQL statements and select 'Run on active connection':

   
                           CREATE TABLE restaurants (id SERIAL PRIMARY KEY, name VARCHAR(255) NOT NULL);
                           INSERT INTO restaurants (name) VALUES ('Casa Latina');
                           SELECT * FROM restaurants;
                       

5. Explore the results in the extension

Accessing databases from Python: SQL

One option is to call SQL directly from a database driver.

                import psycopg2

                conn = psycopg2.connect(
                    host=os.environ["PGHOST"],
                    database=os.environ["PGDATABASE"],
                    user=os.environ["PGUSSER"],
                    password=os.environ["PGPASS"],
                )
                cur = conn.cursor()
                cur.execute("CREATE TABLE restaurants (id SERIAL PRIMARY KEY, name VARCHAR(255) NOT NULL)")
                conn.commit()
                cur.close()
                

Risks of using SQL directly

⚠️ Executing raw SQL makes your app vulnerable to SQL injection attacks.

What you expect:

                user_input = "Bobby"
                query = "SELECT name, id FROM users where name = " + user_input
                

                user_input = "Bobby); DROP TABLE students;"
                query = "SELECT name, title id users where name = " + user_input
                

Accessing databases from Python: ORM

A safer approach is to use an ORM (Object-Relational Mapper) to interact with the database.

An ORM represents table rows as Python objects, and provides methods for querying and modifying data.

A SQLAlchemy example:

                class Restaurant(Base):
                    __tablename__ = "restaurants"
                    id: Mapped[int] = mapped_column(primary_key=True)
                    name: Mapped[str]
                    rating: Mapped[int]
                
                query = select(Restaurant).where(Restaurant.title == "Casa Latina")
                results = session.execute(query)
                

Querying with SQLAlchemy 2.0

Get by primary key:

                session.get(Restaurant, 42)
                

Get all rows in table:

                session.execute(select(Restaurant)).scalars().all()
                

Finding rows by column value:

                session.execute(select(Restaurant).where(
                    Restaurant.name == "Casa Latina")).scalars().all()
                

Play with queries in PostgreSQL Playground
aka.ms/postgres-playground

Using Flask with SQLAlchemy

Flask is a lightweight web framework for Python, which does not come with any built-in database functionality.

A common way to use Flask with SQLAlchemy:

• Install Flask-SQLAlchemy extension
• Install Flask-Migrate extension
• Define models using Python classes
• Run database migrations
• Use SQLAlchemy queries in routes

Example: Flask + PostgreSQL

github.com/Azure-Samples/azure-flask-postgres-flexible-appservice
aka.ms/flask-pg-app

• App setup is in __init__.py.
• Routes are in pages.py.
• Models are in models.py and use SQLAlchemy 2.0.

Run app server:

                python3 -m flask --app src.flaskapp run --debug --reload --port=8000
                

Database migrations

Migrations are a way to keep track of changes to the database schema.

1. When you make a change to the database schema, create a migration file.

   Using Flask-Migrate:

   
                   python3 -m flask --app src.flaskapp db migrate --directory src/flaskapp/migrations
                   

2. Run the migration file to update the database.

   Using Flask-Migrate:

   
                   python3 -m flask --app src.flaskapp db upgrade --directory src/flaskapp/migrations
                   

Exercise: Flask app

Using this repo:
github.com/Azure-Samples/azure-flask-postgres-flexible-appservice
aka.ms/flask-pg-app

1. Follow the readme steps to get the app running.
2. Inspect the local DB tables using SQLTools extension.
3. Submit an info request and find new row in SQLTools.
4. Change the destinations route to return destinations ordered by name, using order_by.
5. Reload and confirm the destinations appear sorted.

More Flask + DB examples

• github.com/pamelafox/flask-db-quiz-example
• github.com/pamelafox/translation-telephone
• github.com/pamelafox/flask-surveys-container-app

Also check out the Flask tutorial.

Vector search

Vector search is a way to find similar items in a database, and is used in recommendation systems, search engines, and chatbots.

SQL:

                SELECT * FROM items ORDER BY embedding <=> '[3,1,2]' LIMIT 5;
                

SQLAlchemy:

                  closest = session.scalars(
                    select(Item).order_by(Item.embedding.cosine_distance(target_embedding)).limit(5)
                  )
                  for item in closest:
                      print(item.title)
                

Try it in: github.com/pamelafox/pgvector-playground

RAG: Retrieval Augmented Generation

RAG with PostgreSQL

We can use LLMs to answer questions about PostgreSQL tables.

Example: Azure-Samples/rag-postgres-openai-python
aka.ms/rag-postgres

That example uses FastAPI, but it could also use Flask/Quart.

More resources: GenAI + PostgreSQL

Recorded talks:

• Building a RAG-powered AI chat app with Python
  by Pamela Fox

Upcoming Posette talks:

• pgvector for Python developers
  by Pamela Fox
• Semantic search with Django, PostgreSQL, & pgvector
  by Paolo Melchiorre

Database hosting options

Consider:

• How much database storage do you need?
• How many database read/writes do you expect?
• How much latency can you tolerate between app/DB?
• What sort of backup policy do you need?
• Data sovereignty: where can your data be stored?
• Do you need multiple replicas for high availability?

Managed services for PostgreSQL on Azure

Cosmos DB for PostgreSQL vs. Flex Server 🔗 aka.ms/flex-vs-cosmos

Exercise: Deploy a PostgreSQL Flexible Server

Using this repo:
github.com/Azure-Samples/azure-postgres-pgvector-python
aka.ms/azure-postgres-pgvector

1. Open in GitHub Codespaces or VS Code with Dev Containers extension
2. Sign up for a free Azure account
3. Follow the Deploying instructions in the README
4. Run a script to confirm the database is working

Web app hosting options

The web app can be hosted on the same server as the database, or on a separate server.

Many possible hosts:

• A rented computer in a data center
• A virtual machine
• A PaaS (platform as a service)

Consider:

• How much control do you want?
• How much do you enjoy administering systems?
• Do you need your app to scale up/out?

Azure web app hosting options

For Flask, App Service is an easy way to get started.

But wait, there's more!

Your app may need more than just a web server and a database.

Hosting Python + PostgreSQL on Azure

Using the Azure Dev CLI:

                azd up
                

Exercise: Deploy Flask + PG

Using this repo:
github.com/pamelafox/flask-db-quiz-example
aka.ms/flask-db-quiz

1. Open in GitHub Codespaces or VS Code with Dev Containers extension
2. Sign up for a free Azure account
3. Follow the Deploying instructions in the README
4. If it deploys successfully, visit the endpoint URL to confirm it's working.
5. Run azd down to un-deploy the app (so that you don't waste cloud resources unnecessarily).

Security considerations

When connecting to a database from a web app, consider...

Restricting authentication:

• ⭐️ Use passwordless authentication for the database
  See aka.ms/azure-postgres-pgvector and aka.ms/flask-db-quiz
• If you do have to use passwords, store in Azure KeyVault
  See aka.ms/flask-pg-app

More Azure resources

• Deploy a Python (Django or Flask) web app to Azure App Service
• Deploy a Python (Django or Flask) web app with PostgreSQL in Azure
• Hosting Python apps on Azure
• Getting Started with Python in VS Code
• MS Python Discord: aka.ms/python-discord