08_Python_SQLite.md

Python with SQLite

1. Connecting to database

Demonstrates the connecting to the database test.db. If test.db does not exist, it will be created and connection object (conn) is returned.

import sqlite3

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

2. Creating table

After making a connection, you can create a table by wrapping the SQL CREATE TABLE query

import sqlite3

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

conn.execute('''CREATE TABLE Student (
  Id INT PRIMARY KEY NOT NULL, 
  Name VARCHAR(30) NOT NULL, 
  DoB DATE NOT NULL, 
  Degree VARCHAR(20)); ''')
  
conn.close()

3. Insert into table

import sqlite3

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

conn.execute( "INSERT INTO Students VALUES (1, 'Ashok', '2000-10-27', 'M.Tech')" )

conn.execute( "INSERT INTO Students (Name, Id, Degree, DoB) VALUES ('Binu', 2, 'M.Tech', '2000-07-15')" )

conn.execute( "INSERT INTO Students (Id, Name, Degree, DoB) \
        VALUES (3, 'Caesar', 'B.Tech', '2002-02-09')" )

conn.execute( "INSERT INTO Students VALUES \
        (4, 'Daniel', '2002-02-09', 'M.C.A')" )
        
conn.commit()

conn.close()

Exercises

• Write a program which will read the input from the user and insert values into the table. Add at least 6 more rows.
  • Input format: 10 Jaydeep 2001-11-23 M.C.A
• Improvise the program to check if the input entered is in correct. Examples of incorrect input.
  • 11 Krishna M.Tech 2000-05-09 (Degree and DoB are out of order)
  • -5 Gopi B.Tech 2002-12-29 (Roll number is the negative)
  • 20 abcdefghijklmnopqrstuvwxyz0123456789 B.Tech 2003-05-11 (Name has more than 30 chars)

4. Retrieve from table

import sqlite3

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

cursor = conn.execute("SELECT Id, Name, DoB, Degree FROM Students")
for row in cursor:
   print( "Id = ", row[0], " | Name = ", row[1], " | DoB = ", row[2], " | Degree = ", row[3] )
   # print( row )  # Comment out above and uncomment this, check the output
   
conn.close()

Exercises

• Count the number of rows in the table.
• Count the number of rows who enrolled to M.Tech degree.

5. Update table

import sqlite3

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

conn.execute("UPDATE Students SET Degree = 'B.E.' WHERE Degree = 'B.Tech'")
conn.commit()
print("Total number of rows updated :", conn.total_changes)

cursor = conn.execute("SELECT Id, Name, DoB, Degree FROM Students WHERE Degree = 'B.E.'")
for row in cursor:
   print(row)

conn.close()

6. Delete from table

import sqlite3

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

conn.execute("DELETE FROM Students WHERE Id = 4")
conn.commit()
print("Total number of rows deleted :", conn.total_changes)

cursor = conn.execute("SELECT * FROM Students")
for row in cursor:
   print(row[0], row[1])

conn.close()