SQL

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

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

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

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

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

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

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

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.

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

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

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