Python Tutorial

Introduction to Creating Table in Python SQLite

How to Create a Table in Python SQLite?

The following Python program will be used to construct a table in the database that has already been built.

import sqlite3

conn = sqlite3.connect('Demodb')

print "Opened database successfully";

conn.execute("ADD INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \

      VALUES (1, 'Sparsh', 25, 'india', 10000.00 )");

conn.execute("ADD INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \

      VALUES (2, 'Bhagirath', 26, 'india', 19000.00 )");

conn.execute("ADD INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \

      VALUES (3, 'Bharat', 24, 'india', 5000.00 )");

conn.execute("ADD INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \

      VALUES (4, ‘Gaurav', 25,'india', 75000.00 )");

conn.commit()

print "We have successfully created the records";

conn.close()

When the above program is run, it will add the given records in the COMPANY table and show the following two lines:

Opened database successfully

We have successfully created the records

Video : https://youtu.be/ng8L5n6r4kw

How to Insert Record Into SQLite Table?

 

The Python program below demonstrates how to add records to the COMPANY table generated in the previous example.

import sqlite3

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

print "Opened database successfully";

conn.execute("ADD INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \

      VALUES (1, Sparsh, 25, 'india', 10000.00 )");

conn.execute("ADD INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \

      VALUES (2, 'Bhagirath', 26, 'india', 19000.00 )");

conn.execute("ADD INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \

      VALUES (3, 'Bharat', 24, 'india', 5000.00 )");

conn.execute("ADD INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \

      VALUES (4, ‘Gaurav', 25,'india', 75000.00 )");

conn.commit()

print "Records created successfully";

conn.close()

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

Opened database successfully

Records created successfully

Video : https://youtu.be/X2wDnnZY0d4

 

Select Operation Into SQLite Table

 

The Python code below demonstrates how to retrieve and display records from the COMPANY table generated in the previous example.

#!/usr/bin/python

import sqlite3

conn = sqlite3.connect('demo.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 aforementioned program is run, the following result is obtained.

Opened database successfully

ID = 1

NAME = Sparsh

ADDRESS = india

SALARY = 10000.0

ID = 2

NAME = Bhagirath

ADDRESS =  india

SALARY = 19000.0

ID = 3

NAME = Bharat

ADDRESS =  india

SALARY = 5000.0

ID = 4

NAME = Gaurav

ADDRESS =  india

SALARY = 75000.0

Operation done successfully

Video : https://youtu.be/aRIFXl1Rw_k

How to Update Records in SQLite Table?

The Python code below demonstrates how to update any record using the UPDATE command, then get and display the modified entries from the COMPANY table.

#!/usr/bin/python

import sqlite3

conn = sqlite3.connect('Demo.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 aforementioned program is run, the following result is obtained.

Opened database successfully

Total number of rows updated : 1

ID = 1

NAME = Sparsh

ADDRESS = india

SALARY = 25000.0

ID = 2

NAME = Bhagirath

ADDRESS =  india

SALARY = 19000.0

ID = 3

NAME = Bharat

ADDRESS =  india

SALARY = 5000.0

ID = 4

NAME = Gaurav

ADDRESS =  india

SALARY = 75000.0

Operation done successfully.

Video : https://youtu.be/vvOX83gCk2Q

 

How to Delete Records in Table in SQLite?

 

The Python code below demonstrates how to delete any record using the DELETE command. The remaining records from the COMPANY database will then be retrieved and displayed.

import sqlite3

conn = sqlite3.connect('Demo.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()

Output:

Opened database successfully
Total number of rows deleted : 1
ID = 1
NAME = Sparsh
ADDRESS = india
SALARY = 20000.0
ID = 3
NAME = Bharat
ADDRESS =  india
SALARY = 5000.0
ID = 4
NAME = Gaurav
ADDRESS =  india
SALARY = 75000.0
Operation done successfully

Video : https://youtu.be/tCfL5NGaUFU

Did you find this article helpful?