Watch folder and import into database

Question

I am trying to set up a system that whenever a new file is created in a folder it will automatically read the content and push it in a database. These files will mainly be .txt files but I might have to convert them to .csv files.

Currently I am using watchdog which works great. It "watches" a specific folder and creates an event every time a file(/folder) is created, edited, or removed. My question is what is the best/most efficient way to handle this event?

Files will be added everyday at a certain time (each day the same time), and can range from a 100 to a 1000 different files a day. All files are added to the folder within a few minutes.

My Code, using Psycopg:

import sys
import time
import logging
import psycopg2
import pprint
from watchdog.observers import Observer
from watchdog.events import LoggingEventHandler

def main():
    conn_string = "host='localhost' dbname='dbname' user='user' password='password'"
    print "Connecting to database\n ->%s" % (conn_string)
    conn = psycopg2.connect(conn_string)
    cursor = conn.cursor()
    cursor.execute("COPY trades FROM 'filepath/test.txt' DELIMITERS ',' CSV;")
    records = cursor.fetchall()
    pprint.pprint(records)

if __name__ == "__main__":
    main()
    logging.basicConfig(level=logging.INFO,
                        format='%(asctime)s - %(message)s',
                        datefmt='%Y-%m-%d %H:%M:%S')
    path = sys.argv[1] if len(sys.argv) > 1 else '.'
    event_handler = LoggingEventHandler()
    observer = Observer()
    observer.schedule(event_handler, path, recursive=True)
    observer.start()
    try:
        while True:
            time.sleep(1)
    except KeyboardInterrupt:
        observer.stop()
    observer.join()

As you can see right now the script connects to the database and imports test.txt (a static location), then it also watches a folder and prints whatever happens. These two events are yet unconnected. Thus my question, more specifically, should I call main() every time a new on_create is triggered (thus a new connection each time)? Or create a connection to the database and then watch the folder (thus maintaining an open connection throughout) and then calling cursos.execute every time a new on_create is triggered.

What would be the most efficient way?
(Of course filepath/test.txt will be changed to path of the found files)

Database: PostgreSQL. Python version 2.7.

Answer 1

I'd retain the connection. If you wanted to get fancy you could have a timeout set where if no new files came in within 10 minutes, you'd close the connection and re-create it next time it was needed.

If keeping the connection open it's very important that you make sure it doesn't keep a transaction open. It must commit (or rollback) the last item of work so the transaction is in the <IDLE> state. Failure to do this will cause database server performance problems and undesirable disk space use increases.

You can verify that the transaction is idle by examining the pg_stat_activity view in PostgreSQL.