SQL Query Using Python

SQL Query Using Python

The time has come now to finally query our “Student” table. Fetching the data from record is as simple as inserting them. The execute method uses the SQL command to get all the data from the table.

SELECT Query

“Select” is the most commonly used statement in SQL. The SELECT Statement in SQL is used to retrieve or fetch data from a table in a database. The syntax for using this statement is “Select * from table_name” and all the table data can be fetched in an object in the form of list of lists.

If you run this program, saved as "sql_Academy_query.py", you would get the following result, depending on the actual data:

It should be noted that the database file that will be created will be in the same folder as that of the python file. If we wish to change the path of the file, change the path while opening the file.

Example

#save the file as “sql_Academy_query.py”

# cursor object

crsr = connection.cursor()

execute the command to fetch all the data from the table Student crsr.execute("SELECT * FROM Student")

#store all the fetched data in the ans variable

ans= crsr.fetchall()

loop to print all the data for i in ans:

print(i)

(i) Displaying all records using fetchall()

The fetchall() method is used to fetch all rows from the database table

Example

import sqlite3

connection = sqlite3.connect("Academy.db")

cursor = connection.cursor()

cursor.execute("SELECT * FROM student")

print("fetchall:")

result = cursor.fetchall()

for r in result:

print(r)

OUTPUT

fetchall:

(1, 'Akshay', 'B', 'M', 87.8, '2001-12-12')

(2, 'Aravind', 'A', 'M', 92.5, '2000-08-17')

(3, 'BASKAR', 'C', 'M', 75.2, '1998-05-17')

(4, 'SAJINI', 'A', 'F', 95.6, '2002-11-01')

(5, 'VARUN', 'B', 'M', 80.6, '2001-03-14')

(6, 'PRIYA', 'A', 'F', 98.6, '2002-01-01')

(7, 'TARUN', 'D', 'M', 62.3, '1999-02-01')

Note

cursor.fetchall() -fetchall () method is to fetch all rows from the database table

cursor.fetchone() - The fetchone () method returns the next row of a query result set or None in case there is no row left.

cursor.fetchmany() method that returns the next number of rows (n) of the result set

(ii) Displaying A record using fetchone()

The fetchone() method returns the next row of a query result set or None in case there is no row left.

Example

import sqlite3

connection = sqlite3.connect("Academy.db")

cursor = connection.cursor()

cursor.execute("SELECT * FROM student")

print("\nfetch one:")

res = cursor.fetchone()

print(res)

OUTPUT

fetch one:

(1, 'Akshay', 'B', 'M', 87.8, '2001-12-12')

(iii) Displaying all records using fetchone()

Using while loop and fetchone() method we can display all the records from a table.

Example

import sqlite3

connection = sqlite3.connect("Academy.db")

cursor = connection.cursor()

cursor.execute("SELECT * FROM student")

print("fetching all records one by one:")

result = cursor.fetchone()

while result is not None:

print(result)

result = cursor.fetchone()

OUTPUT

fetching all records one by one:

(1, 'Akshay', 'B', 'M', 87.8, '2001-12-12')

(2, 'Aravind', 'A', 'M', 92.5, '2000-08-17')

(3, 'BASKAR', 'C', 'M', 75.2, '1998-05-17')

(4, 'SAJINI', 'A', 'F', 95.6, '2002-11-01')

(5, 'VARUN', 'B', 'M', 80.6, '2001-03-14')

(6, 'PRIYA', 'A', 'F', 98.6, '2002-01-01')

(7, 'TARUN', 'D', 'M', 62.3, '1999-02-01')

(iv) Displayingusing fetchmany()

Displaying specified number of records is done by using fetchmany(). This method returns the next number of rows (n) of the result set.

Example : Program to display the content of tuples using fetchmany()

import sqlite3

connection = sqlite3.connect("Academy.db")

cursor = connection.cursor()

cursor.execute("SELECT * FROM student")

print("fetching first 3 records:")

result = cursor.fetchmany(3)

print(result)

OUTPUT

fetching first 3 records:

[(1, 'Akshay', 'B', 'M', 87.8, '2001-12-12'), (2, 'Aravind', 'A', 'M', 92.5, '2000-08-17'), (3, 'BASKAR', 'C', 'M', 75.2, '1998-05-17')]

Example : Program to display the content of tuples in newline without using loops

import sqlite3

connection = sqlite3.connect("Academy.db")

cursor = connection.cursor()

cursor.execute("SELECT * FROM student")

print("fetching first 3 records:")

result = cursor.fetchmany(3)

print(*result,sep="\n")

OUTPUT

fetching first 3 records:

(1, 'Akshay', 'B', 'M', 87.8, '2001-12-12')

(2, 'Aravind', 'A', 'M', 92.5, '2000-08-17')

(3, 'BASKAR', 'C', 'M', 75.2, '1998-05-17')

Note

symbol is used to print the list of all elements in a single line with space. To print all elements in new lines or separated by space use sep= "\n" or sep= "," respectively.

CLAUSES IN SQL

SQL provides various clauses that can be used in the SELECT statements. This clauses can be called through python script. Almost all clauses will work with SQLite. The following frequently used clauses are discussed here

·        DISTINCT

·        WHERE

·        GROUP BY

·        ORDER BY.

·        HAVING

(i) SQL DISTINCT CLAUSE

The distinct clause is helpful when there is need of avoiding the duplicate values present in any specific columns/table. When we use distinct keyword only the unique values are fetched. In this example we are going to display the different grades scored by students from “student table”.

Example

import sqlite3

connection = sqlite3.connect("Academy.db")

cursor = connection.cursor()

cursor.execute("SELECT DISTINCT (Grade) FROM student")

result = cursor.fetchall()

print(result)

OUTPUT

[('B',), ('A',), ('C',), ('D',)]

Without the keyword “distinct” in the above examples 7 records would have been displayed instead of 4, since in the original table there are actually 7 records and some are with the duplicate values.

(ii) SQL WHERE CLAUSE

The WHERE clause is used to extract only those records that fulfill a specified condition. In this example we are going to display the different grades scored by male students from “student table”

import sqlite3

connection = sqlite3.connect("Academy.db")

cursor = connection.cursor()

cursor.execute("SELECT DISTINCT (Grade) FROM student where gender='M'")

result = cursor.fetchall()

print(*result,sep="\n")

OUTPUT

('B',)

('A',)

('C',)

('D',)

(iii) SQL Group By Clause

The SELECT statement can be used along with GROUP BY clause. The GROUP BY clause groups records into summary rows. It returns one records for each group. It is often used with aggregate functions (COUNT, MAX, MIN, SUM, AVG) to group the result-set by one or more columns. The following example count the number of male and female from the student table and display the result.

Example

import sqlite3

connection = sqlite3.connect("Academy.db")

cursor = connection.cursor()

cursor.execute("SELECT gender,count(gender) FROM student Group BY gender")

result = cursor.fetchall()

print(*result,sep="\n")

OUTPUT

('F', 2)

('M', 5)

(iv) SQL ORDER BY Clause

The ORDER BY Clause can be used along with the SELECT statement to sort the data of specific fields in an ordered way. It is used to sort the result-set in ascending or descending order. In this example name and Rollno of the students are displayed in alphabetical order of names

Example

import sqlite3

connection = sqlite3.connect("Academy.db")

cursor = connection.cursor()

cursor.execute("SELECT Rollno,sname FROM student Order BY sname")

result = cursor.fetchall()

print(*result,sep="\n")

OUTPUT

(1, 'Akshay')

(2, 'Aravind')

(3, 'BASKAR')

(6, 'PRIYA')

(4, 'SAJINI')

(7, 'TARUN')

(5, 'VARUN')

(v) SQL HAVING Clause

Having clause is used to filter data based on the group functions. This is similar to WHERE condition but can be used only with group functions. Group functions cannot be used in WHERE Clause but can be used in HAVING clause.

Example

import sqlite3

connection = sqlite3.connect("Academy.db")

cursor = connection.cursor()

cursor.execute("SELECT GENDER,COUNT(GENDER) FROM Student GROUP BY GENDER HAVING COUNT(GENDER)>3") result = cursor.fetchall()

co = [i[0] for i in cursor.description]

print(co)

print(result)

OUTPUT

['gender', 'COUNT(GENDER)']

[('M', 5)]