📂 How to create an SQLite3 database in Python 3 📂
📂 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__
)
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!
I kind of have a bunch of questions...
- does data persist
- does repl do self hosted databases?
- 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)
@AdCharity IDK, yes (or at least for python), IDK
@AdCharity Yeah IK
Dude. This doesn't make sense.
damn tysm for this. Imma yoink it for my discord bot. RIP discord.py
I bet this is incredible, but I don't know anything about sqlite so I have no knolege of that, so um, yeah.
i don't understand
I just don't get it
@gatorade322 What part do you not understand? Make sure you know how sqlite works, and have a good grasp of python first though.
@rediar bruh
@rediar coolio (that is not sarcastic so that you are aware)