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
1 | CREATE TABLE Readers (Country TEXT,Visits INTEGER); |
add new column with a default value
1 | 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!