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

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

Solution 2

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)

Solution 3

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

Solution 4

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.insert(0, tuple(headers))
fp = open('/tmp/file.csv', 'w', newline = '') 
myFile = csv.writer(fp)
myFile.writerows(rows)
fp.close()

Solution 5

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

View more solutions

Comments

[Mathnode]

that's great thanks, any idea how I can write out the field names too?

[mtrw]

@teatime - You can get the fieldnames from result[0].keys (or equivalently row.keys.

[Will]

Great answer. I would just change the 'file' variable to something like myfile since 'file' is a keyword.

[LarsH]

Unfortunately, this answer doesn't completely answer the question's title ("need to show field names"). @mtrw's comment helps, but is not applicable to the code given (tuple object has no attribute 'keys'). It might be applicable if the cursor were a dictionary cursor, i.e. created using dbconn.cursor(dictionary=True).

[LarsH]

When using a dictionary cursor, result[0].keys() gives a list of column names. However it is not (generally) in the same order as you would get from result[0] for a non-dictionary cursor, nor the same order as you would see in a MySQL client. In short, it's a mess. If the column order doesn't matter, this might be OK.

[Jake Wagner]

This works great but is there anyway I could get the field names/column names as well please.

[Helen Neely]

Thanks, found this very handy.

[Mwspencer]

Thank you! I have been open excel and removing the blank lines from there , and it was always a pain, and I never figured out to stop it.

[AK91]

@JakeWagner add writer.writeheader() before writerows()

[Rahul]

Why you are adding header and with rows ? This will write the headers twice.

[greendino]

my csv doesn't show correctly. it doesn't fill the cell just straight lines