从Python执行SQL：格式化结果

Question

我有一个python脚本，它允许我在python中query a GIS database using SQL：

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)

脚本工作正常，但输出有点难以阅读：

^{pr2}$

如果结果集可以以更传统的表格格式输出，那会很有帮助。像这样的东西会很棒的（虽然我没有嫁给这个确切的布局）：

 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 

我该怎么做？我不知道如何格式化这些值，因为它们在一个列表中。我不太担心将COST格式化为货币；我可以在SQL中这样做。在

Answer 1

您可以将输出传递到字典和pandas数据帧。也许有一种更有效的方法，但这样的方法是可行的。在

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)

Answer 2

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

将为您提供len(row)左对齐的列：

^{pr2}$

它只是执行"{:<15}"*len(row)以获得"{:<15}{:<15}...{:<15}"，然后格式化from行中的值。在