Learn to Code via Tutorials on Repl.it!

← Back to all posts
📂 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!

Voters
dimrijaip
MadocM
Muttonhead
CoolCoder1
FrandoMorales
RamonLoureiro
busymichael
Abdullah_MM
rediar
IghoiseO
Comments
hotnewtop
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)

AMR001

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

AdCharity

@AMR001 lmfao that was a late response

AMR001

@AdCharity Yeah IK

Hillo232

Dude. This doesn't make sense.

gatorade322

I just don't get it

rediar

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

gatorade322

@rediar bruh

BrookeBotley

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

gatorade322

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

IghoiseO

i don't understand