Program to demonstrate CRUD (create, read, update and delete) operations on database (SQLite/ MySQL) using python.

Database CRUD Operations on SQLite Database using Python

• Python has built-in support for SQLite database through the sqlite3 module.

• The CRUD represents the basic database operations such as Create, Read, Update and Delete.

• SQLite database is an open-source and self-configured relational database engine that doesn't require a server configuration like other databases.

• SQLite is freely downloadable from its official website.

• To create a new SQLite database, navigate from the command prompt to the folder where you have unzipped the archive and enter the commands to perform the required operations.

• The first step is to create a database in SQLite using a command prompt.

• Here, we create the database named "database.db" and after creating this any SQL query will be executed by using Python.

• To do this first required to connect with SQLite database.

  • The sqlite3 module needs to be imported first. import sqlite3
  • Then connect() function needs to be executed. db=sqlite3.connect('database.db')

Methods used in the Python Program to perform various SQLite database operations are as follows:

Basic SYNTAX for execute() method:

try:
    cur=db.cursor()
    cur.execute("SQL Query")
    db.commit()
    print ("Success Message")
except:
    print ("Error Message")
    db.rollback()
db.close()

1] CREATE a Table in SQLite

• A string enclosing the CREATE TABLE query is passed as a parameter to the execute() method of the cursor object.
• The following code creates the "book" table in the “database.db” database.

import sqlite3
db=sqlite3.connect('database.db')
try:        
    cur =db.cursor()
    cur.execute('''CREATE TABLE book (
    BookID INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT (20) NOT NULL,
    author TEXT (20),
    publisher TEXT (20));''')
    print ('Table Created Successfully')
except:
    print ('Error in Operation')
    db.rollback()
db.close()

• If the "book" table is created successfully in SQLite then the program shows the message as Table Created Successfully otherwise shows an Error in Operation message.
• This can be verified using the .tables command in the SQLite shell as follows:

• This shows a table named "book" because it is created successfully using the python program.

2] Insert a Record into Table

• The execute() method of the cursor object should be called with a string argument representing the INSERT query syntax.
• An earlier table named "book" is created having three fields: title, author, and publisher.
• Therefore string holding the INSERT query can be written as follows:

import sqlite3
db=sqlite3.connect('database.db')
qry="insert into book (title, author, publisher) values('Internet Programming', 'Binita Mayekar', 'Ques10');"
try:
    cur=db.cursor()
    cur.execute(qry)
    db.commit()
    print ("One Record Added Successfully")
except:
    print ("Error in operation")
    db.rollback()
db.close()

• If data is inserted into the table successfully then the program shows the message as One Record added successfully otherwise shows an Error in Operation message.
• Similarly, n number of data records added to the database table.

3] READ Records from the Table

• The query string holds a SELECT query, the execute() method forms a result set object containing the records returned.
• Here, the fetchone() method is used to read the data from the table.
• The same operations can also be performed using the fetchall() method.

import sqlite3
db=sqlite3.connect('database.db')
sql="SELECT * from book;"
cur=db.cursor()
cur.execute(sql)
while True:
    record=cur.fetchone()
    if record==None:
        break
    print (record)
db.close()

• This program shows the following output:

• As we inserted two more records this shows a total of 3 records from the table “book”.

4] UPDATE a Record in the Table

• The query string in the execute() method should contain an UPDATE query syntax.
• The below program updates the value of the 'author' attribute as AUTHOR where 'title = abc' in the “book” table.

import sqlite3
db=sqlite3.connect('database.db')
qry="update book set author=? where title=?;"
try:
    cur=db.cursor()
    cur.execute(qry, ('AUTHOR','abc'))
    db.commit()
    print("Record Updated Successfully")
except:
    print("Error in Operation")
    db.rollback()
db.close()

• If data is updated successfully into the table then the program shows the message as Record Updated Successfully otherwise shows an Error in Operation message.
• This can be verified using the select *book command in SQLite shell OR again run fetchone() or fectchall() method of python program to check table data.

• Here, we READ records from the table "book" again using the python program that shows us updated Record for the third row where title = abc.

5] DELETE a Record from the Table

• The query string should contain the DELETE query syntax.
• The below code is used to delete the record for 'publisher = pqr' from the “book” table.

import sqlite3
db=sqlite3.connect('database.db')
qry="DELETE from book where publisher=?;"
try:
    cur=db.cursor()
    cur.execute(qry, ('pqr',))
    db.commit()
    print("Record Deleted Successfully")
except:
    print("Error in Operation")
    db.rollback()
db.close()

• If data is deleted successfully from the table then the program shows the message as Record Deleted Successfully otherwise shows an Error in Operation message.
• This can be verified using the select *book command in SQLite shell OR again run fetchone() or fectchall() method of python program to check table data.

• Here, we verified this by using the select *book command in SQLite shell using the command prompt that shows the third row where publisher = pqr deleted from the table.