SQLite Python Tutorial

Here we will learn how to use SQLite in PYTHON programming language to create database, tables and to perform INSERT, UPDATE, DELETE and SELECT operations on SQLite database using pysqlite extension in PHP with examples.

SQLite in Python Interface

By using python pysqlite interface we can perform various operations on SQLite3 databases like connect to the database, create tables, INSERT, UPDATE, DELETE and SELECT operations based on our requirements.

 

Pysqlite module offers a standardized Python DB-API 2.0 compliant interface to the SQLite database engine. If you are using Python version 2.5.x then by default Pysqlite module available we don’t need to add pysqlite interface separately.

 

In case if you want to add manually then download PySQLite from this site pysqlite interface for sqlite.

Connect to SQLite Database in Python

Following is the python code to connect the database if it exists otherwise it will create a new database and then connect to it.

 

import sqlite3

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

print "Database opened!!!";

The above code will create new database “DBUSingPython.db” in current program directory and return output like as shown below.

 

Database opened!!!

Create Table in SQLite Database using Python

Now we will create a new table called “Product” in previously created database named DBUsingPython.db using Python programming.

 

Write the python code like as shown following to create a new sqlite table “Product” in the database “DBUsingPython.db”.

 

import sqlite3

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

cur = con.cursor()

cur.execute("CREATE TABLE Product (p_id INTEGER PRIMARY KEY AUTOINCREMENT,p_name TEXT NOT NULL,price REAL,quantity INTEGER)");

print "Table created!!!";

con.close()

The above python program will create new table “Product” in “DBUsingPython.db” sqlite database and return the output like as shown following.

 

Table created!!!

Insert Data in SQLite Table using Python

Now we will insert data in previously created SQLite table called “Product” using python for that write the code like as shown below.

 

import sqlite3

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

cur = con.cursor()

cur.execute("INSERT INTO Product(p_name,price,quantity) VALUES('AutoCAD',200,20)");

cur.execute("INSERT INTO Product(p_name,price,quantity) VALUES('Quick Hill',330,12)");

cur.execute("INSERT INTO Product(p_name,price,quantity) VALUES('Keyboard',250,25)");

cur.execute("INSERT INTO Product(p_name,price,quantity) VALUES('Mouse',150,34)");

print "Data Inserted!!!";

con.commit()

con.close()

When we execute above python program it will insert data in “Product” table.

Display data from SQLite Table in Python

Now we will display or show the data from SQLite table called “Product” using python for that write the code like as shown below.

 

import sqlite3

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

cur = con.cursor()

print "p_id \t p_name \t price \t quantity\n";

cursor=cur.execute("SELECT * FROM Product");

for row in cursor:

print row[0], "\t" ,row[1], "\t" ,row[2], "\t" ,row[3], "\n" ;

con.close()

When we execute above python program we will get result like as shown below.

 

p_id    p_name          price   quantity

----    ---------       ------  --------             

1       AutoCAD         200.0   20                              

2       Quick Hill      330.0   12                            

3       Keyboard        250.0   25                               

4       Mouse           150.0   34

Update SQLite Table Data using Python

Now we will update the “Product” table quantity value by 20% more for that we need to write the code as shown below.

 

import sqlite3

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

cur = con.cursor()

cur.execute("UPDATE Product SET

quantity=quantity+(quantity*0.2)")

con.commit()

print "p_id \t p_name \t price \t quantity\n";

cursor=cur.execute("SELECT * FROM Product");

for row in cursor:

print row[0], "\t" ,row[1], "\t" ,row[2], "\t" ,row[3], "\n" ;

con.close()

When we execute above program it will update quantity column and show the records like as shown below.

 

p_id    p_name          price   quantity  

----    ---------       ------  ------                                                 

1       AutoCAD         200.0   24                              

2       Quick Hill      330.0   14.4                            

3       Keyboard        250.0   30                               

4       Mouse           150.0   40

Delete SQLite Table data using Python

Now we will delete records from sqlite table using python programming language for that write the code like as shown below to delete records from “Product” table and return the result like as shown below.

 

p_id    p_name          price   quantity

-----   ---------       ------  -----------                                           

1       AutoCAD         200.0   24                              

2       Quick Hill      330.0   14.4                            

3       Keyboard        250.0   30                               

This is how we can use SQLite databases in Python programming language to connect, create, database, tables and insert, update, delete, and display data based on our requirements. 

Previous