SQLite - Python

Installation

SQLite3 can be integrated with Python using the sqlite3 module. The sqlite3 module was written by Gerhard Haring. It provides an SQL interface compliant with the DB-API 2.0 specification described by PEP 249. You do not need to install this module separately, as it is shipped by default with Python 2.5.x and later versions.

To use the sqlite3 module, you must first create a connection object that represents the database, and then optionally create a cursor object, which will help you execute all SQL statements.

Python sqlite3 Module API

The following are important sqlite3 module procedures that satisfy the requirements for using SQLite databases in Python programs. For more details, please refer to the official documentation of the Python sqlite3 module.

Connecting to the Database

The following Python code shows how to connect to an existing database. If the database does not exist, it will be created, and finally, a database object will be returned.

Example

#!/usr/bin/python

import sqlite3

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

print("Database opened successfully")

Here, you can also give the database a specific name :memory:, which creates a database in RAM. Now, let's run the above program to create our database test.db in the current directory. You can change the path as needed. Save the above code in a file named sqlite.py and execute it as shown below. If the database is created successfully, the following message will be displayed:

$chmod +x sqlite.py
$./sqlite.py
Open database successfully

Creating a Table

The following Python code snippet will be used to create a table in the previously created database:

Example

#!/usr/bin/python

import sqlite3

conn = sqlite3.connect('test.db')
print("Database opened successfully")
c = conn.cursor()
c.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.commit()
conn.close()

When this program is executed, it will create the COMPANY table in test.db and display the following message:

Database opened successfully
Table created successfully

INSERT Operation

The following Python program shows how to create records in the COMPANY table created above:

Example

#!/usr/bin/python

import sqlite3

conn = sqlite3.connect('test.db')
c = conn.cursor()
print("Database opened successfully")

c.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
          VALUES (1, 'Paul', 32, 'California', 20000.00 )")

c.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
          VALUES (2, 'Allen', 25, 'Texas', 15000.00 )")

c.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
          VALUES (3, 'Teddy', 23, 'Norway', 20000.00 )")

c.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
          VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 )")

conn.commit()
print("Records inserted successfully")
conn.close()

When the above program is executed, it will create a given record in the COMPANY table and display the following two lines:

Database opened successfully
Data inserted successfully

SELECT Operation

The following Python program shows how to retrieve and display records from the COMPANY table created earlier:

Example

import sqlite3

conn = sqlite3.connect('test.db')
c = conn.cursor()
print("Database opened successfully")

cursor = c.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("Data operation successful")
conn.close()

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

Database opened 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

Data operation successful

UPDATE Operation

The following Python code shows how to use the UPDATE statement to update any record, and then retrieve and display the updated records from the COMPANY table:

Example

#!/usr/bin/python

import sqlite3

conn = sqlite3.connect('test.db')
c = conn.cursor()
print("Database opened successfully")

c.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("Data operation successful")
conn.close()

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

Database opened 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

Data operation successful

DELETE Operation

The following Python code shows how to use the DELETE statement to delete any record, and then retrieve and display the remaining records from the COMPANY table:

Example

#!/usr/bin/python

import sqlite3

conn = sqlite3.connect('test.db')
c = conn.cursor()
print("Database opened successfully")

c.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("Data operation successful")
conn.close()

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

Database opened successfully
Total number of rows deleted : 1
ID =  1
NAME =  Paul
ADDRESS =  California
SALARY =  25000.0

ID =  3
NAME =  Teddy
ADDRESS =  Norway
SALARY =  20000.0

ID =  4
NAME =  Mark
ADDRESS =  Rich-Mond
SALARY =  65000.0

Data operation successful

print "SALARY = ", row[3], "\n"

print ("Data operation successful")
conn.close()

When the above program is executed, it produces the following result:

Database opened 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

Data operation successful