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