Issuing delete query in Python script

Lesson Details:
June 29, 2020


I: Introduction

With the advancement in technology, Python language has also taken the lead as one of the most popular programming languages. One can easily learn to code and develop a variety of programs and scripts with it. While learning any new thing first we need to understand the basics and only then we can move forward to do something different and that is why we need basic python programming for beginners.

Python is a high level language and has features like strong typing, dynamic typing, built-in data types and automatic memory management. Another amazing feature of Python programming language is its simplicity and readability. This language has been developed by Guido van Rossum in 1989 and it is an open source language. Perhaps this is the reason why it is so popular among developers and has become an industry standard language for developing web applications and enterprise software.

II: Body

Issuing delete query in python script:

Let us start this section by creating a database and table. The database name will be ‘test’ and table name will be ‘student’. Let us create a table student with three fields as shown below:

Name: CHARACTER(20)

Id: INTEGER

Grade: CHARACTER(3)

Let us now write a simple Python script using sqlite3 module for this database as follows:

#!/usr/bin/env python

import sqlite3 as conn

conn.connect(“test.db”)

cursor = conn.cursor()

cursor.execute(“create table student(id integer primary key autoincrement not null, name varchar(20), grade integer)”)

The above script creates a table named ‘student’ in test.db file with three fields as mentioned above. Now let us insert some records in the ‘student’ table using insert query as follows:

#!/usr/bin/env python

import sqlite3 as conn

conn.connect(“test.db”)

cursor = conn.cursor()

cursor.execute(“create table student(id integer primary key autoincrement not null, name varchar(20), grade integer)”)

result = cursor.execute(“insert into student values (1, ‘Nikesh’, 10)”) # replace Nikesh with your own name if needed! This line inserts one record into the student table with field values as mentioned. You can see the result of this query by printing the result object as follows: print result To fetch records from the student table, first you need to create a cursor object as follows: cursor = conn.cursor() Then use execute method of the ‘cursor’ object to execute select query as follows: cursor.execute(“select * from student;”) This line executes select query on the student table and fetches records. Now let us print all records of the student table using fetchall method of cursor object as follows: print cursor.fetchall() Above line prints all records of the student table. Now let us delete some records from the student table using delete query as follows: #!/usr/bin/env python import sqlite3 as conn conn.connect(“test.db”) cursor = conn.cursor() cursor.execute(“select * from student;”) result = cursor.execute(“delete from student where grade = 9;”) print result If you are able to remove records from the student table then congratulations! You have done it! Otherwise, try again until you succeed! That was all about issuing delete query in python script! For more information you can refer to official documentation of SQLite3 which is available at http://www.sqlite.org/docs.html or post your queries here below for further assistance!

loader
Course content