-1

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.

  • @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 at 14:16
  • Does Python allow you to use copy ... from stdin? That would be much faster. – a_horse_with_no_name Jul 16 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 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 – a_horse_with_no_name Jul 16 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 at 14:27
1

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.

0

Don't use Python with pandas... they prefer bamboo... Take the easier route and just use SQL Server.

Normally I'd point you to SQL Server Migration Assistant, but MS doesn't make a plugin for Postgres. Instead, create a Linked Server to Postgres (instructions here or here), and import each table to SQL Server by way of four-part queries, OPENQUERY, or OPENROWSET. Alternatively, you can also bypass the linked server and just use the OPENDATASOURCE command instead.

For an entire database migration, you will need to create any primary keys/foreign keys, rewrite any procedures (as their's no guarantee they will map 1-to-1), etc. but since your question really looks to be asking how to import a single table, a Linked Server is a pretty streamlined approach from my experience.

NOTE: This is also a pretty straight forward way to migrate data from SQL Server into Postgres as well, in case that's of interest.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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