How to read sql query to pandas dataframe / python / django

Question

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,})

I am able to print row and got a list of something like this below in 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/generated/pandas.read_sql_query.html#pandas.read_sql_query

Answer 1

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