Python SQLITE3 and DATA CLASS
This is a how to create use python sqlite database and dataclass
# import the desired module that will be use by the application
from dataclasses import dataclass
import sqlite3
# Create a data class to help getting and adding data to and from the database easier
@dataclass
class Car:
wheels : str
roof : str
name : str
color : str
# create the database helper class
class DatabaseHelper:
# initialize
def __init__(self) -> None:
self.TABLE_NAME = 'Car'
self.DB_NAME = 'database.db'
self.connect = sqlite3.connect(self.DB_NAME)
self.cursor = self.connect.cursor()
# create the database table
def createTable(self):
self.cursor.execute(f'''CREATE TABLE IF NOT EXISTS {self.TABLE_NAME}(
wheels TEXT, roof TEXT, name TEXT, color TEXT
)
''')
# this will help save the database after adding data to the database.
# without this your data will not save and it might generate error when trying to assess the data
def saveDatabase(self):self.connect.commit()
# this will help close the database when you are done with it, to prevent data leek
def closeConnection(self):self.connect.close()
# this will retrieve all the data saved in the database in list format
def selectAllFromDatabase(self):
return self.cursor.execute(f'SELECT * FROM {self.TABLE_NAME}').fetchall()
# this will retrieve the first data that matches the query and return it as a data class
def selectByName(self, name):
data = self.cursor.execute(
f"SELECT * FROM {self.TABLE_NAME} WHERE name=?",
(name,)
).fetchone()
return Car(wheels=data[0], roof=data[1], name=data[2], color=data[3])
# this will delete all the data that matches the query
def deleteByName(self, name):
return self.cursor.execute(
f"DELETE FROM {self.TABLE_NAME} WHERE name=?",(name,)
)
# by using the data class, you can add stuff into the database
def insertIntoDatabase(self, car):
insert = self.cursor.execute(f'''
INSERT INTO {self.TABLE_NAME} VALUES (
'{car.wheels}','{car.roof}', '{car.name}','{car.color}'
)
''')
return insert
# this will not just delete all the data in the database, it will also delete the table as well
def deleteDatabase(self):
return self.cur.execute(f'DROP TABLE IF EXISTS {self.TABLE_NAME}')
#USAGE
databaseHelper = DatabaseHelper()
databaseHelper.createTable()
databaseHelper.insertIntoDatabase(car=Car(name='tesla', wheels='4', roof='close', color='silver'))
print(databaseHelper.selectAllFromDatabase())
print(databaseHelper.selectByName(name='tesla'))