0

I am able to show the all the tablenames and their respective columns as:

SNO Tables in Database  Column names 
1        table1               a
2        table1               b
3        table2               c
4        table2               d

for which html file is:

<html>
<head><link rel="stylesheet" href="{{ url_for('static', filename='css/index.css') }}"></head>
<body>
<div>
<table border="1" align="center" class="w3-table w3-striped">
    <caption><strong>Farm Automation Details</strong></caption>
  <thead>
    <tr>
      <th>SNO</th>
      <th style="text-align:center">Tables in Database</th>
      <th style="text-align:center">Column names</th>
    </tr>
  </thead>
  <tbody>
  {%for row in result%}
    <tr>
      <td></td>
        <td style="text-align:center">{{ row[0] }}</td>
      <td style="text-align:center">{{ row[1] }} </td>
    </tr>
  {%endfor%}
</table>
</div>
</body>
</html>

and to fetch table and column names i wrote:

sql="select table_name,column_name from information_schema.columns where table_schema = 'farmautomation' order by table_name,ordinal_position"
cursor.execute(sql)
result = cursor.fetchall()

I am expecting to show the table as:

SNO Tables in Database  Column names 
1        table1              a,b
2        table2              c,d

I tried to groupby on table_name,but it didn't work, May I know how can I show as above ? How to show the table name once and show all column names of respective table ?

khelwood
  • 55,782
  • 14
  • 81
  • 108
Sophie
  • 149
  • 1
  • 16
  • I think there may be a way to group the results as you'd like with `COALESCE` directly in SQL (maybe take a look [here](https://stackoverflow.com/questions/11351076/sql-concatenate-column-values-in-a-single-row-into-a-string-separated-by-comma)) But the easiest way would be to loop over the `result` and create a dict looking like `{ table1: [a, b] }`, then using this dict in your Jinja2 (?) template. You can use the `{{ loop.index }}` in your HTML to add a `SNO` – Plopp Aug 27 '19 at 08:00
  • A bit confused, please can you elaborate? – Sophie Aug 27 '19 at 08:08

1 Answers1

2

What you want to use is the GROUP_CONCAT function:

select table_name, group_concat(column_name order by column_name asc) as column_names
    from information_schema.columns
    where table_schema = 'farmautomation'
    group by table_name
    ;
Booboo
  • 38,656
  • 3
  • 37
  • 60