Python SQLite Tutorial

About Python SQLite Tutorial

In Python SQLite Tutorial, we will learn everything about using databases with SQLite3 using Python. We will cover everything from scratch. We will start with installation and creating a connection with Python SQLite Create Connection. We will also learn how to create a table in Python SQLite and much more.

Nowadays, most programmers are working with a python programming language and SQLite is the database that is mostly used today with python. So let’s understand what is basically an SQLite?

SQLite is a server-less database that means you can use it within almost all programming languages including Python. As it doesn’t require a server, there is no need to install it to work with SQLite as it is shipped by default along with Python version 2.5.x onwards and you can create an SQLite database connection directly.

In this Python SQLite tutorial, we will work with the SQLite3 database using python sqlite3 examples.

The Python SQLite library is used to integrate the SQLite database with Python. It 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.

Install SQLite in Python

To install the SQLite database in your system for Python having version 3.x, type the command below in your terminal:

pip install pysqlite3

To install the SQLite database in your system for Python having version 2.x, type the command below in your terminal:

pip install pysqlite

For anaconda users, pip is replaced with conda and the command is:

conda install sqlite3

In this way, you can successfully add the SQLite3 module to your Python.

But, according to this answer on StackOverflow, you don’t need to install sqlite3 module. It is included in the standard library (since Python 2.5). This means you do not need to install using the above commands, you can directly import the sqlite3 module inside your Python code to use it.

Now, let’s see how to work with sqlite3 and Python?

Creating SQLite database python Connection

import sqlite3
con = sqlite3.connect('mydatabase.db')

Here, the first thing to do is to import the sqlite3 module and then creating SQLite database python connection object which will connect us to the database that allows us to use the SQL statements. We have created the connection object i.e con to work with the python sqlite3 connect function.

Now once I run this statement, our database file name “mydatabase.db” will be created.

Output

Python SQLite3 Cursor

con = sqlite3.connect('mydatabase.db')
cursorObj = con.cursor()

The SQLite3 cursor is a method used for object connection. To execute the SQLite statements you need a cursor object and it can be created by the cursor() method. To execute the SQLite3 statements, you should establish a connection at first and then create an object of the cursor using the connection object.

We have created the cursor object (cursorObj), which calls the execute method to execute the SQL queries.

Python SQLite3 tutorial Create Database

Consider the code below in which we have created a database with a try, except, and finally blocks to handle any exceptions:

import sqlite3

from sqlite3 import Error

def sql_connection():

    try:

        con = sqlite3.connect(':memory:')

        print("Connection is established: Database is created")

    except Error:

        print(Error)

    finally:

        con.close()

sql_connection()

Once you create a connection with the sqlite3 python module, it will create a database file automatically if it doesn’t already exist. This database file is created on disk; we can also create a Python SQLite memory in RAM by using :memory: with the connect function. This database is called an in-memory database.

Here first we import the sqlite3 module, we are defining function sql_connection. Within this function, we have a try block, where the connect() function returns the object con after establishing the connection. It will display the messages as “Connection is established, the database is created” if no errors are found.

While in the except block, the error message will be printed if there are any errors. And in the finally block, we are closing a connection to release the memory from unused resources.

Output

Python SQLite tutorial Create a Table

The code will be like this:

import sqlite3

from sqlite3 import Error

def sql_connection():

    try:

        con = sqlite3.connect('mydatabase.db')

        return con

    except Error:

        print(Error)

def sql_table(con):

    cursorObj = con.cursor()

    cursorObj.execute("CREATE TABLE employees(id integer PRIMARY KEY, name text, salary real, department text, position text, hireDate text)")

    con.commit()

con = sql_connection()

sql_table(con)

In the above code, we have defined two methods, the first one establishes a connection and the second method creates a cursor object to execute the create table statement.

The CursorObj.execute() method will execute the create table query for table creation in the database.

The commit() method saves the changes.

To check if our table is created, you can use the DB Browser for SQLite to view your table. Open your, mydatabase.db file with the program, to see your table

You can also use another way to see the table by using the site www.sqliteonline.com.

(Watch the youtube video to see in detail, the use of the website to see the database .)

Output

Python SQLite Insert in Table

The code for inserting into the table :

import sqlite3

con = sqlite3.connect('mydatabase.db')


def sql_insert(con, entities):
    cursorObj = con.cursor()

    cursorObj.execute(
        'INSERT INTO employees(id, name, salary, department, position, hireDate) VALUES(?, ?, ?, ?, ?, ?)', entities)

    con.commit()


entities = (3, 'Rohit','700','IT', 'Tech', '2022-02-06')

sql_insert(con, entities)

We can pass values to an INSERT statement in the execute() method. You can use the question mark (?) as a placeholder for each value. The entities contain the actual values of the placeholders.

To check the data is inserted into the table select the File mydatabase.db, click on table employees, type the query and click on the Run tab.

The next option is to click on browser data in the DB browser.

Output

inserting values in a table in python sqlite3 tutorial

SQLite3 Python Update a Table

Here we have to create a connection, then create a cursor object using the connection, and after that use the UPDATE statement in the execute() method.

Consider the following code:

import sqlite3

con = sqlite3.connect('mydatabase.db')

def sql_update(con):

    cursorObj = con.cursor()

    cursorObj.execute('UPDATE employees SET name = "Atul" where id = 2')

    con.commit()

sql_update(con)

In this code block, we are creating the function def_update() where we have already created the connection object “con” to the database. Here we want to update the name of the “Andrew” to “Atul” whose ID is 2. For this, we are going to use the where clause. Hence in the name field, the output is changed from Andrew to Atul.

Output

updating a table in python SQLite tutorial

Python SQLite3 Tutorial Select statement

The select statement is used to select the data from the table. If you want to select all the columns of the data from a table, you can use the asterisk (*). The syntax for this will be as follows:

select * from table_name

In SQLite3, the SELECT statement is executed in the execute method of the cursor object. For example, select all the columns of the employees’ table, and run the following code:

cursorObj.execute('SELECT * FROM employees ')

If you want to select a few columns from a table, then specify the columns like the following

cursorObj.execute('SELECT id FROM employees')

You can also write the query as follows in the database and Run it:

SELECT id FROM employees;

Output

Python SQLite Tutorial Fetch all data

Consider the following code:

import sqlite3

con = sqlite3.connect('mydatabase.db')

def sql_fetch(con):

    cursorObj = con.cursor()

    cursorObj.execute('SELECT * FROM employees')

    rows = cursorObj.fetchall()

    for row in rows:

        print(row)

sql_fetch(con)

Here, we are using the SELECT statement and then we are using the fetchall() method of the cursor object to store the values into a variable to fetch the data from a database. After that, we will print all the values in the table onto the console.

Output

If you want to fetch the specific data from the database we can use the where clause like:

cursorObj.execute('SELECT id, name FROM employees WHERE salary > 700.0')

Hence we will get the following output after executing the query:

Python SQLite3 rowcount

print(cursorObj.execute('SELECT * FROM employees').rowcount)

The rowcount is used to return the number of rows that are affected or selected by the latest executed SQL query. After using rowcount with the SELECT statement, -1 will be returned as how many rows are selected is unknown until they are all fetched.

Output

To get the rowcount, you need to fetch all the data, and then get the length of the result:

Consider the following code:

import sqlite3

con = sqlite3.connect('mydatabase.db')

def sql_fetch(con):

    cursorObj = con.cursor()

    cursorObj.execute('SELECT * FROM employees')

    rows = cursorObj.fetchall()

    print(len(rows))

sql_fetch(con)

Output

length of rowcount in SQLite with python

Python SQLite List the tables

Consider the code below for python SQLite example:

import sqlite3

con = sqlite3.connect('mydatabase.db')

def sql_fetch(con):

    cursorObj = con.cursor()

    cursorObj.execute('SELECT name from sqlite_master where type= "table"')

    print(cursorObj.fetchall())

sql_fetch(con)

Here in this code block, to we are using the sqlite_master which is the master table in SQLite3, that stores all the tables. To list all the tables in the SQLite3 database you should query the master table and use the fetchall() method to fetch the data.

Output

SQLite3 Python Check if a table exists or not

import sqlite3

con = sqlite3.connect('mydatabase.db')

def sql_fetch(con):

    cursorObj = con.cursor()

    cursorObj.execute('create table if not exists projects(id integer, name text)')

    con.commit()

sql_fetch(con)

In this code block, we are making sure that the table already exists. To check if the table doesn’t already exist, we use “if not exists” with the CREATE TABLE statement. Hence for performing the other operations such as updating, and deleting the table, a table must exist.

Output

Check if a table exists or not in SQLite 3 python

As we can create the table if it doesn’t exist, similarly we can drop the table “if exists” with the DROP TABLE statement as follows:

cursorObj.execute('drop table if exists projects')

Python SQLite3 tutorial Drop Table

Consider the code below for the python SQLite example:

import sqlite3

con = sqlite3.connect('mydatabase.db')

def sql_fetch(con):

    cursorObj = con.cursor()

    cursorObj.execute('DROP table if exists projectss')

    con.commit()

sql_fetch(con)

In the above example, we are using the statement this will drop the table if it exists and the table should exist in the database to drop it. Hence, we should use the “if exists” block statements along with it.

Output

As you can see the table projects are deleted from mydatabase when using the DROP statement.

Python SQLite3 exceptions

Exceptions are the run time errors. In Python programming, all exceptions are the instances of the class derived from the BaseException.

In SQLite3, we have the following main Python exceptions:

SQLite with Python DatabaseError

The DatabaseError is raised when an error related to it occurs.

Python and SQLite IntegrityError

IntegrityError is a subclass of DatabaseError and is raised when there is a data integrity issue.

SQLite 3 Python ProgrammingError

The ProgrammingError is raised when there are syntax errors or a table is not found or a function is called with the wrong number of parameters/ arguments.

SQLite database python OperationalError

This exception is raised when the database operations are failed, for example, unusual disconnection.

Python database SQLite NotSupportedError

When you use some methods that aren’t defined or supported by the database, that will raise the NotSupportedError exception.

SQLite3 datetime

Consider the following code for python SQLite example:

import sqlite3

import datetime

con = sqlite3.connect('mydatabase.db')

cursorObj = con.cursor()

cursorObj.execute('create table if not exists projectss(id integer, name text, date date)')

data = [(1, "Bank management", datetime.date(2017, 1, 2)), (2, "Student Management", datetime.date(2018, 3, 4))]

cursorObj.executemany("INSERT INTO projectss VALUES(?, ?, ?)", data)

con.commit()

We can easily store date or time by importing the datatime module in the Python SQLite3 database. Here we imported the datetime module first, we have created a table named projects with three columns. The data type of the third column is a date. To insert the date in the column, we have used datetime.date.In the same way, we can use the datetim.time to view the time.

data = [(1, "Bank Management", datetime.date(2017, 1, 2)), (2, "Student Management", datetime.date(2018, 3, 4))]
cursorObj.executemany("INSERT INTO assignments VALUES(?, ?, ?)", data)

In this line, we have made the use of executemany() function to insert multiple rows at a time. The data variable has 3 values for each column. We are passing the data variable to executmany method which is having the placeholders that take the values.

Output:

Close Connection

python SQLite example

con = sqlite3.connect('mydatabase.db')

#program statements

con.close()

Here, we are closing the connection, once we are done with the database by using python sqlite close connection as we are not requiring it anymore in the program. We have created the connection object con and called the close() method.

Summary

In this Python SQLite Tutorial, we have used python3 SQLite3 for creating the databases. As it comes inbuilt into various IDEs, it becomes easier for the various python programmers to make it their first preference. You can use it in many operating systems like Windows, Linux, Mac OS, and Android as it is portable in nature.

I hope that this information will be helpful for everyone, thank you for watching this tutorial, and keep visiting us.

Also Read:

About Python SQLite Tutorial

Install SQLite in Python

Creating SQLite database python Connection

Python SQLite3 Cursor

Python SQLite3 tutorial Create Database

Python SQLite tutorial Create a Table

Python SQLite Insert in Table

SQLite3 Python Update a Table

Python SQLite3 Tutorial Select statement

Python SQLite Tutorial Fetch all data

Python SQLite3 rowcount

Python SQLite List the tables

SQLite3 Python Check if a table exists or not

Python SQLite3 tutorial Drop Table

Python SQLite3 exceptions

SQLite with Python DatabaseError

Python and SQLite IntegrityError

SQLite 3 Python ProgrammingError

SQLite database python OperationalError

Python database SQLite NotSupportedError

SQLite3 datetime

Close Connection

Summary

Author: Ayush Purawr

Search….

SiteMap

Recent Posts