Python: Clean SQL views for Documentation

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

img1

Copy view

img2

Output view after python script

img3


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.

Annunci

Un pensiero su “Python: Clean SQL views for Documentation

  1. You’re so cool! I do not believe I have read anything like
    that before. So good to find someone with a few unique thoughts on this topic.
    Really.. many thanks for starting this up. This web site is
    something that’s needed on the internet, someone with some originality!

    Mi piace

Rispondi

Inserisci i tuoi dati qui sotto o clicca su un'icona per effettuare l'accesso:

Logo WordPress.com

Stai commentando usando il tuo account WordPress.com. Chiudi sessione / Modifica )

Foto Twitter

Stai commentando usando il tuo account Twitter. Chiudi sessione / Modifica )

Foto di Facebook

Stai commentando usando il tuo account Facebook. Chiudi sessione / Modifica )

Google+ photo

Stai commentando usando il tuo account Google+. Chiudi sessione / Modifica )

Connessione a %s...