How to create a DataFrame of more than 700 MB data from SQL Server using python pandas

Question

I have SQL table with more than 700 MB and keep increasing on daily basis which I have to write to a TXT file and SFTP it.

The below code is going for a time out.

for df in pd.read_sql(query, conn, chunksize=1000000)

Is there any other way I can chunk the result and send it smoothly?

Umar.H · Accepted Answer · 2019-11-21 15:52:41Z

1

your code doesn't really make any sense as you're not assigning pd.read_sql to anything

try :

chunk_size = 50000
dfs = []
for chunk in pd.read_sql("query",con=engine,chunksize=chunk_size)
    #transformation 
    dfs.append(chunk)

you can then concat the dataframe(s) and save this to a single txt file with a gzip compression since the dataframe is v.large.

final_df = pd.concat(dfs)
final_df.to_csv('file.txt.gz',index=False,compression='gzip')

answered Nov 21, 2019 at 15:52

Umar.H

23.1k8 gold badges50 silver badges94 bronze badges

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

2 Comments

Tried this method. Still timeout when running from server .

what database are you using, whats handling the connection layer?