Export SQL to CSV, Excel, TEXT, JSON, HTML in Python Pandas Module

Export SQL to CSV, Excel, TEXT, JSON, HTML in Python Pandas Module

Instructor-svg Al-Mamun Sarkar
Apr 01 , 2020

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

 

  • Share On:
  • fb
  • twitter
  • pinterest
  • instagram