MySQL

Database configuration, building queries, and cloud hosting options for MySQL.
SQL
14 Mar 2019

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.
Welcome to SQL 4: Aggregate Functions

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

Continue Reading
SQL
25 Feb 2019

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.
Welcome to SQL 3: Building Relations and Combining Data Sets

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

Continue Reading
SQL
21 Feb 2019

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

Explore the many flavors of SQL data manipulation in part 2 of our series.
Welcome to SQL 2: Selecting, Updating, and Deleting Data

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
Continue Reading
SQL
19 Feb 2019

Welcome to SQL: Modifying Databases and Tables

Brush up on SQL fundamentals such as creating tables, schemas, and views.
Welcome to SQL: Modifying Databases and Tables

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

Continue Reading
AWS
29 Oct 2018

Create a REST API Endpoint Using AWS Lambda

Use Python and MySQL to build a serverless endpoint.
Create a REST API Endpoint Using AWS Lambda

Now that you know your way around API Gateway, you have the power to create vast collections of endpoints. If only we could get those endpoints to actually receive and return some stuff.

We'll create a GET function which will solve the common task of retrieving data from a database. The sequence will look something like:

  • Connect to the database
  • Execute the relevant SQL query
  • Map values returned by the query to a key/value dictionary
  • Return a response body containing the prepared response

To get started, create a project on your local machine (this is necessary as we'll need

Continue Reading
MySQL
23 Oct 2018

MySQL, Google Cloud, and a REST API that Generates Itself

Deploy a MySQL database that auto-creates endpoints for itself.
MySQL, Google Cloud, and a REST API that Generates Itself

It wasn’t too long ago that I haphazardly forced us down a journey of exploring Google Cloud’s cloud SQL service. The focus of this exploration was Google’s accompanying REST API for all of its cloud SQL instances. That API turned out to be a relatively disappointing administrative API which did little to extend the features you’d expect from the CLI or console.

You see, I’ve had a dream stuck in my head for a while now. Like most of my utopian dreams, this dream is related to data, or more specifically simplifying the manner in

Continue Reading
Roundup
21 Jun 2018

Lynx Roundup, June 21st

Daily roundup of Data Science news around the industry, 6/21/2018.
Lynx Roundup, June 21st Continue Reading
Python
15 Jun 2018

Using PyMySQL: Python's MySQL Library

The lightweight Python library for interacting with MySQL.
Using PyMySQL: Python's MySQL Library

It's almost Friday night, and the squad at H+S is ready to get cooking. Dim down the lights and slip into something more comfortable as we take you on this 100% organic flavor extravaganza. Tonight's menu? A Python MySQL library: PyMySQL.

PyMySQL is lightweight and perfect for fulfilling MySQL queries. If you want bells and whistles, you're probably barking up the wrong tree (and you probably should’ve used a DB other than MySQL in the first place).

Why write tutorials for technologies we openly trash talk? Out of necessity, of course! There's nothing wrong with MySQL, most enterprises

Continue Reading
MySQL
30 Apr 2018

MySQL on the Cloud with AWS RDS

Spinning up a standalone MySQL Database with Amazon.
MySQL on the Cloud with AWS RDS

Last time we became familiar with the handiwork of setting up MySQL locally, navigating databases via command line, and exposing your database to external access. While badass, it has come to my attention that most people don't bother doing things this way. Unless you're getting deep into some heavy architecture, most people opt to use cloud services such as AWS to set up databases which are intended to be interacted with by multiple services.

A perfect example is one we ran into over the weekend while working on this very blog. We're running a Ghost instance, which is respectably complex

Continue Reading
MySQL
22 Apr 2018

Accessing Self-Hosted MySQL Externally

Connecting to MySQL instances hosted on a VPS.
Accessing Self-Hosted MySQL  Externally

In the previous post, we got familiar with the basics of creating and navigating MySQL databases. This leads us to the next most logical thing to ask: how can I use this in any meaningful way?

MySQL installations default to refusing connections outside of the local machine's IP address, as we should expect. That said, relational databases aren't usually being used by a single person on a single machine forever (but if you do, we should hang out). It goes without saying that our MySQL instance should be focusing on uptime and accessibility, or in other terms, far away from

Continue Reading
MySQL
17 Apr 2018

Setting up a MySQL Database on Ubuntu

Setting up MySQL the old-fashioned way: on a Linux server.
Setting up a MySQL Database on Ubuntu

As frameworks and services evolve to remove us further away from boilerplate code, the first casualty of saved time is the fundamental understanding of what we're actually doing sometimes. This has good reason; one can only learn so much from repetitive command-line interactions with databases, thus making any service's one-click-deploy button all the more sensible.  If I  had to imagine the least sexy title for a post in software development, it would be something along the lines of How to Configure MySQL on a VPS, as opposed to like, a cloud-based solution, or Even a Docker Container, as Though we

Continue Reading