Python writing SQLAlchemy queryobject to CSV

Question

I'm writing to a csv file in batches, and as I currently have it only the last 'batch' is being recorded on the file, whether I open the file with 'w' or 'a' flags. Here are my two attempts:

with open('testdump.csv', 'w') as csvfile:

    fieldnames = ['name', 'price']

    writer = csv.DictWriter(csvfile, fieldnames=fieldnames)        
    writer.writeheader()        

    for date in date_list:
        date = date.strftime('%Y-%m-%d')

        q1 = db.session.query(...query goes here that depends on date...)

        rows = []
        for row in q1:
            rows.append(
           {
            'name': row.name,
            'price': row.price,
            }
         )

        for row in rows:
            writer.writerow(dict(
            (k, v.encode('utf-8') if type(v) is unicode else v) for k, v in row.iteritems()
        ))

which results in only the last batch being written and

    csvfile = open('testdump.csv', 'w')

    fieldnames = ['name', 'price']

    writer = csv.DictWriter(csvfile, fieldnames=fieldnames)        
    writer.writeheader()        

    csvfile.close()

    for date in date_list:
        date = date.strftime('%Y-%m-%d')

        q1 = db.session.query(...query goes here that depends on date...)

        rows = []
        for row in q1:
            rows.append(
           {
            'name': row.name,
            'price': row.price,
            }
         )

        csvfile = open('testdump.csv', 'a+')

        for row in rows:
            writer.writerow(dict(
            (k, v.encode('utf-8') if type(v) is unicode else v) for k, v in row.iteritems()
        ))

        csvfile.close()

which results in only the header being written. Any ideas?

also, try to debug with some print statements and seeing what result you are getting from the db query. — user3089927
– user3089927, Commented May 12, 2015 at 17:55

Steven Bluen · Accepted Answer · 2015-05-12 17:54:21Z

This example, similarly to your first attempt, creates a properly formatted csv file. We would need to know what the query returns and how the return value is structured to know how to fix it.

import csv

with open('testdump.csv', 'wb') as csvfile:

    fieldnames = ['name', 'price']

    writer = csv.DictWriter(csvfile, fieldnames=fieldnames)        
    writer.writeheader()


    rows = [{"name": "apples", "price": "$5"},
            {"name": "oranges", "price": "$4"}]

    for row in rows:
        writer.writerow(dict(
       (k, v.encode('utf-8') if type(v) is unicode else v) for k, v in row.iteritems()
    ))

The obvious problem with your second attempt is that where you wrote

    writer.writerow(dict(
    (k, v.encode('utf-8') if type(v) is unicode else v) for k, v in row.iteritems()
))

you were writing to a closed file object and did not create a new writer for the new file object.

Collectives™ on Stack Overflow

Python writing SQLAlchemy queryobject to CSV

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related