Database table data to Excel using xlsxwriter

MySQL

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

SQLite

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

Query to get data

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

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

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')

Adding chart using Database table data

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()