Using python to write mysql query to csv, need to show field names

I have the following:

    import MySQLdb as dbapi
    import sys
    import csv

    dbServer='localhost'
    dbPass='supersecretpassword'
    dbSchema='dbTest'
    dbUser='root'

    dbQuery='SELECT * FROM pbTest.Orders;'

    db=dbapi.connect(host=dbServer,user=dbUser,passwd=dbPass)
    cur=db.cursor()
    cur.execute(dbQuery)
    result=cur.fetchall()

    c = csv.writer(open("temp.csv","wb"))
    c.writerow(result)

This is produces a garbled mess. I am familiar with using printing record[0] etc. Not sure how I should be going about setting up the formatting. to produce something like what a query would in a console. I cannot do a simple INTO OUTFILE from the mysql server.


Update

It's been 8 years; I still get the occasional update or query about this question.

As stated in some of the comments, the cursor.description from the DBAPI is what I was looking for.

Here is a more modern example in Python 3 using the pymysql driver to connect to MariaDB, which will select and fetch all rows into a tuple, the row headers/description into a list. I then merge these two data structures into a single list, to be written to a csv file.

With the header names being the first entry in the result list; writing the result to a file in a linear fashion, ensures the row header is the first line in the CSV file.

import pymysql
import csv
import sys

db_opts = {
    'user': 'A',
    'password': 'C',
    'host': 'C',
    'database': 'D'
}

db = pymysql.connect(**db_opts)
cur = db.cursor()

sql = 'SELECT * from schema_name.table_name where id=123'
csv_file_path = '/tmp/my_csv_file.csv'

try:
    cur.execute(sql)
    rows = cur.fetchall()
finally:
    db.close()

# Continue only if there are rows returned.
if rows:
    # New empty list called 'result'. This will be written to a file.
    result = list()

    # The row name is the first entry for each entity in the description tuple.
    column_names = list()
    for i in cur.description:
        column_names.append(i[0])

    result.append(column_names)
    for row in rows:
        result.append(row)

    # Write result to file.
    with open(csv_file_path, 'w', newline='') as csvfile:
        csvwriter = csv.writer(csvfile, delimiter=',', quotechar='"', quoting=csv.QUOTE_MINIMAL)
        for row in result:
            csvwriter.writerow(row)
else:
    sys.exit("No rows found for query: {}".format(sql))

Answers


You can dump all results to the csv file without looping:

rows = cursor.fetchall()
fp = open('/tmp/file.csv', 'w')
myFile = csv.writer(fp)
myFile.writerows(rows)
fp.close()

result is a list of rows. So you'll need to iterate through that list and write each row:

for row in result:
    c.writerow(row)

Answer with column header names for anyone who comes across this: (I am using Jwhat's method to write to csv)

result = cur.fetchall()

#Getting Field Header names
column_names = [i[0] for i in cur.description]
fp = open('Result_Set.csv' 'w')
myFile = csv.writer(fp, lineterminator = '\n') #use lineterminator for windows
myFile.writerow(column_names)
myFile.writerows(result)
fp.close()

The previous answer would give an error. The header must be written to the file first, then the rows added. The following worked for me.

rows = cursor.fetchall()
headers = [col[0] for col in cursor.description] # get headers
rows = (tuple(headers),) + rows # add headers to rows
fp = open('/tmp/file.csv', 'w')
myFile = csv.writer(fp)
myFile.writerow(headers)
myFile.writerows(rows)
fp.close()

I know little python, but after a bit of googling, should you be using writerows (with an S) which writes multiple rows, rather than writerow which expects a single row.

As I say this is a bit of a stab in the dark from me.


Just add one more thing to @jwhat answer.

If you intend to open this csv file in windows, you will need to handle the extra empty lines.

just change the code to the following, as the default lineterminator in 2.7 is \r\n

myFile = csv.writer(fp, lineterminator='\n')

import pymysql
import sys
import csv

connection = pymysql.connect(host='abcde',
user='admin',
password='secret',
db='databaseName',
port=12345)

dbQuery="SELECT field1, field2, fieldn FROM database.table;"
cur=connection.cursor()
cur.execute(dbQuery)
rows=cur.fetchall()
column_names = [i[0] for i in cur.description]
fp = open('DimDevice.csv', 'w')
myFile = csv.writer(fp, lineterminator = '\n')
myFile.writerow(column_names)   
myFile.writerows(rows)
fp.close()

Need Your Help

What is the proper way to display the full InnerException?

c# exception inner-exception

What is the proper way to show my full InnerException.

How to make console be able to print any of 65535 UNICODE characters

c# unicode

I am experimenting with unicode characters and taking unicode values from Wikipedia page