How to write 10 million+ rows into csv file from vertica using python?

Question

How to write 10 million+ rows into csv file from vertica using python? When I tried to write into bulk_data.csv as follows, it got struck after 200,000 rows.

con = pyodbc.connect("DRIVER={Vertica};SERVER=***;DATABASE=***;UID=****;PWD=***")
cursor = con.cursor()
cursor.execute('SELECT * FROM ***')
header = map(lambda x: x[0], cursor.description)
with open('bulk_data.csv', 'w+') as f:
    f.write('\t'.join(header) + '\n')
    csv.writer(f, delimiter='\t', quoting=csv.QUOTE_MINIMAL, quotechar='"', lineterminator='\n').writerows(cursor)

Kermit · Accepted Answer · 2016-03-16 03:11:31Z

1

The simple answer is that you don't write row by row for this amount of data. You use a COPY to process the file in bulk. If you're using Python, you may want to leverage one of the many Vertica specific projects which allow for batch import such as PyVertica from Spil Games.

answered Mar 16, 2016 at 3:11

Kermit

34.1k13 gold badges89 silver badges121 bronze badges

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

2 Comments

Ygg Over a year ago

does COPY support exporting data to a file?

Kermit Over a year ago

@Ygg you would use vsql to export data to a file

Collectives™ on Stack Overflow

How to write 10 million+ rows into csv file from vertica using python?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related