📂 How to create an SQLite3 database in Python 3 📂
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:

An example of this in action would be

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:

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

I got the following error:

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

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

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

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:

I can now execute statements like so:

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

Chocolatejade42 out!

You are viewing a single comment. View All
AMR001

@AdCharity Yeah IK