If you are a beginner level programmer then you might don’t come across the database need into your project, but if you want to be a good programmer you should learn databases as they will be used in every application you might be building into future. If you are an intermediate level programmer, then you might encounter the need of databases into your project and that is why you want to learn database right now. No matter what is your intention the main focus is how you can set you up to become comfortable in using databases in any kind of project. In this article we will explore python and sqlite in detail.
First thing in learning databases and that is start from complete basic level database operations with probably some small database and then move on to the next big database.
Let me explain this to you in easy way. You want to learn database right? If you want to learn database then you probably want to start learning it from absolute beginning and you might also want to start learning database from completely scratch so that your logic become clear about database, right?
Let’s do that.
In this Article I will explain what is SQLite and how to perform basic database operations in sqlite3 database.
What is SQLite and sqlite3? (python and sqlite)
SQLite is a lightweight database mainly suitable for small applications and for beginner and intermediate level programmers who want to start digging into the field of databases. And sqlite3 is an upgraded form of SQLite database
Comparing SQLite with SQL:
I think that there is no need to compare these two databases because the actual and big database that is used most of the time is SQL and SQLite is just the light version of SQL database. But if there is no use of anything then why that thing even exists. The answer is pretty straight forward and that is SQL database is used for commercial applications and it can be learned easily after learning SQLite and that’s it.
Basic Crud Operations in SQLite3 database:
In order to perform any operation onto database the first thing that we should keep in mind is simple and that is to connect you programming language with the database itself. So let’s do that first.
Connect python with sqlite3||python sqlite create database:
To connect python with sqlite3 you just have to have python installed and that is all you need. That’s why most programmers prefer to start with sqlite3 database whenever you need to connect database with python.
Open up idle and create a new file there and name it as crud_operations.py. Once you do that just type the following command to connect python with sqlite3 database. Type the following code into code file.
Import sqlite3
conn = sqlite3.connect(“data.db”)
c = conn.cursor()
and your database is connected with your python. You didn’t have this data.db file into your pc and that is ok because it will create one for you. What the above code is doing let’s explain that.
So we are importing the sqlite3 database and don’t worry to install it, it comes built in with python.
In the second line we are establishing a connection of our python file to our database file.
And at last we are placing the cursor function into c variable to use it to execute sqlite3 commands. Now let’s start inserting some data into it so that you can see the database in action.
Insertion(python sqlite3 insert)
To insert data into database file you need to create a table, so we will be creating a function called create_table and we will add the code to create table into this function so that we can reuse this function whenever we need to create any table.
Type the following code:
def create_table():
c.execute(“CREATE TABLE IF NOT EXISTS data (name TEXT, email TEXT, phone VALUE)”)
conn.commit()
what the above code is doing is simple we are just creating a basic function of python and inside function there are two line the first line is used to execute database query and the second line is committing the changes inside database.
now we will create a function to insert data into our database.
def insert_data():
c.execute(“INSERT INTO data VALUES (‘mubashar’, ‘sm@mubashartech.com’, 23233)”)
conn.commit()
c.close()
conn.close()
So we are creating a function called insert_data and first two lines are performing the same functionality as above but the difference is above we are creating a table and here we are inserting data into table. c.close() is used to close the cursor once operation performed and conn.close() is used to close the connection to stop memory from being used. You can skip these two lines called c.close() and conn.close().
Run our program by adding these two lines at the end of code file
create_table()
insert_date()
Save our file as crud_operations.py.
Now run our program by pressing f5 if you are coding in python idle and if you are working on some other compilers then type the required key to run the program.
You will see a new file created called data inside the folder where your crud_operations.py file is present.
Installing db browser for sqlite3 (optional):
Now if you want to see the data then install db browser for sqlite3. And if you don’t want to see table data manually you can skip this step, in the next step we will read our data through sql commands. But if you want to see the data using db browser then search for it on google and download db browser and open data.db file inside db browser for sqlite3.
Read data from database|| python sqlite select:
Now you have successfully input data into database, now let’s retrieve data from the database.
To retrieve data from the database, we will use the SELECT statement. Add these lines of code at the end of crud_operations.py file.
def read_data():
c.execute(‘SELECT * FROM data’)
conn.commit()
what this function will does is simply it will select the entire data from the database table and now let’s print data by iterating over it. We will use a for loop to iterate over data to make it more readable.
Add these lines after conn.commit().
for items in c.fetchall():
print(items)
And you will see the line retrieved that we had inserted before.
One issue you might encounter is cannot run execute on closed connection and that is because in the insert data function we closed the connection, to get rid from this just comment those two functions and add this line at the end of crud operation file.
#create_table() # this sign will make it a comment and it will not run.
#insert_data() # just comment both function calls so that you don’t get an error.
Add this line at the end
read_data()
You will see the output in console/terminal. Ok If you follow the exact same steps then you might get the desired output. The next step is to insert data through variables in python.
Insert Data dynamically|| python sqlite3 insert:
For this type the following code into your code file.
name = input(‘enter your name:’)
email = input(‘enter your email:’)
phone = input(‘enter your phone:’)
Now that you have taken 3 inputs from user, the next step is to store it inside database.
Create a function called insert_data_dynamically and type the following lines of code into it.
def insert_data_dynamically():
c.execute(“INSERT INTO data (name, email, phone) VALUES (?, ?, ?)”,(name, email, phone))
conn.commit()
Commit out the function calls we have made before and add this line at the end of code file.
Insert_date_dynamically()
Once you run the program then you will see it is asking for input your name, email and phone and once you give the input then it will store it inside database and you can check the data by running the function called read_data().
Type the following line of code at the end of file.
read_data()
Delete Data from Database||python sqlite delete:
Now we have successfully inserted and retrieve data from database. it is time to move ahead and start learning delete data from database.
To delete data from database, just make a new function and add these lines of code to it.
def del_data():
c.execute(“DELETE FROM data WHERE name=’mubashar’”)
conn.commit()
call this function and you will see that the line in which name = ‘mubashar’ will be deleted from database.
del_data()
read_data()
Update data in database|| python sqlite update:
Now we will make a new function that will update data into database.
def update_data():
c.execute(“UPDATE data SET name=’mubashartech.com’ WHERE name=’mubashar’”)
conn.commit()
If you call function.
update_data()
read_data()
then you will see the name updated successfully.
Complete code written in this tutorial:
import sqlite3
conn = sqlite3.connect('data.db')
c = conn.cursor()
def create_table():
c.execute("CREATE TABLE IF NOT EXISTS data(name TEXT, email TEXT, phone REAL)")
# insert data dynamically
def insert_data_dynamically():
#taking input from user
name = input('Enter name:')
email = input('Enter email:')
phone = input('Enter phone:')
c.execute("INSERT INTO data (name, email, phone) VALUES (?, ?, ?)", (name, email, phone))
conn.commit()
# running create table and insert_into_table functions
#create_table()
#insert_data_dynamically()
#c.close()
#conn.close()
#Read from database
def read_from_db():
c.execute("SELECT * FROM data")
data = c.fetchall()
for items in data:
print(items)
# selecting data based on particular name
# c.execute("SELECT * FROM data WHERE name='mubashartech.com'")
#for items in c.fetchall():
# print(items)
#update data from database
def update_data():
c.execute("UPDATE data SET name='john1234' WHERE name='john'")
conn.commit()
#c.execute("SELECT * FROM data")
#for rows in c.fetchall():
# print(rows)
def delete_data():
c.execute("DELETE FROM data WHERE name='nothing'")
conn.commit()
delete_data()
read_from_db()
I hope you have learned a lot with this complete guide and if you have any question feel free to ask it in the comment section and I will definitely answer. I have already make some cool project tutorials with python you can check them here as well.
