📂 How to create an SQLite3 database in Python 3 📂

PLEASE NOTE: This article assumes that you are familiar with importing modules in Python and SQL syntax/datatypes. If not, please read the following articles

• How to import modules in Python 3
• SQL Datatypes
• SQL Syntax
• Knowledge of Python's class methods such as __enter__, __call__ and __exit__ (Most commonly known one is __init__)
  - __init__ and __call__
  - __enter__ and __exit__

Introduction

Currently, I'm creating a discord bot in discord.py rewrite and came across the issue of needing a reliable database to store my user data. I tried using https://jsonstore.io but found that saving and loading data took too long from a remote server. After a little research, I came across the Python module sqlite3 which can execute SQL statements from within your python workspace with only a little setting up.

SQLite basic

After a little more googling I came across this tutorial by geeksforgeeks.org which was useful for learning the baby steps of sqlite3. The basic code looked something like this:

# Import the sqlite3 module
import sqlite3

# Setup a connection with our database file
connection = sqlite3.connect("myDatabase.db")

# Create a cursor for the database to execute statements
cursor = connection.cursor()

# Execute a statement
cursor.execute("{{SQL STATEMENT}}")

# Save + close the database, never skip this
# or nothing will be saved!
connection.commit()
connection.close()

An example of this in action would be

import sqlite3

conn = sqlite3.connect("database.db")
c = conn.cursor()

# Create the table, read the article below if you
# are unsure of what they mean
# https://www.w3schools.com/sql/sql_datatypes.asp
SQL_STATEMENT = """CREATE TABLE emp (
	staff_number INTEGER PRIMARY KEY,
	fname VARCHAR(20),
	lname VARCHAR(30),
	gender CHAR(1),
	joining DATE
);"""
c.execute(SQL_STATEMENT)

# Insert some users into our database
c.execute("""INSERT INTO emp VALUES (23, "Rishabh", "Bansal", "M", "2014-03-28");""")
c.execute("""INSERT INTO emp VALUES (1, "Bill", "Gates", "M", "1980-10-28");""")

# Fetch the data 
c.execute("SELECT * FROM emp")

# Store + print the fetched data
result = c.fetchall()
for i in result:
	print(i)

""" Printed:
(1, 'Bill', 'Gates', 'M', '1980-10-28')
(23, 'Rishabh', 'Bansal', 'M', '2014-03-28')
"""

# Remember to save + close
conn.commit()
conn.close()

However, for the use of my discord bot which is a multi-file application, this doesn't serve too well as it is very messy trying to pass the database connection object stored in a variable (in this case above, conn) through multiple files, let alone closing the connection when the bot restarts so I don't lose my data. What I needed was a function that would automatically save the database connection on each run.

My custom function

I then tried creating a custom function like this:

# import sqlite3
def execute_sql(query):
	conn = sqlite3.connect("myDatabase.db")
	c = conn.cursor()
	
	result = c.execute(query)
	
	conn.commit()
	conn.close()
	return result

This, however did not work for trying to retrieve data from the aforementioned database. When I tried to use the function like so,

sql_result = execute_sql("SELECT * FROM emp") # Find data
actual_result = sql_result.fetchall()
for i in actual_result:
	print(i)

I got the following error:

Traceback (most recent call last):
  File "main.py", line 2, in <module>
    actual_result = sql_result.fetchall()
sqlite3.ProgrammingError: Cannot operate on a closed database.

This error means that I cannot use the .fetchall() method to retrieve the data because I have already closed the connection. What I really needed was a way to execute the SQL statement then keep the database open to work with it a little before closing automatically. Something like the inbuilt open() method.

The open() method

I needed something like the open() method because you can execute it in a with statement, like so

with open("my_cool_story.txt") as story:
	for ln in story.readlines():
		print(ln)

As you can see, in line 1 I am able to open a connection to the my_cool_story.txt file and in lines 2 and 3 I am able to work with the data inside the file before Python automatically closes it at the end of the with statement. After asking around on the Python discord server I found out that there is a way to do this with Python's classes using two special class methods such as __enter__ and __exit__. The __enter__ function is called at the beginning of the loop and the __exit__ at the end. So after a little tweaking, I came up with something that looked like this

class Database(object):
	def __enter__(self):
		self.conn = sqlite3.connect("myDatabase.db")
		return self
	def __exit__(self, exc_type, exc_val, exc_tb):
		self.conn.close()

	def execute(self, query):
		c = self.conn.cursor()
		try:
			result = c.execute(query)
			self.conn.commit()
		except Exception as e:
			result = e
		return result

When I tried to use it like this, I found it worked perfectly!

with Database() as db:
	result = db.execute("SELECT * FROM emp")
	result = result.fetchall() # No errors!
	for i in result:
		print(i)

""" Printed:
(1, 'Bill', 'Gates', 'M', '1980-10-28')
(23, 'Rishabh', 'Bansal', 'M', '2014-03-28')
"""

Finally, I had created a reliable way of executing SQL from within python without any errors being thrown about closed databases! I also found out about the __call__ class method and combined it with my previous code to produce the following:

class Database(object):
	def __enter__(self):
		self.conn = sqlite3.connect("myDatabase.db")
		return self
	def __exit__(self, exc_type, exc_val, exc_tb):
		self.conn.close()

	def __call__(self, query):
		c = self.conn.cursor()
		try:
			result = c.execute(query)
			self.conn.commit()
		except Exception as e:
			result = e
		return result

I can now execute statements like so:

with Database() as db:
	result = db("SELECT * FROM emp")
	result = result.fetchall()

Summary

Thats it for the tutorial for now, I hope you liked it and learnt something from it. Let me know about any problems/feedback you have in the comments section below 👍 Give it an upvote if you enjoyed it as it took several hours to construct. 😃

Please note that all SQL must be executed inside the with statement and not outside or it will not work

# Correct
with Database() as db:
	result = db("SELECT * FROM emp")
	result = result.fetchall()

# Incorrect
with Database() as db:
	result = db("SELECT * FROM emp")
result = result.fetchall() # Closed database error is thrown

Chocolatejade42 out!