SQLite User Forum

Excel formulas in SQLITE
Login
Chronological

Excel formulas in SQLITE

(1) By anonymous on 2022-03-23 15:51:27 [link] [source]

about

One of the things I don't like is the things that involve spreadsheets, they are generally bad for managing a certain amount of data. I would like to have the possibility if it is possible to use excel formulas within sqlite, my argumentative basis for this are 4 things:

  1. Users like me don't like spreadsheets, an alternative would be to offer a minimal structure, ie only excel functions in sqlite. When I said, "Users like me don't like spreadsheets", I mean people who know how to work with spreadsheets but don't. I can cite an example, a friend of mine who has a personal store, he doesn't have much money to invest in databases like postgresql, mysql, maridb... A good part of my friend's database is made by electronic spreadsheets, because he has no money, his database ends up in spreadsheets.
  2. If it were possible to use Excel formulas, people could easily migrate to sqlite in one go. There would be no need to use Excel if I had a portable database that provided the functions I use in a spreadsheet.
  3. Remember my first argument? That I talked about "Users like me don't like spreadsheets" - so that's it, there are newbie users I've met throughout my life as a frontend who never stopped doing things in spreadsheets, that's no problem at all. I just see and observe that things could be better, my intention here would be to report some personal issues - in fact, bad experiences I've had over the years as a frontend using spreadsheets as my personal database.

Let's go now!

  1. Before knowing sqlite, I saved everything in spreadsheets. This is the worst thing you can do, you can corrupt your data if you don't have a backup or even if the software changes you can lose this type of file.
  2. This has happened with Microsoft Office before, but they solved this problem by adopting an open file format like ODT - Open Document Text. Here's where the problem is! Some formulas excel does not work in ODT or ODT in Exel. Also, most people just want to save data without having something complex.
  3. Open source files or closed source files usually don't have a good implementation of the file format.
  4. I mention this because spreadsheets don't have something as cool as a sql for querying data and they are not relational.
  5. Therefore, making spreadsheets is something that I think is a waste of time. Also, it's not worth it because it's not a query language like sql and it's not relational.
  6. If your small excel files take up almost 10gb, if I use sqlite as a personal database that number would reduce to less, maybe something like 5gb because generally the information I need can be more efficient and "more compact" ie - as is relational information, I tend not to repeat certain data, which usually does not go unnoticed in the spreadsheets I use.

Notes

  1. Here I talked about my personal experience with spreadsheets,
  2. I spoke of the experience of some people who use electronic spreadsheets
  3. Talk about the data migration issue that many people want but don't have the money for. And in that case, if I had the excel formulas in SQLITE, I could be more efficiently and replace as worksheets once and for all.
  4. I spoke of my intention that I use sqlite with excel formulas.
  5. Sqlite is a portable database I would find it pretty cool if it had support for excel formulas
  6. I'm not promoting excel in Sqlite, I'm just citing the feature of excel formulas that could be implemented in sqlite.
  7. My goal is not to criticize the existing open format as ODT, just to cite my bad experience using ODT in some cases that I mentioned.
  8. sqlite has implemented some math functions, reference: https://www.sqlite.org/lang_mathfunc.html - if it is possible to use excel formulas within sqlite too?

short solutions

  1. Create an rfc that requests CSV with excel formulas. csv is an open file format, however there is no standardization. It would be really cool to have a structured/unstructured data language in RFC - Resource For Comments for CSV that has excel formulas. It's an idea I thought of, but I haven't written anything yet.
  2. Request that excel formulas be implemented in SQLITE.

cases 1 allowed synxtax

sqlite> SELECT CustomerSample, City FROM Customers WITH CONCATENATE
sqlite> SELECT CustomerSample, City FROM Customers WITH FLOOR(number)
sqlite> SELECT CustomerSample, City FROM Customers WITH BINOMDIST(number)
sqlite> SELECT CustomerSample, City FROM Customers WITH CHIDIST(number)
sqlite> SELECT CustomerSample, City FROM Customers WITH CHIINV(number)
sqlite> SELECT CustomerSample, City FROM Customers WITH CHITEST(number)
sqlite> SELECT CustomerSample, City FROM Customers WITH CONFIDENCE(number)
sqlite> SELECT CustomerSample, City FROM Customers WITH FTEST(number)
sqlite> SELECT CustomerSample, City FROM Customers WITH LOGINV(number)
sqlite> SELECT CustomerSample, City FROM Customers WITH LOGNORMDIST(number)
sqlite> SELECT CustomerSample, City FROM Customers WITH NORMDIST(number)
sqlite> SELECT CustomerSample, City FROM Customers WITH NORMINV(number)
sqlite> SELECT CustomerSample, City FROM Customers WITH NORMSDIST(number)
sqlite> SELECT CustomerSample, City FROM Customers WITH NORMSINV(number)
sqlite> SELECT CustomerSample, City FROM Customers WITH PERCENTILE(number)
sqlite> SELECT CustomerSample, City FROM Customers WITH PERCENTRANK(number)
sqlite> SELECT CustomerSample, City FROM Customers WITH POISSON(number)
sqlite> SELECT CustomerSample, City FROM Customers WITH QUARTILE(number)
sqlite> SELECT CustomerSample, City FROM Customers WITH RANK(number)
sqlite> SELECT CustomerSample, City FROM Customers WITH STDEV(number)
sqlite> SELECT CustomerSample, City FROM Customers WITH STDEVP(number)
sqlite> SELECT CustomerSample, City FROM Customers WITH TDIST(number)
sqlite> SELECT CustomerSample, City FROM Customers WITH TINV(number)
sqlite> SELECT CustomerSample, City FROM Customers WITH VAR(number)
sqlite> SELECT CustomerSample, City FROM Customers WITH VARP(number)
sqlite> SELECT CustomerSample, City FROM Customers WITH FINV(number)
sqlite> SELECT CustomerSample, City FROM Customers WITH FORECAST(number)
sqlite> SELECT CustomerSample, City FROM Customers WITH BETADIST(number)
sqlite> SELECT CustomerSample, City FROM Customers WITH BETAINV(number)
sqlite> SELECT CustomerSample, City FROM Customers WITH COVAR(number)
sqlite> SELECT CustomerSample, City FROM Customers WITH CRITBINOM(number)
sqlite> SELECT CustomerSample, City FROM Customers WITH EXPONDIST(number)
sqlite> SELECT CustomerSample, City FROM Customers WITH POISSON(number)
sqlite> SELECT CustomerSample, City FROM Customers WITH FDIST(number)
sqlite> SELECT CustomerSample, City FROM Customers WITH GAMMADIST(number)
sqlite> SELECT CustomerSample, City FROM Customers WITH GAMMAINV(number)
sqlite> SELECT CustomerSample, City FROM Customers WITH HYPGEOMDIST(number)
sqlite> SELECT CustomerSample, City FROM Customers WITH NEGBINOMDIST(number)
sqlite> SELECT CustomerSample, City FROM Customers WITH TTEST(number)
sqlite> SELECT CustomerSample, City FROM Customers WITH WEIBULL(number)
sqlite> SELECT CustomerSample, City FROM Customers WITH ZTEST(number)

cases 2 allowed synxtax

sqlite> B1:B7 WITH =CONCATENATE(number)
sqlite> B1:B7 WITH =FLOOR(number)
sqlite> B1:B7 WITH =BINOMDIST(number)
sqlite> B1:B7 WITH =CHIDIST(number)
sqlite> B1:B7 WITH =CHIINV(number)
sqlite> B1:B7 WITH =CHITEST(number)
sqlite> B1:B7 WITH =CONFIDENCE(number)
sqlite> B1:B7 WITH =FTEST(array1,array2)
sqlite> B1:B7 WITH =LOGINV(probability,mean,standard_dev)
sqlite> B1:B7 WITH =LOGNORMDIST(x,mean,standard_dev)
sqlite> B1:B7 WITH =NORMDIST(x,mean,standard_dev,cumulative)
sqlite> B1:B7 WITH =NORMINV(probability,mean,standard_dev)
sqlite> B1:B7 WITH =NORMSDIST(z)
sqlite> B1:B7 WITH =NORMSINV(probability)
sqlite> B1:B7 WITH =PERCENTILE(array,k)
sqlite> B1:B7 WITH =PERCENTRANK(array,x,significance)
sqlite> B1:B7 WITH =POISSON(x,mean,cumulative)
sqlite> B1:B7 WITH =QUARTILE(array,quart)
sqlite> B1:B7 WITH =RANK(number,ref,order)
sqlite> B1:B7 WITH =STDEV(number1,number2,…)
sqlite> B1:B7 WITH =STDEVP(number1,number2,…)
sqlite> B1:B7 WITH =TDIST(x,deg_freedom,tails)
sqlite> B1:B7 WITH =TINV(probability,deg_freedom)
sqlite> B1:B7 WITH =VAR(number1,number2,…)
sqlite> B1:B7 WITH =VARP(number1,number2,…)
sqlite> B1:B7 WITH =FINV(probability,deg_freedom1,deg_freedom2)
sqlite> B1:B7 WITH =FORECAST(x,known_y’s,known_x’s)
sqlite> B1:B7 WITH =BETADIST(x,alpha,beta,A,B)
sqlite> B1:B7 WITH =BETAINV(probability,alpha,beta,A,B)
sqlite> B1:B7 WITH =COVAR(array1,array2)
sqlite> B1:B7 WITH =CRITBINOM(trials,probability_s,alpha)
sqlite> B1:B7 WITH =EXPONDIST(x,lambda,cumulative)
sqlite> B1:B7 WITH =POISSON(x,mean,cumulative)
sqlite> B1:B7 WITH =FDIST(x,deg_freedom1,deg_freedom2)
sqlite> B1:B7 WITH =GAMMADIST(x,alpha,beta,cumulative)
sqlite> B1:B7 WITH =GAMMAINV(probability,alpha,beta)
sqlite> B1:B7 WITH =HYPGEOMDIST(sample_s,number_sample,population_s,number_pop)
sqlite> B1:B7 WITH =NEGBINOMDIST(number_f,number_s,probability_s)
sqlite> B1:B7 WITH =TTEST(array1,array2,tails,type)
sqlite> B1:B7 WITH =WEIBULL(x,alpha,beta,cumulative)
sqlite> B1:B7 WITH =ZTEST(array,x,sigma)

cases 3 allowed synxtax

sqlite> B1:B7,=CONCATENATE(number)
sqlite> B1:B7,=FLOOR(number)
sqlite> B1:B7,=BINOMDIST(number)
sqlite> B1:B7,=CHIDIST(number)
sqlite> B1:B7,=CHIINV(number)
sqlite> B1:B7,=CHITEST(number)
sqlite> B1:B7,=CONFIDENCE(number)
sqlite> B1:B7,=FTEST()
sqlite> B1:B7,=LOGINV(probability,mean,standard_dev)
sqlite> B1:B7,=LOGNORMDIST(x,mean,standard_dev)
sqlite> B1:B7,=NORMDIST(x,mean,standard_dev,cumulative)
sqlite> B1:B7,=NORMINV(probability,mean,standard_dev)
sqlite> B1:B7,=NORMSDIST(z)
sqlite> B1:B7,=NORMSINV(probability)
sqlite> B1:B7,=PERCENTILE(array,k)
sqlite> B1:B7,=PERCENTRANK(array,x,significance)
sqlite> B1:B7,=POISSON(x,mean,cumulative)
sqlite> B1:B7,=QUARTILE(array,quart)
sqlite> B1:B7,=RANK(number,ref,order)
sqlite> B1:B7,=STDEV(number1,number2,…)
sqlite> B1:B7,=STDEVP(number1,number2,…)
sqlite> B1:B7,=TDIST(x,deg_freedom,tails)
sqlite> B1:B7,=TINV(probability,deg_freedom)
sqlite> B1:B7,=VAR(number1,number2,…)
sqlite> B1:B7,=VARP(number1,number2,…)
sqlite> B1:B7,=FINV(probability,deg_freedom1,deg_freedom2)
sqlite> B1:B7,=FORECAST(x,known_y’s,known_x’s)
sqlite> B1:B7,=BETADIST(x,alpha,beta,A,B)
sqlite> B1:B7,=BETAINV(probability,alpha,beta,A,B)
sqlite> B1:B7,=COVAR(array1,array2)
sqlite> B1:B7,=CRITBINOM(trials,probability_s,alpha)
sqlite> B1:B7,=EXPONDIST(x,lambda,cumulative)
sqlite> B1:B7,=POISSON(x,mean,cumulative)
sqlite> B1:B7,=FDIST(x,deg_freedom1,deg_freedom2)
sqlite> B1:B7,=GAMMADIST(x,alpha,beta,cumulative)
sqlite> B1:B7,=GAMMAINV(probability,alpha,beta)
sqlite> B1:B7,=HYPGEOMDIST(sample_s,number_sample,population_s,number_pop)
sqlite> B1:B7,=NEGBINOMDIST(number_f,number_s,probability_s)
sqlite> B1:B7,=TTEST(array1,array2,tails,type)
sqlite> B1:B7,=WEIBULL(x,alpha,beta,cumulative)
sqlite> B1:B7,=ZTEST(array,x,sigma)

cases 4 allowed synxtax

sqlite> =CONCATENATE(B1:B7,B1:B7,2)
sqlite> =FLOOR(B1:B7,B1:B7,2)
sqlite> =BINOMDIST(B1:B7,B1:B7,2)
sqlite> =CHIDIST(B1:B7,B1:B7,2)
sqlite> =CHIINV(B1:B7,B1:B7,2)
sqlite> =CHITEST(B1:B7,B1:B7,2)
sqlite> =CONFIDENCE(B1:B7,B1:B7,2)
sqlite> =FTEST(B1:B7,B1:B7,2)
sqlite> =LOGINV(B1:B7,B1:B7,2)
sqlite> =LOGNORMDIST(B1:B7,B1:B7,2)
sqlite> =NORMDIST(B1:B7,B1:B7,2)
sqlite> =NORMINV(B1:B7,B1:B7,2)
sqlite> =NORMSDIST(B1:B7,B1:B7,2)
sqlite> =NORMSINV(B1:B7,B1:B7,2)
sqlite> =PERCENTILE(B1:B7,B1:B7,2)
sqlite> =PERCENTRANK(B1:B7,B1:B7,2)
sqlite> =POISSON(B1:B7,B1:B7,2)
sqlite> =QUARTILE(B1:B7,B1:B7,2)
sqlite> =RANK(B1:B7,B1:B7,2)
sqlite> =STDEV(B1:B7,B1:B7,2)
sqlite> =STDEVP(B1:B7,B1:B7,2)
sqlite> =TDIST(B1:B7,B1:B7,2)
sqlite> =TINV(B1:B7,B1:B7,2)
sqlite> =VAR(B1:B7,B1:B7,2)
sqlite> =VARP(B1:B7,B1:B7,2)
sqlite> =FINV(B1:B7,B1:B7,2)
sqlite> =FORECAST(B1:B7,B1:B7,2)
sqlite> =BETADIST(B1:B7,B1:B7,2)
sqlite> =BETAINV(B1:B7,B1:B7,2)
sqlite> =COVAR(B1:B7,B1:B7,2)
sqlite> =CRITBINOM(B1:B7,B1:B7,2)
sqlite> =EXPONDIST(B1:B7,B1:B7,2)
sqlite> =POISSON(B1:B7,B1:B7,2)
sqlite> =FDIST(B1:B7,B1:B7,2)
sqlite> =GAMMADIST(B1:B7,B1:B7,2)
sqlite> =GAMMAINV(B1:B7,B1:B7,2)
sqlite> =HYPGEOMDIST(B1:B7,B1:B7,2)
sqlite> =NEGBINOMDIST(B1:B7,B1:B7,2)
sqlite> =TTEST(B1:B7,B1:B7,2)
sqlite> =WEIBULL(B1:B7,B1:B7,2)
sqlite> =ZTEST(B1:B7,B1:B7,2)

references

https://yodalearning.com/tutorials/excel-formulas-pdf/

(2) By anonymous on 2022-03-23 16:27:05 in reply to 1 [link] [source]

Notes

  1. Other reasons to implement this in Sqlite: it will allow people without a lot of technical knowledge to like open source tools, that is, what I'm asking can help these people and can really I think make SQLITE even more recognized.
  2. There are no database tools that have a built-in excel formulas implementation. If SQLITE implements this, it would be an innovative and pretty awesome feature. I would be very happy
  3. The cases I mentioned are querying/entering data using sql as base with excel formulas

(3) By Ryan Smith (cuz) on 2022-03-23 17:39:11 in reply to 1 [source]

That's quite a write-up.
Some thoughts on this:

  1. SQLite isn't a spreadsheet program, it's functionality hardly overlaps with SS programs at all - but it can very well be the engine for one.

  2. Most of the SS functions already exist in SQLite, with a bunch that are not in there by default but that are easy to add (such as Time-Value of Money), and then a specific section of functions/functionalities that are not in there and hard to add (like Graphing, Pivot Tables, etc.)

  3. The focus of a SS program is very much user-interface-oriented, while the focus of SQLite is more Engine-oriented, and more in line with other Relational DB engines.

  4. The code for Excel/Open Office/Libre Office Calc/etc. all dwarf that of SQLite by several magnitudes for good reason - they absolutely need all that GUI fat, which is Ok but also mean they can never match SQLite's testing regime. (In fact while they are all quite buggy, it's a miracle they are as relatively bug-free as they are, it's not a fault of their creators so much as a fault of their nature).

It's easy and alluring to imagine spread-sheety things that can be done in SQLite, or vice-versa. A simple analogy would be to imagine it'd be great if your car could simply be made stronger so it could carry way heavier loads. In fact, it'd be cool if we can make it do all the things a truck does, right? Sure, but it's easy to see there are a wealth of reasons why cars will remain car-like and trucks will remain truck-like and mixing the two, while not impossible, is never great. Put another way, the best trucks are very un-car-like, and the best cars are very un-truck-like. They're different things cause we need different things from them, not because some engineer simply haven't had the combinatory "vision" yet.

All that said, I'm all for making libraries that add more spreadsheety functions to SQLite, but I think they would probably come with much fat and would have to always remain extensions (rather than incorporate in the core) - but please do make them!

(4) By anonymous on 2022-03-23 18:57:11 in reply to 3 [link] [source]

  1. You're absolutely right!
  2. Thanks so much for the reply, just a note I wish I had added this earlier. are my new ideas

Notes

1. SQLite is not a spreadsheet program, R- Yes I know. That's not my goal.

2. "new ideas"

I'm thinking of calling this extension as ExDMLS (Spreadsheets Extended Data Manipulation Language):

  1. SELECT ColumnName, ColumnName2 ... FROM Customers WITH COMMAND
  2. COLUMN_AND_LINE:COLUMN_AND_LINE WITH =COMMAND(Value)
  3. COLUMN_AND_LINE:COLUMN_AND_LINE WITH =COMMAND(Value)
  4. COLUMN_AND_LINE:COLUMN_AND_LINE, ... =COMMAND(value)
  5. =COMMAND(COLUMN_AND_LINE:COLUMN_AND_LINE,COLUMN_AND_LINE:COLUMN_AND_LINE,value)
  6. SELECT * FROM ColumnName WHERE ID IN (id1, id2, ..., idn) with COMMAND

2.1 "my ideas" for graphics support - SEDDL (Spreadsheets Extended Data Definition Language):

  1. About this idea of having excel functions in sqlite, I don't like the graphical interface, I like everything from the command line. I think it's huge overkill to have a GUI if I can do all that in a database as good as sqlite.
  2. My suggestion I can make would be to add a graphics support in sqlite https://weidagang.github.io/text-diagram/ - this would be possible if I have an implementation like this:

input

object April Todd Monad
April->April: Prepare food for lunch.
note left of April: Lunch is ready.
space 2
April->Todd: Todd,\nwhat are you doing?
note right of Todd: Programming @_@
Todd->April: Well, I'm programming.
April->Monad: How about you?
Monad->April: I'm reading book.
April->Monad: Good boy!
note right of Monad: I'm so happy. \n ^_^
example real with SEDDL
  • https://i.ibb.co/6m99pdF/img1.png
#### output

      +-------+                     +-------+               +-------+
                | April |                     | Todd  |               | Monad |
                +-------+                     +-------+               +-------+
                    |                             |                       |
                    | Prepare food for lunch.     |                       |
                    |------------------------     |                       |
                    |                       |     |                       |
                    |<-----------------------     |                       |
------------------\ |                             |                       |
| Lunch is ready. |-|                             |                       |
|-----------------| |                             |                       |
                    |                             |                       |
                    |                             |                       |
                    |                             |                       |
                    | Todd,                       |                       |
                    | what are you doing?         |                       |
                    |---------------------------->|                       |
                    |                             | ------------------\   |
                    |                             |-| Programming @_@ |   |
                    |                             | |-----------------|   |
                    |                             |                       |
                    |      Well, I'm programming. |                       |
                    |<----------------------------|                       |
                    |                             |                       |
                    | How about you?              |                       |
                    |---------------------------------------------------->|
                    |                             |                       |
                    |                             |     I'm reading book. |
                    |<----------------------------------------------------|
                    |                             |                       |
                    | Good boy!                   |                       |
                    |---------------------------------------------------->|
                    |                             |                       | ----------------\
                    |                             |                       |-| I'm so happy. |
                    |                             |                       | | ^_^           |
                    |                             |                       | |---------------|
                    |                             |                       |

Scheme Description: This code above is a plain text that generates an image as png, jpeg, jpg, svg or it can generate a file as html as well.

others references, bibliographic references

  • https://xosh.org/text-to-diagram/
  • https://plantuml.com/class-diagram
  • https://tomassetti.me/domain-specific-languages/
  • https://medium.grid.is/excel-a-domain-specific-language-for-finance-e9ff6e6e3972
  • https://arxiv.org/ftp/arxiv/papers/0801/0801.4274.pdf
  • https://www.researchgate.net/publication/301452878_Towards_tool_support_for_spreadsheet-based_domain-specific_languages
  • https://tomassetti.me/domain-specific-languages/
  • https://en.wikipedia.org/wiki/QUEL_query_languages
  • https://stackoverflow.com/questions/9084818/bnf-notation-of-t-sql
  • https://stackoverflow.com/questions/13788074/excel-formula-grammar-in-bnf-or-any-other-notations
  • https://stackoverflow.com/questions/5803472/sql-where-id-in-id1-id2-idn

(5) By anonymous on 2022-03-24 13:59:12 in reply to 4 [link] [source]

As you don’t like GUI, probably not a option for you:

Actually, I was looking for a simple/generic GUI to use a database instead of a spreadsheet depending on the use case. The usual suspects would be solutions such as Baserow, LocoKit, nocoDB or Seatable, similar to Airtable.

Recently, I came across Grist. According to their intro-video it is based on SQLite and supports pivot, formulas, charts and also Python code. I haven’t tested it yet, though.

(8) By anonymous on 2022-03-25 20:05:38 in reply to 5 [link] [source] 

Grist is actually a very nice and promising system.

On initial spreadsheet upload (XLS, ODS, etc), grist converts the spreadsheet to SQLite database, trying hard to infer the structure of the table column names and types, closely to what a human will do.

A Grist document (workbook) consist of these pure SQLite tables filled with the sheets data + additional metadata tables about the schema, widgets to be used on individual fields, pages structure and layout, etc.

Grist solves some of the fundamental bugs of the today spreadsheets (in the form which we know of what MS was popularized). For example, a grist page can contain several sheets with different structure, placed in different panes of the page (e.g. header, footer) - exactly what the Excel users (painfully) trying to do in a single sheet, constantly. Also the editing history is preserved, and many others.

https://templates.getgrist.com/

Grist supports the "traditional" Excel/Libre office style formulas, but additionally SQlite tables are wrapped in Python classes, making writing more complex "formulas" in the form of Python methods easy. (sandboxed python engine is used only for spreadsheet computations, the rest of the system is in JS - both the server and client).

The hosted SaaS product have free and paid plans, but the team is FOSS oriented, so everyone can build and customize own Grist setup (Apache license) or directly execute the prebuilt container as-is (which support collaborative editing out of the box, as in SaaS offering).

https://github.com/gristlabs/grist-core

The only missing significant feature so far, is the option for translating user formulas in a sheet to an equivalent SQLite view (including WASM/Python UDFs is needed) and support of sheet over pre-existing SQLite views as prerequisite:

https://github.com/gristlabs/grist-core/issues/45

This feature is probably the first thing, which the devs on that list would implement (going to spreadsheets), but we refuse to understand that for the average user (and even for the majority of the programmers), the database is just a stupid bag for data, not the place where we should do computations - that is, load, compute, store, and go on :-). Perhaps the first Grist users have been just the average users (and IT consultants), hence Python, instead of SQL (or to-sql translator).

The github-issue is started by Paul Fitzpatrick itself, lead developer at Grist Labs and author of many OSS console data utilities in the past.

Kind regards,
decalek (Sorry, I lost my password)

P.S. BTW, Speaking of to-sql translators and Python, in case someone is interested to tackle the issue, ibis [1] is in very good shape these days and even started an alternative backend leveraging/targeting substrait IR (the emerging standard for execution plans exchange and optimization)

https://ibis-project.org/ibis-for-sql-programmers/

(9) By anonymous on 2022-03-27 08:21:14 in reply to 8 [link] [source]

Poster of post (5) here.

I hope I am not too off topic: Did I understand you correctly, that all the formulas and Python codes are also accessible and readable in the same SQLite file?

A little off topic: The mentioned flexibility regarding the panes is also available in Apple Numbers which makes it very easy and fast to layout and arrange different tables etc. I am using it on iOS.

Unfortunately, afaik, not possible to access SQLite files directly with Apple Numbers (neither via Apple Shortcuts)on iOS; no any kind of connector that I am aware of …

(10) By anonymous on 2022-03-28 22:50:32 in reply to 9 [link] [source] 

> I hope I am not too off topic: Did I understand you correctly, that
> all the formulas and Python codes are also accessible and readable in
> the same SQLite file?

Yes. The grist document is a self-contained SQLite DB (you can download a
document from one Grist instance and the upload it to another). The
formula's python code is stored mostly in `_grist_Tables_column.formula`
column.

You can dive into the mechanics yourself, without local setup or online
account - grab some generated python code from the templates e.g. from
Digital-Sales-CRM sample [1], then download and browse with a SQLite
tool the underlying DB [2].

IMHO, The well awareness of great developments like Datasette, Grist,
Litestream, etc. - projects which build on, and demonstrate the great
potential of SQLite, are considered on-topic here.

> A little off topic: The mentioned flexibility regarding the panes is
> also available in Apple Numbers which makes it very easy and fast to
> layout and arrange different tables etc. I am using it on iOS.
> Unfortunately, afaik, not possible to access SQLite files directly
> with Apple Numbers (neither via Apple Shortcuts)on iOS; no any kind of
> connector that I am aware of …

I don't really know, but most probably, Apple Numbers is propriety,
non-portable product, locked into the world of Apple devices, as the
rest of their stuff? When you know about this Numbers feature, the only
thing you may do with that, is to buy an Apple device and use what the
The Big Smart Company had determined for you, as-is. (Or more precisely
for its average user level). You can not add the rest of needed features
yourself (or hire someone to do so) ... IMHO - irrelevant information.

Kind regards,
Alek Paunov (decalek)

[1] https://templates.getgrist.com/eVgQezBkmQVc/Digital-Sales-CRM/p/code
[2] https://api.getgrist.com/o/templates/api/docs/eVgQezBkmQVccB9qV6ParT/download?template=0

(11) By anonymous on 2022-03-29 00:52:55 in reply to 10 [link] [source]

Thank you for your follow up and the further explanations!

Not here to discuss about Apple’s closed environment. Recently, I had a simple use case, and with Apple Numbers in iOS it was quite easy and fast for me to create and share “App-like” sheets (using the panes mentioned). Just thinking it would be great, if Numbers supported SQLite files as well.

For many of my use cases combining Excel and SQLite works out well (similarly also MS Access as a GUI for the data stored in SQLite/MySQL); basically as no-code/low-code solutions in a given (limited) environment.

At the same time, I can follow the intention of the OP to move away from Excel by adding more functions to the “sql layer”. Actually, it kind of reminds me of mindsDB for ML.

(7) By anonymous on 2022-03-25 00:02:49 in reply to 4 [link] [source]

Just saw the news about the new release of Nushell:

While it is not the extension of SQL syntax as you are looking for but a shell, it can handle SQLite databases and support dataframes to process and manipulate tables (stored in columnar format).

It can also run Python code and has even some chart function, etc.

Edit: obviously, I was not the first one with this idea, as I just find out that it is used as a CLI for Couchbase already

(6) By Víctor (wzrlpy) on 2022-03-24 18:44:41 in reply to 1 [link] [source]

Your selection of Excel functions suggest you need quite specific statistical functions. Then you may want learn R (though not your goal).

For your friend that has a store, I dare say SQLite current math extensions cover well his/her needs (google for extensions in this forum).

For any needs not covered, instead of making a new language, with a new syntax, I would suggest instead that you follow regular SQL and you create extensions as functions or virtual tables.

And if I understood well your graphic needs, I can recommend the .dot language