Using Object Oriented Programming to interface with an SQL Database

source: https://www.sqlitetutorial.net/sqlite-sample-database/

For this example we will use a sample SQLite database called “Chinook” which you can download from https://www.sqlitetutorial.net/sqlite-sample-database/. This database stores music and album information as well as customer and employee data.

We will build a Chinook class that will allow us to easily access information in our database without having to write SQL queries every time. We can do this with attributes and methods. Our class should have an attribute called tables that returns a list of tables within the database. To collect the table names from a SQLite database, we can do the following:

1. Open up a connection to our database

import sqlite3
import pandas as pd
import os
path = os.path.join(‘data’, ‘chinook.db’)
conn = sqlite3.connect(path)
cursor = conn.cursor()

2. Execute a SQL query

tables = cursor.execute(‘’’SELECT name FROM sqlite_master
                  WHERE
                  type = ‘table’
                  AND
                  name NOT LIKE ‘sqlite_%’;’’’).fetchall()
# The above returns a list of tuples, we just need the table names 
# in position 0. Using list comprehension we can update tables to 
# be a list of just the table names:
tables = [table[0] for table in tables]
tables

The above returns our list of tables:

['albums',
 'artists',
 'customers',
 'employees',
 'genres',
 'invoices',
 'invoice_items',
 'media_types',
 'playlists',
 'playlist_track',
 'tracks']

Now let’s create a class called Chinook. The class should have an __init__() method. You can create methods inside classes with self as the first parameter of the function.

The __init__() method should have two paramaters:

1. self
2. database_path

Within the __init__() method:

1. A connection should be opened up to the database using the database_path variable and saved as a attribute.
2. A cursor attribute should be created.
3. A tables attribute should be created.

The code to create the tables attribute will be almost identical to the code up above.

class Chinook():
   def __init__(self, database_path):
   Chinook.conn = sqlite3.connect(path)
   Chinook.cursor = Chinook.conn.cursor()
tables = Chinook.cursor.execute(‘’’SELECT name FROM sqlite_master
   WHERE
   type = ‘table’
   AND
   name NOT LIKE ‘sqlite_%’;’’’)
   self.tables = [table[0] for table in tables]

Let’s add a method to our class called search_employees.

This method should use pd.read_sql to return a dataframe with a single row for the employee you search for.

search_employees should receive three parameters.

1. self
2. The first name of an employee.
3. The last name of an employee.

If the employee is not found, the method should return the string 'Employee was not found.'

class Chinook():
    def __init__(self, database_path):
        Chinook.conn = sqlite3.connect(database_path)
        Chinook.cursor = Chinook.conn.cursor()
        tables = Chinook.cursor.execute('''SELECT name 
            FROM sqlite_master
            WHERE
            type = 'table'
            AND
            name NOT LIKE  'sqlite_%';''')
        self.tables = [x[0] for x in tables]
    
    def search_employee(self, first_name, last_name):
        result = pd.read_sql('''SELECT * FROM employees
            WHERE FirstName = "{}"
            AND LastName = "{}"'''.format(first_name, last_name), Chinook.conn)
        if len(result) < 1:
            return 'Employee was not found.'
        else:
            return result
data = Chinook(path)
data.search_employee('Jane', 'Peacock')

The above will return the information of employee Jane Peacock. The above class allows us to easily access and search for an employee with just a first and last name.