SQLite – Python

SQLite - Python

Installation

SQLite3 can be integrated with Python using the sqlite3 module. T he sqlite3 module was written by Gerhard Haring. I t provides a SQL interface that is compatible with the DB-API 2.0 specification described by PEP 249. You do not need to install the module separately, as the Python 2.5.x version above brings the module by default.

In order to use the sqlite3 module, you must first create a connection object that represents the database, and then you can selectively create a cursor object, which will help you execute all the SQL statements.

Python sqlite3 module API

Here are some important sqlite3 module programs to meet your needs for using the SQLite database in your Python program. If you need more details, check out the official documentation for the Python sqlite3 module.

Connect to the database

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

#!/usr/bin/python

import sqlite3

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

print "Opened database successfully";

Here, you can also copy the database name to a specific name: memory: , which creates a database in RAM. N ow, let's run the program above and create our database test in the .db. Y ou can change the path as needed. S ave the above code sqlite.py file and execute as follows. If the database is successfully created, the message shown below is displayed:

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

Create a table

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

#!/usr/bin/python

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 executes, it creates a COMPANY .db in the test table and displays the message shown below:

Opened database successfully
Table created successfully

INSERT operation

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

#!/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()
print "Records created successfully";
conn.close()

When the above program executes, it creates a given record in the COMPANY table and displays the following two lines:

Opened database successfully
Records created successfully

SELECT operation

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

#!/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()

When the above procedure is executed, it produces the following results:

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 done successfully

UPDATE operation

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

#!/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 procedure is executed, it produces the following results:

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 done successfully

DELETE operation

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

#!/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()

When the above procedure is executed, it produces the following results:

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 done successfully