Query a MySQL Database and Return a HTML Table Report using Flask/jQuery


I have two tables: `accounts` and `journal`.

Accounts is:


| account | level | report | nature |


| salary | level 4 | income statement | 1 |

| rent | level 4 | income statement | 0 |

| expense1 | level 4 | income statement | 0 |

| expense2 | level 4 | income statement | 0 |


Journal is:


| date | credit | debit | value |


| 11-10-2017 | cash | stocks | $ 100 |

| 10-09-2017 | savings | cash | $ 500 |

| 10-09-2017 | revenue | cash | $ 2.000 |


I came up with the following query:


SELECT date_format(date_cash, '%b-%y'), account, ROUND(sum(value), 2)


SELECT [login to view URL] AS report, date_cash, debit AS account, SUM(CASE WHEN [login to view URL] = 0 THEN value ELSE -value END) AS value

FROM journal

JOIN accounts AS act_debit ON [login to view URL] = [login to view URL]

GROUP BY debit, YEAR(date_cash), MONTH(date_cash)


SELECT [login to view URL] AS report, date_cash, credit AS account, SUM(CASE WHEN [login to view URL] = 0 THEN value ELSE -value END) AS value

FROM journal

JOIN accounts AS act_credit ON [login to view URL] = [login to view URL]

GROUP BY credit, YEAR(date_cash), MONTH(date_cash)

) AS table

WHERE (report="Income Statement")

GROUP BY account, YEAR(date_cash), MONTH(date_cash)

ORDER BY date_cash ASC


Which gives me the following table:



| date | account | value |


| Jan-2017 | salary | 2.500 |

| Jan-2017 | rent | 500 |

| Feb-2017 | salary | 2.500 |

| Feb-2017 | rent | 500 |



This is a "stacked" version of an Income Statement, with all months stacked (and repeated) in the date column, and all accounts stacked (and repeated) in the value accounts column.

It definitely does not seem like the optimal code - it runs at about 150ms in Sequel Pro and has too many lines and repeated (similar) arguments - but it does work for flow account.

Even though it does not return NULL values (which are important in this case), they come up as soon as I transfer this table to a DataFrame in Python Pandas, and manipulate the query to get a Pivot Table just as the desired output from the question - accounts that have never been used do not appear though, which is something that I still have to work on - tried `RIGHT JOIN` in both statements to get all values from the accounts table, but that did not work.

Python code is:


df = pandas.read_sql(query, cnx)

report_is = pandas.pivot_table(df, values='Value', index='account', columns='data')


The "duplicated" `SELECT` is there because some accounts are credit by nature (credit increases the account), and some accounts are debit by nature (debit increases the account) - edited the question to reflect the change that had to be done in my DB. Probably there's a better way to do this.

Now, how do I get this into a HTML table with collapsable rows - Level 1 can collapse to show only the Level 1 total, or expand to show all acounts (which are child of the Level 1 parent) up to Level 4.

[Example]([login to view URL])

Kemahiran: Javascript, MySQL, Python

Lihat lagi: vbscript query mysql database, mysql database crud table php, query mysql database unix script, pass data from flask to javascript, flask javascript template, flask update page without refresh, jquery python example, flask call javascript function, flask javascript example, ajax python flask, $script_root, mysql, python, javascript, jquery, unix script query mysql database, php script query mysql database, php query mysql database, calendar query mysql database, joomla query mysql database view

Tentang Majikan:
( 0 ulasan ) Brazil

ID Projek: #15944256

5 pekerja bebas membida secara purata R$414 untuk pekerjaan ini

R$466 BRL dalam 3 hari
(92 Ulasan)

Hello, I have deep understanding of javascript. Also, I know UI design very [login to view URL] work I will use jquery, Bootstrap and jqueryUi libraries. UI elements which I built on jquery: [login to view URL] Lagi

R$466 BRL dalam 10 hari
(74 Ulasan)

I am an experienced professional in this field for about 3 years. I have done so many large scaled and medium scaled PHP MySQL projects. I too have worked with ERP system for diamond industry. I can surely deliver you Lagi

R$555 BRL dalam 2 hari
(15 Ulasan)

We are trying to revolution the way the web technology is operating in India . We are having the Complete team of HTML, CSS, PHP, .Net, Word press, SEO, Digital Marketing. Give the Best quality by our best team at a Lagi

R$466 BRL dalam 3 hari
(0 Ulasan)

Hello! I hope you be well, I want to help you in your project, listen your ideas and have a good deal. Thank you

R$115 BRL dalam 10 hari
(0 Ulasan)