I am using matplotlib to scatter plot some data from an Oracle table. One of the fields is a DATE and when converting it to a to_char in the below query I get an error. I want to scatter plot by hours 00-23 not by date. So Y axis should show 00-23. See below code example along with error.
Thanks for any help.
import cx_Oracle, os
import numpy
import matplotlib.pyplot as plt
This works
sql = """select number_of_small_reads, number_of_small_writes, number_of_large_reads, number_of_large_writes, end_time from schema.table order by end_time"""
This does not work
sql = """select number_of_small_reads, number_of_small_writes, number开发者_开发技巧_of_large_reads, number_of_large_writes, to_char(end_time, 'HH24') e from schema.table order by e"""
I get the following error: "cannot perform reduce with flexible type"
conn = cx_Oracle.Connection("user/password@server:1521/database")
cursor = conn.cursor()
cursor.execute(sql)
rowset = cursor.fetchall()
cursor.close()
cx1 = []
cx2 = []
cx3 = []
cx4 = []
cy = []
cx1, cx2, cx3, cx4, cy = zip(*rowset)
ax = plt.figure().add_subplot(111)
ax.scatter(cx1, cy, s=9, c='b', marker='o', label='number_of_small_reads')
ax.scatter(cx2, cy, s=9, c='r', marker='o', label='number_of_small_writes')
ax.scatter(cx3, cy, s=9, c='g', marker='o', label='number_of_large_reads')
ax.scatter(cx4, cy, s=9, c='y', marker='o', label='number_of_large_writes')
plt.legend(shadow=True)
plt.title('Python Generated Chart')
plt.xlabel('IOPS')
plt.ylabel('By Date')
plt.xlim(0)
plt.show()
conn.close()
You can just select the hour instead of the date.
SELECT .. DATEPART(mm, end_date) AS end_hour
http://www.bennadel.com/blog/172-Ask-Ben-Selecting-Parts-of-a-Date-Time-Stamp-In-SQL.htm
精彩评论