SQLite Tutorial for iOS: Creating and Scripting

Storing Failed Banks by Year in a SQLite Database

Storing Failed Banks by Year in a SQLite Database

There are many ways to retrieve and store data on the iPhone, from property lists to NSCoding, from SQLite to Core Data.

This two-part SQLite tutorial series is going to cover one of those options: SQLite. SQLite is a simple and easy to use library that implements a simple database engine, treating a database as a simple flat file.

In this series, we’re going to show how to make an app that displays a list of failed US banks from a SQLite database.

In this first part of the series, we will cover what SQLite is and why we’d want to use it, how to use the sqlite3 command-line utility, and how to import data programmatically via Python.

In the second part of the series, we will cover how to make an iPhone app that reads data from the SQLite database we created, and display data in a table view and a drill down detail view.

This SQLite tutorial does not assume any prior knowledge with Python or SQLite, however it assumes a basic knowledge of SQL.

SQLite Overview

Before we begin, let’s give a high level overview of SQLite.

One of the nicest aspects of SQLite is its simplicty. As mentioned above, databases are just flat files!

This means that you don’t need to have a database server running at all. You simply tell the sqlite3 library the name of the file the database is stored in, and it contains all of the smarts about how to handle SQL statements to get the data in and out of the file.

One easy way to interact with sqlite3 databases is through the sqlite3 command line utility. To use it, simply fire up a Terminal and type sqlite3 followed by the name of the database you want to open, such as the following:

sqlite3 test.sqlite3

You will see a sqlite prompt appear, and you can begin to enter in SQL statements to create tables and work with data like the following:

sqlite> create table awesome_video_games(name text, type text);
sqlite> insert into awesome_video_games values('fallout', 'rpg');
sqlite> insert into awesome_video_games values('final fantasy', 'rpg');
sqlite> insert into awesome_video_games values('deus ex', 'rpg');
sqlite> insert into awesome_video_games values('hitman', 'stealth');
sqlite> select * from awesome_video_games where type='rpg';
fallout|rpg
final fantasy|rpg
deus ex|rpg

If you come from another database background such as MySQL, you’ll notice some differences with the syntax and some missing commands. I’d recommend checking out the SQLite SQL reference and the SQLite datatype reference for full details.

Note that the sqlite3 command line utility contains some useful “meta” commands to list tables and get table schemas like the following:

sqlite> .tables
awesome_video_games
sqlite> .schema awesome_video_games
CREATE TABLE awesome_video_games(name text, type text);

You can get a full list of “meta” commands by typing .help at the sqlite3 command line.

Why Use SQLite?

So why would we want to use SQLite, anyway?!

Well, SQLite has a lot of advantages over property list and NSCoding serialization when it comes to large sets of data.

First, it’s quite easy to pull out only a subset of the data available from the database at a time, which is good for memory usage and speed.

Secondly, the database engine can do a lot of neat work for you such as giving you counts of rows or sums of fields quite easily.

According to Apple, most of the cases where you find yourself wanting to use SQLite3, you would probably be better served using Core Data instead because it has a lot of useful built-in features that can both save you time programming, reduce memory usage, and improve speed.

However, I’ve found the learning curve for Core Data to be significantly higher than SQLite3. Since Core Data is an API on top of SQLite3, learning SQLite3 first can be a good stepping stone to Core Data.

I may have some more articles in the future covering Core Data, but for now let’s start simple and work with SQLite!

Obtaining Some Data

Before we can get an app together, first we need to do is get a data set to work with!

If you aren’t already aware, the US government has a neat web site that makes a lot of data obtained by various government agencies publicly available at data.gov.

I was browsing through the site and came across a list of banks that have failed since October 1, 2000. I thought this would be a great set of data to work with, since a) it’s quite simple, and b) the data set is interesting to me! :]

So go ahead and visit the above link, and click to download the data in CSV format. Once you download it and take a look, you’ll see that the data contains lines of comma separated entries like so:

Desert Hills Bank,Phoenix,AZ,57060,26-Mar-10,26-Mar-10
Unity National Bank,Cartersville,GA,34678,26-Mar-10,26-Mar-10
Key West Bank,Key West,FL,34684,26-Mar-10,26-Mar-10

Creating Our Database : Via sqlite3?

Once we have the data, the next step we need to do is create a sqlite3 database with that data.

We could load up the sqlite3 command line utility and start creating our table and starting to import the data from it, line by line. However this would be incredibly tedious.

If our CSV was very simple, we could use the sqlite3 command line to create a table, specify the CSV separator value, and import the data from the CSV:

sqlite> create table failed_banks(name text, city text, state text, 
    zip integer, close_date, updated_date text)
sqlite> .separator ","
sqlite> .import banklist.csv failed_banks

However, unfortunately our CSV value is not that simple. If you look through the data, you will see some lines such as the following:

"La Jolla Bank, FSB",La Jolla,CA,32423,19-Feb-10,24-Feb-10

In this instance, the quotes around “La Jolla Bank, FSB” are meant to indicate that that entire string is the name of the bank. However, the sqlite3 importer does not handle embedded quote marks, so we have to find another option.

We could massage the CSV a bit to get rid of those quotes\commas of course. But there’s something else we can do that is pretty easy and a good learning experience too: import the data into our database with Python!