Download our sample SQLite database with student table which we will export to Excel page. Using the same Excel page we will create student table in our SQLite database.
# download my_db.db SQLite Database from plus2net.com
!wget https://www.plus2net.com/python/download/my_db.db
Above command will place a copy of the SQLite database at our google Colab platform.
Here we are using sqlite3 library to connect to SQLite database, other type of connections using SQLAlchemy can also be used.
import sqlite3
my_conn = sqlite3.connect('my_db.db') # connect to db
As we have connected to Database , let us get a list of tables available in this Database.
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
Structure of our student table which we will export to Excel page.
query="pragma table_info([student])"
my_data=list(my_conn.execute(query)) # rows of data as list
print(my_data) # structure of the table
First five rows of data from our student 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
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
From the above DataFrame we will create the excel file by using to_excel(). This file will be available at our left side window frame and we can download the same to our local system.
Using the above created my_student.xlsx file we will crete the student table in our SQLite database. First we will delete the student table already avilable in our database.
#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)
We will create the DataFrme by reading the data from our my_student.xlsx Excel file by using read_excel() here .
By using Pandas to_sql() we can create the table in our database. Here we have given the name of the table as student2. ( You can give as student if you have already deleted the table in above steps) .
In above code in place of Excel file we can use csv file to export Sqlite database table to a csv file and vice versa. In place of read_excel() and to_excel() we will use read_csv() and to_csv() to read and write to csv file.
Note that we can't read Excel file and create the SQLite table by using xlsxwrite. We will only create the Excel file from SQLite database table.
We have to install xlsxwrite in Colab platform first before using the same.
pip install xlsxwriter
Here is the code using the same sample database and SQLite3 database connection.
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
We will use openpyxl library to read from SQLite database to create Excel file and then using the Excel file we will create the SQLite database table. Install the library in your colab platform.
pip install openpyxl # Install the library
This code will create the my_student.xlsx file using the above mentioned SQLite database file and database connection.
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)
To create the table from the Excel file , first we will delete the source database table in our SQLite database.
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 matching to our data. ( Here using Pandas library is a better solution ).
# 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");
Read the Excel page and create a list with rows of data to generate the query for inserting to SQLite table.
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
Create the query and insert to SQLite database student table.
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()