Data export from SQLite to Excel and vice versa in Colab

Installation of database and connecting 🔝

# download my_db.db SQLite Database from plus2net.com
!wget https://www.plus2net.com/python/download/my_db.db

import sqlite3
my_conn = sqlite3.connect('my_db.db') # connect to db

query="SELECT name FROM sqlite_master WHERE type='table' "
my_data=list(my_conn.execute(query)) # rows of data as list
print(my_data) # List of tables

query="pragma table_info([student])"
my_data=list(my_conn.execute(query)) # rows of data as list
print(my_data) # structure of the table

query="SELECT *  FROM student LIMIT 0,5" # sql
my_data=list(my_conn.execute(query)) # rows of data as list
print(my_data) # display  rows

From Sqlite table to Excel 🔝

import pandas as pd
query='SELECT * FROM student' # SQL to collect all records from table
df=pd.read_sql(query,my_conn,index_col='id') # create dataframe
print(df) # Print the dataframe with all rows

df.to_excel('my_student.xlsx') # create Excel file

From Excel to SQLite table 🔝

#query="DELETE FROM student" # Query to delete records 
query='DROP TABLE student' # Query to delete table
try:
  r_set=my_conn.execute(query)
  print("No of Records deleted : ",r_set.rowcount)
  my_conn.commit()
except sqlite3.Error as my_error:
  print("error: ",my_error)

df = pd.read_excel('my_student.xlsx',index_col='id')

df.to_sql(con=my_conn,name='student2',if_exists='append')

query="SELECT *  FROM student2 LIMIT 0,5" # sql
my_data=list(my_conn.execute(query)) # rows of data as list
print(my_data)

Using csv (Comma separated value ) file 🔝

Export import to other formats 🔝

Without using Pandas and by using xlsxwriter 🔝

pip install xlsxwriter

query="pragma table_info([student])" # structure of the table
my_data=list(my_conn.execute(query)) # rows of data as list
l1=[r[1] for r in my_data] # List of column header 

import xlsxwriter # Or install by using pip install xlsxwriter
wb = xlsxwriter.Workbook('my_student.xlsx') # create workbook 
ws = wb.add_worksheet() # Add worksheet
query="SELECT * FROM student LIMIT 0,10" # query 
my_data=my_conn.execute(query) 
my_data=[r for r in my_data] # List of rows of data 
my_format = wb.add_format({'bold': True, 'font_color': 'red',
 'align':'center','bg_color':'#FFFF00'}) # Header row format 
ws.write_row('A1',l1,my_format) # Add header row

r,c=1,0 # row=1 and column=0
for row_data in my_data:
    ws.write_row(r,c,row_data) # add row to excel page 
    r=r+1 # increase row by 1 for next row
wb.close() # Save worksheet 

By using openpyxl 🔝

pip install openpyxl # Install the library

from openpyxl import Workbook
from openpyxl.styles import PatternFill,Font 

query="pragma table_info([student])" # structure of the table
my_data=list(my_conn.execute(query)) # rows of data as list
l1=[r[1] for r in my_data] # List of column header 

query="SELECT * FROM student LIMIT 0,10" # query 
my_data=my_conn.execute(query) 
my_data=[r for r in my_data] # List of rows of data 

wb=Workbook()
ws1=wb.active # work with default worksheet
ws1.append(l1) # adding column headers at first row 

my_font=Font(size=14,bold=True) # font styles
my_fill=PatternFill(fill_type='solid',start_color='FFFF00') #Background color
for cell in ws1["1:1"]: # First row 
    cell.font = my_font
    cell.fill= my_fill

r,c=2,0 # row=2 and column=0
for row_data in my_data:
    d=[r for r in row_data]
    ws1.append(d)
my_path='my_student.xlsx'#Path 
wb.save(my_path)

query='DROP TABLE student'
try:
  r_set=my_conn.execute(query)
  print("No of Records deleted : ",r_set.rowcount)
  my_conn.commit()
except sqlite3.Error as my_error:
  print("error: ",my_error)

# We have to create the table by directly writing the query
my_conn.execute('''
CREATE TABLE IF NOT EXISTS student(id integer primary key, 
                      name text, 
                      class text, 
                      mark integer, 
                      gender text 
                      );''')
my_conn.commit()
print("Student Table created successfully");

from openpyxl import load_workbook
wb = load_workbook(filename='my_student.xlsx', read_only=True) # change path
ws = wb['Sheet'] # connecting to sheet
my_data=[]
# remove the column headers and start from 2nd row with data 
for data in ws.iter_rows(min_row=2,max_col=5,max_row=5,values_only=True):
    print(data[0],data[1]) # Show column 1,2 - id, name
    my_data.append(data)
wb.close()# Close the workbook after reading

query2='INSERT INTO `student` (`id`, `name`, `class`, `mark`, `gender`) VALUES '
my_str=",".join(map(str,my_data))
query2=query2+my_str+';'
print(query2) # for checking 
r_set=my_conn.execute(query2) # insert data to table 
my_conn.commit()