Back in the bad old days, database systems cost a fortune. That’s why Larry Ellison is still the fifth-richest person on the Forbes 400 list. Thankfully there are plenty of cost efficient options for data storage and retrieval these days. One of these is SQLite, and it has the most generous license of all – public domain!
SQLite is an embedded SQL database engine
Unlike other databases like MySQL, Oracle, or PostgreSQL; SQLite does not have a separate server. SQLite reads and writes directly to ordinary files on your hard drive. While this is not always the best choice, for some projects it makes allot of sense. SQLite is very small and fast, but if you are running a website that gets hundreds of thousands of visitors a day it might not be right for you. If however you are creating an app for an embedded device, a desktop app that needs to keep track of limited data, or even for testing your code, SQLite can be a great choice.
The first thing you are going to want to do is go to the SQLite website and download the appropriate version for you. In my case I grabbed the Pre-compiled Binaries for Windows.
One potential mis-step to look out for. If you are planning on using SQLite for use with a programming language (like Python) on your Windows machine like I am, you want the version at the bottom of this graphic. You want the sqlite-tools-win32-x86-3220000.zip
Make a directory like the example shown above. Simply name your directory sqlite. You can create it anywhere, but if you go ahead and make it right on your C drive. This will help you avoid some potential issues by making sure it’s in your path. Unzip the file you downloaded and put the files in the directory you created. Once you have this done, fire up a shell ( cmd ) and navigate to your sqlite directory. To make sure it is installed correctly type: sqlite3 – you should see something like this:
If so it is ready to use! Yes, it really is a simple as that. As you can see in the graphic .help at the sqlite command line will get you a print out of some commands to get you started. OH! You will also need to know .quit Make sure that you quit sqlite with the quit command instead of just closing the window.
If you are a reader of this blog you are most likely aware that my main interest in SQLite is using it with Python. SQLite has a standardized Python API compliant interface. This means that accessing SQLite is very similar to accessing other databases in Python. Creating a new database in Python is super simple. If you try and connect to a database that does not exist SQLite creates one for you.
Here is a simple SQLite database connection in Python:
import sqlite3 new_db_file = 'trip_kendalls_data.sqlite' # this will be the name of the database file table_name = 'this_new_table' # create a new table id_column = 'a_new_column' # name of the PRIMARY KEY column column_type = 'BLOB' # E.g., INTEGER, TEXT, NULL, REAL, BLOB default_val = 'Default Values Are Boring' # add a default value # Connecting to the database file conn = sqlite3.connect(new_db_file) #connect to the the database file we defined up there c = conn.cursor() # place the cursor # make changes ( commit ) and close the database file conn.commit() # make it so conn.close() #close it up
That will create a new database file for you called: trip_kendalls_data.sqlite in whatever directory your script is in.
Hopefully that will help get you up and running with SQLite and Python. Of course that is just the tip of the iceberg, play around with it and see what you can do. There is a SQLite gui tool ( ala PHPMyAdmin ) called SQLite Studio if you are interested in that sort of thing. Let me know in the comments how you are using SQLite, I’m interested!