How to read SQL query in pandas dataframe / python / django
I am using this below in views.py to get my 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 can print a string and got a list of something like this below in test.html
row((x1,yvalue1),(x2,yvalue2) , .... ))
But what I am trying to do is get all data with my column name which I have selected and placed in the dataframe, hopefully something like this below:
source to share
I think aus_lacy is a bit in its solution - first you need to convert QuerySet
to a string containing SQL supportQuerySet
from django.db import connection query = str(ModelToRetrive.objects.all().query) df = pandas.read_sql_query(query, connection)
There is also a less efficient but still relevant solution:
df = DataFrame(list(ModelToRetrive.objects.values('id','some_attribute_1','some_attribute_2')))
source to share
You need to use Django's built in QuerySet API
. More information about him can be found here . Once created, QuerySet
you can use the pandas method read_sql_query
to build the dataframe. The easiest way to build QuerySet
is to just query the entire database, which can be done like this:
db_query = YourModel.objects.all()
You can use filters that are passed as arguments when querying the database to create different objects QuerySet
depending on your needs.
Then, using pandas, you can do something like:
d_frame = pandas.read_sql_query(db_query, other_args...)
source to share