Python SQLite Tutorial - How to Install SQLite

Python SQLite is a powerful library that allows developers to quickly and easily interact with SQLite databases.  It provides an easy-to-use interface, allowing developers to write queries, create tables, and perform other operations on the database.

With Python SQLite, developers can access the power of a relational database without having to learn complex query languages or use expensive software packages. 

This Python SQLite tutorial will show you how to use the SQLite database to build database applications in Python. You will learn how to use Python to work with SQLite databases.

Table of Content: Python SQLite Tutorial

What is PySQLite Why use SQLite in python? How to Install SQLite? API & Description Connecting a Database Creating a Database Inserting Data into a Database Selecting Data for a Database Update Data to a Database Deleting Data From a Database Querying Data in a Table Python SQLite Data Types Python SQLite FAQs  Conclusion

What is PySQLite

The PySQLite gives the SQLite database a standard Python interface that works with the DBI API 2.0. The sqlite3 module is a valuable component of Python's standard library; it enables us to work with a fully-featured SQL database on disc without installing additional software.

PySQLite is a good choice if your app needs to work with the SQLite database and other databases like MySQL, PostgreSQL, and Oracle. Since Python 2.5, PySQLite has been part of the standard library.

If you are looking forward to enhancing your career as an Oracle PL SQL Developer? Check out the "Oracle PL SQL Training" and get certified today

Why use SQLite in Python?

SQLite is a lightweight and easy-to-use database engine that is embedded into Python.  It is used to store, query, and manipulate data. 

With the help of SQLite, developers can quickly create databases that can be used to store data in a structured way.

SQLite offers many advantages over traditional database engines, such as scalability, portability, and ease of use. It also provides powerful features like transactions, triggers, and views, which make it an ideal choice for developing applications with Python. SQLite is also secure as it uses strong encryption techniques to protect the data from unauthorized access.

SQLite is a C library that provides us with a lightweight disk-based database that doesn't require a separate server process. This also allows accessing the database, which uses nonstandard variants of the SQL query language.

How to Install SQLite?

An SQL interface that complies with the DB-API 2.0 specification can be obtained using SQLite3, as detailed in PEP 249. Since Python 2.5.x, this module has been included in Python's default distribution. Therefore there's no need to install it separately.

To use the sqlite3 module, you first need to construct a connection object that stands in for the database. After that, you have the option of creating a cursor object, which will assist you in carrying out every SQL statement.

Create a python file "connect.py", having the following code:

#!/usr/bin/python    
import sqlite3    
conn = sqlite3.connect('javatpoint.db')  
print "Opened database successfully"; 

The above-mentioned code is important in the Python SQLite module routine. This can suffice your Python program's requirement to work with SQLite database.

API & Description

Python SQLite is an API that allows developers to access and manipulate data stored in an SQLite database. It provides an easy-to-use interface for working with the database, allowing developers to quickly create, read, update and delete records from the database. 

With Python SQLite, developers can create applications that can store and retrieve data from an SQLite database without having to write complex queries or learn specialized programming languages. This makes it ideal for creating web applications that need to access and manipulate data stored in an SQLite database.

1.sqlite3.connect(database [,timeout, other optional arguments])

Remember that this API establishes a new link to your SQLite database. The ":memory:" prefix facilitates connections to databases that reside in RAM rather than on a disc. Successful database connection results in the return of a connection object.

SQLite databases are locked while a transaction is being committed if many connections are accessing the same database, and one of those processes is making changes to the database. The timeout value indicates the time the connection has before throwing an exception because the lock has been released. The timeout value is 5.0 by default (five seconds).

2.connection.cursor([cursorClass])

Using this function, you can quickly and efficiently generate a cursor for usage across your Python database applications. The cursor class is the only optional parameter this method will take. If specified, this must be a custom cursor class that extends sqlite3.Cursor.

3.cursor.execute(SQL [, optional parameters])

This procedure runs the SQL statement. Parameterization of the SQL statement is possible (i. e., placeholders instead of SQL literals). Sqlite3 allows both question marks and named placeholders (named style).

Example − cursor.execute("insert into people values (?, ?)", (who, age))

4.connection.execute(SQL [, optional parameters])

It is a shortened version of the cursor's execute method. This method first invokes the cursor method and then calls the cursor's execute method with the specified arguments. Execute comes after the cursor.

5.cursor.execute many(SQL, seq_of_parameters)

This procedure will execute a SQL command once it has been performed against any parameter sequences or mappings that are found in the sequence sql. The sequence sql is the location of the parameter sequences and mappings.

6.connection.execute many(sql[, parameters])

This shortcut uses the cursor method to create a temporary cursor object and then uses those parameters to call the cursors to execute many functions.

7. cursor.execute script(sql_script)

If many SQL statements are provided in script format, this method will execute them simultaneously. After the COMMIT command, the SQL script sent as an argument is executed. Each SQL statement needs to be separated by a semicolon (;).

8. connection.execute script(sql_script)

This method is a shortcut that, after generating an interim cursor object by invoking the cursor method, then invoking the execute script method of the cursor using the arguments provided. The cursor method generates an interim cursor object by using the cursor method.

9. connection.total_changes()

This procedure provides a return value that indicates the total number of database rows altered, inserted, or removed from the database since the connection to the database was initially opened. This value can be used to track changes made to the database after the procedure has been completed.

10. connection.commit()

The currently active transaction is committed using this approach. If you do not call this method, any changes you have made to the database since your previous call to commit() will not be visible to other connections.

Connecting a Database

The below-mentioned python code displays how to connect the existing database. If the database is non-existent, then it will return.

#!/usr/bin/python
import sqlite3
conn = sqlite3.connect('test.db')
print "Opened database successfully";

Creating a Database

With Python SQLite, you can quickly create and query databases, store data in various formats, and use powerful SQL queries to manipulate the data. This makes it an ideal choice for applications such as web development, mobile development, data analysis, and machine learning. 

Now that we’re connected to the database, let’s create a table. Tables are where we store our data in SQLite. We can create a table using the CREATE TABLE SQL statement. 

Let’s create a table for storing information about our users. 

We’ll call it “users”. The table will have three columns: id (integer), name (text), and email (text). sql = """ CREATE TABLE users ( id INTEGER PRIMARY KEY, name TEXT, email TEXT ) """ conn.execute(sql)

The below-mentioned code will help in creating a table

import sqlite3
conn = sqlite3.connect('test.db')
print "Opened database successfully";
conn.execute('''CREATE TABLE COMPANY
         (ID INT PRIMARY KEY     NOT NULL,
         NAME           TEXT    NOT NULL,
         AGE            INT     NOT NULL,
         ADDRESS        CHAR(50),
         SALARY         REAL);''')
print "Table created successfully";
conn.close()

When the above program is executed, it will create the COMPANY table in your test.db and it will display the following messages −

Opened database successfully
Table created successfully

Inserting Data to a Database

Once we have our table, we can start inserting data into it.  We can do this using the INSERT INTO SQL statement. 

The below-mentioned code shows how to create records in the company

#!/usr/bin/python
import sqlite3
conn = sqlite3.connect('test.db')
print "Opened database successfully";
conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (1, 'Paul', 32, 'California', 20000.00 )");
conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (2, 'Allen', 25, 'Texas', 15000.00 )");
conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (3, 'Teddy', 23, 'Norway', 20000.00 )");
conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 )");
conn.commit()

When the above program is executed, it will create the given records in the COMPANY table and it will display the following two lines −

print "Records created successfully";
conn.close()

Selecting Data to a Database

The below-mentioned code shows the way to fetch and display records from the COMPANY table 

#!/usr/bin/python
import sqlite3
conn = sqlite3.connect('test.db')
print "Opened database successfully";
cursor = conn.execute("SELECT id, name, address, salary from COMPANY")
for row in cursor:
   print "ID = ", row[0]
   print "NAME = ", row[1]
   print "ADDRESS = ", row[2]
   print "SALARY = ", row[3], "\n"
print "Operation done successfully";
conn.close()

After the program as mentioned earlier is executed, it will show the following result.

Opened database successfully
ID = 1
NAME = Paul
ADDRESS = California
SALARY = 20000.0
ID = 2
NAME = Allen
ADDRESS = Texas
SALARY = 15000.0
ID = 3
NAME = Teddy
ADDRESS = Norway
SALARY = 20000.0
ID = 4
NAME = Mark
ADDRESS = Rich-Mond
SALARY = 65000.0
Operation is done successfully.

Update Data to a Database

We can also update data that’s already in our table. 

The below-mentioned code shows how to use an update statement for updating any records

#!/usr/bin/python
import sqlite3
conn = sqlite3.connect('test.db')
print "Opened database successfully";
conn.execute("UPDATE COMPANY set SALARY = 25000.00 where ID = 1")
conn.commit()
print "Total number of rows updated :", conn.total_changes
cursor = conn.execute("SELECT id, name, address, salary from COMPANY")
for row in cursor:
   print "ID = ", row[0]
   print "NAME = ", row[1]
   print "ADDRESS = ", row[2]
   print "SALARY = ", row[3], "\n"
print "Operation done successfully";
conn.close()

When the above program is executed, it will produce the following result.

Opened database successfully
Total number of rows updated: 1
ID = 1
NAME = Paul
ADDRESS = California
SALARY = 25000.0
ID = 2
NAME = Allen
ADDRESS = Texas
SALARY = 15000.0
ID = 3
NAME = Teddy
ADDRESS = Norway
SALARY = 20000.0
ID = 4
NAME = Mark
ADDRESS = Rich-Mond
SALARY = 65000.0
Operation is done successfully.

Deleting Data From a Database

We can also delete data from our table.  The below-mentioned code shows us how to use the delete statement

#!/usr/bin/python
import sqlite3
conn = sqlite3.connect('test.db')
print "Opened database successfully";
conn.execute("DELETE from COMPANY where ID = 2;")
conn.commit()
print "Total number of rows deleted :", conn.total_changes
cursor = conn.execute("SELECT id, name, address, salary from COMPANY")
for row in cursor:
   print "ID = ", row[0]
   print "NAME = ", row[1]
   print "ADDRESS = ", row[2]
   print "SALARY = ", row[3], "\n"
print "Operation done successfully";
conn.close()

The code, as mentioned earlier, will display the following result.

Opened database successfully
Total number of rows deleted: 1
ID = 1
NAME = Paul
ADDRESS = California
SALARY = 20000.0
ID = 3
NAME = Teddy
ADDRESS = Norway
SALARY = 20000.0
ID = 4
NAME = Mark
ADDRESS = Rich-Mond
SALARY = 65000.0
Operation is done successfully.

Querying Data in a Table

We can also query data from our table. This is done with the SELECT SQL statement. 

Let’s say we want to get all of the records from our table. We can do this with the following code:

SQL statement: sql = """ SELECT * FROM users """ conn.execute(sql)

he Requests library is a popular library for making HTTP requests in Python. It’s simple to use and easy to get started with. 

To use SQLite with Requests, we’re going to need to install the Requests-SQLite library. We can install it using pip: $ pip install requests-SQLite Once the library is installed, we can use it to make SQLite requests. We need first to create a Connection object and then pass it to the SQLite () function. We can then use the Connection object to make SQLite requests. 

For example: 

Let’s say we want to query the user's table. We can do this with the following code: 

import requests import requests_sqlite conn = requests_sqlite.Connection('example.db') sql = """ SELECT * FROM users """ r = requests.get('https://example.com/api/v1/users', params={'query': sql}) data = r.json() 9.

Python SQLite Data Types

The number of available data types in SQLite for Python is smaller compared to other SQL implementations. In some ways, this is limiting. But as you'll discover, SQLite simplifies a lot more than just database management. First, let's have a glance at the various data types:

1. INTEGER — Includes an integer
2. NULL — Includes a NULL value
3. TEXT — Includes text
4. REAL — Includes a floating-point (decimal) value
5. BLOB — Includes a binary large object that is stored exactly as input

Python SQLite FAQs 

1. What is Python SQLite used for?

Some programs can use Python SQLite for internal data storage.

2. Is SQLite good for Python?

The PySQLite gives the SQLite database a standard Python DBI API 2.0 compliant interface. PySQLite is a good choice if your application needs to support the SQLite database and other databases like MySQL, PostgreSQL, and Oracle.

3. Is Python faster than SQL Server?

SQL is typically faster than Python for querying data warehouses and performing basic aggregations. This is primarily due to the schema applied to the data and the proximity of computation to the data.

4. Is SQLite included in Python?

Yes, SQLite is included with Python and can be utilized in any Python application without installing additional software.

5. Why is SQLite better than MySQL?

For MySQL to work, it needs a database server. So, it uses a client/server structure. SQLite didn't require a server to run. So, it doesn't have any servers.

6. Is SQLite a frontend or a backend?

SQLite will work well as a website's database backend. But if the website has a lot of writing or is so busy that it needs more than one server, you might want to use a server/client database engine instead of SQLite.

7. Is SQLite a JSON?

SQLite stores JSON like any other text file. Due to rules about backward compatibility, SQLite can only store integers, NULL, text, floating-point numbers, and BLOBs. There is no way to add a sixth "JSON" type. At the moment, SQLite does not support a binary version of JSON.

8. Can you use Python instead of SQL?

Python and SQL can do some of the same things, but developers usually use SQL when they need to work directly with databases and Python for more general programming tasks. Whether you use English or another language depends on the question you want to answer.

9. Is SQLite 32 or 64-bit?

ODBC Driver for SQLite is compatible with 32-bit and 64-bit programs on x32 and x64 platforms. Therefore no additional configuration of the driver, apps, or environment is required.

10. How do I run SQLite in Python?

Step 1: In Python, we must first import the sqlite3 module.

Step 2: Create the database and connect to it.

Step 3: After connecting to the database, create the cursor object.

Step 4: Create an executable SQL query.

Step 5: Enter the cursor object to use.

Conclusion

Python SQLite is the most popular database system available today.  Python SQLite enables developers to easily create, update, delete and query data from the database. With its simple syntax and intuitive code structure, Python SQLite helps developers write complex queries with ease.  Visit Mindmajix - A Global Online Training Platform for the "Python Training Course” if you would like to become a Python-certified professional.