EXPORT SQL DATA FROM PANDAS TO EXCEL

Listing 4.9 displays the contents of sql_query_excel.py that reads the contents of the people table into a Pandas DataFrame and then exports the latter to an Excel file.

LISTING 4.9: sql_query_excel.py

from sqlalchemy import create_engine
import pymysql
import pandas as pd

engine = create_engine('mysql+pymysql://root:yourpassword@
127.0.0.1/',pool_recycle=3600)

query_1 = '''
select * from mytools.people
'''

print("create dataframe from table:")
df_2 = pd.read_sql_query(query_1, engine)

print("Contents of Pandas dataframe:")
print(df_2)

import openpyxl
print("saving dataframe to people.xlsx")
df_2.to_excel('people.xlsx', index=False)

Listing 4.9 contains several import statements followed by the variable engine that is initialized to an “endpoint” from which a MySQL database can be accessed. The next code snippet initializes the variable query_1 as a...