Write sql data to csv from python API

Question

I am running SQL query from python API and want to collect data in Structured(column-wise data under their own header).CSV format.

This is the code so far I have.

import pymysql.cursors
import csv


conn = pymysql.connect(host='159.XXX.XXX.XXX',user='proXXX',password='PXX',db='pXX',charset='utf8mb4',cursorclass=pymysql.cursors.DictCursor)
cursor = conn.cursor()
print (type(conn))
sql = "SELECT id,author From researches WHERE id < 20 " 
cursor.execute(sql)
data = cursor.fetchall()
print (data)
with open('metadata.csv', 'w', newline='') as f_handle:
    writer = csv.writer(f_handle,delimiter=',')
    header = ['id', 'author']
    writer.writerow(header)
    for row in data:
        writer.writerow(row)

Now the data is being printed on the console but not getting in.CSV file this is what I am getting asnoutput. What is that I am missing? Please help.

You should use the a mode, otherwise you re-write your file each time. Also, write your headers just once... — cs95
– cs95, Commented Nov 5, 2017 at 6:50
I am very new to this. Can you please help me with the code? — Shad
– Shad, Commented Nov 5, 2017 at 6:53
Maybe... if you could update your question to include how this code is called - I want to see the loop, and any other relevant code, so I can show you how to fix it. — cs95
– cs95, Commented Nov 5, 2017 at 6:54

JerryTheo · Accepted Answer · 2017-11-05 06:56:26Z

2

with open('metadata.csv', 'w', newline='') as f_handle:
    fieldnames = ['id', 'author']
    writer = csv.DictWriter(f_handle, fieldnames=fieldnames)
    writer.writeheader()
    for row in data:
        writer.writerow(row)

So the thing is, your data is in the form of dictionaries, while the Writer object expects tuples. You should be using the DictWriter object instead.

answered Nov 5, 2017 at 6:56

JerryTheo

915 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Shad Over a year ago

Awesome. Thanks @JerryTheo

Shad Over a year ago

A simple question. My database contains a column named as published_date but I want the header (Fieldname)to be as "Published Date". I tried and it is throwing an error.Is it possible to get the desired header?

JerryTheo Over a year ago

Sorry for the delayed response, I've had a lot on my hands this week. Anyway, there are two ways you can do this, you either replace the key in the dictionary for each of the rows, or you can skip writer.writeheader() and use this instead, writer.writerow({'id': 'id', 'author': 'author', 'published_date': 'Published Date'}).

Collectives™ on Stack Overflow

Write sql data to csv from python API

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related