[sqlite ] exec-sql command create sheets with unique or helpful names.

[frosencrantz]

@saulpw added this wonderful exec-sql command in response to #1719

The wishlist item is to make that command more useful.

• The command always creates sheets with the same name query. Add a way to automatically create unique names for the sheets like make the name based on the query, or simply add a unique count suffix to the sheet name query1, query2, etc.
  • Add an input field for specifying the sheet name
• Make it easy to see the query associated with the sheet. I can not see this in the SheetsSheet. There is likely a way to do it, but I do not know.
• Make it easy to make an edit to the query used and create a new sheet
• Add column types

[midichef]

add a unique count suffix to the sheet name query1, query2, etc.

This works, in .visidatarc making names like 'query_01', 'query_02' etc for easier sortability:

@SqliteSheet.api
def rawSql(self, q:str) -> 'SqliteSheet':
    if not hasattr(self, 'query_ctr'):
        self.query_ctr = 0
    self.query_ctr += 1
    return SqliteSheet(f'query_{self.query_ctr:>02}', source=self.source, query=q)

see the query associated with the sheet. I can not see this in the SheetsSheet. There is likely a way to do it, but I do not know.

To see the query on SheetsSheet, = and then row.query

[frosencrantz]

Thanks, @midichef! Those are both useful tips.

I think vd should handle generating the unique names for the query sheets. But if it doesn't this works.

[dufferzafar]

@frosencrantz I was taking a look at the implementation of this to figure out if I can find an easy way to display the SQL query, because being able to view/edit/retrigger the query definitely sounds useful.

I thought perhaps I could reuse the source column in the SheetsSheet for this purpose and show the query there, which you could perhaps copy/paste in exec-sql command again?

But I couldn't get the sources bit to work, it seems source requires a Sheet .

[midichef]

@frosencrantz @dufferzafar
Try this in your .visidatarc, it should add a query column to SheetsSheet, you can edit it with edit-cell and it will reload after each edit:

SheetsSheet.columns.append(
    Column('query',
        getter=lambda c,r: getattr(r, 'query') if isinstance(r, SqliteSheet) else None,
        setter=lambda c,r,v: (setattr(r, 'query', v), r.reload()) if isinstance(r, SqliteSheet) else None
    )
)

You could change the order of the columns by changing .append( to .insert(1, to make it faster to get to the query. And you can customize the width by changing Column('query', to Column('query', width=80,

[frosencrantz]

Thank you @midichef! I just tried this and it works great.