File names to database table

Question

I have files in subfolders on a network drive. I want to create a list of files, and insert that list into an Oracle table.

I can successfully iterate through the files in the subfolders and do the insert:

• Loop through a user-defined list of subfolders called subfolder_list (I only want the files in specific subfolders; not all of the subfolders)
• Loop through the files in each subfolder
• Using an ArcSDESQLExecute class in the ArcPy site package, insert each file name into the records table

---

import arcpy
import os

db_conn_folder = r'C:\connection\folder\connection.sde'
conn = arcpy.ArcSDESQLExecute(db_conn_folder)

folder = r'\\my\network\folder'
subfolder_list = [r'\DRAWINGS', r'\DOCUMENTS', r'\VIDEOS']

#Delete all records in the table
db_exec = conn.execute('delete from records')

#Loop through the list of subfolders
for i in subfolder_list:
    #loop through the files in the subfolder
    for filename in os.listdir(folder + i):
        #insert the file names into the table
        db_exec = conn.execute('insert into records (file_name) values (' + filename + '\')')

#Commit the insert statements
db_exec = conn.execute('commit')

print "Complete."

The SQL is a little hard to read with all the python parsing and formatting. Plain-language SQL would look like this:

delete from records;
insert into records (file_name) values ('plan_profile_1.tif');
commit;

---

While the code works just fine, I'm wondering, can it be improved?

(I can count the number of Python scripts I've written on one hand.)

Answer 1

folder = r'\\my\network\folder'
subfolder_list = [r'\DRAWINGS', r'\DOCUMENTS', r'\VIDEOS']

Don't write the type into the variable name. The type should be able to change without the variable having to be renamed. subfolders already tells you that it is a collection of subfolders. subfolder_names might be better, to make clear that it contains the folders' names, not wrapper objects for the file system.

Removing the backslash from the beginning can prevent you from having to read up the code to make sure where the backslash is, at the beginning or the end. subfolder or subfolder_name implies just the name. So add the backslash wherever you concatenate the strings.

---

#Delete all records in the table
db_exec = conn.execute('delete from records')

That comment is obsolete, as it adds no information that is not already in the next line of code.

---

#Loop through the list of subfolders
for i in subfolder_list:
    #loop through the files in the subfolder
    for filename in os.listdir(folder + i):
        #insert the file names into the table
        db_exec = conn.execute('insert into records (file_name) values (' + filename + '\')')

Again, the comments are not needed, and instead impair readability. Instead of explaining with comments what the code does, improve the code to better explain itself. i can be acceptable as a loop variable, but whenever possible, use a more explicit name, like in this case subfolder_name. Then you get this more expressive code:

for subfolder_name in subfolder_names:
    for file_name in os.listdir(folder + r'\' + subfolder_name)
        db_exec = conn.execute('insert into records (file_name) values (' + file_name + '\')')

---

#Commit the insert statements
db_exec = conn.execute('commit')

Again the comment is not needed.

---

print "Complete."

What is complete? Give the user more info, e. g. `"42 file names inserted into database."