Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

up vote 0 down vote accepted

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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