How to export Mysql Table data to excel file using python script?

Question

I have been trying to export that data from table to excel file using python 2.7.8 but I have not succeeded. Please help me. My Requirement is to export the data from the table and store it in local(windows C Drive).

#!/usr/bin/python
import smtplib
import base64
import os
import sys
import xlswriter
import xlwt
import datetime
import MySQLdb
from pyh import *
from email.MIMEMultipart import MIMEMultipart
from email.MIMEText import MIMEText
db = MySQLdb.connect("192.168.1.118","stp","stp","STP")
cursor = db.cursor()
query = ("""select * from stp_automation_output""")
cursor.execute(query)
myresults = cursor.fetchall()
workbook = xlwt.Workbook()
worksheet = workbook.add_sheet("My Sheet")
#date_format = workbook.add_format({'num_format': 'd mmmm yyyy'})
bold = workbook.add_format({'bold': 1})
worksheet.write('A1','Sno',bold)
worksheet.write('B1','function_name',bold)
worksheet.write('C1','input1',bold)
worksheet.write('D1','input2',bold)
worksheet.write('E1','input3',bold)
worksheet.write('F1','Expected_output',bold)
worksheet.write('G1','Actual_output',bold)
worksheet.write('H1','Result',bold)
row = 1
col = 0
for Sno,function_name,input1,input2,input3,Expected_output,Actual_output,Result in (myresults):
  Sno = row[0]
  function_name = row[1]
  input1 = row[2]
  input2 = row[3]
  input3 = row[4]
  Expected_output = row[5]
  Actual_output = row[6]
  Result = row[7]
workbook.save()

Answer 1

Wrote this:

import mysql.connector
from openpyxl import Workbook

def main():

    # Connect to DB -----------------------------------------------------------
    db = mysql.connector.connect( user='root', password='', host='127.0.0.1')
    cur = db.cursor()

    # Create table ------------------------------------------------------------
    database = 'test_database'
    SQL = 'CREATE DATABASE IF NOT EXISTS ' + database + ';'
    cur.execute(SQL)
    db.commit()

    SQL = 'USE ' + database + ';'
    cur.execute(SQL)

    # Create car data ---------------------------------------------------------
    cars_table_name = 'cars'
    SQL = (
        'CREATE TABLE IF NOT EXISTS ' + cars_table_name +
        '('
        '    model_year YEAR, '
        '    manufacturer VARCHAR(40), '
        '    product VARCHAR(40)'
        ');')
    cur.execute(SQL)
    db.commit()

    # Python list of dictionaries
    # More info at:
    #     https://stackoverflow.com/questions/8653516/python-list-of-dictionaries-search
    car_data = [
      { 'model_year': '2010', 'manufacturer': 'Toyota', 'product': 'Prius' },
      { 'model_year': '2010', 'manufacturer': 'Honda', 'product': 'CR-V' },
      { 'model_year': '1998', 'manufacturer': 'Honda', 'product': 'Civic' },
      { 'model_year': '1997', 'manufacturer': 'Ford', 'product': 'F-150' },
      { 'model_year': '2017', 'manufacturer': 'Tesla', 'product': 'Model 3' },
    ]

    # Code adapted from: 
    #     https://dev.mysql.com/doc/connector-python/en/connector-python-example-cursor-transaction.html
    add_cars = ('INSERT INTO ' + cars_table_name + ' (model_year, manufacturer, product) '
                '    VALUES (%(model_year)s, %(manufacturer)s, %(product)s)')

    for car_datum in car_data:
        cur.execute(add_cars, car_datum);
    db.commit()

    # Create manufacturer data -----------------------------------------------
    manufacturer_table_name = 'manufacturer'
    SQL = (
        'CREATE TABLE IF NOT EXISTS ' + manufacturer_table_name +
        '('
        '    name VARCHAR(40), '
        '    headquarters VARCHAR(40), '
        '    number_of_employees INT, '
        '    website VARCHAR(40)'
        ');')
    cur.execute(SQL)
    db.commit()

    add_manufacturer = (
        'INSERT INTO ' + manufacturer_table_name + 
        ' (name, headquarters, number_of_employees, website) '
        '    VALUES (%s, %s, %s, %s)')

    # Python list of lists
    # More info at:
    #     https://stackoverflow.com/questions/18449360/access-item-in-a-list-of-lists
    # Data from:
    # https://en.wikipedia.org/wiki/Toyota
    # Honda data from: https://en.wikipedia.org/wiki/Honda
    # Ford data from: https://en.wikipedia.org/wiki/Ford
    # Tesla data from: https://en.wikipedia.org/wiki/Tesla,_Inc.
    manufacture_data = [
      [ 'Toyota', 'Toyota, Aichi, Japan', '364445', 'http://toyota-global.com/' ],
      [ 'Honda', 'Minato, Tokyo, Japan', '208399', 'http://world.honda.com/' ],
      [ 'Ford', 'Dearborn, Michigan, U.S.', '201000', 'http://www.ford.com/' ],
      [ 'Tesla, Inc.', 'Palo Alto, California, US', '33000', 'http://www.tesla.com/' ],
    ]

    for manufacturer_datum in manufacture_data:
        cur.execute(add_manufacturer, manufacturer_datum);
    db.commit()

    # Create Excel (.xlsx) file -----------------------------------------------
    wb = Workbook()

    SQL = 'SELECT * from '+ cars_table_name + ';'
    cur.execute(SQL)
    results = cur.fetchall()
    ws = wb.create_sheet(0)
    ws.title = cars_table_name
    ws.append(cur.column_names)
    for row in results:
        ws.append(row)

    SQL = 'SELECT * from '+ manufacturer_table_name + ';'
    cur.execute(SQL)
    results = cur.fetchall()
    ws = wb.create_sheet(0)
    ws.title = manufacturer_table_name
    ws.append(cur.column_names)
    for row in results:
        ws.append(row)

    workbook_name = "test_workbook"
    wb.save(workbook_name + ".xlsx")

    # Remove tables and database ----------------------------------------------
    SQL = 'DROP TABLE ' + manufacturer_table_name + ';'
    cur.execute(SQL)
    db.commit()
    SQL = 'DROP TABLE ' + cars_table_name + ';'
    cur.execute(SQL)
    db.commit()
    SQL = 'DROP DATABASE ' + database + ';'
    cur.execute(SQL)
    db.commit()

if  __name__ =='__main__':main() 

Answer 2

This is probably something you can do more easily without using Python, but here's a skeleton structure. Note this writes to a .csv, not an Excel file. You can use the xlwt library to accomplish the latter.

Note you'll need to pip install MySQL-python first, which is usually painless but occasionally not painless.

import MySQLdb
import csv

user = '' # your username
passwd = '' # your password
host = '' # your host
db = '' # database where your table is stored
table = '' # table you want to save

con = MySQLdb.connect(user=user, passwd=passwd, host=host, db=db)
cursor = con.cursor()

query = "SELECT * FROM %s;" % table
cursor.execute(query)

with open('outfile','w') as f:
    writer = csv.writer(f)
    for row in cursor.fetchall():
        writer.writerow(row)

EDIT — This should write it to Excel, but I'm not very familiar with xlwt and I haven't tested this code.

import MySQLdb
from xlsxwriter.workbook import Workbook

user = '' # your username
passwd = '' # your password
host = '' # your host
db = '' # database where your table is stored
table = '' # table you want to save

con = MySQLdb.connect(user=user, passwd=passwd, host=host, db=db)
cursor = con.cursor()

query = "SELECT * FROM %s;" % table
cursor.execute(query)

workbook = Workbook('outfile.xlsx')
sheet = workbook.add_worksheet()
for r, row in enumerate(cursor.fetchall()):
    for c, col in enumerate(row):
        sheet.write(r, c, col)