0

I am using Python with pandas to import a CSV file into a table in Postgres

import pandas as pd
import psycopg2
from sqlalchemy import create_engine

df = pd.read_csv('products.csv', sep=';', low_memory=False)
engine = create_engine('postgresql://myuser:mypass@server/postgres')
df.to_sql('new_table', con=engine, if_exists='append', index=False, chunksize=20000)

The .csv file size is ~10GB. I left the script running for 15 hours but it's nowhere near finishing. What better way can I use to push the db to the server?

I can't import the db from the server directly because the compressed file size is larger than the size allowed.

Improve this question
7
  • @a_horse_with_no_name I'm trying to upload a CSV file(table) to a server, on a PostgreSQL db. I will remove the sql-server tag, sorry about that
    – Snow
    Jul 16, 2019 at 14:16
  • Does Python allow you to use copy ... from stdin? That would be much faster.
    – user1822
    Jul 16, 2019 at 14:18
  • @a_horse_with_no_name I would get a memory error then. I can't upload the whole table in one go.
    – Snow
    Jul 16, 2019 at 14:21
  • copy from stdin streams the file from the client to the server. It does not load the whole file into memory - at least with psql and Java this is the case. I don't know Python though
    – user1822
    Jul 16, 2019 at 14:22
  • can't you just write a sql script to insert them? like COPY table FROM 'path' WITH (FORMAT csv); keep in mind the names of the columns have to match
    – Chessbrain
    Jul 16, 2019 at 14:27

1 Answer 1

Reset to default
2

I used psql to push the CSV file to the table, as suggested by @a_horse_with_no_name. 

psql -h port -d db -U user -c "\copy products from 'products.csv' with delimiter as ',' csv header;"

It only took a couple of minutes to copy the table, compared to 10+ hours with the python script.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.