xlsxwriter to create and add data from MySQL or SQLite database table to Excel file in Python

xlsxwriter adding Database table to Excel page

Transfer data from MySQL or SQLite database table to create Excel worksheet by xlsxwriter library

MySQL

Here is the connection string , after successful connection we will use the variable my_conn in our examples below.

from sqlalchemy import create_engine
my_conn = create_engine("mysql+mysqldb://userid:pw@localhost/my_db")

usrid : Your MySql login user id
pw : Your MySql login password
localhost : Address of MySQL server, IP address or localhost
my_db : Database name of MySQL where our table is available.

Full code to copy and add all rows to student table is here

SQLite

from sqlalchemy import create_engine
my_conn = create_engine("sqlite:///D:\\testing\\my_db\\my_db.db")

Full code to copy and add all rows to student table is here

Query to get data

Here is the query to get 5 rows of data and all the columns from student table. Change the Query as per your requirement.

query="SELECT * FROM student LIMIT 0,5" # query

Full code to create Excel file with student table data is here

from sqlalchemy import create_engine
import xlsxwriter
my_path='G:\\My drive\\testing\\xlsxwriter\\student.xlsx' # change path

my_conn = create_engine("mysql+mysqldb://root:pw@localhost/my_tutorial")   

wb = xlsxwriter.Workbook(my_path) # create workbook 
ws = wb.add_worksheet() # Add worksheet

my_format = wb.add_format({'bold': True, 'font_color': 'red',
 'align':'center','bg_color':'#FFFF00'}) # Header row format 

query="SELECT * FROM student LIMIT 0,10" # query 
my_data=my_conn.execute(query) 
ws.write_row('A1',my_data.keys(),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)
    r=r+1 # increase row by 1 for next row
wb.close()

Using Pandas DataFrame

import pandas as pd 
from sqlalchemy import create_engine
my_conn = create_engine("mysql+mysqldb://userid:pw@localhost/my_db")
sql="SELECT * FROM student "
df = pd.read_sql(sql,my_conn)
df.to_excel('D:\\my_data\\student.xlsx') # Change the path

Using xlsxwriter and adding different worksheets

import pandas as pd 
from sqlalchemy import create_engine
my_conn = create_engine("mysql+mysqldb://root:test@localhost/my_tutorial")
sql="SELECT * FROM student WHERE class='Three'"
sql2="SELECT * FROM student WHERE class='Four'"
df=pd.read_sql(sql,my_conn)   # class Three students 
df2=pd.read_sql(sql2,my_conn) # class Four students

my_path='G:\\My drive\\testing\\xlsxwriter\\student2.xlsx' 

with pd.ExcelWriter(my_path,engine='xlsxwriter') as my_obj:
    df.to_excel(my_obj,sheet_name='Three')
    df2.to_excel(my_obj,sheet_name='Four')

Pandas to_excel()

Adding chart using Database table data

xlsxwriter adding bar chart using Database table data in Excel page

from sqlalchemy import create_engine
import xlsxwriter
my_path='G:\\My drive\\testing\\xlsxwriter\\student.xlsx' 

my_conn = create_engine("mysql+mysqldb://root:test@localhost/my_tutorial")   

wb = xlsxwriter.Workbook(my_path) # create workbook 
ws = wb.add_worksheet() # Add worksheet

my_format = wb.add_format({'bold': True, 'font_color': 'red',
 'align':'center','bg_color':'#FFFF00'}) # Header row format 

query="SELECT * FROM student LIMIT 0,5" # query 
my_data=my_conn.execute(query) 
ws.write_row('A1',my_data.keys(),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)
    r=r+1 # increase row by 1 for next row
	
chart = wb.add_chart({'type': 'column'}) # chart object 
chart.add_series({'values': '=Sheet1!$D$2:$D$6'}) # Adding series
ws.insert_chart('A7', chart) # adding chart 
wb.close()

xlsxwriter Library SQlite to Excel using Pandas DataFrame Pandas DataFrame to Excel by to_excel()
Python

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author Watch on YouTube

Connect:

Subscribe to our YouTube Channel here

Database table data to Excel using xlsxwriter