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 select = [x.strip() for x in parts.split(',')] frm = parts.strip() where = parts.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, statement, statement, MaxRows, FromRow) if to_dict: headers = statement 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