How to execute mysql query and get the output as data frame in python

December 13, 2021

I have to extract data from my remote server using sql query and show it as a database. So I used following code for that.

db_connection = sql.connect(host='10.10.10.10', database='cd', user='root', password='',charset='utf8')
cursor = db_connection.cursor()
raw_data_query =  "select date(originating_date_time),count(*) as Calls,sum(if(call_duration>0,1,0)) as Duration,sum(CEILING(call_duration/100))/60 from calldetailrecs where term_trunk_group in (986,985,984,983) group by date(originating_date_time)"
cursor.execute(raw_data_query)
cursor.fetchall()

I need to get the output as follows,

| date(originating_date_time) | calls  | sum(if(call_duration>0,1,0)) | sum(CEILING(call_duration/100))/60 |
+-----------------------------+--------+------------------------------+------------------------------------+

| 2021-12-12                  | 538507 |                       115322 |                        403437.7833 |
| 2021-12-13                  | 442065 |                       103372 |                        323565.3333 |
+-----------------------------+--------+------------------------------+------------------------------------+
4 rows in set (25.75 sec)

But when I used the above code, I could only get following output.

[(datetime.date(2021, 12, 12),
  538507,
  Decimal('115322'),
  Decimal('403437.7833')),
 (datetime.date(2021, 12, 13),
  442065,
  Decimal('103372'),
  Decimal('323565.3333'))]

Can someone explain how should I get a table like above mentioned?

>Solution :

You could use pandas read_sql if you want a pandas dataframe:

db_connection = sql.connect(host='10.10.10.10', database='cd', user='root', password='',charset='utf8')
raw_data_query = "select date(originating_date_time),count(*) as Calls,sum(if(call_duration>0,1,0)) as Duration,sum(CEILING(call_duration/100))/60 from calldetailrecs where term_trunk_group in (986,985,984,983) group by date(originating_date_time)"
df = pd.read_sql(raw_data_query, db_connection)

It is easy, fast and you don’t have to remember to close the connection afterwards.