Getting Started with SQLite and Sequel Pro

This is a quick guide to the interface for the 2 graphical user interfaces recommended for our initial foray into SQL databases. They are by no means the only ones, I recommend them because they work well and are free.

SQL, SQLite, and MySQL

First, some terminology: SQL stands for Structured Query Language, which is a type of programming language used in working with databases. When you write SQL to do data work, you are programming.

SQLite and MySQL are two different database systems that use two different variants of SQL. For the scope of our work, they will basically be interchangeable because they are both very similar. SQLite and MySQL refer to the database software itself, not the graphical user interfaces that we'll be using to work with the databases (e.g. Sequel Pro for MySQL, and SQLite Manager for SQLite)

A quick technical aside: Why am I recommending either SQLite or MySQL? Because both have their tradeoffs in terms of installing it on your computer. Getting SQLite on your computer can be as easy as installing the Firefox Browser, which comes packaged with SQLite. But the graphical user interface for SQLite is not as nice as some of the GUIs available for MySQL. However, with MySQL, you typically have to install MySQL yourself, which is typically not as easy as installing Firefox.

Choosing a GUI

The graphical user interfaces (i.e. GUIs) mentioned here make it so that working with databases have some of the conveniences of working with spreadsheets.

To sum up the descriptions below: If you're on Mac OS X, try to use Sequel Pro, which sits atop the MySQL software. Windows and Linux have their own MySQL clients, including HeidiSQL. But you may have problems installing MySQL itself.

If all else fails, install the Firefox browser, then the SQLite Manager plugin. The sacrifice you make is having a more spartan of an interface.

More details below:

Sequel Pro

Download Sequel Pro here.

If you have a Mac, then the Sequel Pro client is hands-down the best client to use when interfacing with a MySQL database. It has the nice look-and-feel of a modern, native OS X app because it is one. The main drawback of course is that Sequel Pro is only available for OS X.

Another drawback for novice users is that Sequel Pro uses MySQL, which means you'll have to first install the MySQL software yourself…which can be easy or very hard, depending on your situation.

See the official Installing MySQL on Mac OS X instructions here. However, you may have to Google around for independently-written guides on it.

Jump to Getting Started with Sequel Pro

SQLite Manager for Firefox

Download the Firefox Browser here. Then add the SQLite Manager plugin here.

The upside of SQLite Manager is that it runs on anything that the Firefox Browser runs on, which is most modern systems. And you don't have to install the SQLite software yourself, because Firefox comes with it. The downside is that the SQLite Manager, while an excellent piece of free software, isn't as user-friendly as Sequel Pro.

Jump to Getting Started with SQLite Manager

Alternative GUIs

Here are a few alternative packages, some of which cost money:

MySQL GUIs

• HeidiSQL - a popular and free GUI for MySQL (and a few other SQL variants). Developed for Windows but can be made to work with OS X.
• MySQL Workbench - a GUI maintained by MySQL's developers. Free and cross-platform
• Navicat - a cross-platform commercial GUI. It's functional, and common in the workplace, but it costs money.

SQLite GUIs

• SQLite Browser - a popular and free GUI for SQLite for Windows and OS X.
• Base 2 - A commercial GUI available only for Mac OS X.
• Navicat - Just like they have commercial cross-platform GUIs for MySQL.

Getting started with Sequel Pro

These instructions are for the Sequel Pro client for OS X. Go here for SQLite Manager instructions

First, you must install MySQL – this might be a very difficult situation depending on your setup. If that's the case, you might want to settle for SQLite Manager. I'm not going to give much guidance on this step except to point to the official instructions. Good luck!

After MySQL is installed, download Sequel Pro.

Importing SQL files

Sequel Pro can import CSV files, but to make things easy, we'll just import SQL files, which contain all the code to build a database with pre-inserted data.

You can download either one of these files:

• SFPD Incident Reports 2003-2013 (231MB) - All the San Francisco Police Department incident reports, from 2003 to 2013. Roughly 1.5 million records total
• SFPD Assault Reports 2003-2013 (19MB) - Just the SFPD reports categorized as ASSAULT. Roughly 130,000 records.

Obviously, if you have a slower computer, you may want to start off with the smaller file.

Creating a new MySQL database

1. Open the Sequel Pro app. A Connection dialog box should pop up. The Settings here will differ depending on how you installed things. Hopefully a Host value of 127.0.0.1, username of root, and a Port of 3306 will be your default settings. Hit the Connect button. If you get error messages at this point, um…look around for help on your machine's setup. Or just use Firefox and SQLite Manager.
2. In the menubar, select Database > Add Database
3. Name it something like sfpd and hit the Add button
4. In the menubar, select File > Import, then select the SQL file that you downloaded.
5. After the import is done, look at the left sidebar and click on the sfpd_incidents table.

Video

Sequel Pro interface

The Sequel Pro interface is about as simplified of a database GUI as I've ever seen. Here's an overview of the functions and views we'll be using:

• Structure: In the rare situations where we add columns via point-and-click, we can do it here.

  

• Content: In the relatively-rare situations (usually when we're unfamiliar with the database) where we want to browse the data as if it were a spreadsheet, this tab provides limited browsing and editing capability.

  

• Query: This is where we'll spend a majority of our time: writing SQL queries, executing them, and seeing the filtered results.

  

For the most part, you only have to be familiar with Query, as that's where the work gets done.

The Query tab

Here's a quick run-through of how to run a basic SELECT query in Sequel Pro. After this, you'll know enough about the Sequel Pro to do some basic work, and you can move on to the Select and Where tutorial

1. Click on the Query tab

2. Type in a query, such as:

   sql SELECT * FROM sfpd_incidents WHERE date > "2012"

3. Hit Run Current, which will execute the query you've written. You'll see the results of your query in the bottom panel.
4. Optional: You can click the little Gear button at the bottom of the results and and Export Result as CSV. So in our current scenario, we would produce a CSV of reports from 2012 to 2013.

Video

OK, now you know enough to write and execute SQL. Move on to the Select and Where tutorial.

Getting started with SQLite Manager

These instructions are for the SQLite Manager client. Go here for Sequel Pro

First, install the Firefox Browser. Then install the SQLite Manager plugin.

Importing SQLite databases

The SQLite Manager can import CSV files, but to make things easy, we'll start off with actual SQLite database files.

You can download either one of these files:

• SFPD Incident Reports 2003-2013 (270.3MB) - All the San Francisco Police Department incident reports, from 2003 to 2013. Roughly 1.5 million records total
• SFPD Assault Reports 2003-2013 (21.9MB) - Just the SFPD reports categorized as ASSAULT. Roughly 130,000 records.

Obviously, if you have a slower computer, you may want to start off with the smaller file.

Opening a SQLite database

1. Open your Firefox Browser
2. In the menubar, select Tools > SQLite Manager
3. The menubar will now change context to reflect that you're basically using a different program, i.e. SQLite Manager versus the Firefox Browser. From this new menubar, select Database > Connect Database
4. Now select the sqlite database you just downloaded.
5. Our database is simple and contains only one table. In the left sidebar, click the Tables dropdown and then the table label, sfpd_incidents

Video

SQLite Manager's interface

I won't lie, SQLIte Manager's interface can be a bit confusing and cluttered. Here's a quick overview of the 3 SQLite's tab/submenus that we'll end up using:

• Structure: In the rare situations where we add columns via point-and-click, we can do it here.

  

• Browse & Search: In the relatively-rare situations (usually when we're unfamiliar with the database) where we want to browse the data as if it were a spreadsheet, this tab provides limited searching and editing capability.

  

• Execute SQL: This is where we'll spend a majority of our time: writing SQL queries, executing them, and seeing the filtered results.

  

For the most part, you only have to be familiar with Execute SQL, as that's where the work gets done.

Executing queries

Here's a quick run-through of how to run a basic SELECT query in the SQLite Manager. After this, you'll know enough about the SQLite Manager to do some basic work, and you can move on to the Select and Where tutorial

1. Click on the Execute SQL tab

2. Type in a query, such as:

   ~~~sql SELECT * FROM sfpd_incidents WHERE date > "2012" ~~~

3. Hit Run SQL. You'll see the results of your query in the bottom panel.
4. Optional: You can click the Actions dropdown menu and export the results of your query as a CSV file by clicking Save Result (CSV) to File . So in our current scenario, we would produce a CSV of reports just in 2012 to 2013.

Video

Basic database terminology

This class is concerned only with how to query databases. I don't have any intention to teach database administration, but at the same time, it's necessary to understand a little bit of the underlying structure.

Databases

Think of it as a spreadsheet file, referred to as a __Workbook__in Microsoft Excel, that contains multiple sheets or tabs:

Typically with database software, you'll be asked to connect to a database. This is not much different than just opening a Workbook file in Excel before doing work on it.

In this lesson, the database will be named sfpd.

Tables

Think of these as the individual tabs/sheets of an Excel Workbook. They're all part of the same file, but they contain their own data fields and rows. Most of the fun with databases involves joining different tables together via some common field.

If you're familiar with the VLOOKUP functionality in a spreadsheet, then you might have tried to link different tabs/sheets within the same workbook:

=VLOOKUP(something_, table range, column number, [true/false])

In this lesson, the table will be named sfpd_incidents because it contains incident reports.

Database engine

This refers to the software you're using to work with a database, e.g. MySQL or SQLite. It is analogous to Microsoft Excel, Mac OSX Numbers, OpenOffice, or Google Spreadsheets. Each of those are different programs though they all involve manipulating spreadsheets. To work with an Excel spreadsheet inside of Google Spreadsheets, you typically export data from the Excel file and import it into Google Spreadsheets, which has its own spreadsheet file format.

In the same way, you can export data out of SQLite (typically, as a CSV) and import it into MySQL. But you can't just open a SQLite database and work with it directly from MySQL.

Database server

This refers to the computer that hosts the database and runs the database software needed to access that database. For the purposes of this lesson, the database server will be your own computer, i.e. what you're using to read this right now. In the real world, you often connect to remote databases hosted on other computers – either across the world or perhaps somewhere in your company's offices.

I suppose in spreadsheet-terminology, the computer that you've been using Excel on could be thought of (somewhat inaccurately) as a local "Excel server".

Note: To be really technical about it, a MySQL database server uses a different server process than what you, the client uses. SQLite is a "serverless" database.

The upshot for you, the novice is that installing and using SQLite will be little different than what it takes to install and run Excel. Choosing a "database" means finding where the database file (usually with a sqlite extension) is saved on your computer.

MySQL servers

With MySQL, you have to not only install the MySQL "client" software, but your computer will be running a server process in the background to serve up the databases.

So with any MySQL graphical-user-interface you use, you'll typically be asked to choose a server to connect to, and then you'll select a database to work with.

(If you are having problems getting MySQL to run on your personal computer, often times it will be a server connection type of issue. Hope you don't run into it, because fixing it is beyond the scope of this tutorial…)

Database GUI

This refers to the friendly graphical interface you're using on top of the database software, e.g. Sequel Pro for MySQL and Firefox's SQLite Manager for SQLite.

There is no direct analogy to the spreadsheet world, because part of the appeal of spreadsheet programs is that the GUI is inseparable from the spreadsheet-software – i.e. Excel is both the spreadsheet-manipulating program and the nice interface for it.

This is what MySQL looks like without a GUI

Video

Watch me do a SELECT query from the command-line:

Compare that to using the Sequel Pro GUI:

All together

For the purposes of this lesson, all we care about are databases and tables. For this specific lesson, we're using a database which has a single table, so it's no different than opening a Workbook in Excel and having just a single tab/sheet.

But when I refer to "open the database", what I mean is:

1. Run your database GUI (e.g. Sequel Pro or SQLite Manager)
2. Turn on the database engine (MySQL or SQLite); the GUI will already to that for you.
3. Connect to the database server that's currently running on your computer (If you're using MySQL/Sequel Pro), which is usually set to 127.0.0.1
4. Select a database (in this lesson, it'll be named sfpd)
5. Query a particular table, e.g. sfpd_incidents.

OK, now you know enough to write and execute SQL. Move on to the Select and Where tutorial.