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 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?
filepath/test.txt will be changed to path of the found files)
Database: PostgreSQL. Python version 2.7.