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

Question

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?

I prefer pandas read_sql method for this. It is easier, less code, fast, safe and you don't have to remember to open and close connections: pandas.pydata.org/docs/reference/api/pandas.read_sql.html — It works on my pc
– It works on my pc, Commented Dec 13, 2021 at 14:15

EsmaeelE · Accepted Answer · 2024-02-06 19:58:38Z

1

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.

edited Feb 6, 2024 at 19:58

EsmaeelE

2,7557 gold badges28 silver badges36 bronze badges

answered Dec 13, 2021 at 14:20

It works on my pc

1668 bronze badges

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

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related