· 7 years ago · Sep 04, 2018, 09:12 PM
1"""A tool for saving files to and from a postgresql db.
2
3 *** THIS IS WRITTEN FOR PYTHON 2.7 ***
4"""
5
6# To use it for the current directory, run this in the command line:
7# python uploader_script.py .\ --store
8
9
10import os
11import sys
12import argparse
13import psycopg2
14import time
15import datetime
16import msvcrt as m
17
18db_conn_str = "xxx"
19
20# Define your table schema
21create_table_stm = """
22CREATE TABLE IF NOT EXISTS data_warehouse (
23 id serial primary key,
24 orig_filename text not null,
25 file_extension text not null,
26 created_date date not null,
27 last_modified_date date not null,
28 upload_timestamp_UTC timestamp not null,
29 uploaded_by text not null,
30 file_size_in_bytes integer not null,
31 original_containing_folder text not null,
32 file_data bytea not null
33)
34"""
35
36# raw_input will be different in python3
37uploaded_by = raw_input("Please, enter your [Firstname] [Lastname]: ")
38
39if not uploaded_by:
40 print "You did not enter your name. Press ENTER to exit this script, then attempt to run the script again."
41 m.getch()
42 exit()
43else:
44 print "Thank you, " + uploaded_by + "! Please, press ENTER to upload the files."
45 m.getch()
46
47# Walk through the directory
48def main():
49 parser = argparse.ArgumentParser()
50 parser_action = parser.add_mutually_exclusive_group(required=True)
51 parser_action.add_argument("--store", action='store_const', const=True, help="Load an image from the named file and save it in the DB")
52 parser_action.add_argument("--fetch", type=int, help="Fetch an image from the DB and store it in the named file, overwriting it if it exists. Takes the database file identifier as an argument.", metavar='42')
53 parser.add_argument("parentdir", help="Name of folder to write to / fetch from")
54 args = parser.parse_args()
55
56 conn = psycopg2.connect(db_conn_str)
57 curs = conn.cursor()
58
59 # Run the create_table_stm code at the top of this file to generate the table if it does not already exist
60 curs.execute(create_table_stm)
61
62 for root, dirs, files in os.walk(args.parentdir):
63 for name in files:
64 # Store the original file path from the computer the file was uploaded from.
65 joined_var = os.path.join(root)
66 original_path_raw = os.path.abspath(joined_var)
67 original_path = os.path.normpath(original_path_raw)
68
69 # Set the file the script is looking at to a variable for later use to pull filesize
70 filestat = os.stat(os.path.join(root, name))
71
72 # Split the file extension from the filename
73 file_extension_holder = os.path.splitext(name)[1]
74
75 # Time module: https://docs.python.org/3.7/library/time.html#module-time
76 # The return value is a number giving the number of seconds since the epoch (see the time module).
77 # The epoch is the point where the time starts, and is platform dependent. For Windows and Unix, the epoch is January 1, 1970, 00:00:00 (UTC).
78 # To find out what the epoch is on a given platform, look at time.gmtime(0). The code below is written for Windows.
79
80 # Datetime module: https://docs.python.org/3/library/datetime.html
81
82 # More info: https://stackoverflow.com/questions/237079/how-to-get-file-creation-modification-date-times-in-python
83
84 # Generate the created_date -- I suspect there is a more straightforward way to do this with the time or datetime module. But this works.
85 c_time_in_seconds = os.path.getctime(os.path.join(root, name))
86 c_time_array = str(time.gmtime(c_time_in_seconds)[:3])
87 c_date_str = ''.join(c_time_array)
88 c_format_str = '(%Y, %m, %d)'
89 c_datetime_obj = datetime.datetime.strptime(c_date_str, c_format_str)
90 created_date = c_datetime_obj.date()
91
92 # Generate the last_modified_date
93 m_time_in_seconds = os.path.getmtime(os.path.join(root, name))
94 m_time_array = str(time.gmtime(m_time_in_seconds)[:3])
95 m_date_str = ''.join(m_time_array)
96 m_format_str = '(%Y, %m, %d)'
97 m_datetime_obj = datetime.datetime.strptime(m_date_str, m_format_str)
98 last_modified_date = m_datetime_obj.date()
99
100 # Generate the timestamp of the upload (in UTC timezone)
101 py_uploaded_timestamp = datetime.datetime.now()
102
103
104
105 if args.store:
106 handler = os.path.join(root, name)
107 with open(handler,'rb') as f:
108
109 # read the binary
110 filedata = psycopg2.Binary(f.read())
111
112 # Call the st_size command from os.stat to read the filesize in bytes
113 filesize = filestat.st_size
114
115 # This has to agree with the table schema you set at the top of this file
116 curs.execute("INSERT INTO data_warehouse(id, orig_filename, file_extension, created_date, last_modified_date, upload_timestamp_UTC, uploaded_by, file_size_in_bytes, original_containing_folder, file_data) VALUES (DEFAULT,%s,%s,%s,%s,%s,%s,%s,%s,%s) RETURNING id", (name, file_extension_holder, created_date, last_modified_date, py_uploaded_timestamp, uploaded_by, filesize, original_path, filedata))
117 print curs
118 returned_id = curs.fetchone()[0]
119 print("Stored {0} into DB record {1}".format(args.parentdir, returned_id))
120 conn.commit()
121
122 elif args.fetch is not None:
123 with open(args.parentdir,'wb') as f:
124 curs.execute("SELECT file_data, orig_filename FROM files WHERE id = %s", (int(args.fetch),))
125 (file_data, orig_parentdir) = curs.fetchone()
126 f.write(file_data)
127 print("Fetched {0} into file {1}; original parentdir was {2}".format(args.fetch, args.parentdir, orig_filename))
128
129
130
131
132 conn.close()
133
134if __name__ == '__main__':
135 main()