Save MySQL data to an Excel file

mysql2excel.py

# -*- coding: utf-8 -*-
"""
Purpose: Import data from a MySQL database to an Excel file
Created: 2019-05-25
Authors: Derek Hohls <[email protected]>
"""
import getpass
# require pip install:
import MySQLdb
from xlsxwriter.workbook import Workbook


def write_to_excel(cursor, SQL, headers=None, filename='out.xlsx'):
    query = SQL
    try:
        cursor.execute(query)
        workbook = Workbook(filename)
        sheet = workbook.add_worksheet()
        top = 0
        if headers:
            for c, col in enumerate(headers):
                sheet.write(0, c, col)
            top += 1
        for r, row in enumerate(cursor.fetchall()):
            for c, col in enumerate(row):
                sheet.write(r + top, c, col)
        workbook.close()
    except IOError as err:
        print(err)
    except Exception as err:
        print(err)
    return None    


def connect_to_mysql(database, user='root', host='localhost'):
    user = user or 'root'  # your username
    host = host or 'localhost'  # your host
    db = database or 'test'  # your database 
    pword = getpass.getpass('"%s" database password (default:root)? ' % db)
    password = pword or 'root'
    try:
        con = MySQLdb.connect(user=user, passwd=password, host=host, db=db)
        cursor = con.cursor()
        return cursor
    except Exception as err:
        print(err)
    return None
        

# Example
database = 'test'
SQL = "select number, name from staff;"
headers = ['Number', 'Name', ]

cursor = connect_to_mysql(database=database)
if cursor:
    write_to_excel(cursor, SQL, headers=headers, filename='staff.xlsx')

Should really use logging rather than print functions!