Python-An introduction to SQLite3














































Python-An introduction to SQLite3



Preview:

Python-SQLite3
SQLite3 can be integrated with Python by using sqlite3 module that comes with Python version
 2.5.x onwards and needs no extra installations. To use sqlite3 module, you must first create
 a connection object that represents the database and then optionally you can create a cursor 
object, which will help you in executing all the SQL statements.

Creating and connecting to a database:
The Python code below connects to a database named 'MyDB.db' and will create this database if it doesn't exist. 
This will return a database object.

#!/usr/bin/python
import sqlite3
conn = sqlite3.connect('MyDB.db')
print "Opened database successfully";

Creating a table:
To create a table name 'PHONEBOOK' in previously created database 'MyDB.db', you add the 
following commands to the above code:

conn.execute('''CREATE TABLE PHONEBOOK (ID INT PRIMARY KEY     NOT NULL,
         FIRST_NAME           TEXT    NOT NULL,
         LAST_NAME           TEXT    NOT NULL,
         PHONE_NUMBER          TEXT     NOT NULL,
         ADDRESS     TEXT);''')
print ("Table created successfully")

Inserting record in the table :
To insert records in the table 'PHONEBOOK', add the following command:

conn.execute("INSERT INTO PHONEBOOK(ID,FIRST_NAME,LAST_NAME,PHONE_NUMBER,ADDRESS) VALUES(?,?,?,?,?)", values)
conn.commit()
print("Record inserted")

If you look at the code above, the statement 'conn.commit()' is required to complete any 
changes being made to the table when using INSERT,UPDATE and DELETE operations.This method 
commits the current transaction. If you don't call this method, anything you did since the 
last call to commit() is not visible from other database connections.

Selecting records from a table:
Add the following command to select all records from table PHONEBOOK and print each record
 row-wise:

cursorObj = conn.cursor()
cursonObj.execute("SELECT * FROM PHONEBOOK")
rows = cursorObj.fetchall()
for row in rows:
     print(row)
     
conn.cursor([cursorClass]) routine creates a cursor which will be used throughout of your
 database programming with Python.
This method accepts a single optional parameter cursorClass. If supplied, this must be a
custom cursor class that extends sqlite3.Cursor. cursor.fetchall() routine fetches all 
(remaining) rows of a query result, returning a list. An empty list is returned when no rows
are available.The SELECT operation can also be used with the WHERE clause.

Updating record:
To update a record,execute the following code:
conn.execute("UPDATE PHONEBOOK set ADDRESS = 'NEW YORK' where ID = 1")
conn.commit

Deleting a record:
Deleting a certain record is done in the following commands:

conn.execute("DELETE from PHONEBOOK where ID = 2;")
conn.commit()

Here is a simple phone book program that allows you to search a record based on phone number,
see all records and add new records.The following code is saved to a file Phonebook.py .

import sqlite3

class Phonebook():
    # create the database connection
    def __init__(self,DB):
        self.DB=DB
        conn=sqlite3.connect(self.DB)
        self.conn=conn
        print('Connected to DB '+ self.DB)
    #create the table  and if the table exists don't call this method,will give you error
    def createTable(self):
        self.conn.execute('''CREATE TABLE PHONEBOOK (ID INT PRIMARY KEY     NOT NULL,
         FIRST_NAME           TEXT    NOT NULL,
         LAST_NAME           TEXT    NOT NULL,
         PHONE_NUMBER          TEXT     NOT NULL,
         ADDRESS     TEXT);''')
        print ("Table created successfully")
    #insert a record into the table
    def insertIntoTable(self,values):
        cursorObj = self.conn.cursor()
        cursorObj.execute("INSERT INTO PHONEBOOK(ID,FIRST_NAME,LAST_NAME,PHONE_NUMBER,ADDRESS) VALUES(?,?,?,?,?)", values)
        self.conn.commit()
        print("Record inserted")
    #shows all records of the table,will show nothing if the table is empty
    def showAll(self):
        cursorObj = self.conn.execute("SELECT * FROM PHONEBOOK")
        rows = cursorObj.fetchall()
        for row in rows:
              print(row)
    #search a record based on phonenumber and table name
    def searchPhoneNumber(self,number):
        cursorObj = self.conn.execute("SELECT * FROM PHONEBOOK WHERE PHONE_NUMBER='"+ number+"'")
        row,rows=None,None
        rows = cursorObj.fetchone()
        if rows is None:
            print("NO RECORDS FOUND")
        else:
             for row in rows:
                print(row)
def main():
    db="MyDB.db"
    pb=Phonebook(db)        
    choice='x'          #USER INPUT FOR THE OPERATION
    while choice!='0':
     choice=input("Enter 1 to insert record,2 to see all records,3 to search by phone number,0 to quit")
     if choice=='1':
        id=input("Enter ID:")
        fname=input("Enter first name")
        lname=input("Enter last name")
        number=input("Enter phone number")
        addr=input("Enter address")
        values=(id,fname,lname,number,addr)
        pb.insertIntoTable(values)
     if choice=='2':
        pb.showAll(table)
     if choice=='3':
        num=input("Please enter phone number to search")
        pb.searchPhoneNumber(table,num)
    print("Closing connection")

if __name__=='__main__':
    main()


Here a screenshot of the execution of the above program:


Comments