title: Using python to write mysql query to csv, need to show field names
body: <p>I have the following:</p>
  
  
  
  <pre class="lang-python prettyprint-override"><code>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)
  </code></pre>
  
  <p>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.</p>
  
  <hr>
  
  <p><strong>Update</strong></p>
  
  <p>It's been 8 years; I still get the occasional update or query about this question.</p>
  
  <p>As stated in some of the comments, the <a href="https://www.python.org/dev/peps/pep-0249/#description" rel="noreferrer">cursor.description</a> from the DBAPI is what I was looking for.</p>
  
  <p>Here is a more modern example in Python 3 using the <a href="https://pymysql.readthedocs.io/en/latest/" rel="noreferrer">pymysql</a> driver to connect to <a href="https://mariadb.org" rel="noreferrer">MariaDB</a>, 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 <a href="https://docs.python.org/3.7/library/csv.html#csv.writer" rel="noreferrer">written to a csv file</a>.</p>
  
  <p>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.</p>
  
  <pre class="lang-python prettyprint-override"><code>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))
  </code></pre>