Python SQLAlchemy: Row limit to store to PostgreSQL?

Question

I am using SQLAlchemy in Python3 to store pandas dataframes to PostreSQL table. Things work until 78M rows to store, things work with 20M rows but

Got 75032111 rows.

Total time taken 11222.68 s.
Finished at 2018-05-04 06:07:34.
Killed

where the storing gets killed. I use the SQLAlechemy command df.to_sql(dbName, engine).

Is there some limit to store data to PSQL database with SQLAlchemy in Python? What is the preferred way to store big tables, some sync command to continue storing if things get intercepted because of large size?

Maybe your process was OOM killed? One way to store potentially huge amounts of data to Postgresql is to use the COPY command, but then you'd have to convert your data to a suitable format, though you could do it generatively. — Ilja Everilä
– Ilja Everilä, Commented May 4, 2018 at 6:21
Run dmesg and see if that is exactly the case. Will be a very apparent spam that says it killed Python. — metatoaster
– metatoaster, Commented May 4, 2018 at 6:23
@metatoaster temperatures of my CPUs are getting too high when operating with very large files, potentially killing some operations but I was able to solve this problem with the answer below with chunksize=100000, dunno about heat problem, have to monitor it. Thank you for helping to figure it out +1 — hhh
– hhh, Commented May 10, 2018 at 21:28

Scratch'N'Purr · Accepted Answer · 2018-05-04 06:22:21Z

1

I haven't hit this limit before, but what you could do is insert by batches using:

df.to_sql(dbName, engine, chunksize=100000)  # alter chunksize to your liking

answered May 4, 2018 at 6:22

Scratch'N'Purr

10.5k2 gold badges39 silver badges54 bronze badges

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

2 Comments

hhh Over a year ago

This fixed the issue but dmesg reveals that the temperatures of my CPUs are getting too high when doing the heavy lifting on very large files +1

Scratch'N'Purr Over a year ago

I think that's natural since your CPUs have to do the inserts to the db. Did you try altering the chunksize to see if that alleviates the temps? (e.g. chunksize=10000).

Collectives™ on Stack Overflow

Python SQLAlchemy: Row limit to store to PostgreSQL?

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