Execute SQL from Python: Format the result set

Question

I have a python script that lets me query a GIS database using SQL in Python:

import sys
import arcpy

try:
    arcpy.env.workspace = sys.path[0]
    egdb_conn = arcpy.ArcSDESQLExecute(r"Database Connections\Connection1.sde")
--> sql_statement = """SELECT * FROM USER1.A_TEST_TABLE"""
    sql_statement_list = sql_statement.split(";")
    print("+++++++++++++++++++++++++++++++++++++++++++++\n")
    # For each SQL statement passed in, execute it.
    for sql in sql_statement_list:
        print(sql)
        try:
            egdb_return = egdb_conn.execute(sql)
        except Exception as err:
            print(err)
            egdb_return = False
        if isinstance(egdb_return, list):
            for row in egdb_return:
                print(row)
            print("+++++++++++++++++++++++++++++++++++++++++++++\n")
        else:
            if egdb_return == True:
                print("SQL statement: {0} ran successfully.".format(sql))
            else:
                print("SQL statement: {0} FAILED.".format(sql))
            print("+++++++++++++++++++++++++++++++++++++++++++++\n")
except Exception as err:
    print(err)

The script works just fine, but the output is a little hard to read:

+++++++++++++++++++++++++++++++++++++++++++++

SELECT * FROM INFRASTR.A_TEST_TABLE
[1, u'1', u'APPLE', 0.1]
[2, u'2', u'PEAR', 0.2]
[3, u'3', u'BANANA', 0.3]
[4, u'4', u'MANGO', 0.4]
[5, u'5', u'ORANGE', 0.5]
[6, u'6', u'BLUEBERRY', 0.6]
[7, u'7', u'STRAWBERRY', 0.7000000000000001]
[8, u'8', u'RASPBERRY', 0.8]
[9, u'9', u'KIWI', 0.9]
[10, u'10', u'PINEAPPLE', 1.0]
+++++++++++++++++++++++++++++++++++++++++++++

It would be helpful if the result set could be output in a more traditional table format. Something like this would be great (although I'm not married to this exact layout):

 OBJECTID  ID  TYPE        COST 
 1         1   APPLE       0.01 
 2         2   PEAR        0.02 
 3         3   BANANA      0.03 
 4         4   MANGO       0.04 
 5         5   ORANGE      0.05 
 6         6   BLUEBERRY   0.06 
 7         7   STRAWBERRY  0.07 
 8         8   RASPBERRY   0.08 
 9         9   KIWI        0.09 
 10        10  PINEAPPLE   1.00

How can I do this? I'm not sure how to format the values, since they're in a list. I'm not too worried about formatting the COST as currency; I can do that in SQL.

Random Google search hit: https://pypi.python.org/pypi/tabulate — Tomalak, Apr 19 '17 at 19:37
Another one: http://stackoverflow.com/questions/9535954/printing-lists-as-tabular-data — Tomalak, Apr 19 '17 at 19:37
And another one: http://stackoverflow.com/questions/5909873/how-can-i-pretty-print-ascii-tables-with-python — Tomalak, Apr 19 '17 at 19:38
And yet another one: http://stackoverflow.com/questions/11245381/formatting-console-output — Tomalak, Apr 19 '17 at 19:38
It's hard to decide what question to pick as a duplicate target. — Tomalak, Apr 19 '17 at 19:39
`print("{:<10} {:4} {:12} {:<4}".format(*row))` See the dupe target for more info — TemporalWolf, Apr 19 '17 at 19:42

score 2 · Accepted Answer · answered Apr 19 '17 at 21:33

2

print(("{:<15}"*len(row)).format(*row))

will give you len(row) left aligned columns:

>>> row = [1, 3.4, "Pie", "pi", "a"]
>>> print(("{:<15}"*len(row)).format(*row))
1              3.4            Pie            pi             a

which is just doing "{:<15}"*len(row) to get "{:<15}{:<15}...{:<15}" then formatting in the values from row.

answered Apr 19 '17 at 21:33

TemporalWolf

7,727
1
30
50

1

This worked well for me because I didn't have to use any new libraries and was dead simple. – User1974 Apr 19 '17 at 23:34

score 1 · Answer 2 · answered Apr 19 '17 at 19:39

You could pass the output to a dictionary and pandas dataframe. There may be a more efficient way, but something like this would work.

import pandas

s = []
for row in egdb_return:
    r = []
    r.append(row)
    d = {}
    d["ObjectId"] = r.[1]
    d["ID"] = r[2]
    d["Type"] = r[3]
    d["Cost"] = r[4]
    s.append(d)

Table = pandas.dataframe(s)
print(Table)

Execute SQL from Python: Format the result set

2 Answers2

Linked