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.