Python and SQLite

I’m starting another review of my recently published book, Self-Guided SQL: Build Your SQL Skills with SQLite, looking for anything I might have missed, and one thought I had was adding more material on how to access SQLite from programming environments. A couple of months ago, I uploaded a video on how to create a DNS-less connection to SQLite with MS-Access VBA and, of course, I’ve been playing around with Python again lately.

I was surprised at how easy it was to connect to SQLite with Python. Here’s an entire script, minus error handling:

import sqlite3

# Define connection and cursor.
conn = sqlite3.connect('chinook.db')
cursor = conn.cursor()

# Execute SQL and retrieve results.
cursor.execute("select customerid, lastName, firstName, \
Phone from customers limit 10;")

rows = cursor.fetchall()

# Display results.
for row in rows:
    print(*list(row), sep=', ')

# Close database
cursor.close()
conn.close()

Python has a built-in SQLite3 module so it’s just a matter of importing it, creating a connection off of it, creating a cursor from the connection and using that connection to execute the SQL and retrieve the rows as tuples. Then you can do whatever you need to with the information from there.

SQLite3 –> Connection –> Cursor –> Data

The print statement above uses the * to remove the brackets from the list display and defines a comma for a field separator so the output could actually be redirected to a CSV file as shown above. On the other hand, you can also import the CSV module which automatically handles quoting and newline characters.

with open('output.csv', mode='w', newline='') as file:
    writer = csv.writer(file)
    writer.writerows(list(rows))

So far, this is nothing that the SQLite terminal program can’t do with its CSV output mode but it is one more option for connecting to a database and, with Python’s abilities to quickly parse and analyze large amounts of data, there are all sorts of possibilities that I’m sure I’ll stumble upon as I continue to review the book. Stay tuned.