Dealing With Single-File Databases in Python

# Introducing sqlite3 for beginners

A single-file database is a database that consists of a single file. Yes, there is such a thing. No, it is not simply a text file that we append stuff to. We usually use .db as an extension, and sqlite3 to interact with it.

Let’s explore step by step how we can deal with this.

1) Importing sqlite3

sqlite3 is part of the Python standard library. Which means that we don’t need to pip install anything, and we can simply import it.

import sqlite3

2) Creating a connection

import sqlite3

connection = sqlite3.connect('test.db')

If test.db exists, we’ll simply connect to it.

If test.db does not exist, Python will automatically create a new test.db file in the same current directory, and this will be a new, empty database.

This test.db file itself is our database.

3) Creating a cursor

From our connection object, we create a cursor. By design, we use the cursor to execute statements.

import sqlite3

connection = sqlite3.connect('test.db')
cursor = connection.cursor()

From this point on, we’ll be using our cursor to execute SQL statements.

4) Creating a table in our database

import sqlite3

connection = sqlite3.connect('test.db')
cursor = connection.cursor()

# create table
cursor.execute('create table dogs(name varchar(64) primary key, age int)')
connection.commit()

Here, we use the cursor.execute method to execute an SQL create query. Note that our SQL query must be valid, or else we’ll simply get an error.

Also, note that we need to connection.commit() for changes to be saved in our database test.db. At this point, our table has already been created. So if you attempt to run this script again, you’ll get an error stating that the table dogs already exists.

So now, we have an empty table dogs containing 2 columns — name and age (name being the primary key)

5) Inserting values into our newly created table

import sqlite3

connection = sqlite3.connect('test.db')
cursor = connection.cursor()

# insert values
cursor.execute("insert into dogs values ('rocky',4), ('fifi',5)")
connection.commit()

Similarly, to insert stuff into our newly created table dogs, we simply need to use the cursor.execute method. Once again, we need to connection.commit() in order for changes to be saved.

Again, at this point, the dogs rocky and fifi has already been inserted into our test.db database. So if we run this exact script twice, we will get an SQL error stating that the dogs rocky and fifi already exist (due to the dog name being the primary key)

Now, we need to ascertain that the values that we’ve added into our table are actually still there persistently.

6) Reading values from our table

import sqlite3

connection = sqlite3.connect('test.db')
cursor = connection.cursor()

# read values
cursor.execute('select * from dogs')
print(cursor.fetchall())

# [('rocky', 4), ('fifi', 5)]

Here, we simply use the same cursor.execute method to run a select statement. And the .fetchall method to get every single row of data from our cursor.

7) Getting the column names

Sometimes when we have many many columns, we need to gte the column names too just to be extra sure.

import sqlite3

connection = sqlite3.connect('test.db')
cursor = connection.cursor()

# read values
cursor.execute('select * from dogs')
print(cursor.fetchall())

# [('rocky', 4), ('fifi', 5)]

# fetching column names
print([i[0] for i in cursor.description])
# ['name', 'age']

Here, we can use cursor.description to get the column names.

Conclusion

Hopefully this was clear and helpful to even beginners.

Some Final words

If this story was helpful and you wish to show a little support, you could:

1. Clap 50 times for this story
2. Leave a comment telling me what you think
3. Highlight the parts in this story that resonate with you

These actions really really help me out, and are much appreciated!

Ebooks I’ve Written: https://zlliu.co/ebooks

LinkedIn: https://www.linkedin.com/in/zlliu/