8000 experimenting with sqlite json · Issue #850 · winpython/winpython · GitHub
[go: up one dir, main page]
Skip to content

experimenting with sqlite json #850

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom
Closed
stonebig opened this issue May 2, 2020 · 12 comments
Closed

experimenting with sqlite json #850

stonebig opened this issue May 2, 2020 · 12 comments
Labels
Experiment Experiment sql
Milestone
2020-03 Jupyterla...

Comments

@stonebig
Copy link
Contributor
stonebig commented May 2, 2020 •
edited
Loading

This is to get handy in json in sql

here, tr

DROP TABLE IF EXISTS packages;
CREATE TABLE packages(datas JSON);
pydef py_pydeptree(s):
    "get a json experience from pip"
    import pipdeptree as pdt
    pkgs = pdt.get_installed_distributions()
    dist_index = pdt.build_dist_index(pkgs)
    tree = pdt.construct_tree(dist_index)
    result=pdt.render_json_tree(tree, indent=4).replace('\n', '\n')  # no need to remove newline ?
    return ("%s" %  result);

INSERT INTO packages select py_pydeptree('hi');


-- select  datas  from packages;
--select json_type(datas) from packages;
--select atom, fullkey, value from packages, json_tree(packages.datas) where json_tree.type NOT IN ('object','array');

with x as (select json_extract(Value,'$.key') package
                , json_extract(Value,'$.package_name') name
                , json_extract(Value,"$.installed_version")version
                ,  json_extract(Value,'$.dependencies') Dependancies
           from packages , json_each(packages.datas))
select * from x

```
@stonebig
Copy link
Contributor Author
stonebig commented May 2, 2020 •
edited
Loading 
select  datas  from packages;

gives

datas
"[
    {
        ""key"": ""pipdeptree"",
        ""package_name"": ""pipdeptree"",
        ""installed_version"": ""0.13.2"",
        ""required_version"": ""0.13.2"",
        ""dependencies"": [
            {
                ""key"": ""pip"",
                ""package_name"": ""pip"",
                ""installed_version"": ""20.1"",
                ""required_version"": "">=6.0.0"",
                ""dependencies"": []
            }
        ]
    },
    {
        ""key"": ""setuptools"",
        ""package_name"": ""setuptools"",
        ""installed_version"": ""46.1.3"",
        ""required_version"": ""46.1.3"",
        ""dependencies"": []
    },
    {
        ""key"": ""sqlite-bro"",
        ""package_name"": ""sqlite-bro"",
        ""installed_version"": ""0.9.1"",
        ""required_version"": ""0.9.1"",
        ""dependencies"": []
    },
    {
        ""key"": ""wheel"",
        ""package_name"": ""wheel"",
        ""installed_version"": ""0.34.2"",
        ""required_version"": ""0.34.2"",
        ""dependencies"": []
    },
    {
        ""key"": ""winpython"",
        ""package_name"": ""winpython"",
        ""installed_version"": ""2.4.20200425"",
        ""required_version"": ""2.4.20200425"",
        ""dependencies"": []
    }
]"

@stonebig
Copy link
Contributor Author
stonebig commented May 2, 2020 
select json_type(datas) as type_is from packages;

gives

type_is
array

@stonebig
Copy link
Contributor Author
stonebig commented May 2, 2020 
select atom, fullkey, value from packages, json_tree(packages.datas) where json_tree.type NOT IN ('object','array');

gives
image

("atom","fullkey","value")
("pipdeptree","$[0].key","pipdeptree")
("pipdeptree","$[0].package_name","pipdeptree")
("0.13.2","$[0].installed_version","0.13.2")
("0.13.2","$[0].required_version","0.13.2")
("pip","$[0].dependencies[0].key","pip")
("pip","$[0].dependencies[0].package_name","pip")
("20.1","$[0].dependencies[0].installed_version","20.1")
(">=6.0.0","$[0].dependencies[0].required_version",">=6.0.0")
("setuptools","$[1].key","setuptools")
("setuptools","$[1].package_name","setuptools")
("46.1.3","$[1].installed_version","46.1.3")
("46.1.3","$[1].required_version","46.1.3")
("sqlite-bro","$[2].key","sqlite-bro")
("sqlite-bro","$[2].package_name","sqlite-bro")
("0.9.1","$[2].installed_version","0.9.1")
("0.9.1","$[2].required_version","0.9.1")
("wheel","$[3].key","wheel")
("wheel","$[3].package_name","wheel")
("0.34.2","$[3].installed_version","0.34.2")
("0.34.2","$[3].required_version","0.34.2")
("winpython","$[4].key","winpython")
("winpython","$[4].package_name","winpython")
("2.4.20200425","$[4].installed_version","2.4.20200425")
("2.4.20200425","$[4].required_version","2.4.20200425")

@stonebig
Copy link
Contributor Author
stonebig commented May 2, 2020 •
edited
Loading 
select atom, fullkey, value from packages, json_tree(packages.datas) where json_tree.type NOT IN

gives:
image

with x as (select json_extract(Value,'$.key') package
                , json_extract(Value,'$.package_name') name
                , json_extract(Value,"$.installed_version")version
                ,  json_extract(Value,'$.dependencies') Dependancies
           from packages , json_each(packages.datas))
select * from x
("package","name","version","Dependancies")
("pipdeptree","pipdeptree","0.13.2","[{""key"":""pip"",""package_name"":""pip"",""installed_version"":""20.1"",""required_version"":"">=6.0.0"",""dependencies"":[]}]")
("setuptools","setuptools","46.1.3","[]")
("sqlite-bro","sqlite-bro","0.9.1","[]")
("wheel","wheel","0.34.2","[]")
("winpython","winpython","2.4.20200425","[]")

@stonebig
Copy link
Contributor Author
stonebig commented May 2, 2020 
with x(j) as (select '{"i":"fg1","c":"irl","yp":202001,"qty":[1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18]}')
select json_extract(j, '$.qty') from  x

gives

[1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18]

@stonebig
Copy link
Contributor Author
stonebig commented May 2, 2020 
with x(j) as (select '{"i":"fg1","c":"irl","yp":202001,"qties":[1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18]}')
, y as (select json_extract(j, '$.i') i , json_extract(j, '$.c') c 
, json_extract(j, '$.yp') yp ,json_extract(j, '$.qties') qties ,json_array_length(json_extract(j, '$.qties')) length from  x, json_each(x.j) where Key='qties')
select * ,  json_array_length(y.qties) nb_qties from y , json_each(y.qties)

image

@stonebig
Copy link
Contributor Author
stonebig commented May 3, 2020

missing in sqlite.org the range in an array:

json_extract(j, '$.qties[3:99]')
or
json_extract(j, '$.qties[3 to last]')

@stonebig
Copy link
Contributor Author
stonebig commented May 3, 2020 •
edited
Loading 
with x(datas) as (select '[
{"i":"fg1","c":"irl","yp":202001,"qties":[1,22,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18]}
,{"i":"fg2","c":"irl","yp":202003,"qties":[1,2,33,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19]}
,{"i":"fg3","c":"irl","yp":202008,"qties":[1,2,3,44,5,6,7,8,9,10,11,12,13,14,15,16,17]}
]')
, y as (select json_extract(Value, '$.i') item, json_extract(Value, '$.c') Client
        , round (json_extract(Value, '$.yp')/100 , 0) yearprimo, json_extract(Value, '$.yp') % 100 monthprimo, json_array_length(json_extract(Value, '$.qties')) nbmonths , json_extract(Value, '$.qties')  qties from  x, json_each(x.datas) )
, z as ( select Item, Client, round((yearprimo*12 + monthprimo  - nbmonths + Key)/12 - 0.5 , 0)  as NewYear,
    (yearprimo*12 + monthprimo  - nbmonths + Key)%12 +1 as NewMonth, Value as Qty       from y , json_each(y.qties)  )
select Item, Client, NewYear*100 + NewMonth as YearMonth, Qty from z

@stonebig
Copy link
Contributor Author
stonebig commented May 3, 2020 
with x(datas) as (select '[
{"i":"fg1","c":"irl","yp":202001,"qties":[1,22,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18]}
,{"i":"fg2","c":"bra","yp":202003,"qties":[1,2,33,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19]}
,{"i":"fg3","c":"usa","yp":202008,"qties":[1,2,3,44,5,6,7,8,9,10,11,12,13,14,15,16,17]}
]')
, y as (select json_extract(Value, '$.i') item, json_extract(Value, '$.c') Client
        , round (json_extract(Value, '$.yp')/100 , 0) yearprimo, json_extract(Value, '$.yp') % 100 monthprimo, json_array_length(json_extract(Value, '$.qties')) nbmonths , json_extract(Value, '$.qties')  qties from  x, json_each(x.datas) )
, z as ( select Item, Client, round((yearprimo*12 + monthprimo  - nbmonths + Key)/12 - 0.5 , 0)  as NewYear,
    (yearprimo*12 + monthprimo  - nbmonths + Key)%12 +1 as NewMonth, Value as Qty       from y , json_each(y.qties)  )
select   Client, NewYear, sum(Qty) qt from z group by NewYear , Client

@stonebig
Copy link
Contributor Author
stonebig commented May 9, 2020 •
edited
Loading

alos, still dreaming of the Math extension "extension-functions" to come in SQLite for python:

@stonebig
Copy link
Contributor Author
stonebig commented May 9, 2020 •
edited
Loading

comment added here to remember how to activate an extension, as soon as it is in SQLite amalgation:


compiling sqlite is easy: 
.download+unzip sqlite-amalgamation
.via menu, choose vstudio x64 native tools cmd line,
.cd unzip-ed directory, 
.cl sqlite3.c -DSQLITE_ENABLE_JSON1 -DSQLITE_ENABLE_FTS4 -DSQLITE_ENABLE_FTS5 -DSQLITE_API=__declspec(dllexport) -link -dll -out:sqlite3.dll

@stonebig
Copy link
Contributor Author
stonebig commented Jan 1, 2021

compiling sqlite is easy ... from amalgation only:
.download+unzip sqlite-amalgamation
.via menu, choose vstudio x64 native tools cmd line,
.cd unzip-dir,
.cl sqlite3.c -DSQLITE_ENABLE_JSON1 -DSQLITE_ENABLE_FTS4 -DSQLITE_ENABLE_FTS5 -DSQLITE_API=__declspec(dllexport) -link -dll -out:sqlite3.dll

@stonebig stonebig mentioned this issue Aug 8, 2022
Closed
@stonebig stonebig added sql Experiment Experiment labels May 4, 2025
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
Experiment Experiment sql
Projects
None yet
Milestone
2020-03 Jupyterlab-2.2.2 / Qt-5.15 / ...
Development

No branches or pull requests
1 participant
@stonebig
0