issue_comments
12 rows where issue = 1352932716
This data as json, CSV (advanced)
Suggested facets: user, author_association, reactions, created_at (date), updated_at (date)
| id ▼ | html_url | issue_url | node_id | user | created_at | updated_at | author_association | body | reactions | issue | performed_via_github_app |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1229116423 | https://github.com/simonw/sqlite-utils/issues/471#issuecomment-1229116423 | https://api.github.com/repos/simonw/sqlite-utils/issues/471 | IC_kwDOCGYnMM5JQtQH | simonw 9599 | 2022-08-27T04:00:52Z | 2022-08-27T04:00:52Z | OWNER | Alternative design would be `--function name definition` - like this: ``` sqlite-utils data.db 'update images set domain = extract_domain(url)' --function extract_domain ' from urllib.parse import urlparse return urlparse(url).netloc ' ``` I like the `--functions` design better because it leaves space for import statements at the top of the code block, and allows more than one function to be defined in a single go. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | sqlite-utils query --functions mechanism for registering extra functions 1352932716 | |
| 1229118619 | https://github.com/simonw/sqlite-utils/issues/471#issuecomment-1229118619 | https://api.github.com/repos/simonw/sqlite-utils/issues/471 | IC_kwDOCGYnMM5JQtyb | simonw 9599 | 2022-08-27T04:14:52Z | 2022-08-27T04:14:52Z | OWNER | Quick prototype: ```diff diff --git a/sqlite_utils/cli.py b/sqlite_utils/cli.py index 43e76fa..5dee4f6 100644 --- a/sqlite_utils/cli.py +++ b/sqlite_utils/cli.py @@ -1633,6 +1633,9 @@ def drop_view(path, view, ignore, load_extension): type=(str, str), help="Named :parameters for SQL query", ) +@click.option( + "--functions", help="Python code defining one or more custom SQL functions" +) @load_extension_option def query( path, @@ -1649,6 +1652,7 @@ def query( raw, param, load_extension, + functions, ): """Execute SQL query and return the results as JSON @@ -1665,6 +1669,16 @@ def query( _load_extensions(db, load_extension) db.register_fts4_bm25() + # Register any Python functions as SQL functions: + if functions: + locals = {} + globals = {} + exec(functions, globals, locals) + # Register all callables in the locals dict: + for name, value in locals.items(): + if callable(value): + db.register_function(value, name=name) + _execute_query( db, sql, param, raw, table, csv, tsv, no_headers, fmt, nl, arrays, json_cols ) ``` Demo: ```bash % sqlite-utils :memory: 'select 1 + dog()' --functions ' quote> def dog(): quote> return 2 quote> ' [{"1 + dog()": 3}] ``` | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | sqlite-utils query --functions mechanism for registering extra functions 1352932716 | |
| 1229119171 | https://github.com/simonw/sqlite-utils/issues/471#issuecomment-1229119171 | https://api.github.com/repos/simonw/sqlite-utils/issues/471 | IC_kwDOCGYnMM5JQt7D | simonw 9599 | 2022-08-27T04:18:28Z | 2022-08-27T04:18:28Z | OWNER | I tried this: ``` sqlite-utils :memory: 'select extract_domain("https://www.google.com/blah")' --functions ' from urllib.parse import urlparse def extract_domain(url): from urllib.parse import urlparse return urlparse(url).netloc ' ``` And got: ``` NameError: name 'urlparse' is not defined Error: user-defined function raised exception ``` But this worked OK: ``` % sqlite-utils :memory: 'select extract_domain("https://www.google.com/blah")' --functions ' def extract_domain(url): from urllib.parse import urlparse return urlparse(url).netloc ' [{"extract_domain(\"https://www.google.com/blah\")": "www.google.com"}] ``` | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | sqlite-utils query --functions mechanism for registering extra functions 1352932716 | |
| 1229119999 | https://github.com/simonw/sqlite-utils/issues/471#issuecomment-1229119999 | https://api.github.com/repos/simonw/sqlite-utils/issues/471 | IC_kwDOCGYnMM5JQuH_ | simonw 9599 | 2022-08-27T04:24:58Z | 2022-08-27T04:24:58Z | OWNER | I've encountered this problem before: https://sqlite-utils.datasette.io/en/stable/cli.html#cli-convert-complex > ``` > $ sqlite-utils convert content.db articles score ' > import random > random.seed(10) > > def convert(value): > global random > return random.random() > ' > ``` > Note the `global random` line here. Due to the way the tool compiles Python code, this is necessary to ensure the `random` module is available within the `convert()` function. If you were to omit this you would see a `NameError: name 'random' is not defined` error. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | sqlite-utils query --functions mechanism for registering extra functions 1352932716 | |
| 1229120104 | https://github.com/simonw/sqlite-utils/issues/471#issuecomment-1229120104 | https://api.github.com/repos/simonw/sqlite-utils/issues/471 | IC_kwDOCGYnMM5JQuJo | simonw 9599 | 2022-08-27T04:25:39Z | 2022-08-27T04:25:39Z | OWNER | This works: ``` sqlite-utils :memory: 'select extract_domain("https://www.google.com/blah")' --functions ' from urllib.parse import urlparse def extract_domain(url): global urlparse return urlparse(url).netloc ' ``` | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | sqlite-utils query --functions mechanism for registering extra functions 1352932716 | |
| 1229120653 | https://github.com/simonw/sqlite-utils/issues/471#issuecomment-1229120653 | https://api.github.com/repos/simonw/sqlite-utils/issues/471 | IC_kwDOCGYnMM5JQuSN | simonw 9599 | 2022-08-27T04:29:49Z | 2022-08-27T04:30:03Z | OWNER | Found a fix for that! I replaced this: ```python locals = {} globals = {} exec(functions, globals, locals) # Register all callables in the locals dict: for name, value in locals.items(): if callable(value): db.register_function(value, name=name) ``` With this: ```python globals = {} exec(functions, globals) # Register all callables in the globals dict: for name, value in globals.items(): if callable(value): db.register_function(value, name=name) ``` Because https://docs.python.org/3/library/functions.html#exec says: > If only *globals* is provided, it must be a dictionary (and not a subclass of dictionary), which will be used for both the global and the local variables. If *globals* and *locals* are given, they are used for the global and local variables, respectively. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | sqlite-utils query --functions mechanism for registering extra functions 1352932716 | |
| 1229120899 | https://github.com/simonw/sqlite-utils/issues/471#issuecomment-1229120899 | https://api.github.com/repos/simonw/sqlite-utils/issues/471 | IC_kwDOCGYnMM5JQuWD | simonw 9599 | 2022-08-27T04:31:35Z | 2022-08-27T04:32:38Z | OWNER | I should add this `--functions` feature to the `memory` and `bulk` commands too. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | sqlite-utils query --functions mechanism for registering extra functions 1352932716 | |
| 1229124379 | https://github.com/simonw/sqlite-utils/issues/471#issuecomment-1229124379 | https://api.github.com/repos/simonw/sqlite-utils/issues/471 | IC_kwDOCGYnMM5JQvMb | simonw 9599 | 2022-08-27T05:02:21Z | 2022-08-27T05:02:21Z | OWNER | Documentation: https://sqlite-utils.datasette.io/en/latest/cli.html#defining-custom-sql-functions | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | sqlite-utils query --functions mechanism for registering extra functions 1352932716 | |
| 1229124549 | https://github.com/simonw/sqlite-utils/issues/471#issuecomment-1229124549 | https://api.github.com/repos/simonw/sqlite-utils/issues/471 | IC_kwDOCGYnMM5JQvPF | simonw 9599 | 2022-08-27T05:03:39Z | 2022-08-27T05:03:39Z | OWNER | I don't think I need separate documentation for `sqlite-utils memory` and `sqlite-tils bulk` since they work the same, and the `--help` text provides the necessary hints. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | sqlite-utils query --functions mechanism for registering extra functions 1352932716 | |
| 1229125114 | https://github.com/simonw/sqlite-utils/issues/471#issuecomment-1229125114 | https://api.github.com/repos/simonw/sqlite-utils/issues/471 | IC_kwDOCGYnMM5JQvX6 | simonw 9599 | 2022-08-27T05:08:58Z | 2022-08-27T05:08:58Z | OWNER | Testing `bulk --functions`: ``` % sqlite-utils create-table chickens.db chickens id integer name text name_upper text % echo 'id,name 1,Blue 2,Snowy 3,Azi 4,Lila 5,Suna 6,Cardi' | sqlite-utils bulk chickens.db ' insert into chickens (id, name, name_upper) values (:id, :name, myupper(:name)) ' - --functions ' def myupper(s): return s.upper() ' --csv % sqlite-utils rows chickens.db chickens [{"id": 1, "name": "Blue", "name_upper": "BLUE"}, {"id": 2, "name": "Snowy", "name_upper": "SNOWY"}, {"id": 3, "name": "Azi", "name_upper": "AZI"}, {"id": 4, "name": "Lila", "name_upper": "LILA"}, {"id": 5, "name": "Suna", "name_upper": "SUNA"}, {"id": 6, "name": "Cardi", "name_upper": "CARDI"}] ``` | {"total_count": 2, "+1": 1, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 1, "rocket": 0, "eyes": 0} | sqlite-utils query --functions mechanism for registering extra functions 1352932716 | |
| 1229430228 | https://github.com/simonw/sqlite-utils/issues/471#issuecomment-1229430228 | https://api.github.com/repos/simonw/sqlite-utils/issues/471 | IC_kwDOCGYnMM5JR53U | stonebig 4312421 | 2022-08-28T10:43:35Z | 2022-08-28T10:43:35Z | NONE | Is it still nfortunately slow and tricky when playing with floats ? | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | sqlite-utils query --functions mechanism for registering extra functions 1352932716 | |
| 1238873948 | https://github.com/simonw/sqlite-utils/issues/471#issuecomment-1238873948 | https://api.github.com/repos/simonw/sqlite-utils/issues/471 | IC_kwDOCGYnMM5J17dc | simonw 9599 | 2022-09-07T03:46:26Z | 2022-09-07T03:46:26Z | OWNER | > Is it still nfortunately slow and tricky when playing with floats ? Not sure what you mean here? | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | sqlite-utils query --functions mechanism for registering extra functions 1352932716 |
Advanced export
JSON shape: default, array, newline-delimited, object
CREATE TABLE [issue_comments] (
[html_url] TEXT,
[issue_url] TEXT,
[id] INTEGER PRIMARY KEY,
[node_id] TEXT,
[user] INTEGER REFERENCES [users]([id]),
[created_at] TEXT,
[updated_at] TEXT,
[author_association] TEXT,
[body] TEXT,
[reactions] TEXT,
[issue] INTEGER REFERENCES [issues]([id])
, [performed_via_github_app] TEXT);
CREATE INDEX [idx_issue_comments_issue]
ON [issue_comments] ([issue]);
CREATE INDEX [idx_issue_comments_user]
ON [issue_comments] ([user]);