Create a Simple CRUD Application Using SQLite3 Databases with Python - Part 1
SQL and Python have become essential skills for anyone taking on serious Data Analysis
So, the point is why we should learn SQL ?
We know that in a world where businesses are dependent on big data, understanding what data is available and what data we actually need is an invaluable skill.
If we can quickly extract the information from the database, we’ll become increasingly valuable to our team. Learning SQL is a simple and fast way to accomplish this.
So many Database Management System such as MySQL, Oracle, Postgres, etc. All these are client-server-based DBMS, but SQLite is a standalone DBMS.
Right now we all are moving to IoT and will be internet-based, therefore to store all these data using databases such as MySQL won’t be practical, because we will be dealing with small amounts of data and that’s where SQLite comes in action.
Since it’s lightweight and works standalone, SQLite will be faster and more efficient for such purposes.
SQLite is a lightweight database that can provide a relational database management system with zero-configuration because there is no need to configure or set up anything to use it.
Python has built-in support for SQLite that can be accessed using the sqlite3 module. Sqlite3 module is used to connect the SQLite database and perform all the operations including CRUD operations.
CRUD (Create, Retrieve, Update, and Delete) operations are essential in any application. The purpose of understanding CRUD operations is for beginners who are curious about backend development who want to learn database technology with server-side programing language.
In this post, we’ll cover off:
- Import Module
- Make Connection to a Database
- Create Function to Create a Table
- Create Function to Create an Auto Increment for ID
- Create Function to Display Data in Table Format
- Create Function to Display Data about Email
- Create Function to Insert Data
- Create Function to Update Data
- Create Function to Delete Data
So let’s start with the basics of SQLite Database & CRUD operations.
- Import Module
There are two Modules sqlite3 and prettytable.
sqlite3 provides a SQL-like interface to read, query, and write SQL databases from Python
prettytable is a simple Python library for easily displaying tabular data in a visually appealing ASCII table format.
2. Make Connection to a Database
Following Python code shows how to connect to an existing database and if the database does not exist, then it will be automatically created.
3. Create Function to Create a Table
And then we create function to create a table on existing database and if the table does not exist, then it will be automatically created.
4. Create Function to Create an Auto Increment for ID
Every table must have an ID and usually as a primary key. So We have to create function to create an auto increment for ID & it will be insert in the table
5. Create Function to Display Data in Table Format
When user want to know about the information from a table, we should create function to display data in Table Format using prettytable module and the data come from Select Query that display all data in table member.
And than all the data will display in a table format so that it will be very easy to read and to understand by an user
6. Create Function to Create Data List about Email
After that, we should create function to Create Data List about Email and it used for validate the email that will be input by the user
7. Create Function to Insert Data
When user want to add or input the data, we have to create function to insert data into the member table and it will be input by the user
8. Create Function to Update Data
After input the data, there are possibilites that user can input the wrong data. So, we have to create function to update data about first name and last name in the member table, also it validate by an email
9. Create Function to Delete Data
The last function is to delete data from member table and it will filter by an email that will be input by the user.
Okay, right now we have already prepared all the needs to create a simple CRUD application.
On the next post, we will implement all the function that we have defined. So keep stay tune & enjoy to learn !
