How To Build A Database Using Python

By Irfan Alghani Khalid, Computer Science Student

Photo by Taylor Vick on Unsplash

Introduction

 
 
SQLAlchemy is a Python library for implementing SQL databases without using the SQL language itself. In other words, all you need to do is to implement your database using the Python language.

Flask-SQLAlchemy is a library for connecting the SQLAlchemy library inside your Flask project, and it makes your database implementation easier than ever. This article will show you how to build your database using the Flask-SQLAlchemy library.

Without further, let’s get started!

Database

 
 
Before we get into the implementation, let me explain to you about the database. What is a database? The database is a collection of data that is integrated by each other, where we can access it using our computer.

In data science, you probably will insert and analyze the data in a form like a spreadsheet. In the software development field, it’s a bit different. Let’s take a look at this spreadsheet.

The image is captured by the author.

Inside the spreadsheet, we can see there are two columns. There are the book’s title and the author’s name. If you look at the author column, you will see that some values are repeated several times. This condition we called redundancy.

It’s not a best practice to use the whole dataset as one table, especially for those who want to build a website or an app. Instead, we have to separate the table, where we called it normalization.

In summary, the normalization process will separate the dataset into several tables, and each table contains their unique identifier to it. We will call each of the identifiers as the primary key. If we separate the dataset above, we will have a dataset that looks like this:

The image is captured by the author.

As you can see from above, the dataset is already separated into two tables. There are the Book table and the Author table. The author’s name is on the Author table. Therefore, we cannot access the name directly like what we’ve done on the spreadsheet.

To retrieve the author’s name, we must connect the Book table with the Author table by taking the id from the author table. We treat the author’s id as the foreign key of the Book table.

Maybe it is complicated, but if you start implementing a database for building apps, it will improve your app’s performance.

Implementation

Install the library

 
Before we get into the implementation, the first thing that we need to do is to install the library inside our computer. To install it, we can use pip for doing that. Here is the syntax for installing the library:

To load the library, we can call the syntax below:

As you know from the library’s name, we also need to load the Flask library.

Starting the database engine

 
 
After we load the library, the next step is to set up our SQLAlchemy object and the path to our database. By default, SQLAlchemy comes with SQLite software.

SQLite is a database management system where we can build and analyze databases that we have build. You can use another DBMS, such as MySQL, PostgreSQL, or any DBMS that you prefer. To set up our database, Please add these lines of code:

This code will initialize the Flask and the SQLAlchemy object, and we set a parameter that contains the path to our database. In this case, the database path is sqlite:///C:\\sqlite\\library.db.

Implement the database

 
 
After we set up objects and parameters, we can start to implement the database. Let’s recall our database tables above:

The image is captured by the author.

As you can see from above, there are two tables on it. Each table will have its class, where we can initialize the column’s name and the relationship between tables. We will inherit a class called Model for implementing our tables. Based on the figure above, the code looks like this:

Insert the value

 
 
After we create the class, we can build our database. To build the database, you can access your terminal and run this command:

Now let’s try to insert values on our tables. For this example, let’s input the data from the previous spreadsheet above. You can follow this code below for inserting values:

Query the table

 
 
After you insert values inside the tables, now let’s run the query to see if the data exist. Here is the code for doing that:

As you can see from above, our submission is successful. Well, it’s still an introduction, but I hope you can grasp the concept and implement it on a larger project.

Addition: The SQL Query

 
 
In addition to the Flask-SQLAlchemy library, I will demonstrate to you how to access the database using the native SQLite. We do this for making sure that our database has already been created on it. For accessing SQLite, you can open your terminal and write this script:

sqlite3

After that, It will show the interface of SQLite like this:

The image is captured by the author.

In the next step, you can open your database by using the .open command and add the path to the database like this:

.open absolute//path//to//your//database.db

To make sure that we have opened the database, please write .tables on the terminal like this:

.tables

And it will generate this result:

The image is captured by the author.

Nice, It works. Now let’s try the SQL language for querying our database:

SELECT * FROM books

And here is the result:

The image is captured by the author.

Now let’s try to combine both tables as one. You can write this line of code on your terminal:

SELECT Book.title, Author.name FROM Book
INNER JOIN Author ON Book.author_id = Author.id;

And here is the result:

The image is captured by the author.

Final Remarks

 
 
Well done! Now you have implemented your database using the Flask-SQLAlchemy library. I hope this article helps you for implementing a database in your projects, especially when you want to build a web application using Flask.

In case that you are interested in my article, you can follow me on Medium or you can subscribe to my newsletter. Also, if you have any questions or just want to say hi, you can follow me on LinkedIn.

Thank you for reading my article!

 
Bio: Irfan Alghani Khalid is a Computer Science Student @ IPB University, interested in Data Science, Machine Learning, and Open Source.

Original. Reposted with permission.

Related:

• How to Build An Image Classifier in Few Lines of Code with Flash
• Easy SQL in Native Python
• How to Query Your Pandas Dataframe