I am relatively new to the Python world. I wonder how I can achive below requirement using Python ecosystem, 1. Simple Interactive Web page for user to select date range. E.g. 'days' range selection for Orders data. 2. Ferch data from Oracle 11g DB based on days range selection. 3. Transformed the data into bar charts and present the same on Web app.
I googled a bit but did not find any complete information. Many thanks in advance. Thanks, Yogesh
There are several methods available which includes using python libraries sqlalchemy or pandas . However, for the method I describe here, you need to have the following Python libraries installed.
cx_Oracle
matplotlib
I will not be explaining the methods to get user input from the webpage or storing the plot as image and displaying the plot on the webpage. you can google or refer to these questions for various methods :-
Extracting Fields Names of an HTML form - Python
Dynamically serving a matplotlib image to the web using python
Let's assume you have read the user input and the two dates stored in the variables st_date and end_date. Here is the code.
import cx_Oracle
import matplotlib.pyplot as plt
query = 'SELECT order_date, count(order_id) order_count
FROM orders WHERE order_date BETWEEN ' + st_date + ' AND ' + end_date +
' GROUP BY order_date '
order_date = []
order_count = []
#connect to database and execute query.
db = cx_Oracle.connect('user', 'password', 'localhost:1521/XE')
cursor = db.cursor()
cursor.execute(query)
#loop through the rows fetched and store the records as arrays.
for row in cursor:
order_date.append(row[0])
order_count.append(row[1])
#plot the bar chart
plt.bar(order_date,order_count)
