Creating and Managing Databases with SQLite and Python

Introduction

During phase 3 as a Flatiron student, I was introduced to python as well as SQL. Though it has been overwhelming at times, I wanted to share what I have learned about database management during that process. In this blog post, I'll cover how to set up databases, manage relationships between databases, perform CRUD actions, and how to design a schema for your database.

Setting up Database with SQLite

The first step is of course to make sure we have the right tools to work with SQLite. Because it is already contained in the standard Python library, no installation is required, only import. Here is what that would look like:
import sqlite3

Once we know we have SQLite, we can take our first steps in creating a workable database. First, we need to enter two lines of code that are essential in creating and interacting with the data.

CONN = sqlite3.connect('database.db')
CURSOR = CONN.cursor()

Lets take a look at this code line by line. The first line allows us to connect to a SQL database, and it will create a file if one does not exist. This is our door to interacting with the data.
The second line creates a cursor object which is essential for executing commands and queries in SQLite. The cursor object is responsible for executing CRUD commands that are used to add, delete, update, and find information within the table. We also use the cursor object to execute queries and fetch information. In those cases, we use CURSOR.fetchone() or CURSOR.fetchall() to retrieve results from the database.

Designing Schema for Data

Schemas are absolutely needed for creating uniform tables with all the desired information. A schema is essentially a template that you create, showing how you want the table to be set up. The schema defines what columns you want in your table, which will represent each different category of information you want to store. In addition, it will specify the data type you want the table to store in those columns. Common data types include TEXT, INTEGER(whole numbers), and REAL(floating-point numbers). Let's see an example:

    @classmethod
    def create_table(cls):
        try:
            CURSOR.execute(
                """
                CREATE TABLE IF NOT EXISTS customers (
                    id INTEGER PRIMARY KEY,
                    name TEXT,
                    age INTEGER,
                    company TEXT,
                    email TEXT UNIQUE
                );
                """

In this example, we use the cursor objects to create a table with 5 columns. The first column is the primary key, which allows each row to have a unique number to be found with. The other columns are name, age company, and email. Name, company, and email are all followed by TEXT, meaning they expect a string when a new row is created. Age on the other hand, is followed by INTEGER, meaning a number will be accepted for each row. It is also worth noting the use of the UNIQUE keyword, which forces uniqueness for every row among the email column.

CRUD Operations in SQLite

CRUD operations are the primary way to populate databases, update information when needed, retrieve information, and delete data from the database. CRUD is an acronym for create, read, update, delete. SQLite has its own keywords that are used to perform these essential functions.

The first keyword we will discuss is INSERT INTO, which allows us to add new rows to an existing table:
cursor.execute('INSERT INTO Users (name, email) VALUES (?, ?)', ('Mason', 'Mason@example.com'))
Here you can see that we added a new row into our table with name and email values.

The keyword that allows us to "read" information is SELECT. SELECT is flexible and allows us to pick and choose which rows we want by using WHERE, for example:

    @classmethod
    def find_by_id(cls, id):
        CURSOR.execute(
            """
          SELECT * FROM customers
          WHERE id == ?
            """, (id,)
        )

In this example, we select the whole row using *, but only the row that matches the id given.

To update tables in SQLite, we use the keyword UPDATE. For example:

 UPDATE students SET grade = 100 WHERE name = “Mason”;

As you can see, we can make use of WHERE once again to selectively update only the rows we want to.

Finally, we come to the delete part of CRUD. Delete is pretty simple, and intuitively uses the keyword DELETE. Lets take a look at an example:

cursor.execute('DELETE FROM customers WHERE name = ?', ('Mason',))

here we use the delete method to remove the row with the name 'Mason' from the data table.

Conclusion

In this post, we took a look at the basic, but important aspects of working with SQLite and Python, seeing how to create and manage databases effectively. While there is much much more to know about SQL and Python, these tools will allow you to create your own data tables and interact with them how you wish!