Export SQL to CSV, Excel, TEXT, JSON, HTML in Python Pandas Module
Author: Al-mamun Sarkar
Date: 2020-04-01 19:55:30
Export SQL to CSV, Excel, TEXT, JSON, HTML in Python Pandas Module. The following code shows how to read data from SQL database and Export to CSV, Excel, TEXT, JSON, HTML for working with the Python Pandas module.
Import Modules:
import pandas as pd
import mysql.connector
Query Data From SQL Database:
conn = mysql.connector.connect(user='root', password='root', database='pandas')
cursor = conn.cursor()
cursor.execute("SELECT * FROM test")
data = cursor.fetchall()
Conver SQL data to Pandas DataFrame:
df = pd.DataFrame(data=data, columns=['ID', 'Title', 'Number', 'Date'])
df
Output:
| ID | Title | Number | Date | |
|---|---|---|---|---|
| 0 | 1 | Hello | 12 | 2018-02-10 14:20:11 |
| 1 | 2 | World | 324 | 2018-02-10 14:20:11 |
| 2 | 3 | Hello | 12 | 2018-02-10 14:20:20 |
| 3 | 4 | World | 324 | 2018-02-10 14:20:20 |
| 4 | 5 | Hello | 12 | 2018-02-10 14:20:20 |
| 5 | 6 | World | 324 | 2018-02-10 14:20:20 |
| 6 | 7 | Hello | 12 | 2018-02-10 14:20:20 |
| 7 | 8 | World | 324 | 2018-02-10 14:20:20 |
| 8 | 9 | Hello | 12 | 2018-02-10 14:20:20 |
| 9 | 10 | World | 324 | 2018-02-10 14:20:20 |
| 10 | 11 | Hello | 12 | 2018-02-10 14:20:20 |
| 11 | 12 | World | 324 | 2018-02-10 14:20:20 |
| 12 | 13 | Hello | 12 | 2018-02-10 14:20:20 |
| 13 | 14 | World | 324 | 2018-02-10 14:20:20 |
Export data to CSV:
df.to_csv('Data/BD_Data.csv', index=False)
Export data to Excel:
df.to_excel('Data/BD_Data.xlsx', index=False)
Export data to TXT:
df.to_csv('Data/BD_Data.txt', index=False)
Export data to JSON:
df.to_json('Data/BD_Data.json')
Export data as HTML:
df.to_html('Data/BD_Data.html')