github
html_url | issue_url | id | node_id | user | created_at | updated_at | author_association | body | reactions | issue | performed_via_github_app |
---|---|---|---|---|---|---|---|---|---|---|---|
https://github.com/simonw/sqlite-utils/issues/476#issuecomment-1229320114 | https://api.github.com/repos/simonw/sqlite-utils/issues/476 | 1229320114 | IC_kwDOCGYnMM5JRe-y | 9599 | 2022-08-27T23:26:48Z | 2022-08-27T23:26:48Z | OWNER | - #458 - the `register_function(name=...)` argument - New tutorial: [Cleaning data with sqlite-utils and Datasette](https://datasette.io/tutorials/clean-data) provides a tutorial introduction (and accompanying ten minute video) about using this tool. - New Discord community, https://discord.gg/Ass7bCAMDw - #469 `sqlite-utils rows --order` option - #471 `sqlite-utils query --functions` option - #472 Improved code compilation pattern - #473 Support entrypoints for `--load-extension` - #455 - #475 `table.default_values` property - #467 | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 1353196970 | |
https://github.com/simonw/sqlite-utils/issues/467#issuecomment-1229311612 | https://api.github.com/repos/simonw/sqlite-utils/issues/467 | 1229311612 | IC_kwDOCGYnMM5JRc58 | 9599 | 2022-08-27T23:19:41Z | 2022-08-27T23:19:41Z | OWNER | Documentation: - https://sqlite-utils.datasette.io/en/latest/python-api.html#explicitly-creating-a-table - https://sqlite-utils.datasette.io/en/latest/cli.html#creating-tables | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 1348169997 | |
https://github.com/simonw/sqlite-utils/pull/468#issuecomment-1229303691 | https://api.github.com/repos/simonw/sqlite-utils/issues/468 | 1229303691 | IC_kwDOCGYnMM5JRa-L | 9599 | 2022-08-27T23:13:06Z | 2022-08-27T23:13:06Z | OWNER | Documentation: - https://sqlite-utils--468.org.readthedocs.build/en/468/python-api.html#explicitly-creating-a-table - https://sqlite-utils--468.org.readthedocs.build/en/468/cli.html#creating-tables | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 1348294436 | |
https://github.com/simonw/sqlite-utils/pull/468#issuecomment-1229284539 | https://api.github.com/repos/simonw/sqlite-utils/issues/468 | 1229284539 | IC_kwDOCGYnMM5JRWS7 | 9599 | 2022-08-27T22:54:07Z | 2022-08-27T22:55:01Z | OWNER | Just needs documentation now, which can go here: https://sqlite-utils--468.org.readthedocs.build/en/468/python-api.html#explicitly-creating-a-table | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 1348294436 | |
https://github.com/simonw/sqlite-utils/issues/475#issuecomment-1229283063 | https://api.github.com/repos/simonw/sqlite-utils/issues/475 | 1229283063 | IC_kwDOCGYnMM5JRV73 | 9599 | 2022-08-27T22:44:45Z | 2022-08-27T22:44:45Z | OWNER | Documentation: - https://sqlite-utils.datasette.io/en/latest/python-api.html#default-values - https://sqlite-utils.datasette.io/en/latest/reference.html#sqlite_utils.db.Table.default_values | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 1353189941 | |
https://github.com/simonw/sqlite-utils/pull/468#issuecomment-1229279539 | https://api.github.com/repos/simonw/sqlite-utils/issues/468 | 1229279539 | IC_kwDOCGYnMM5JRVEz | 9599 | 2022-08-27T22:24:33Z | 2022-08-27T22:24:33Z | OWNER | Interesting challenge with `default_value`: I need to be able to tell if the default values passed to `.create()` differ from those in the database already. Introspecting that is a bit tricky: ```pycon >>> import sqlite_utils >>> db = sqlite_utils.Database(memory=True) >>> db["blah"].create({"id": int, "name": str}, not_null=("name",), defaults={"name": "bob"}) <Table blah (id, name)> >>> db["blah"].columns [Column(cid=0, name='id', type='INTEGER', notnull=0, default_value=None, is_pk=0), Column(cid=1, name='name', type='TEXT', notnull=1, default_value="'bob'", is_pk=0)] ``` Note how a default value of the Python string `bob` is represented in the results of `PRAGMA table_info()` as `default_value="'bob'"` - it's got single quotes added to it! So comparing default values from introspecting the database needs me to first parse that syntax. This may require a new table introspection method. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 1348294436 | |
https://github.com/simonw/sqlite-utils/pull/468#issuecomment-1229276554 | https://api.github.com/repos/simonw/sqlite-utils/issues/468 | 1229276554 | IC_kwDOCGYnMM5JRUWK | 9599 | 2022-08-27T22:05:40Z | 2022-08-27T22:05:51Z | OWNER | For the moment I'm not going to pay attention to `foreign_keys` changes - I will note that these are not modified in the documentation. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 1348294436 | |
https://github.com/simonw/sqlite-utils/issues/474#issuecomment-1229265285 | https://api.github.com/repos/simonw/sqlite-utils/issues/474 | 1229265285 | IC_kwDOCGYnMM5JRRmF | 9599 | 2022-08-27T20:52:53Z | 2022-08-27T20:52:53Z | OWNER | A couple of tricks I use here. Firstly, I often create the table before the import using the `sqlite-utils create-table` command: https://sqlite-utils.datasette.io/en/stable/cli.html#creating-tables The other current option is to use the `bulk` command, which lets you construct a custom SQL query to execute against every row from a CSV file: https://sqlite-utils.datasette.io/en/stable/cli.html#executing-sql-in-bulk Do either of those options work here or is there a useful new feature that would work better? | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 1353074021 | |
https://github.com/simonw/sqlite-utils/pull/468#issuecomment-1229207725 | https://api.github.com/repos/simonw/sqlite-utils/issues/468 | 1229207725 | IC_kwDOCGYnMM5JRDit | 9599 | 2022-08-27T14:55:32Z | 2022-08-27T22:03:50Z | OWNER | The main challenge here is coming up with comprehensive tests. The cases I need to cover are from this block of code: https://github.com/simonw/sqlite-utils/blob/c5f8a2eb1a81a18b52825cc649112f71fe419b12/sqlite_utils/db.py#L1468-L1480 | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 1348294436 | |
https://github.com/simonw/sqlite-utils/issues/467#issuecomment-1229206306 | https://api.github.com/repos/simonw/sqlite-utils/issues/467 | 1229206306 | IC_kwDOCGYnMM5JRDMi | 9599 | 2022-08-27T14:47:04Z | 2022-08-27T14:47:04Z | OWNER | I could add a `--transform` option to `sqlite-utils create-table` too. | {"total_count": 1, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 1} | 1348169997 | |
https://github.com/simonw/sqlite-utils/pull/455#issuecomment-1229205990 | https://api.github.com/repos/simonw/sqlite-utils/issues/455 | 1229205990 | IC_kwDOCGYnMM5JRDHm | 9599 | 2022-08-27T14:44:45Z | 2022-08-27T14:44:45Z | OWNER | I did not know `is` worked against strings and integers and booleans! https://latest.datasette.io/_memory?sql=select+1+is+1%2C+2.3+is+2.3%2C+true+is+true%2C+true+is+false%2C+%27dog%27+is+%27dog%27%2C+%27dog%27+is+%27cat%27%2C+null+is+null%2C+null+is+not+null ```sql select 1 is 1, 2.3 is 2.3, true is true, true is false, 'dog' is 'dog', 'dog' is 'cat', null is null, null is not null ``` | 1 is 1 | 2.3 is 2.3 | true is true | true is false | 'dog' is 'dog' | 'dog' is 'cat' | null is null | null is not null | |----------|--------------|----------------|-----------------|------------------|------------------|----------------|--------------------| | 1 | 1 | 1 | 0 | 1 | 0 | 1 | 0 | | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 1309542173 | |
https://github.com/simonw/sqlite-utils/pull/473#issuecomment-1229130453 | https://api.github.com/repos/simonw/sqlite-utils/issues/473 | 1229130453 | IC_kwDOCGYnMM5JQwrV | 22429695 | 2022-08-27T05:55:12Z | 2022-08-27T05:55:18Z | NONE | # [Codecov](https://codecov.io/gh/simonw/sqlite-utils/pull/473?src=pr&el=h1&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=Simon+Willison) Report > Merging [#473](https://codecov.io/gh/simonw/sqlite-utils/pull/473?src=pr&el=desc&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=Simon+Willison) (72db599) into [main](https://codecov.io/gh/simonw/sqlite-utils/commit/a46a5e3a9e03dcdd8c84a92e4a5dbfa02ba461fa?el=desc&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=Simon+Willison) (a46a5e3) will **decrease** coverage by `0.07%`. > The diff coverage is `50.00%`. ```diff @@ Coverage Diff @@ ## main #473 +/- ## ========================================== - Coverage 96.67% 96.60% -0.08% ========================================== Files 6 6 Lines 2587 2590 +3 ========================================== + Hits 2501 2502 +1 - Misses 86 88 +2 ``` | [Impacted Files](https://codecov.io/gh/simonw/sqlite-utils/pull/473?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=Simon+Willison) | Coverage Δ | | |---|---|---| | [sqlite\_utils/cli.py](https://codecov.io/gh/simonw/sqlite-utils/pull/473/diff?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=Simon+Willison#diff-c3FsaXRlX3V0aWxzL2NsaS5weQ==) | `95.94% <50.00%> (-0.18%)` | :arrow_down: | Help us with your feedback. Take ten seconds to tell us [how you rate us](https://about.codecov.io/nps?utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=Simon+Willison). Have a feature suggestion? [Share it here.](https://app.codecov.io/gh/feedback/?utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=Simon+Willison) | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 1352953535 | |
https://github.com/simonw/sqlite-utils/issues/470#issuecomment-1229130422 | https://api.github.com/repos/simonw/sqlite-utils/issues/470 | 1229130422 | IC_kwDOCGYnMM5JQwq2 | 9599 | 2022-08-27T05:55:01Z | 2022-08-27T05:55:01Z | OWNER | cc @asg017 since this feature was inspired by his work on Datasette. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 1352932038 | |
https://github.com/simonw/sqlite-utils/issues/470#issuecomment-1229130242 | https://api.github.com/repos/simonw/sqlite-utils/issues/470 | 1229130242 | IC_kwDOCGYnMM5JQwoC | 9599 | 2022-08-27T05:53:31Z | 2022-08-27T05:53:31Z | OWNER | Tests passed in CI against Ubuntu: ``` tests/test_cli.py::test_load_extension[None-should_pass0-should_fail0] PASSED [ 19%] tests/test_cli.py::test_load_extension[sqlite3_ext_b_init-b-should_fail1] PASSED [ 19%] tests/test_cli.py::test_load_extension[sqlite3_ext_c_init-c-should_fail2] PASSED [ 19%] ``` | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 1352932038 | |
https://github.com/simonw/sqlite-utils/issues/470#issuecomment-1229130158 | https://api.github.com/repos/simonw/sqlite-utils/issues/470 | 1229130158 | IC_kwDOCGYnMM5JQwmu | 9599 | 2022-08-27T05:52:46Z | 2022-08-27T05:52:46Z | OWNER | To run the tests locally on my Mac I needed to run Ubuntu in Docker (for the compilation step). This worked: docker run -it -v "$(pwd):/app" ubuntu:latest /bin/bash Then: ``` apt-get update && apt-get install python3-pip gcc libsqlite3-dev -y cd /app/tests gcc ext.c -fPIC -shared -o ext.so cd /app pip3 install -e '.[test]' pytest -k test_load_extension ``` | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 1352932038 | |
https://github.com/simonw/sqlite-utils/issues/470#issuecomment-1229128950 | https://api.github.com/repos/simonw/sqlite-utils/issues/470 | 1229128950 | IC_kwDOCGYnMM5JQwT2 | 9599 | 2022-08-27T05:42:04Z | 2022-08-27T05:42:04Z | OWNER | Extension looks to be correctly compiled: ``` Run (cd tests && gcc ext.c -fPIC -shared -o ext.so && ls -lah) total 412K drwxr-xr-x 3 runner docker 4.0K Aug 27 05:40 . drwxr-xr-x 8 runner docker 4.0K Aug 27 05:40 .. -rw-r--r-- 1 runner docker 0 Aug 27 05:40 __init__.py -rw-r--r-- 1 runner docker 760 Aug 27 05:40 conftest.py -rw-r--r-- 1 runner docker 1.6K Aug 27 05:40 ext.c -rwxr-xr-x 1 runner docker 16K Aug 27 05:40 ext.so drwxr-xr-x 2 runner docker 4.0K Aug 27 05:40 sniff ``` So why doesn't this check pass? https://github.com/simonw/sqlite-utils/blob/3f694e51a0212a297f663e2fc7f9cdad8775a550/tests/test_cli.py#L25-L30 | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 1352932038 | |
https://github.com/simonw/sqlite-utils/issues/470#issuecomment-1229128617 | https://api.github.com/repos/simonw/sqlite-utils/issues/470 | 1229128617 | IC_kwDOCGYnMM5JQwOp | 9599 | 2022-08-27T05:39:06Z | 2022-08-27T05:39:06Z | OWNER | That didn't run the tests as expected on Ubuntu: https://github.com/simonw/sqlite-utils/runs/8048229213?check_suite_focus=true ``` tests/test_cli.py::test_load_extension[None-should_pass0-should_fail0] SKIPPED [ 19%] tests/test_cli.py::test_load_extension[sqlite3_ext_b_init-b-should_fail1] SKIPPED [ 19%] tests/test_cli.py::test_load_extension[sqlite3_ext_c_init-c-should_fail2] SKIPPED [ 19%] ``` That should have worked because of this: https://github.com/simonw/sqlite-utils/blob/3f694e51a0212a297f663e2fc7f9cdad8775a550/.github/workflows/test.yml#L38-L41 | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 1352932038 | |
https://github.com/simonw/sqlite-utils/issues/472#issuecomment-1229125890 | https://api.github.com/repos/simonw/sqlite-utils/issues/472 | 1229125890 | IC_kwDOCGYnMM5JQvkC | 9599 | 2022-08-27T05:15:26Z | 2022-08-27T05:15:26Z | OWNER | Here's the docs I get to update to remove the documented `global` hack: https://sqlite-utils.datasette.io/en/3.28/cli.html#cli-convert-complex | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 1352946135 | |
https://github.com/simonw/sqlite-utils/issues/472#issuecomment-1229125614 | https://api.github.com/repos/simonw/sqlite-utils/issues/472 | 1229125614 | IC_kwDOCGYnMM5JQvfu | 9599 | 2022-08-27T05:12:59Z | 2022-08-27T05:12:59Z | OWNER | Annoyingly this means the `--import` option is no longer required, but removing it would be a backwards incompatible breakage so I need to leave it in. I can minimize it in the documentation though. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 1352946135 | |
https://github.com/simonw/sqlite-utils/issues/471#issuecomment-1229125114 | https://api.github.com/repos/simonw/sqlite-utils/issues/471 | 1229125114 | IC_kwDOCGYnMM5JQvX6 | 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} | 1352932716 | |
https://github.com/simonw/sqlite-utils/issues/471#issuecomment-1229124549 | https://api.github.com/repos/simonw/sqlite-utils/issues/471 | 1229124549 | IC_kwDOCGYnMM5JQvPF | 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} | 1352932716 | |
https://github.com/simonw/sqlite-utils/issues/471#issuecomment-1229124379 | https://api.github.com/repos/simonw/sqlite-utils/issues/471 | 1229124379 | IC_kwDOCGYnMM5JQvMb | 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} | 1352932716 | |
https://github.com/simonw/sqlite-utils/issues/471#issuecomment-1229120899 | https://api.github.com/repos/simonw/sqlite-utils/issues/471 | 1229120899 | IC_kwDOCGYnMM5JQuWD | 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} | 1352932716 | |
https://github.com/simonw/sqlite-utils/issues/469#issuecomment-1229120779 | https://api.github.com/repos/simonw/sqlite-utils/issues/469 | 1229120779 | IC_kwDOCGYnMM5JQuUL | 9599 | 2022-08-27T04:30:48Z | 2022-08-27T04:30:48Z | OWNER | Documentation: https://sqlite-utils.datasette.io/en/latest/cli.html#returning-all-rows-in-a-table | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 1352931464 | |
https://github.com/simonw/sqlite-utils/issues/471#issuecomment-1229120653 | https://api.github.com/repos/simonw/sqlite-utils/issues/471 | 1229120653 | IC_kwDOCGYnMM5JQuSN | 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} | 1352932716 | |
https://github.com/simonw/sqlite-utils/issues/471#issuecomment-1229120104 | https://api.github.com/repos/simonw/sqlite-utils/issues/471 | 1229120104 | IC_kwDOCGYnMM5JQuJo | 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} | 1352932716 | |
https://github.com/simonw/sqlite-utils/issues/471#issuecomment-1229119999 | https://api.github.com/repos/simonw/sqlite-utils/issues/471 | 1229119999 | IC_kwDOCGYnMM5JQuH_ | 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} | 1352932716 | |
https://github.com/simonw/sqlite-utils/issues/471#issuecomment-1229119171 | https://api.github.com/repos/simonw/sqlite-utils/issues/471 | 1229119171 | IC_kwDOCGYnMM5JQt7D | 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} | 1352932716 | |
https://github.com/simonw/sqlite-utils/issues/471#issuecomment-1229118619 | https://api.github.com/repos/simonw/sqlite-utils/issues/471 | 1229118619 | IC_kwDOCGYnMM5JQtyb | 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} | 1352932716 | |
https://github.com/simonw/sqlite-utils/issues/471#issuecomment-1229116423 | https://api.github.com/repos/simonw/sqlite-utils/issues/471 | 1229116423 | IC_kwDOCGYnMM5JQtQH | 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} | 1352932716 | |
https://github.com/simonw/sqlite-utils/issues/470#issuecomment-1229115501 | https://api.github.com/repos/simonw/sqlite-utils/issues/470 | 1229115501 | IC_kwDOCGYnMM5JQtBt | 9599 | 2022-08-27T03:54:26Z | 2022-08-27T03:54:26Z | OWNER | I can borrow Alex's example extension for the tests too: https://github.com/simonw/datasette/blob/ba35105eee2d3ba620e4f230028a02b2e2571df2/tests/ext.c | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 1352932038 |