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.
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
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
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
show all tables
show table creation statement (table schema)
exit sqlite terminal
show all indexes
“show” various DB settings
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
Below, getting a cursor object that holds our S
ELECT query results. Then iterating through each row of the cursor object with a for loop, as demonstrated in the documentation.
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.