从 Microsoft SQL 查询到 Pandas 数据框 [英] Querying from Microsoft SQL to a Pandas Dataframe

问题描述

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

I am trying to write a program in Python3 that will run a query on a table in Microsoft SQL and put the results into a Pandas DataFrame.

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

My first try of this was the below code, but for some reason I don't understand the columns do not appear in the order I ran them in the query and the order they appear in and the labels they are given as a result change, stuffing up the rest of my program:

 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()

如果有人能提出为什么会发生这些错误或提供更有效的方法来解决这个问题，我们将不胜感激.

If anyone could suggest why either of those errors are happening or provide a more efficient way to do it, it would be greatly appreciated.

谢谢

推荐答案

如果你只是使用 read_sql?喜欢:

If you just use read_sql? Like:

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()

这篇关于从 Microsoft SQL 查询到 Pandas 数据框的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！