???? 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

경축! 아무것도 안하여 에스천사게임즈가 새로운 모습으로 재오픈 하였습니다.
어린이용이며, 설치가 필요없는 브라우저 게임입니다.
https://s1004games.com

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

[출처] https://repl.it/talk/learn/How-to-create-an-SQLite3-database-in-Python-3/15755