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.
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.
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 .)
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.
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.
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;
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.
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.
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)
Python SQLite List the tables
Consider the code below for python SQLite example:
con = sqlite3.connect('mydatabase.db')
cursorObj = con.cursor()
cursorObj.execute('SELECT name from sqlite_master where type= "table"')
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.
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.
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.
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.
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.
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.
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.
- Download 1000+ Projects, All B.Tech & Programming Notes, Job, Resume & Interview Guide, and More – Get Your Ultimate Programming Bundle!
- Flower classification using CNN
- Music Recommendation System in Machine Learning
- Create your own ChatGPT with Python
- Bakery Management System in Python | Class 12 Project
- SQLite | CRUD Operations in Python
- Event Management System Project in Python
- Ticket Booking and Management in Python
- Hostel Management System Project in Python
- Sales Management System Project in Python
- Bank Management System Project in C++
- Python Download File from URL | 4 Methods
- Python Programming Examples | Fundamental Programs in Python
- Spell Checker in Python
- Portfolio Management System in Python
- Stickman Game in Python
- Contact Book project in Python
- Loan Management System Project in Python
- Cab Booking System in Python
- Brick Breaker Game in Python
- 100+ Java Projects for Beginners 2023
- Tank game in Python
- GUI Piano in Python
- Ludo Game in Python
- Rock Paper Scissors Game in Python
- Snake and Ladder Game in Python
- Puzzle Game in Python
- Medical Store Management System Project in Python
- Creating Dino Game in Python
- Tic Tac Toe Game in Python