SQL

Configure relational databases, brush up on your query language syntax, or find third-party services to interact with your data.
Constructing Database Queries with SQLAlchemy

Constructing Database Queries with SQLAlchemy

Query your data models using SQLAlchemy's query API.

So far in our SQLAlchemy journey, we've covered managing database connections and model creation. So... how do we actually extract the data we want from our database?

SQLAlchemy's ORM query API simplifies the way we write database queries. Instead of writing raw SQL queries, we can construct queries on our SQLAlchemy session by chaining together methods to retrieve data. We're going to dive into SQLAlchemy's extensive query API to get an idea of all the ways we can query our data.

This tutorial will assume you know how to create an SQLAlchemy session. We'll also assume you have some data

Managing Relationships in SQLAlchemy Data Models

Managing Relationships in SQLAlchemy Data Models

Using the SQLAlchemy ORM to build data models with meaningful relationships.

There are plenty of good reasons to use SQLAlchemy, from managing database connections to easy integrations with libraries such as Pandas. If you're in the app-building business, I'd be willing to bet that managing your app's data via an ORM is at the top of your list of use cases for SQLAlchemy.

Most software engineers likely find database model management to be easier than SQL queries. For people with heavy data backgrounds (like us), the added abstractions can be a bit off-putting: why do we need foreign keys to execute JOINs between two tables? Why do we need to distinguish

Welcome to SQL 4: Aggregate Functions

Welcome to SQL 4: Aggregate Functions

Become more intimate with your data- use SQL's aggregate functions to explore the traits which make your data unique and beautiful.

Aggregate functions in SQL are super dope. When combining these functions with clauses such as GROUP BY and HAVING, we discover ways to view our data from completely new perspectives. Instead of looking at the same old endless flat table, we can use these functions to give us entirely new insights; aggregate functions help us to understand bigger-picture things. Those things might include finding outliers in datasets, or simply figuring out which employee with a family to feed should be terminated, based on some arbitrary metric such as sales numbers.

With the basics of JOINs under our belts, this

Welcome to SQL 3: Building Relations and Combining Data Sets

Welcome to SQL 3: Building Relations and Combining Data Sets

This week we look at the fun side of SQL where we JOIN tables and create UNIONs.

If you've felt a bit distance or estranged from SQL so far in the series, never fear: we're about to discover the magic of what makes relational databases so... relational. Turn down the lights and put on your favorite Marvin Gaye track; we're about to make connections on a whole other level.

I find that existing attempts to explain Database relations (JOINs in particular) have been an utter failure in illustrating these concepts. The Venn Diagrams we're all accustomed to seeing mean nothing to somebody who has never seen a JOIN occur, and even then, do they really describe what's

Welcome to SQL 2: Selecting, Updating, and Deleting Data

Welcome to SQL 2: Selecting, Updating, and Deleting Data

Explore the many flavors of SQL data manipulation in part 2 of our series.

Now that we've gotten the fundamentals of creating databases and tables out of the way, we can start getting into the meat and potatoes of SQL interactions: selecting, updating, and deleting data.

We'll start with the basic structure of these queries and then break into the powerful operations with enough detail to make you dangerous.

Selecting Data From a Table

As mentioned previously, SQL operations have a rather strict order of operations which clauses have to respect in order to make a valid query. We'll begin by dissecting a common SELECT statement:

SELECT
  column_name_1,
  column_name_2
FROM
Welcome to SQL: Modifying Databases and Tables

Welcome to SQL: Modifying Databases and Tables

Brush up on SQL fundamentals such as creating tables, schemas, and views.

SQL: we all pretend to be experts at it, and mostly get away with it thanks to StackOverflow. Paired with our vast experience of learning how to code in the 90s, our field work of PHPMyAdmin and LAMP stacks basically makes us experts. Go ahead and chalk up a win for your resume.

SQL has been around longer than our careers have, so why start a series on it now? Surely there’s sufficient enough documentation that we can Google the specifics whenever the time comes for us to write a query? That, my friends, is precisely the problem. Regardless

Manage Database Models with Flask-SQLAlchemy

Manage Database Models with Flask-SQLAlchemy

Connect your Flask app to a database using Flask-SQLAlchemy.

By now you're surely familiar with the benefits of Python's SQLAlchemy library: the all-in-one solution for basically anything database related. Like most major Python libraries, SQLAlchemy has been ported into a version specifically compatible with Flask, aptly named Flask-SQLAlchemy.

Similar to the core SQLAlchemy package, Flask-SQLAlchemy provides an ORM for us to modify application data by easily creating defined models. Regardless of what your database of choice might be, Flask-SQLAlchemy will ensure that the models we create in Python will translate to the syntax of our chosen database. Given the ease-of-use and one-size-fits-all  nature of Flask-SQLAlchemy, it's no wonder that

From CSVs to Tables: Infer Data Types From Raw Spreadsheets

From CSVs to Tables: Infer Data Types From Raw Spreadsheets

The quest to never explicitly set a table schema ever again.

Back in August of last year (roughly 8 months ago), I hunched over my desk at 4 am desperate to fire off a post before boarding a flight the next morning. The article was titled Creating Database Schemas: a Job for Robots, or Perhaps Pandas. It was my intent at the time to solve a common annoyance: creating database tables out of raw data, without the obnoxious process of explicitly setting each column's datatype. I had a few leads that led me to believe I had the answer... boy was I wrong.

The task seems somewhat reasonable from the surface.

Psycopg2: PostgreSQL & Python the Old Fashioned Way

Psycopg2: PostgreSQL & Python the Old Fashioned Way

Manage PostgreSQL database interactions in Python with the Psycopg2 library.

Last time we met, we joyfully shared a little tirade about missing out on functionality provided to us by libraries such as SQLAlchemy, and the advantages of interacting with databases where ORMs are involved. I stand by that sentiment, but I’ll now directly contradict myself by sharing some tips on using vanilla Psycopg2 to interact with databases.

We never know when we’ll be stranded on a desert island without access to SQLAlchemy, but a lonesome Psycopg2 washes up onshore. Either that or perhaps you’re part of a development team stuck in a certain way of doing things

Pythonic Database Management with SQLAlchemy

Pythonic Database Management with SQLAlchemy

The iconic Python library for handling any conceivable database interaction.

Something we've taken for granted thus far on Hackers and Slackers is a library most data professionals have accepted as standard: SQLAlchemy.

In the past, we've covered database connection management and querying using libraries such as PyMySQL and Psycopg2, both of which do an excellent job of interacting with databases just as we'd expect them to. The nature of opening/closing DB connections and working with cursors hasn't changed much in the past few decades. While boilerplate is boring, at least it has remained consistent, one might figure. That may have been the case, but the philosophical boom of MVC

Cloud SQL: Relational Databases on Google Cloud

Cloud SQL: Relational Databases on Google Cloud

Create a database in Google Cloud using Cloud SQL, compare Cloud SQL to RDS, and learn the admin API.

I've found that the strength of Google Cloud's services comes from consistently delivering across a few key metrics, namely reliability and performance. When dealing with SQL databases, it's hard to imagine any metrics more important than these two things, and Google's Cloud SQL delivers.

To become familiar with Cloud SQL, we're going to walk through the creation of a SQL database and explore the advantages Google Cloud offers us when compared to other solutions. We'll also be dipping into the Cloud SQL API to see how we might manage our SQL database programmatically.

Cloud SQL Versus Alternatives

There aren't many

A Dirty Way of Cleaning Data (ft. Pandas & SQL)

A Dirty Way of Cleaning Data (ft. Pandas & SQL)

Cleaning data in Pandas the dirty way.

Warning: The following is FANTASTICALLY not-secure.  Do not put this in a script that's going to be running unsupervised.  This is for interactive sessions where you're prototyping the data cleaning methods that you're going to use, and/or just manually entering stuff.  Especially if there's any chance there could be something malicious hiding in the data to be uploaded.  We're going to be executing formatted strings of SQL unsanitized code.  Also, this will lead to LOTS of silent failures, which are arguably The Worst Thing - if guaranteed correctness is a requirement, leave this for the tinkering table.  Alternatively, if