如何读取对 pandas 数据框/python/Django的SQL查询 [英] How to read sql query to pandas dataframe / python / django

问题描述

我正在下面的 views.py 中使用它来获取应用

I'm using this below in views.py to get app

from django.db import connection

def test(request):

    cursor = connection.cursor()
    sql = """
    SELECT x , n
    from table1 limit 10
    """
    cursor.execute(sql)
    rows = cursor.fetchall()

    # df1 = pd.read_sql_query(sql,cursor)  <==> not working ) 
    # df1.columns = cursor.keys()    <==> not working ) 

    return render(request, 'app/test.html',{ "row" : rows,})

我能够打印行，并在 test.html

row((x1,yvalue1),(x2,yvalue2) , .... ))

但是我想做的是获取所有具有我提取的列名并将其放入数据框的数据，希望使用以下类似的内容:

But what I'm trying to do is to get all data with its column name that I fetched and put into dataframe , hopefully to use something like this below :

http://pandas.pydata. org/pandas-docs/stable/generation/pandas.read_sql_query.html#pandas.read_sql_query

推荐答案

我认为aus_lacy在他的解决方案中有点过头-首先，您必须将QuerySet转换为包含支持QuerySet的SQL的字符串. >

I think aus_lacy is a bit off in his solution - first you have to convert the QuerySet to a string containing the SQL backing the QuerySet

from django.db import connection

query = str(ModelToRetrive.objects.all().query)
df = pandas.read_sql_query(query, connection)

还有一种内存效率较低但仍然有效的解决方案:

Also there is a less memory efficient but still valid solution:

df = DataFrame(list(ModelToRetrive.objects.values('id','some_attribute_1','some_attribute_2'))) 

这篇关于如何读取对 pandas 数据框/python/Django的SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！