CODEWITHSUNDEEP
csvpyodbc
Rakesh Kadukutty
Rakesh Kadukutty

Reputation: 11

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

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)

Upvotes: 0

Views: 1384

Answers (1)

Kermit
Kermit

Reputation: 34054

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.

Upvotes: 1

Related Questions