One of the most tedious tasks of working with databases is to write and maintain documentation, in particular writing reports from tables and views.
So why not try to make this task a bit less heavy by using Python?
Step0-The input and the output
The script requires that the views are created using create view.
Example:
Right click on view

Copy view

Output view after python script

Step1 – Extract Columns Function
def extract_columns(in_txt):
"""Simple function to extract columns between select and from"""
# Splitting by select i only choose text after select
out_view = re.split(r'(?i)\bselect\b',in_txt)[1]
# Splitting by from i only choose text before from
out_view = re.split(r'(?i)\bfrom\b',out_view)[0]
# Cleaning extra spaces and tabs
out_view.strip()
return out_view
Let’s start by defining a function with the purpose of extracting only the columns from the script of a view. Thus ignoring everything before the Select statement and everything after the From statement.
Step2 -Let’s start from the variables
import re
# We open the file containing the view
view = extract_columns(open('input.txt','r',encoding='utf-8').read())
# Now we insert text into the list and we create a new list
view_l = view.split('\n')
out_l = []
Now we declare the variables we are going to use.
- view: contains the input text
- view_l: is a list created by splitting text by line
- out_l: is the output list
Step3-Main Code
# Now we loop through all the lines of the view
for num,line in enumerate(view_l):
# Clean extra spaces
line = line.strip()
# Remove Comments
line = line.split('--')[0]
# Remove Commas
line = line.replace(',','',1)
# Substitute Tabs with Spaces
line = line.replace('\t',' ')
# While two spaces are in line we substitute the with one space
while ' ' in line:
line = line.replace(' ',' ')
# Remove [ and ]
line = line.replace ('[','').replace(']','')
# If line is not empty
if len(line)>0:
# If line is not a comment
if line[0]!='-':
# We add the new line to out_l
out_l.append(re.split(r'(?i)\bas\b',line))
Finally we come to the main code.
The first part is a For loop, using the list we created in step2.
For each line the script removes useless characters and comments and then adds the line to the new list.
Step4-Output Text
# Open output file
with open('out.txt','w') as out_txt:
# For line in output list
for line in out_l:
# Try / Except to output all the values to the file separated by ','
print(line)
if len(line)>1:
out_txt.write('as'.join(line[0:-1]).strip()+'|'+str(line[-1]).strip()+'\n')
else:
out_txt.write(str(line[0]).strip()+'|'+str(line[0]).strip()+'\n')
In the last part we write to an external file using a Try/Except to avoid IndexErrors.