Once, because I was not familiar with SQLAlchemy, I stubbornly abandoned it and directly introduced the native CX_Oracle to generate reports, but after a lot of “context and so on” errors, I came back to SQLAlchemy.
Study SQLAlchemy carefully, you will find unexpected surprises. This article focuses on how to generate reports with different requirements.
Project description
- On the first page, with “ousistaff.phone” and “ousiguest.staff_phone” as the association, query out the employee’s associated customers and the client’s asset balance of this month.
- The second page, with ousistaff.phone and ousiguest.staff_phone as the correlation, query out each employee all related customers’ assets of this month, last month’s assets and this month’s asset increase management account.
- Employees with administrator privileges can see the relevant information of all employees under their department, while employees without administrator privileges can only view their own relevant information.
- The query function is left to the next chapter.
Create Report Templates
No further details. Please download the code to view the details.
Implement the first page
Set up the attempt function of the first page
The first page is easy to query using SQLAlchemy. The key points are:
- Distinguish permissions.
- Join table queries.
- Create the virtual field for the previous month.
- Paging display.
- Query nesting.
Views. Py content:
@show.route('/01', methods = ['GET', 'POST']) @login_required def _01(): g1 = aliased(OusiGuest) # This database is used again and again. G2 = aliased(OusiGuest) # The database is used repeatedly. Page = request. Args. Get ('page', 1, type=int) Database = db.session.query(ousistaff.department, ousistaff.name.label ('staff_name'), ousistaff.phone, OusiStaff.role, g1.name.label('guest_name'), g1.month, g1.balance, func.nvl(db.session.query(g2.balance).filter( g1.name == g2.name, func.to_date(g2.month, 'yyyy-mm') == func.add_months( func.to_date(g1.month, 'yyyy-mm'), -1) ), 0).label('last_balance') ).filter( OusiStaff.phone == g1.staff_phone, current_user.department == OusiStaff.department, g1.month == date.today().strftime('%Y-%m') ).order_by(g1.name).group_by(OusiStaff.department, OusiStaff.name.label('staff_name'), OusiStaff.phone, OusiStaff.role, g1.name.label('guest_name'), g1.month, g1.balance) else: database = db.session.query(OusiStaff.department, OusiStaff.name.label('staff_name'), OusiStaff.phone, OusiStaff.role, g1.name.label('guest_name'), g1.month, g1.balance, func.nvl(db.session.query(g2.balance).filter( g1.name==g2.name, func.to_date(g2.month, 'yyyy-mm')==func.add_months( func.to_date(g1.month, 'yyyy-mm'), -1) ), 0).label('last_balance') ).filter( OusiStaff.phone==g1.staff_phone, current_user.phone==g1.staff_phone, g1.month==date.today().strftime('%Y-%m') ).order_by(g1.name).group_by(OusiStaff.department, OusiStaff.name.label('staff_name'), OusiStaff.phone, OusiStaff.role, g1.name.label('guest_name'), g1.month, g1.balance) data = database.paginate(page, per_page=current_app.config['OUSI_POSTS_PER_PAGE'], error_out=False) return render_template('show/01.html', data=data)In fact, the above content is to use SQLAlchemy federated table query problem, will OUSISTAFF and OUSIGUEST with phone value as the association query, and then display the results.
One of the difficulties is to use func. To_date and func. Add_months to perform correlation queries against the last month’s time (all database supported functions can be operated by using func and function names in SQLAlchemy). , the second is to use the func. NVL function to assign 0 to the initialization data. For the SQLAlchemy syntax, see the code.
Tip:Note the syntax of SQLAlchemy.
func.nvl(db.session.query(g2.balance).filter(g1.name==g2.name, func.to_date(g2.month, 'yyyy-mm')==func.add_months(func.to_date(g1.month, 'yyyy-mm'), -1)), 0).label('last_balance')Oracle NVL = 0; Oracle NVL = 0; Oracle NVL = 0; Oracle NVL = 0;
The first page displays the results
The user with the permission of admin displays information about all employees under this department.
The user with the permission of general only displays the relevant information about himself.
Implementing the second page
Create an attempt function for the second page
The second page using SQLAlchemy is a little more complicated. The key points besides the requirements of the first page are:
- Add the related fields using func.sum.
- Subqueries are used.
Views. Py content:
@show.route('/02', methods = ['GET', 'POST'])
@login_required
def _02():
page = request.args.get('page', 1, type=int)
g1 = aliased(OusiGuest)
g2 = aliased(OusiGuest)
if current_user.role == 'admin':
sbq = db.session.query(OusiStaff.department, OusiStaff.role, OusiStaff.name.label('staff_name'),
g1.staff_phone, g1.name.label('guest_name'), g1.month, g1.balance,
func.nvl(db.session.query(g2.balance).filter(
g1.name==g2.name,
func.add_months(func.to_date(g1.month, 'yyyy-mm'), -1) == func.to_date(g2.month,
'yyyy-mm')
), 0).label('last_balance')).filter(
OusiStaff.phone==g1.staff_phone,
OusiStaff.department==current_user.department
).group_by(OusiStaff.department, OusiStaff.role, OusiStaff.name.label('staff_name'),
g1.staff_phone, g1.name.label('guest_name'), g1.month, g1.balance).subquery()
database = db.session.query(sbq.c.department, sbq.c.role, sbq.c.staff_name, sbq.c.staff_phone,
sbq.c.month, func.count(sbq.c.guest_name).label('members'),
func.sum(sbq.c.balance).label('balance'), func.sum(sbq.c.last_balance).label('last_balance')).\
filter(sbq.c.month==date.today().strftime('%Y-%m')).group_by(sbq.c.department, sbq.c.role,
sbq.c.staff_name, sbq.c.staff_phone,
sbq.c.month)
else:
sbq = db.session.query(OusiStaff.department, OusiStaff.role, OusiStaff.name.label('staff_name'),
g1.staff_phone, g1.name.label('guest_name'), g1.month, g1.balance,
func.nvl(db.session.query(g2.balance).filter(
g1.name == g2.name,
func.add_months(func.to_date(g1.month, 'yyyy-mm'), -1) == func.to_date(g2.month,
'yyyy-mm')
), 0).label('last_balance')).filter(
OusiStaff.phone == g1.staff_phone,
OusiStaff.phone == current_user.phone
).group_by(OusiStaff.department, OusiStaff.role, OusiStaff.name.label('staff_name'),
g1.staff_phone, g1.name.label('guest_name'), g1.month, g1.balance).subquery()
database = db.session.query(sbq.c.department, sbq.c.role, sbq.c.staff_name, sbq.c.staff_phone,
sbq.c.month, func.count(sbq.c.guest_name).label('members'),
func.sum(sbq.c.balance).label('balance'),
func.sum(sbq.c.last_balance).label('last_balance')). \
filter(sbq.c.month == date.today().strftime('%Y-%m')).group_by(sbq.c.department, sbq.c.role,
sbq.c.staff_name, sbq.c.staff_phone,
sbq.c.month)
data = database.paginate(page, per_page=current_app.config['OUSI_POSTS_PER_PAGE'], error_out=False)
return render_template('show/02.html', data=data)
Notice the fields in Query and the fields in GROUP_BY that group the query.
The second page displays the results
The user with the permission of admin displays information about all employees under this department.
The user with the permission of general only displays the relevant information about himself.
Download the source code
The next section is even more exciting, implementing the search query functionality, with another bunch of SQLAlchemy statements.