Python: Export Large SQL Server Query Result to .txt File

Question

What is the most performance-efficient and memory-efficient way to copy a SQL Server query result of > 600,000,000 rows to a local .txt file? You may assume that I do not have have user permissions to export from SQL Server Management Studio. For this reason, Python seems to be my best option.

I am currently using the Python pyodbc package:

connection = pyodbc.connect('Driver=DRIVER;' \
                            'Server=SERVER;' \
                            'Database=DATABASE;' \
                            'uid=USERNAME;' \
                            'pwd=PASSWORD')

cursor = connection.cursor()
try:
    cursor.execute("SELECT * FROM %s" % table)
except:
    print('===== WAITING ===== EXECUTE ERROR =====')
    time.sleep(15)
    cursor.execute("SELECT * FROM %s" % table)

try:
    data = cursor.fetchall()
except:
    print('===== WAITING ===== FETCH ERROR =====')
    time.sleep(15)
    data = cursor.fetchall()

with open(output_file, 'w', newline='', encoding='utf-8') as f:
    writer = csv.writer(f, delimiter=delimiter)
    writer.writerow([x[0] for x in cursor.description])  # column headers
    for row in data:
        writer.writerow(row)
cursor.close()

Side note: my goal is to transfer several hundred SQL tables as .txt files to an Amazon S3 bucket. Is there a better way to do that instead of downloading the file to a local drive and then uploading to S3?

Have you tried bcp?

James Z
– James Z

2018-04-07 04:08:18 +00:00
Commented Apr 7, 2018 at 4:08 — James Z
– James Z, Commented Apr 7, 2018 at 4:08

FlipperPA · Accepted Answer · 2021-01-17 18:44:31Z

3

It depends on the result set, but as a general rule, I'd use fetchmany to grab a bunch of rows at a time instead of pulling everything into memory:

fetch_rows = 1000
rows = cursor.fetchmany(fetch_rows)
while rows is not None:
    for row in rows:
        do_something()
    rows = cursor.fetchmany(fetch_rows)

Good luck!

edited Jan 17, 2021 at 18:44

answered Apr 7, 2018 at 3:12

FlipperPA

14.4k5 gold badges46 silver badges76 bronze badges

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

Collectives™ on Stack Overflow

Python: Export Large SQL Server Query Result to .txt File

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