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