Formatting results from cursor.execute

Question

I need help to have my results show as amount, amount_discounted and month in a table. I was able to do it in MySQL workbench but not through Python. Here is my code.

#! /usr/bin/env python

# Importing connector

import mysql.connector
conn = None
#Opening a connection
try:
    conn = mysql.connector.Connect(host="localhost", user="root",password="Karimanzira1978!", database="classicmodels")
    print("Connected to MySQL!")
except Exception as ex:
    print ("cannot connect to MySQL : exception : " + str(ex))

# Setting a cursor

cursor1 = conn.cursor()
cursor2= conn.cursor()

c = conn.cursor()


cursor1.execute("SELECT sum(amount), sum(amount *.99) as discounted_amount, paymentDate from payments where paymentDate >= '2003-12-01' AND paymentDate <='2003-12-31'")

print(cursor2.fetchall())

cursor2.execute("SELECT sum(amount), sum(amount *.99) as discounted_amount, paymentDate from payments where paymentDate >= '2004-12-01' AND paymentDate <='2004-12-31'")


print(cursor2.fetchall())

conn.commit()

conn.close

score 1 · Answer 1 · answered May 27 '18 at 07:20

You can use the pandas module's DataFrame

First: python -m pip install -U pandas to install pandas for your version of Python.

Second import it with from pandas import DataFrame.

Then, (using the second cursor as an example) store data from the .execute to a variable like so (just add r = infront of the cursor2.execute):

r = cursor2.execute("SELECT sum(amount), sum(amount *.99) as discounted_amount, paymentDate from payments where paymentDate >= '2004-12-01' AND paymentDate <='2004-12-31'")

Now, store the fetchall() into a dataframe: df = DataFrame(r.fetchall())

Last, set the columns of the dataframe: df.columns = r.keys()

You should be able to see the first 5 rows of the table with: df.head()

Referenced: How to convert SQL Query result to PANDAS Data Structure?

score 0 · Answer 2 · answered May 27 '18 at 10:35

Going forward, you might also want to take a look at the pandas.read_sql function (documented here). You pass it a SQL query and a database connection and it does all the work of reading that into a Pandas dataframe for you!

For example:

import mysql.connector
from pandas import read_sql

conn = None
#Opening a connection
try:
    conn = mysql.connector.Connect(host="localhost", user="root",password="Karimanzira1978!", database="classicmodels")
    print("Connected to MySQL!")
except Exception as ex:
    print ("cannot connect to MySQL : exception : " + str(ex))

query = "SELECT sum(amount), sum(amount *.99) as discounted_amount, paymentDate from payments where paymentDate >= '2003-12-01' AND paymentDate <='2003-12-31'"

df = read_sql(query, conn)

Formatting results from cursor.execute

2 Answers2