Querying a Business PostGreSQL Database with SQLAlchemy Core

Tech Stack

• Python 3,

• PostGreSQL, SQLAlchemy (Core),

• VsCode, Jupyter, GitHub

Project

• Execution of basic and complex SQL queries, demonstrating practical professional usage.

Benefits

Experiencing PostGreSQL with a real DVD rental shop database.

Goals

Enhancing my SQL and SQLAlchemy skills.

I spent 13 hours divided this way and in this order:

• 10 hrs:

  • setting up the database (from a .tar backup file)

    • and a PostGreSQL connection to it

    • with a new user and relevant rights

  • exploring and analysing the data via basic to complex queries

• 3 hrs:

  • reviewing code, reviewing the notebook outline, cleaning up

  • prepping the folder for GitHub (.gitignore, .env, path, requirements.txt) & publishing it

What is SQLAlchemy and Its Versatility

SQLAlchemy is a powerful Python toolkit for working with databases. It’s renowned for its versatility, allowing developers to interact with a wide range of database management systems like:

• PostgreSQL,

• MySQL,

• SQLite,

• and more.

Whether you prefer writing object-oriented queries using its Object Relational Mapper (ORM) or prefer a declarative SQL approach with SQLAlchemy Core, this tool adapts seamlessly to your workflow.

During my practice with the SQLAlchemy Core approach, I worked on a PostgreSQL DVD rental business database containing over 16,000 entries. This experience gave me insights into the tool's capabilities and its applications in real-world data analysis tasks.

You can find the full notebook and more queries in my GitHub repository.
For another hands-on practice project example, read my previous SQLAlchemy Core article where I create and query an SQLite3 simple library database.

Real-Life Business Scenario: Extracting Insights from Data

Working with this database mirrored the challenges faced by data analysts in real business situations. The primary goal was to extract actionable insights and identify patterns hidden within the data. Here are a couple of queries I tackled:

1. Finding Weekend Customers with High Spending: To identify customers who rented DVDs over the weekend and spent over $100 in total:

2. Finding Each Customer’s First Rental Date: To get the earliest rental date for each customer:

These types of queries are fundamental for business intelligence tasks such as customer segmentation, trend analysis, and calculating Customer Lifetime Value (CLV).

Why SQLAlchemy Core Seems Verbose but is Better?

At first glance, SQLAlchemy Core queries might appear more verbose than raw PostgreSQL. For example, a simple raw SQL query like:

translates into a longer SQLAlchemy Core equivalent:

However, this verbosity has a purpose. SQLAlchemy Core provides:

• Readability: The Pythonic syntax makes queries easier to understand for developers already familiar with Python.

• Database Portability: Since Core abstracts the underlying SQL, the same code can work on different databases with minimal changes.

• Enhanced Query Composition: Core’s declarative syntax makes it easier to write complex queries programmatically, such as dynamically adding filters or joins.

Why SQLAlchemy Core Is Better Than Raw SQL?

1. Database Agnosticism: SQLAlchemy’s Core approach allows you to switch between databases without rewriting queries. This is invaluable for projects where database management systems may change over time.

2. Code Maintainability: With Core, you can dynamically build queries in a way that is difficult to achieve with raw SQL. For instance, adding filters based on user input becomes straightforward.

3. Security: SQLAlchemy helps prevent SQL injection by automatically parameterizing queries, ensuring that user inputs are safely handled.

When to Use SQLAlchemy vs. Raw PostgreSQL?

Use SQLAlchemy If:

• You need database-agnostic code.

• You want to integrate queries into Python scripts.

• Security and scalability are priorities.

Use Raw PostgreSQL If:

• You are working exclusively with PostgreSQL.

• You’re writing ad-hoc queries for reporting.

• Performance is critical and raw SQL is faster.

SQLAlchemy also provides the text() function for executing raw SQL directly within Python. This hybrid approach is useful when a query is too complex to express in Core’s syntax. However, raw SQL should be parameterized to avoid SQL injection vulnerabilities:

Conclusion

Practicing SQL with SQLAlchemy Core on a PostgreSQL database was a practical, business-oriented experience. I learned how databases hold critical business insights, from understanding customer behavior to calculating CLV. While SQLAlchemy Core can seem verbose compared to raw SQL, its flexibility, portability, and security make it a valuable tool for modern data analysis workflows.

You can find the full notebook and more queries in my GitHub repository. Let’s continue exploring how SQL and Python together can unlock powerful data-driven solutions.

For another hands-on practice project example, read my previous SQLAlchemy Core article where I create and query an SQLite3 simple library database.