Create nice formatting in Python

Question

Im trying to write a python script that helps me with formatting sql. Goal is to have the data format and the NULL's line out neatly.

It looks like this:

CREATE TABLE [dbo].[?????????????????????????](
 , [timestamp] TIMESTAMP NULL
 , [??????] INT NULL
 , [?????????] NVARCHAR(20) NULL

I want it to look like this:

CREATE TABLE [dbo].[?????????????????????????](
 , [timestamp]      TIMESTAMP    NULL
 , [??????]         INT          NULL
 , [?????????]      NVARCHAR(20) NULL

I wrote code that lines out the datatypes. This works!

maxh = 0

fhand = open('file name')
for line in fhand:
    line = line.rstrip()
    if line.startswith(' , ['):                #find lines that need formating
        hindex = line.index(']')               #find the position of ]
        if hindex > maxh: maxh = hindex        #find the range that needs to be covered
        x = maxh - hindex                      #find the number of spaces needed 
        print(line.replace(']',(']'+(' '*x)))) #print the right amount of spaces
    else:
        print(line)                            #print all lines that do not need formating

My code to line out the NULLS does not work When i run this the output is the same as the file.

maxnull = 0

fhand = open('filename')
for line in fhand:
    line = line.rstrip()
    if line.startswith(' , ['):
        nullindex = line.find('NULL')
        if nullindex > maxnull: maxnull = nullindex
        y = maxnull - nullindex
        print(line.replace('NULL',('NULL'+(' '*y))))
    else:
        print(line)

I cant figure out why is the first part working but the second part isn't.

Are you doing it as a personal training exercise? Otherwise, there are existing libraries that do this very well - for example: pygments-pprint-sql, sqlparse, format-sql, sql_formatter. See this similar question (which starts with SQLalchemy, the output is standard SQL): https://stackoverflow.com/questions/44335158/how-to-pretty-format-the-printing-of-sql-queries-in-sqlalchemy. — Alan, Jul 19 '21 at 17:10
TL;DR but this `'NULL'+(' '*y)` adds spaces *after* the word NULL and not before. — , Jul 19 '21 at 17:15

Create nice formatting in Python

0 Answers0