Python SQLite3 Tutorial to Perform Basic Database Operation

Hello guys, welcome to Python SQLite3 Tutorial. Today in this Python SQLite3 Tutorial we will learn to perform some basic database operations in SQLite3 database with Python. So let’s start our Python SQLite3 Tutorial.

Python SQLite3 Tutorial

#Introduction to SQLite

Before we start covering this Python SQLite3 tutorial I want to give a brief introduction to those who are not familiar with SQLite at all. SQLite is basically a database management system where we can store our data for future use. Every programming software needs data to operate on. This is where we store our relevant data. Further, these data can be retrieved and can be manipulated accordingly.

Here is an example of how the data are stored in a database management system.

Student Id	Student Name	Student Roll	Student Ph. No
101	Mehtab	374	7549161023
102	Vivek	375	8877009587
103	Sumit	376	9798162302

This is a basic view of any database. In SQL we store data in the form of tables. Every record is entered in each row of the table. Table’s columns and their types are well defined and every record entered in the table should fit the table definition. The data types of the each column should be kept in mind too while creating the table.

#Using SQLite3 in Python

Now the next question is how to use SQLite database in python?

For that, first we need to import the SQLite library so that we can use predefined methods/functions or you may say the SQLite modules in python programming. Then we have to create a connection to the database. And to do so we need to create a connection object which is then linked to the database that we will operate on. How to do this has been shown below:-


import sqlite3
conn = sqlite3.connect(‘database.db’ [other optional arguments])

import sqlite3

conn = sqlite3.connect(‘database.db’ [other optional arguments])

Explanation:

Now at this stage, you guys must be familiar with importing libraries and what this is all about. What needs an explanation is


conn = sqlite3.connect( database_name.db [ other_optional_arguments ] )

conn = sqlite3.connect( database_name.db [ other_optional_arguments ] )

Here “conn” is the name of an object that is being associated with the database that we are using. In this example, the name of the database that we are using is ‘database.db’ , .db is the extension of course. The connect() method is a factory function in the SQLite library. Inside this connect() method we provide some arguments such as the name of our database. There are a lot more arguments that can be used which we will discuss later in this Python SQLite3 Tutorial Series. But for instance, we are using only one argument that is the name of the database which we are using that is “database.db”.

Now the name of the connection object that we created is very important because at times we may need to operate on more than one databases in one block of a program so by using the name of the connection object we can identify that for which database we are executing which SQL statement.

#Writing SQL statements in python

Here the problem is, we cannot write SQL statements in python directly. So we need another method which will serve the purpose that is execute() method. Below I have shown how to use this method to execute the SQL statements in python.

Example :


conn.execute(‘sql_statements’)

conn.execute(‘sql_statements’)

We are using the connection object “conn” that we created with the execute() method and inside the argument section of the execute() method we write the SQL statements that we want to execute for that specific database which is connected to that connection object ‘conn’.

#Some basic SQLite operations in python

Now in this part of the Python Sqlite3 Tutorial I have demonstrated how to perform some basic operations like creating a table, inserting records into them, updating records, fetching data from a database and to delete/drop the table. These are the most basic operations that come in handy for any programmer that needs a database to store any kind of data.

Creating a table

In a database we store data in the form of records into the tables and for that first we need to create a table first. So in order to create a table what we need to do is given below :-


import sqlite3
conn = sqlite3.connect('database.db') 
print('Database connection created.') 
conn.execute('''create table Student 
(S_id int primary key, 
S_name text, 
S_roll int, 
S_phone int)''') 
print ('Table created.') 
conn.close()

import sqlite3

conn = sqlite3.connect('database.db')

print('Database connection created.')

conn.execute('''create table Student

(S_id int primary key,

S_name text,

S_roll int,

S_phone int)''')

print ('Table created.')

conn.close()

The output of the above program will look like this:-