Modular SQL Using SQLAlchemy

04:00 PM - 04:25 PM on July 16, 2016, Room CR7

Bo Du

Audience level:

novice

Description

SQLAlchemy is a powerful library which provides a number of powerful abstractions and patterns for SQL. One of these patterns is the ability to build "composable" queries.

This talk will cover:

• When to use SQLAlchemy to modularize your SQL into composable parts
• How to reason about the deconstruction of a query
• Step by step example of how to write modular SQL using SQLAlchemy

Abstract

Defined Problem: SQL is difficult to composable. Many applications have conditional logic describing the data they need to fetch from the database. Piecing together snippets of SQL to satisfy the conditional logic of your application is not optimal.

Solution: With SQLAlchemy, you can write python code that will transpile into the necessary SQL query. SQLAlchemy allows you to modularize your SQL and DRY up repetitive lines of raw SQL scattered throughout your code base. Just rinse and reuse.

When is it Useful: Any ORM can be used to perform simple CRUD operations. SQLAlchemy is built around two layers, one mirroring SQL constructs and one being the ORM. They SQL layer provides both standard and database specific SQL functions, data types and and operators. This is especially useful when writing advanced queries based on 100 different input parameters.