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

Question

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

Answer 1

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