Python and SAP: Part 3 - Write your queries in SQL

This is the third part of my SAP and Python series (see part 1 and part 2 first!)

After you complete part 1 and part 2 - you can connect to SAP and query data. However, the RFC call parameters are clunky and the whole thing feels a bit complicated for daily use (to me at least). 

To make my life easier,  I wrote a little SQL parser. It's not special and it's not even all that efficient, but the it does the job.

In part 2, I wrote the following (without comments for a quicker read):

s = main() 

fields = ['MATNR', 'EAN11']
table = 'MEAN'
where = ['MATNR <> 0']
maxrows = 10
fromrow = 0

results, headers = s.qry(fields, table, where, maxrows, fromrow)

print headers
print results

Hardly pretty... However, with my SQL parser, I write this instead:

s = main() 

query = "select matnr, ean11 from mean where matnr <> 0"
maxrows = 10
fromrow = 0

results, headers = s.sql_query(query, maxrows, fromrow)

print headers
print results

Much cleaner. I just write SQL and suddenly, writing a little program do some mundane query is a breeze!

The two functions for this lovely toy is below. Let me know if you find a better method... 

Good luck with your SQL adventures :)

def split_where(self, seg):
    # This magical function splits by spaces when not enclosed in quotes..
    where = seg.split(' ')
    where = [x.replace('@', ' ') for x in where]
    return where

def select_parse(self, statement):
    statement = " ".join([x.strip('\t') for x in statement.upper().split('\n')])

    if 'WHERE' not in statement:
        statement = statement + ' WHERE '

    regex = re.compile("SELECT(.*)FROM(.*)WHERE(.*)")

    parts = regex.findall(statement)
    parts = parts[0]
    select = [x.strip() for x in parts[0].split(',')]
    frm = parts[1].strip()
    where = parts[2].strip()

    # splits by spaces but ignores quoted string with ''
    PATTERN = re.compile(r"""((?:[^ '"]|'[^']*'|"[^"]*")+)""")
    where = PATTERN.split(where)[1::2]

    cleaned = [select, frm, where]
    return cleaned

*** Update *** As requested by Florian, here is the code for sql_query. I've removed the headers option (as I prefer to get the rows into a dictionary, which provides the headers). 

def sql_query(self, statement, MaxRows=0, FromRow=0, to_dict=False):
    statement = self.select_parse(statement)

    results = self.qry(statement[0], statement[1], statement[2], MaxRows, FromRow)
    if to_dict:
        headers = statement[0]
        results2 = []
        for line in results:
            new_line = OrderedDict()
            header_counter = 0
            for field in line:
                try:
                    new_line[headers[header_counter]] = field.strip()
                    header_counter += 1
                except Exception as e:
                    new_line[headers[header_counter-1]] = new_line[headers[header_counter-1]]+ " " + " ".join(line[header_counter:])
                    break

            results2.append(new_line)
        results = results2
    return results