SQLite3: Pythonic First Impressions & Lay of the Land

introduction

SQLite is one of the heavy hitters in the database space, up there with other popular choices like MySQL, Postgres, Microsoft SQLServer, Cassandra and MariaDB. There is no shortage of database technologies but SQLite is certainly one that is commonly used. It also has a positive reputation. Its terminal interface reminds me of MySQL. The syntax of both seem similarly “SQL-like” and easy to pick up.

I finally got around to test-driving a SQLite database this week. In this post, I’ve listed my impressions of some practical SQLite commands. The “dot” syntax is helpful to do a lot things as you’ll see below. I’ll conclude by briefly exploring the sqlite3 python library in the python interpreter.

getting started

I installed SQLite from the terminal with apt on Ubuntu Linux.

There are also downloads for Windows. A popular GUI is SQLite Studio.

create a new database

sqlite3 PythonMarketer.db

create a new db + new table and import a csv file to the table, “Readers”

sqlite3 PythonMarketer.db
.mode csv Readers
.import PythonMarketerReaders2015-2020.csv Readers

[source]

create a table

CREATE TABLE Readers (Country TEXT,Visits INTEGER);

add new column with a default value

ALTER TABLE Readers ADD TEXT DEFAULT '0';

show all help (and . syntax) options

.help

show all tables

.tables

show table creation statement (table schema)

.schema Readers

exit sqlite terminal

.exit

show databases

.databases

show all indexes

.indexes

“show” various DB settings

.show

Pictured: “showing” DB settings and “EXPLAIN-ing” a query

Exploring sqlite operators: the GLOB operator

Exploring my new table with the Python sqlite3 library in the Python interpreter

sqlite3 is in the python standard library, always a nice convenience to simply import it! Here we are connecting to an existing .db with sqlite3.connect()

Below, getting a cursor object that holds our SELECT query results. Then iterating through each row of the cursor object with a for loop, as demonstrated in the documentation.

Shown above: printing the rows within the cursor object returned from executing a SELECT SQL statement.

Comparable Cursors and PEP 249

The cursor object has a variety of methods you can call on it for database operations and to execute SQL. You can read more about them in the sqlite3 module documentation. This library also follows PEP 249 – Python Database API Specification for recommended Database API interfaces. I’ve noticed that in pyodbc, for example, the cursor object looks and feels the same as the cursor object in sqlite3. This is because they are both likely following PEP 249. Very cool!

Before You Go, Check Out Python Marketer’s Open Data Github Repository

The data shown in this post is published on Github. There you’ll find you this blog’s reader by country data, with analysis in a Jupyter Notebook. This post I wrote shows more info about using Jupyter and matplotlib for data analysis if you’re curious about it.

In 2020, 105 countries or 53% of the world’s countries have read about Python on this blog. 🤯👏👏👏 Amazing.

I think this data offers a hint of perspective into the question, “Where in the world are people learning about Python?” The short answer is all around the world! That’s pretty rad. Thanks for reading and stay curious.