Skip to content
← Back to Community
📂 How to create an SQLite3 database in Python 3 📂
Profile icon
chocolatejade42

📂 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

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!

Voters
Profile icon
shaneooo
Profile icon
Fireye
Profile icon
dimrijaip
Profile icon
MadocM
Profile icon
Muttonhead
Profile icon
CoolCoder1
Profile icon
FrandoMorales
Profile icon
RamonLoureiro
Profile icon
busymichael
Profile icon
Abdullah_MM
Comments
hotnewtop
Profile icon
gatorade322

I just don't get it

Profile icon
rediar

@gatorade322 What part do you not understand? Make sure you know how sqlite works, and have a good grasp of python first though.

Profile icon
gatorade322

@rediar bruh

Profile icon
BrookeBotley

@rediar coolio (that is not sarcastic so that you are aware)

Profile icon
AdCharity

I kind of have a bunch of questions...

  1. does data persist
  2. does repl do self hosted databases?
  3. would it be possible to replicate this in node.js (it seems a bit different from the documentation I read, but that's probably cause lite is different)
Profile icon
AMR001

@AdCharity IDK, yes (or at least for python), IDK

Profile icon
AdCharity

@AMR001 lmfao that was a late response

Profile icon
AMR001

@AdCharity Yeah IK

Profile icon
Hillo232

Dude. This doesn't make sense.

Profile icon
Fireye

damn tysm for this. Imma yoink it for my discord bot. RIP discord.py

Profile icon
gatorade322

I bet this is incredible, but I don't know anything about sqlite so I have no knolege of that, so um, yeah.

Profile icon
IghoiseO

i don't understand