kze1qfN7

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