1

Apologies for the newbie question! I am working with a largeish dataset (500m rows, 150GB of data) and Postgres 9.1 running on Debian.

I have a Python import script for importing the source data, which is in the form of multiple CSV files. The script does the following:

  • Create the database (using Django's migrate command)
  • Delete indexes and foreign key checks, to improve loading time
  • Import the data using COPY
  • Re-create the indexes and foreign keys
  • Run VACUUM ANALYZE

Currently all this is in a Python script, using psycopg2.

This does the job. However, it's very important that this script loads the data in full, without any errors, every time I use it.

So I would like to check what I can do to make this script more robust.

I'm already handling errors, but I was wondering about validating the data once loaded to make sure that it looks how I expect.

I don't know if this is a standard or sensible thing to do, but given that I know how many entries there are in each CSV file (using wc -l) I could run COUNT queries afterwards to check that the number of rows is as expected.

Here is the full script, anyway - suggestions for improvements very welcome.

files = glob.glob('./raw_data/*_formatted.csv')
if len(files) != 45:
    # Check that we can see all the files we expect. 
    print 'Not all files present!'
    sys.exit()

for filename in files:
    print filename
    copy_str = "copy my_table(field1, field2) FROM STDIN WITH DELIMITER AS ','"
    file_obj = open(filename)
    cursor.copy_expert(copy_str, file_obj)
    try:
        conn.commit()
    except Exception as err:
        print err
        sys.exit()

try:
    cursor.execute('CREATE INDEX my_table_field1 ON my_table(field1)')
    cursor.execute('CREATE INDEX my_table_field2_varchar ON my_table(field2 varchar_pattern_ops)')
    cursor.execute('CREATE INDEX my_table_field1_field2 ON my_table(field1, field2 varchar_pattern_ops)')
    cursor.execute('VACUUM ANALYZE')
except Exception as err:
    print err

Your Answer

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

Browse other questions tagged or ask your own question.