Python: How to Sort SQL statements in a text file?

Question

The output below is from Oracle; where it generates "create table" statements using a supplied package. I feed them into the python diff tool HtmlDiff which uses difflib under the covers. Each table is followed by a number of "alter table add constraint" commands that add the various constraints.

The problem is that there is no explicit ordering of the "alter table" commands, and I have to re-order them after generating the output file, before I run the comparison. I need help doing this; ideally using python, sed or awk.

The rules would be After each line containing "CREATE TABLE" until the next line containing "CREATE TABLE" sort each line containing " ADD CONSTRAINT "

This is a sample of my output:

    CREATE TABLE T1
    (     BOOK_ID NUMBER NOT NULL ENABLE,
      LOCATION_ID NUMBER NOT NULL ENABLE,
             NAME VARCHAR2(255) NOT NULL ENABLE,
     LEGAL_ENTITY VARCHAR2(255) NOT NULL ENABLE,
           STATUS CHAR(1) NOT NULL ENABLE
    ) ;

ALTER TABLE T1 ADD CONSTRAINT T1_CHK_1 CHECK ( status IN ( 'A', 'I' , 'T' ) ) ENABLE;
ALTER TABLE T1 ADD CONSTRAINT T1_PK PRIMARY KEY (BOOK_ID) ENABLE;
ALTER TABLE T1 ADD CONSTRAINT T1_AK_1 UNIQUE (NAME) ENABLE;
ALTER TABLE T1 ADD CONSTRAINT T1_FK_1 FOREIGN KEY (LOCATION_ID) REFERENCES T6 (LOCATION_ID) ENABLE;

CREATE TABLE T2
        (    
                    BUCKET_ID NUMBER  NOT NULL ENABLE,
         DATA_LOAD_SESSION_ID NUMBER,
                    IS_LOCKED CHAR(1) NOT NULL ENABLE,
               LOCK_DATE_TIME DATE
        ) ;
ALTER TABLE T2 ADD CONSTRAINT CKC_IS_LOCKED_BUCKET CHECK (IS_LOCKED in ('T','F')) ENABLE;
ALTER TABLE T2 ADD CONSTRAINT T2_PK PRIMARY KEY (BUCKET_ID) ENABLE;
ALTER TABLE T2 ADD CONSTRAINT T2_FK_2 FOREIGN KEY (RRDB_STAGING_TABLE_ID) REFERENCES T4 (RRDB_STAGING_TABLE_ID) ENABLE;

CREATE TABLE T3
        (                   VALUE_DATE DATE,
                               NODE_ID NUMBER,
                            RESULT_UID VARCHAR2(255),
         LATEST_EOD_SING_VAL_RESULT_ID NUMBER NOT NULL ENABLE
        )
ALTER TABLE T3 ADD CONSTRAINT T3_PK PRIMARY KEY (VALUE_DATE, NODE_ID, RESULT_UID) ENABLE;

Desired output: the only difference is the ordering of the " ADD CONSTRAINT " lines

CREATE TABLE T1
    (     BOOK_ID NUMBER NOT NULL ENABLE,
      LOCATION_ID NUMBER NOT NULL ENABLE,
             NAME VARCHAR2(255) NOT NULL ENABLE,
     LEGAL_ENTITY VARCHAR2(255) NOT NULL ENABLE,
           STATUS CHAR(1) NOT NULL ENABLE
    ) ;
ALTER TABLE T1 ADD CONSTRAINT T1_AK_1 UNIQUE (NAME) ENABLE;
ALTER TABLE T1 ADD CONSTRAINT T1_CHK_1 CHECK ( status IN ( 'A', 'I' , 'T' ) ) ENABLE;
ALTER TABLE T1 ADD CONSTRAINT T1_FK_1 FOREIGN KEY (LOCATION_ID) REFERENCES T6 (LOCATION_ID) ENABLE;
ALTER TABLE T1 ADD CONSTRAINT T1_PK PRIMARY KEY (BOOK_ID) ENABLE;

CREATE TABLE T2
    (    
                BUCKET_ID NUMBER  NOT NULL ENABLE,
     DATA_LOAD_SESSION_ID NUMBER,
                IS_LOCKED CHAR(1) NOT NULL ENABLE,
           LOCK_DATE_TIME DATE
    ) ;
ALTER TABLE T2 ADD CONSTRAINT CKC_IS_LOCKED_BUCKET CHECK (IS_LOCKED in ('T','F')) ENABLE;
ALTER TABLE T2 ADD CONSTRAINT T2_FK_2 FOREIGN KEY (RRDB_STAGING_TABLE_ID) REFERENCES T4 (RRDB_STAGING_TABLE_ID) ENABLE;
ALTER TABLE T2 ADD CONSTRAINT T2_PK PRIMARY KEY (BUCKET_ID) ENABLE;

CREATE TABLE T3
    (                   VALUE_DATE DATE,
                           NODE_ID NUMBER,
                        RESULT_UID VARCHAR2(255),
     LATEST_EOD_SING_VAL_RESULT_ID NUMBER NOT NULL ENABLE
    )
ALTER TABLE T3 ADD CONSTRAINT T3_PK PRIMARY KEY (VALUE_DATE, NODE_ID, RESULT_UID) ENABLE;

Answer 1

Load it all in to a string, then split on ";" to build an array of SQL commands. Loop the array and build a new sorted array. Pass along the CREATE TABLE bits and slice out the ALTER TABLE statements in to a separate list. sort() the alter list and extend it to the result array. When you're done, ';\n'.join(result_array) + ';'.

Answer 2

Quick-and-dirty, but i think this will do it for your case (worked on the example for sure):

conList = []
for ln in f:
    if ' ADD CONSTRAINT ' in ln:
        conList.append(ln)
    else:
        for it in sorted(conList):
            print it
        conList = []
        print ln
# finish any unfinished business 
for it in sorted(conList):
    print it