从 Microsoft SQL 查询到 Pandas Dataframe

我正在尝试用 Python3 编写一个程序，它将在 Microsoft SQL 中的表上运行查询并将结果放入 Pandas DataFrame 中。

我的第一次尝试是下面的代码，但由于某种原因，我不明白这些列没有按照我在查询中运行它们的顺序出现，它们出现的顺序以及它们作为结果更改而给出的标签，塞满我的程序的其余部分：

 import pandas as pd, pyodbc    

    result_port_mapl = []

    # Use pyodbc to connect to SQL Database
    con_string = 'DRIVER={SQL Server};SERVER='+ <server> +';DATABASE=' + 
<database>


     cnxn = pyodbc.connect(con_string)
    cursor = cnxn.cursor()

    # Run SQL Query
    cursor.execute("""
                   SELECT <field1>, <field2>, <field3>
                   FROM result
                   """)

    # Put data into a list
    for row in cursor.fetchall():
        temp_list = [row[2], row[1], row[0]]
        result_port_mapl.append(temp_list)

    # Make list of results into dataframe with column names
    ## FOR SOME REASON HERE row[1] AND row[0] DO NOT CONSISTENTLY APPEAR IN THE 
    ## SAME ORDER AND SO THEY ARE MISLABELLED
    result_port_map = pd.DataFrame(result_port_mapl, columns={'<field1>', '<field2>', '<field3>'})

我也尝试过以下代码

    import pandas as pd, pyodbc

    # Use pyodbc to connect to SQL Database
    con_string = 'DRIVER={SQL Server};SERVER='+ <server> +';DATABASE=' + <database>
    cnxn = pyodbc.connect(con_string)
    cursor = cnxn.cursor()

    # Run SQL Query
    cursor.execute("""
                   SELECT <field1>, <field2>, <field3>
                   FROM result
                   """)

    # Put data into DataFrame
    # This becomes one column with a list in it with the three columns 
    # divided by a comma
    result_port_map = pd.DataFrame(cursor.fetchall())

    # Get column headers
    # This gives the error "AttributeError: 'pyodbc.Cursor' object has no 
    # attribute 'keys'"
    result_port_map.columns = cursor.keys()

如果有人能提出为什么会发生这些错误或提供更有效的方法来实现这一点，我们将不胜感激。

Thanks

如果你只使用read_sql？喜欢：

import pandas as pd, pyodbc    
con_string = 'DRIVER={SQL Server};SERVER='+ <server> +';DATABASE=' + <database>
cnxn = pyodbc.connect(con_string)
query = """
  SELECT <field1>, <field2>, <field3>
  FROM result
"""
result_port_map = pd.read_sql(query, cnxn)
result_port_map.columns.tolist()