Convert the mysql table details to an Excel (.xls) or comma separated file (.csv) using Python

Question

Hi,

I want to convert the mysql database table contents to an Excel(.xls) or comma separated file(csv) using python script... Is it possible? Any one can help me?

Thanks in advance, Nimmy

Answer 1

With third-party project mysqldb installed you can easily read that table, e.g:

import MySQLdb
conn = MySQLdb.connect (host = "localhost",
                        user = "testuser",
                        passwd = "testpass",
                        db = "test")
cursor = conn.cursor()
cursor.execute("SELECT * FROM thetable")
while True:
   row = cursor.fetchone()
   if row is None: break
   # here: do something with the row

You can of course write each row to a csv file with Python's standard library csv module -- you'll just need an import csv at the start of your code. Then, after the cursor.execute, you can use code such as:

with open('thefile.csv', 'w') as f:
    writer = csv.writer(f)
    while True:
        row = cursor.fetchone()
        if row is None: break
        writer.writerow(row)

If you want to write to an .xls file instead of a .csv, see third-party module xlwt.