Database table data to Excel using xlsxwriter

xlsxwriter
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
Subscribe to our YouTube Channel here

Subscribe

* indicates required
Subscribe to plus2net

    plus2net.com



    Post your comments , suggestion , error , requirements etc here





    Python Video Tutorials
    Python SQLite Video Tutorials
    Python MySQL Video Tutorials
    Python Tkinter Video Tutorials
    We use cookies to improve your browsing experience. . Learn more
    HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
    ©2000-2022 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer