Integrating Query With Csv File

Integrating Query With Csv File

You can even store the query result in a CSV file. This will be useful to display the query output in a tabular format. In the following example (EXAMPLE 15.12 -1) Using Python script the student table is sorted “gender” wise in descending order and then arranged the records alphabetically. The output of this Query will be written in a CSV file called “SQL.CSV”, again the content is read from the CSV file and displayed the result.

Example

import sqlite3

import io          # to access replace()

import csv

#CREATING CSV FILE

d=open('c:/pyprg/sql.csv','w')

c=csv.writer(d)

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

cursor = connection.cursor()

#a=Connection.cursor()

cursor.execute("SELECT * FROM student ORDER BY GENDER DESC,SNAME")

#WRITING THE COLUMN HEADING

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

c.writerow(co)

data=cursor.fetchall()

for item in data:

c.writerow(item)

d.close()

#Reading the CSV File

#replace() is used to eliminate the newline character at the end of each row with open('c:/pyprg/sql.csv', "r", newline=None) as fd:

#r = csv.reader(fd)

for line in fd:

line = line.replace("\n", "")

print(line)

cursor.close()

connection.close()

OUTPUT

Rollno,Sname,Grade,gender,Average,birth_date

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

7, TARUN, D, M, 62.3, 1999-02-01

5, VARUN, B, M, 80.6, 2001-03-14

6, PRIYA, A, F, 98.6, 2002-01-01

4, SAJINI, A, F, 95.6, 2002-11-01

Example Opening the file (“sqlexcel.csv”) through MS-Excel and view the result (Program is same similar to EXAMPLE 15.12 -1 script)

import sqlite3

import io                                                       #to access replace()

import csv

#database name to be passed as parameter

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

print(“Content of the table before sorting and writing in CSV file”)

cursor = conn.execute("SELECT * FROM Student")

for row in cursor:

print (row)

#CREATING CSV FILE

d=open('c:\\pyprg\\sqlexcel.csv','w')

c=csv.writer(d)

cursor = conn.cursor()

cursor.execute("SELECT * FROM student ORDER BY GENDER DESC,SNAME")

#WRITING THE COLUMN HEADING

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

c.writerow(co)

data=cursor.fetchall()

for item in data:

c.writerow(item)

d.close()

print(”sqlexcel.csv File is created open by visiting c:\\pyprg\\sqlexcel.csv”)

conn.close()

OUTPUT

Content of the table before sorting and writing in CSV file

(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, 'Priyanka', 'A', 'F', 98.6, '2002-01-01')

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

sqlexcel.csv File is created open by visiting c:\\pyprg\\sqlexcel.csv

OUTPUT THROUGH EXCEL