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

I don't know anything about django, but i believe the only 'native' connection you can use with read_sql_query is for sql lite. You need to use sql alchemy for all others. — Bob Haffner
– Bob Haffner, Commented Jan 4, 2015 at 22:24

JohnnyM · Accepted Answer · 2015-02-24 10:16:47Z

32

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

answered Feb 24, 2015 at 10:16

JohnnyM

1,2732 gold badges13 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

alacy · Accepted Answer · 2015-01-04 23:10:27Z

0

You need to use Django's built in QuerySet API. More information on it can be seen here. Once you create a QuerySet you can then use pandas read_sql_query method to construct the data frame. The simplest way to construct a QuerySet is simply query the entire database which can be done like so:

db_query = YourModel.objects.all()

You can use filters which are passed in as args when querying the database to create different QuerySet objects depending on what your needs are.

Then using pandas you could do something like:

d_frame = pandas.read_sql_query(db_query, other_args...)

answered Jan 4, 2015 at 23:10

alacy

5,0748 gold badges32 silver badges47 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related