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:
Video : https://youtu.be/tCfL5NGaUFU