I want to create some apps for various personal projects. Thus, I’m learning how to use SQLite with Python[1]. Since I enjoy learning new things, I plan to start with SQL, which I learned many years ago and since forgot the specific query syntax. Yes, I know I could use SQLAlchemy. I used Ruby on Rails in the past, for which ActiveRecord is similar. However, I want to develop a good understanding of creating a CRUD workflow without all that abstraction.
Here goes…starting with a single table and no related tables. I will use hard-coded SQL statements in this iteration to ensure I have the syntax correct and mitigate troubleshooting placeholder variables. Furthermore, I plan not to use explicit row ids and instead rely on SQLite’s ROWID alias[2]. I may change my mind on this in the future.
During exploration, I’ll use SQLite’s command line tool. Python installs SQLite for us, but I’m unsure about the command line tools. I downloaded sqlite-tools-win32-x86-3430000.zip[3], unzipped and put the files in C:\Program Files (x86)\SQLite\, and added that directory to the end of Windows System variable, Path[4].
Open the database with the SQLite shell to explore. Use .exit to exit the shell.
sqlite3 .\home.db
sqlite> .exit
This first module needs three import statements. All are in Python’s standard library.
import sqlite3
import sys
from pathlib import Path
I don’t know if this will turn into a library module, but let’s use a main() function and if __name__ == "__main__": bit to keep things tidy and in good form[5]. Add a variable with the path to and name of the database. If no database exists, one will be created. Run with sys.exit(main()) instead of main() because reasons[6].
def main():
db_path = Path(".") / "home.db"
if __name__ == "__main__":
sys.exit(main())
Open a connection to the database and create a cursor.
conn = sqlite3.connect(db_path)
cur = conn.cursor()
Create a table called “institutions.” Execute the query.
sql = "CREATE TABLE IF NOT EXISTS institutions (name, comments)"
conn.execute(sql)
I stop the execution of the module after this code to inspect the database. The first .schema shows that there are no tables in the database. The second shows that institutions was created.
sqlite> .schema
sqlite> .schema
CREATE TABLE institutions (name, comments);
Create a record in the institutions table.
sql = """INSERT INTO institutions (name, comments)
VALUES ('ABC Bank', 'A regional bank')"""
conn.execute(sql)
conn.commit()
An explicit commit statement is required to finish the transaction and write the data to the database.
Let’s go to the command line and explore the database again. Yay, the record is there! Note that * will not fetch ROWID. Thus, ROWID is explicitly requested.
sqlite> SELECT ROWID, * FROM institutions;
1|ABC Bank|A regional bank
Now, let’s fetch the record with Python. We need a cursor to fetch the record. In this case, use the cursor objected already created.
sql = "SELECT ROWID, name, comments FROM institutions WHERE ROWID = 1"
cur = conn.execute(sql)
res = cur.fetchone() # <-- Tuple
print(res)
The result from fetchone() is a tuple as shown with the print(res) statement.
> py institution_01.py
(1, 'ABC Bank', 'A regional bank')
>
Since I am exploring all this, I want to clean up the database. I probably should use pyTest for all of this, but it’s another thing to reason about. I just don’t feel like adding another layer to all this right now.
sql = "DROP TABLE IF EXISTS institutions"
conn.execute(sql)
Let’s take a final look at the database. Yep, .schema returns nothing and the table is gone.
sqlite> .schema
sqlite>
Do the right thing. Close the cursor[7] and the connection.
cur.close()
conn.close()
Here is the complete module.
institution_01.py
import sqlite3
import sys
from pathlib import Path
def main():
db_path = Path(".") / "home.db"
# Open and connection and create a cursor
conn = sqlite3.connect(db_path)
cur = conn.cursor()
# Create the table
sql = "CREATE TABLE IF NOT EXISTS institutions (name, comments)"
conn.execute(sql)
# Create one record
sql = """INSERT INTO institutions (name, comments)
VALUES ('ABC Bank', 'A regional bank')"""
conn.execute(sql)
conn.commit()
# Read a record by id
sql = "SELECT ROWID, name, comments FROM institutions WHERE ROWID = 1"
cur = conn.execute(sql)
res = cur.fetchone() # <-- Tuple
print(res)
# Delete the table
sql = "DROP TABLE IF EXISTS institutions"
conn.execute(sql)
# Close the cursor and connection
cur.close()
conn.close()
if __name__ == "__main__":
sys.exit(main())
My journey with Python and SQLite
References:
- sqlite3 — DB-API 2.0 interface for SQLite databases, Python.org Documentation
- SQLite Autoincrement, SQLite Documentation
- SQLite Download Page, SQLite Documentation
- How To Download & Install SQLite Tools, SQLite Tutorial
- __main__ — Top-level code environment, Python.org Documentation
- __main__ — Top-level code environment | Idiomatic usage, Python.org Documentation
- Solution: Always close the cursor for (even read-only) queries!, StackOverflow