issue_comments
7,931 rows where author_association = "OWNER" sorted by issue_url descending
This data as json, CSV (advanced)
id | html_url | issue_url ▲ | node_id | user | created_at | updated_at | author_association | body | reactions | issue | performed_via_github_app |
---|---|---|---|---|---|---|---|---|---|---|---|
1539101853 | https://github.com/simonw/sqlite-utils/issues/525#issuecomment-1539101853 | https://api.github.com/repos/simonw/sqlite-utils/issues/525 | IC_kwDOCGYnMM5bvNSd | simonw 9599 | 2023-05-08T21:52:44Z | 2023-05-08T21:52:44Z | OWNER | I like the `lambda-{uuid}` idea. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Repeated calls to `Table.convert()` fail 1575131737 | |
1539108140 | https://github.com/simonw/sqlite-utils/issues/525#issuecomment-1539108140 | https://api.github.com/repos/simonw/sqlite-utils/issues/525 | IC_kwDOCGYnMM5bvO0s | simonw 9599 | 2023-05-08T21:59:41Z | 2023-05-08T21:59:41Z | OWNER | That original example passes against `main` now. | {"total_count": 1, "+1": 1, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Repeated calls to `Table.convert()` fail 1575131737 | |
1369333759 | https://github.com/simonw/sqlite-utils/issues/520#issuecomment-1369333759 | https://api.github.com/repos/simonw/sqlite-utils/issues/520 | IC_kwDOCGYnMM5Rnl__ | simonw 9599 | 2023-01-03T02:23:43Z | 2023-01-03T02:23:43Z | OWNER | The documentation here does at least say the following: https://sqlite-utils.datasette.io/en/3.30/python-api.html#reading-rows-from-a-file > - **fp** (*BinaryIO*) -- a file-like object containing binary data | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | rows_from_file() raises confusing error if file-like object is not in binary mode 1516644980 | |
1539109587 | https://github.com/simonw/sqlite-utils/issues/520#issuecomment-1539109587 | https://api.github.com/repos/simonw/sqlite-utils/issues/520 | IC_kwDOCGYnMM5bvPLT | simonw 9599 | 2023-05-08T22:00:46Z | 2023-05-08T22:00:46Z | OWNER | > Hey, isn't this essentially the same issue as #448 ? Yes it is, good catch! | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | rows_from_file() raises confusing error if file-like object is not in binary mode 1516644980 | |
550694197 | https://github.com/simonw/sqlite-utils/issues/52#issuecomment-550694197 | https://api.github.com/repos/simonw/sqlite-utils/issues/52 | MDEyOklzc3VlQ29tbWVudDU1MDY5NDE5Nw== | simonw 9599 | 2019-11-07T04:11:05Z | 2019-11-07T04:11:05Z | OWNER | I just hit this bug while running `healthkit-to-sqlite` | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Throws error if .insert_all() / .upsert_all() called with empty list 476413293 | |
1539058795 | https://github.com/simonw/sqlite-utils/pull/519#issuecomment-1539058795 | https://api.github.com/repos/simonw/sqlite-utils/issues/519 | IC_kwDOCGYnMM5bvCxr | simonw 9599 | 2023-05-08T21:12:52Z | 2023-05-08T21:12:52Z | OWNER | This is a really neat fix, thank you. | {"total_count": 1, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 1, "rocket": 0, "eyes": 0} | Fixes breaking DEFAULT values 1505568103 | |
1344965367 | https://github.com/simonw/sqlite-utils/issues/517#issuecomment-1344965367 | https://api.github.com/repos/simonw/sqlite-utils/issues/517 | IC_kwDOCGYnMM5QKor3 | simonw 9599 | 2022-12-10T01:26:31Z | 2022-12-10T01:26:31Z | OWNER | At some point I should drop it from all of these other projects too: https://cs.github.com/?scopeName=All+repos&scope=&q=user%3Asimonw+%223.6%22+path%3A.github%2Fworkflows%2F* | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Drop support for Python 3.6 1487757143 | |
1339834918 | https://github.com/simonw/sqlite-utils/issues/516#issuecomment-1339834918 | https://api.github.com/repos/simonw/sqlite-utils/issues/516 | IC_kwDOCGYnMM5P3EIm | simonw 9599 | 2022-12-06T19:00:18Z | 2022-12-06T19:00:35Z | OWNER | Right now the command produces no output at all. Maybe a `--verbose` mode that writes these numbers to standard error (or even standard output since it's an option)? Is there a better name than `--verbose` for this? `--summary` perhaps? | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Feature request: output number of ignored/replaced rows for insert command 1479914599 | |
1539077777 | https://github.com/simonw/sqlite-utils/pull/515#issuecomment-1539077777 | https://api.github.com/repos/simonw/sqlite-utils/issues/515 | IC_kwDOCGYnMM5bvHaR | simonw 9599 | 2023-05-08T21:27:10Z | 2023-05-08T21:27:10Z | OWNER | I should have spotted this PR before I shipped my own fix! https://github.com/simonw/sqlite-utils/commit/2376c452a56b0c3e75e7ca698273434e32945304 | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | upsert new rows with constraints, fixes #514 1465194930 | |
1539078429 | https://github.com/simonw/sqlite-utils/issues/514#issuecomment-1539078429 | https://api.github.com/repos/simonw/sqlite-utils/issues/514 | IC_kwDOCGYnMM5bvHkd | simonw 9599 | 2023-05-08T21:27:40Z | 2023-05-08T21:27:40Z | OWNER | Dupe of: - #538 | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | upsert of new row with check constraints fails 1465194249 | |
1539079507 | https://github.com/simonw/sqlite-utils/issues/514#issuecomment-1539079507 | https://api.github.com/repos/simonw/sqlite-utils/issues/514 | IC_kwDOCGYnMM5bvH1T | simonw 9599 | 2023-05-08T21:28:37Z | 2023-05-08T21:28:37Z | OWNER | > This means that a table with NON NULL (or other constraint) columns that aren't part of the pkey can't have new rows upserted. Huh... on that basis, it's possible my fix in https://github.com/simonw/sqlite-utils/commit/2376c452a56b0c3e75e7ca698273434e32945304 is incomplete. I only covered the 'not null' case. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | upsert of new row with check constraints fails 1465194249 | |
1539094287 | https://github.com/simonw/sqlite-utils/issues/514#issuecomment-1539094287 | https://api.github.com/repos/simonw/sqlite-utils/issues/514 | IC_kwDOCGYnMM5bvLcP | simonw 9599 | 2023-05-08T21:44:11Z | 2023-05-08T21:44:11Z | OWNER | OK, this fails silently: ```python import sqlite_utils db = sqlite_utils.Database(memory=True) db.execute('''CREATE TABLE employees ( id INTEGER PRIMARY KEY, name TEXT, age INTEGER, salary REAL, CHECK (salary is not null and salary > 0) );''') db["employees"].upsert({"id": 1, "name": "Bob"}, pk="id") list(db["employees"].rows) ```` It outputs: ```python [] ``` | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | upsert of new row with check constraints fails 1465194249 | |
1539099703 | https://github.com/simonw/sqlite-utils/issues/514#issuecomment-1539099703 | https://api.github.com/repos/simonw/sqlite-utils/issues/514 | IC_kwDOCGYnMM5bvMw3 | simonw 9599 | 2023-05-08T21:50:06Z | 2023-05-08T21:50:06Z | OWNER | Applying the fix from the PR here doesn't fix the above problem either: - https://github.com/simonw/sqlite-utils/pull/515 So it looks like these kinds of `check` constraints currently aren't compatible with how `upsert()` works. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | upsert of new row with check constraints fails 1465194249 | |
1539100300 | https://github.com/simonw/sqlite-utils/issues/514#issuecomment-1539100300 | https://api.github.com/repos/simonw/sqlite-utils/issues/514 | IC_kwDOCGYnMM5bvM6M | simonw 9599 | 2023-05-08T21:50:51Z | 2023-05-08T21:50:51Z | OWNER | Seeing as `sqlite-utils` doesn't currently provide mechanisms for adding `check` constraints like this I'm going to leave this - I'm happy with the fix I put in for the `not null` constraints. | {"total_count": 1, "+1": 1, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | upsert of new row with check constraints fails 1465194249 | |
1316437748 | https://github.com/simonw/sqlite-utils/issues/512#issuecomment-1316437748 | https://api.github.com/repos/simonw/sqlite-utils/issues/512 | IC_kwDOCGYnMM5Odz70 | simonw 9599 | 2022-11-16T06:24:31Z | 2022-11-16T06:24:31Z | OWNER | ``` sqlite-utils % pipx run no_implicit_optional . Calculating full-repo metadata... Executing codemod... 11.43s 98% complete, 0.24s estimated for 5 files to go... ``` Then: ``` Finished codemodding 239 files! - Transformed 239 files successfully. - Skipped 0 files. - Failed to codemod 0 files. - 0 warnings were generated. ``` Here's the diff: ```diff diff --git a/sqlite_utils/db.py b/sqlite_utils/db.py index a06f4b7..e819d17 100644 --- a/sqlite_utils/db.py +++ b/sqlite_utils/db.py @@ -297,12 +297,12 @@ class Database: def __init__( self, - filename_or_conn: Union[str, pathlib.Path, sqlite3.Connection] = None, + filename_or_conn: Optional[Union[str, pathlib.Path, sqlite3.Connection]] = None, memory: bool = False, - memory_name: str = None, + memory_name: Optional[str] = None, recreate: bool = False, recursive_triggers: bool = True, - tracer: Callable = None, + tracer: Optional[Callable] = None, use_counts_table: bool = False, ): assert (filename_or_conn is not None and (not memory and not memory_name)) or ( @@ -341,7 +341,7 @@ class Database: self.conn.close() @contextlib.contextmanager - def tracer(self, tracer: Callable = None): + def tracer(self, tracer: Optional[Callable] = None): """ Context manager to temporarily set a tracer function - all executed SQL queries will be passed to this. @@ -378,7 +378,7 @@ class Database: def register_function( self, - fn: Callable = None, + fn: Optional[Callable] = None, deterministic: bool = False, replace: bool = False, name: Optional[str] = None, @@ -879,7 +879,7 @@ class Database: pk: Optional[Any] = None, foreign_keys: Optional[ForeignKeysType] = None, column_order: Optional[List[str]] = None, - not_null: Iterable[str] = None, + not_null: Optional[Iterable[… | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | mypy failures in CI 1450952393 | |
1316447182 | https://github.com/simonw/sqlite-utils/issues/512#issuecomment-1316447182 | https://api.github.com/repos/simonw/sqlite-utils/issues/512 | IC_kwDOCGYnMM5Od2PO | simonw 9599 | 2022-11-16T06:32:31Z | 2022-11-16T06:32:31Z | OWNER | Test failed again: https://github.com/simonw/sqlite-utils/actions/runs/3476950474/jobs/5812663096 `E: Failed to fetch http://azure.archive.ubuntu.com/ubuntu/pool/universe/s/spatialite/libsqlite3-mod-spatialite_4.3.0a-6build1_amd64.deb Unable to connect to azure.archive.ubuntu.com:http:` That looks like an intermittent error. I'll try running it again in the morning. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | mypy failures in CI 1450952393 | |
1316530539 | https://github.com/simonw/sqlite-utils/issues/512#issuecomment-1316530539 | https://api.github.com/repos/simonw/sqlite-utils/issues/512 | IC_kwDOCGYnMM5OeKlr | simonw 9599 | 2022-11-16T07:49:50Z | 2022-11-16T07:49:50Z | OWNER | Tests passed. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | mypy failures in CI 1450952393 | |
515756563 | https://github.com/simonw/sqlite-utils/pull/51#issuecomment-515756563 | https://api.github.com/repos/simonw/sqlite-utils/issues/51 | MDEyOklzc3VlQ29tbWVudDUxNTc1NjU2Mw== | simonw 9599 | 2019-07-28T11:56:10Z | 2019-07-28T11:56:10Z | OWNER | Interesting. The tests failed presumably because the version of SQLite I am running on Travis CI doesn't have that same 999 limit. I'm going to enforce the 999 limit within the library itself, to discourage people from writing code which will fail if it runs on a host that DOES have that limit. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Fix for too many SQL variables, closes #50 473733752 | |
1296358636 | https://github.com/simonw/sqlite-utils/issues/506#issuecomment-1296358636 | https://api.github.com/repos/simonw/sqlite-utils/issues/506 | IC_kwDOCGYnMM5NRNzs | simonw 9599 | 2022-10-30T21:52:11Z | 2022-10-30T21:52:11Z | OWNER | This could work in a similar way to `db.insert(...).last_rowid`. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Make `cursor.rowcount` accessible (wontfix) 1429029604 | |
1298877872 | https://github.com/simonw/sqlite-utils/issues/506#issuecomment-1298877872 | https://api.github.com/repos/simonw/sqlite-utils/issues/506 | IC_kwDOCGYnMM5Na02w | simonw 9599 | 2022-11-01T17:35:30Z | 2022-11-01T17:35:30Z | OWNER | This may not make sense. First, `.last_rowid` is a property on table - but that doesn't make sense for `rowcount` since it should clearly be a property on the database itself (you can run a query directly using `db.execute()` without going through a `Table` object). So I tried this prototype: ```diff diff --git a/docs/python-api.rst b/docs/python-api.rst index 206e5e6..78d3a8d 100644 --- a/docs/python-api.rst +++ b/docs/python-api.rst @@ -186,6 +186,15 @@ The ``db.query(sql)`` function executes a SQL query and returns an iterator over # {'name': 'Cleo'} # {'name': 'Pancakes'} +After executing a query the ``db.rowcount`` property on that database instance will reflect the number of rows affected by any insert, update or delete operations performed by that query: + +.. code-block:: python + + db = Database(memory=True) + db["dogs"].insert_all([{"name": "Cleo"}, {"name": "Pancakes"}]) + print(db.rowcount) + # Outputs: 2 + .. _python_api_execute: db.execute(sql, params) diff --git a/sqlite_utils/db.py b/sqlite_utils/db.py index a06f4b7..c19c2dd 100644 --- a/sqlite_utils/db.py +++ b/sqlite_utils/db.py @@ -294,6 +294,8 @@ class Database: _counts_table_name = "_counts" use_counts_table = False + # Number of rows inserted, updated or deleted + rowcount: Optional[int] = None def __init__( self, @@ -480,9 +482,11 @@ class Database: if self._tracer: self._tracer(sql, parameters) if parameters is not None: - return self.conn.execute(sql, parameters) + cursor = self.conn.execute(sql, parameters) else: - return self.conn.execute(sql) + cursor = self.conn.execute(sql) + self.rowcount = cursor.rowcount + return cursor def executescript(self, sql: str) -> sqlite3.Cursor: """ ``` But this happens: ```pycon >>> from sqlite_utils import Database >>> db = Database(memory=True) >>> db["dogs"].insert_a… | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Make `cursor.rowcount` accessible (wontfix) 1429029604 | |
1298879701 | https://github.com/simonw/sqlite-utils/issues/506#issuecomment-1298879701 | https://api.github.com/repos/simonw/sqlite-utils/issues/506 | IC_kwDOCGYnMM5Na1TV | simonw 9599 | 2022-11-01T17:37:13Z | 2022-11-01T17:37:13Z | OWNER | The question I was originally trying to answer here was this: how many rows were actually inserted by that call to `.insert_all()`? I don't know that `.rowcount` would ever be useful here, since the "correct" answer depends on other factors - had I determined to ignore or replace records with a primary key that matches an existing record for example? So I think if people need `rowcount` they can get it by using a `cursor` directly. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Make `cursor.rowcount` accessible (wontfix) 1429029604 | |
1291203911 | https://github.com/simonw/sqlite-utils/issues/505#issuecomment-1291203911 | https://api.github.com/repos/simonw/sqlite-utils/issues/505 | IC_kwDOCGYnMM5M9jVH | simonw 9599 | 2022-10-25T22:21:02Z | 2022-10-25T22:21:02Z | OWNER | - Now tested against Python 3.11. ([#502](https://github.com/simonw/sqlite-utils/issues/502)) - New `table.search_sql(include_rank=True)` option, which adds a `rank` column to the generated SQL. Thanks, Jacob Chapman. ([#480](https://github.com/simonw/sqlite-utils/pull/480)) - Progress bars now display for newline-delimited JSON files using the `--nl` option. Thanks, Mischa Untaga. ([#485](https://github.com/simonw/sqlite-utils/issues/485)) - New `db.close()` method. ([#504](https://github.com/simonw/sqlite-utils/issues/504)) - Conversion functions passed to [table.convert(...)](https://sqlite-utils.datasette.io/en/stable/python-api.html#python-api-convert) can now return lists or dictionaries, which will be inserted into the database as JSON strings. ([#495](https://github.com/simonw/sqlite-utils/issues/495)) - `sqlite-utils install` and `sqlite-utils uninstall` commands for installing packages into the same virtual environment as `sqlite-utils`, [described here](https://sqlite-utils.datasette.io/en/stable/cli.html#cli-install). ([#483](https://github.com/simonw/sqlite-utils/issues/483)) - New [sqlite_utils.utils.flatten()](https://sqlite-utils.datasette.io/en/stable/reference.html#reference-utils-flatten) utility function. ([#500](https://github.com/simonw/sqlite-utils/issues/500)) - Documentation on [using Just](https://sqlite-utils.datasette.io/en/stable/contributing.html#contributing-just) to run tests, linters and build documentation. - Documentation now covers the [Release process](https://sqlite-utils.datasette.io/en/stable/contributing.html#release-process) for this package. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Release sqlite-utils 3.30 1423182778 | |
1291216193 | https://github.com/simonw/sqlite-utils/issues/505#issuecomment-1291216193 | https://api.github.com/repos/simonw/sqlite-utils/issues/505 | IC_kwDOCGYnMM5M9mVB | simonw 9599 | 2022-10-25T22:41:16Z | 2022-10-25T22:41:16Z | OWNER | Tweeted about it here: https://twitter.com/simonw/status/1585038766678609921 | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Release sqlite-utils 3.30 1423182778 | |
1291136971 | https://github.com/simonw/sqlite-utils/issues/504#issuecomment-1291136971 | https://api.github.com/repos/simonw/sqlite-utils/issues/504 | IC_kwDOCGYnMM5M9S_L | simonw 9599 | 2022-10-25T21:00:29Z | 2022-10-25T21:00:29Z | OWNER | Documentation: https://sqlite-utils.datasette.io/en/latest/reference.html#sqlite_utils.db.Database.close | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | db.close() method, calling db.conn.close() 1423069384 | |
1291071627 | https://github.com/simonw/sqlite-utils/issues/503#issuecomment-1291071627 | https://api.github.com/repos/simonw/sqlite-utils/issues/503 | IC_kwDOCGYnMM5M9DCL | simonw 9599 | 2022-10-25T20:02:18Z | 2022-10-25T20:02:18Z | OWNER | Passes on Windows with other Python versions for some reason. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | test_recreate failing on Windows Python 3.11 1423000702 | |
1291076031 | https://github.com/simonw/sqlite-utils/issues/503#issuecomment-1291076031 | https://api.github.com/repos/simonw/sqlite-utils/issues/503 | IC_kwDOCGYnMM5M9EG_ | simonw 9599 | 2022-10-25T20:06:28Z | 2022-10-25T20:06:28Z | OWNER | This is the failing test: https://github.com/simonw/sqlite-utils/blob/7b2d1c0ffd0b874e280292b926f328a61cb31e2c/tests/test_recreate.py#L21-L32 I'm going to try a different way of creating the temporary file: https://docs.pytest.org/en/7.1.x/how-to/tmp_path.html | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | test_recreate failing on Windows Python 3.11 1423000702 | |
1291083188 | https://github.com/simonw/sqlite-utils/issues/503#issuecomment-1291083188 | https://api.github.com/repos/simonw/sqlite-utils/issues/503 | IC_kwDOCGYnMM5M9F20 | simonw 9599 | 2022-10-25T20:12:52Z | 2022-10-25T20:12:52Z | OWNER | Failed again, but just noticed this: https://github.com/simonw/sqlite-utils/actions/runs/3323932266/jobs/5494890223 ``` > Database(filepath, recreate=True)["t2"].insert({"foo": "bar"}) tests\test_recreate.py:31: _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ self = <[AttributeError("'Database' object has no attribute 'conn'") raised in repr()] Database object at 0x29fc125aa90> ``` | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | test_recreate failing on Windows Python 3.11 1423000702 | |
1291088108 | https://github.com/simonw/sqlite-utils/issues/503#issuecomment-1291088108 | https://api.github.com/repos/simonw/sqlite-utils/issues/503 | IC_kwDOCGYnMM5M9HDs | simonw 9599 | 2022-10-25T20:17:36Z | 2022-10-25T20:17:36Z | OWNER | Now `mypy` is failing: ``` sqlite_utils/db.py:474: error: Item "None" of "Optional[Any]" has no attribute "execute" sqlite_utils/db.py:476: error: Item "None" of "Optional[Any]" has no attribute "execute" sqlite_utils/db.py:486: error: Item "None" of "Optional[Any]" has no attribute "executescript" sqlite_utils/db.py:603: error: Item "None" of "Optional[Any]" has no attribute "__enter__" sqlite_utils/db.py:603: error: Item "None" of "Optional[Any]" has no attribute "__exit__" sqlite_utils/db.py:604: error: Item "None" of "Optional[Any]" has no attribute "execute" sqlite_utils/db.py:607: error: Item "None" of "Optional[Any]" has no attribute "execute" sqlite_utils/db.py:1082: error: Item "None" of "Optional[Any]" has no attribute "__enter__" sqlite_utils/db.py:1082: error: Item "None" of "Optional[Any]" has no attribute "__exit__" sqlite_utils/db.py:1083: error: Item "None" of "Optional[Any]" has no attribute "cursor" sqlite_utils/db.py:1155: error: Item "None" of "Optional[Any]" has no attribute "enable_load_extension" sqlite_utils/db.py:1156: error: Item "None" of "Optional[Any]" has no attribute "load_extension" Found 12 errors in 1 file (checked 51 source files) ``` | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | test_recreate failing on Windows Python 3.11 1423000702 | |
1291093581 | https://github.com/simonw/sqlite-utils/issues/503#issuecomment-1291093581 | https://api.github.com/repos/simonw/sqlite-utils/issues/503 | IC_kwDOCGYnMM5M9IZN | simonw 9599 | 2022-10-25T20:23:00Z | 2022-10-25T20:23:00Z | OWNER | I'm not hugely happy with my fix there: https://github.com/simonw/sqlite-utils/blob/c5d7ec1dd71fa1dce829bc8bb82b639018befd63/sqlite_utils/db.py#L321-L328 The problem here was that in the case where the `os.remove()` failed the `self.conn` property was NOT being set to a valid connection - which caused `__repr__` to fail later on. So now I catch the `os.remove()` error, set `self.conn` to a memory connection, then raise the error again. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | test_recreate failing on Windows Python 3.11 1423000702 | |
1291103021 | https://github.com/simonw/sqlite-utils/issues/503#issuecomment-1291103021 | https://api.github.com/repos/simonw/sqlite-utils/issues/503 | IC_kwDOCGYnMM5M9Kst | simonw 9599 | 2022-10-25T20:32:01Z | 2022-10-25T20:32:01Z | OWNER | This test reliably fails on Windows with Python 3.11. I'm going to skip the test for the moment to get back to green CI... but I'll leave this issue open. This is definitely concerning, I just don't have the right local environment to solve this at the moment. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | test_recreate failing on Windows Python 3.11 1423000702 | |
1291111357 | https://github.com/simonw/sqlite-utils/issues/503#issuecomment-1291111357 | https://api.github.com/repos/simonw/sqlite-utils/issues/503 | IC_kwDOCGYnMM5M9Mu9 | simonw 9599 | 2022-10-25T20:36:06Z | 2022-10-25T20:36:06Z | OWNER | ... or maybe Windows doesn't like attempts to remove a file that the process has opened? | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | test_recreate failing on Windows Python 3.11 1423000702 | |
1291115986 | https://github.com/simonw/sqlite-utils/issues/503#issuecomment-1291115986 | https://api.github.com/repos/simonw/sqlite-utils/issues/503 | IC_kwDOCGYnMM5M9N3S | simonw 9599 | 2022-10-25T20:39:24Z | 2022-10-25T20:39:24Z | OWNER | Used `psutil` to confirm that closing a SQLite connection closes the underlying file: https://til.simonwillison.net/python/too-many-open-files-psutil ```pycon >>> import psutil >>> import sqlite3 >>> for f in psutil.Process().open_files(): print(f) ... >>> sqlite3.connect("/tmp/blah.db") <sqlite3.Connection object at 0x1007264d0> >>> conn = _ >>> for f in psutil.Process().open_files(): print(f) ... popenfile(path='/private/tmp/blah.db', fd=3) >>> conn.close() >>> for f in psutil.Process().open_files(): print(f) ... >>> ``` | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | test_recreate failing on Windows Python 3.11 1423000702 | |
1291122389 | https://github.com/simonw/sqlite-utils/issues/503#issuecomment-1291122389 | https://api.github.com/repos/simonw/sqlite-utils/issues/503 | IC_kwDOCGYnMM5M9PbV | simonw 9599 | 2022-10-25T20:45:43Z | 2022-10-25T20:45:43Z | OWNER | That fixed it. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | test_recreate failing on Windows Python 3.11 1423000702 | |
1291124413 | https://github.com/simonw/sqlite-utils/issues/503#issuecomment-1291124413 | https://api.github.com/repos/simonw/sqlite-utils/issues/503 | IC_kwDOCGYnMM5M9P69 | simonw 9599 | 2022-10-25T20:47:34Z | 2022-10-25T20:47:34Z | OWNER | TIL about this: https://til.simonwillison.net/python/os-remove-windows | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | test_recreate failing on Windows Python 3.11 1423000702 | |
1291029761 | https://github.com/simonw/sqlite-utils/issues/502#issuecomment-1291029761 | https://api.github.com/repos/simonw/sqlite-utils/issues/502 | IC_kwDOCGYnMM5M840B | simonw 9599 | 2022-10-25T19:21:44Z | 2022-10-25T19:21:44Z | OWNER | Replicated locally using a fresh virtual environment with Python 3.11 and: pytest -k test_query_invalid_function | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Fix tests for Python 3.11 1422954582 | |
1282813168 | https://github.com/simonw/sqlite-utils/issues/501#issuecomment-1282813168 | https://api.github.com/repos/simonw/sqlite-utils/issues/501 | IC_kwDOCGYnMM5Mdizw | simonw 9599 | 2022-10-18T18:12:15Z | 2022-10-18T18:12:15Z | OWNER | Here's the new Tabulate release: - https://github.com/astanin/python-tabulate/releases/tag/v0.9.0 - https://github.com/astanin/python-tabulate/compare/v0.8.10...v0.9.0 | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Tests failing due to updated tabulate library 1413641049 | |
1282817901 | https://github.com/simonw/sqlite-utils/issues/501#issuecomment-1282817901 | https://api.github.com/repos/simonw/sqlite-utils/issues/501 | IC_kwDOCGYnMM5Mdj9t | simonw 9599 | 2022-10-18T18:14:35Z | 2022-10-18T18:14:35Z | OWNER | Now the 3.6 tests fail - because the new release of tabulate dropped support for that Python version (so on Python 3.6 you get an older version): https://github.com/simonw/sqlite-utils/actions/runs/3275842849/jobs/5391181675 https://github.com/astanin/python-tabulate/blame/20c6370d5da2dae89b305bfb6c7f12a0f8b7236c/pyproject.toml#L22 shows minimum is 3.7 now. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Tests failing due to updated tabulate library 1413641049 | |
1282819035 | https://github.com/simonw/sqlite-utils/issues/501#issuecomment-1282819035 | https://api.github.com/repos/simonw/sqlite-utils/issues/501 | IC_kwDOCGYnMM5MdkPb | simonw 9599 | 2022-10-18T18:15:05Z | 2022-10-18T18:15:05Z | OWNER | I'm going to skip the cog test on Python 3.6 to address this. The documentation on the website will show the available list of options for 3.7 and higher. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Tests failing due to updated tabulate library 1413641049 | |
1282830806 | https://github.com/simonw/sqlite-utils/issues/501#issuecomment-1282830806 | https://api.github.com/repos/simonw/sqlite-utils/issues/501 | IC_kwDOCGYnMM5MdnHW | simonw 9599 | 2022-10-18T18:23:36Z | 2022-10-18T18:23:36Z | OWNER | Tests pass now. Updated docs: - https://sqlite-utils.datasette.io/en/latest/cli.html#table-formatted-output - https://sqlite-utils.datasette.io/en/latest/cli-reference.html#query - and many other places on that page | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Tests failing due to updated tabulate library 1413641049 | |
1282778928 | https://github.com/simonw/sqlite-utils/issues/500#issuecomment-1282778928 | https://api.github.com/repos/simonw/sqlite-utils/issues/500 | IC_kwDOCGYnMM5Mdacw | simonw 9599 | 2022-10-18T17:44:20Z | 2022-10-18T17:44:20Z | OWNER | Here's how it works: https://github.com/simonw/sqlite-utils/blob/d792dad1cf5f16525da81b1e162fb71d469995f3/sqlite_utils/cli.py#L1847-L1848 https://github.com/simonw/sqlite-utils/blob/d792dad1cf5f16525da81b1e162fb71d469995f3/sqlite_utils/cli.py#L1082-L1088 | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Turn --flatten into a documented utility function 1413610718 | |
1282779755 | https://github.com/simonw/sqlite-utils/issues/500#issuecomment-1282779755 | https://api.github.com/repos/simonw/sqlite-utils/issues/500 | IC_kwDOCGYnMM5Mdapr | simonw 9599 | 2022-10-18T17:45:10Z | 2022-10-18T17:45:10Z | OWNER | It should go in `sqlite_utils.utils` - documented here: https://sqlite-utils.datasette.io/en/stable/reference.html#sqlite-utils-utils | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Turn --flatten into a documented utility function 1413610718 | |
1282780770 | https://github.com/simonw/sqlite-utils/issues/500#issuecomment-1282780770 | https://api.github.com/repos/simonw/sqlite-utils/issues/500 | IC_kwDOCGYnMM5Mda5i | simonw 9599 | 2022-10-18T17:45:56Z | 2022-10-18T17:46:05Z | OWNER | I think the public interface is a `flatten(row)` function that does `dict(_flatten(row))`. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Turn --flatten into a documented utility function 1413610718 | |
1282800547 | https://github.com/simonw/sqlite-utils/issues/500#issuecomment-1282800547 | https://api.github.com/repos/simonw/sqlite-utils/issues/500 | IC_kwDOCGYnMM5Mdfuj | simonw 9599 | 2022-10-18T18:02:09Z | 2022-10-18T18:02:09Z | OWNER | Documentation: https://sqlite-utils.datasette.io/en/latest/reference.html#sqlite-utils-utils-flatten | {"total_count": 1, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 1, "eyes": 0} | Turn --flatten into a documented utility function 1413610718 | |
515751719 | https://github.com/simonw/sqlite-utils/issues/50#issuecomment-515751719 | https://api.github.com/repos/simonw/sqlite-utils/issues/50 | MDEyOklzc3VlQ29tbWVudDUxNTc1MTcxOQ== | simonw 9599 | 2019-07-28T10:40:11Z | 2019-07-28T10:40:11Z | OWNER | I think the fix here is for me to switch to using `executemany()` - example from the Python docs: https://docs.python.org/3/library/sqlite3.html ```python purchases = [('2006-03-28', 'BUY', 'IBM', 1000, 45.00), ('2006-04-05', 'BUY', 'MSFT', 1000, 72.00), ('2006-04-06', 'SELL', 'IBM', 500, 53.00), ] c.executemany('INSERT INTO stocks VALUES (?,?,?,?,?)', purchases) ``` | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | "Too many SQL variables" on large inserts 473083260 | |
515752129 | https://github.com/simonw/sqlite-utils/issues/50#issuecomment-515752129 | https://api.github.com/repos/simonw/sqlite-utils/issues/50 | MDEyOklzc3VlQ29tbWVudDUxNTc1MjEyOQ== | simonw 9599 | 2019-07-28T10:46:49Z | 2019-07-28T10:46:49Z | OWNER | The problem with `.executemany()` is it breaks `lastrowid`: > This read-only attribute provides the rowid of the last modified row. It is only set if you issued an INSERT or a REPLACE statement using the execute() method. For operations other than INSERT or REPLACE or when executemany() is called, lastrowid is set to None. So I think I need to continue to use my existing way of executing bulk inserts (with a giant repeated `INSERT INTO ... VALUES` block) but ensure that I calculate the chunk size such that I don't ever try to pass more than 999 values at once. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | "Too many SQL variables" on large inserts 473083260 | |
515752204 | https://github.com/simonw/sqlite-utils/issues/50#issuecomment-515752204 | https://api.github.com/repos/simonw/sqlite-utils/issues/50 | MDEyOklzc3VlQ29tbWVudDUxNTc1MjIwNA== | simonw 9599 | 2019-07-28T10:48:14Z | 2019-07-28T10:48:14Z | OWNER | Here's the diff where I tried to use `.executemany()` and ran into the `lastrowid` problem: ```diff diff --git a/sqlite_utils/db.py b/sqlite_utils/db.py index ef55976..7f85759 100644 --- a/sqlite_utils/db.py +++ b/sqlite_utils/db.py @@ -881,13 +881,10 @@ class Table: or_what=or_what, table=self.name, columns=", ".join("[{}]".format(c) for c in all_columns), - rows=", ".join( - """ + rows=""" ({placeholders}) """.format( - placeholders=", ".join(["?"] * len(all_columns)) - ) - for record in chunk + placeholders=", ".join(["?"] * len(all_columns)) ), ) values = [] @@ -902,15 +899,15 @@ class Table: extract_table = extracts[key] value = self.db[extract_table].lookup({"value": value}) record_values.append(value) - values.extend(record_values) + values.append(record_values) with self.db.conn: try: - result = self.db.conn.execute(sql, values) + result = self.db.conn.executemany(sql, values) except sqlite3.OperationalError as e: if alter and (" has no column " in e.args[0]): # Attempt to add any missing columns, then try again self.add_missing_columns(chunk) - result = self.db.conn.execute(sql, values) + result = self.db.conn.executemany(sql, values) else: raise self.last_rowid = result.lastrowid ``` | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | "Too many SQL variables" on large inserts 473083260 | |
1291146850 | https://github.com/simonw/sqlite-utils/issues/497#issuecomment-1291146850 | https://api.github.com/repos/simonw/sqlite-utils/issues/497 | IC_kwDOCGYnMM5M9VZi | simonw 9599 | 2022-10-25T21:09:28Z | 2022-10-25T21:09:28Z | OWNER | Yeah, `table.columns` and `table.columns_dict` are meant to handle this: https://sqlite-utils.datasette.io/en/stable/python-api.html#columns | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | column_names 1393212964 | |
1291167887 | https://github.com/simonw/sqlite-utils/issues/496#issuecomment-1291167887 | https://api.github.com/repos/simonw/sqlite-utils/issues/496 | IC_kwDOCGYnMM5M9aiP | simonw 9599 | 2022-10-25T21:33:25Z | 2022-10-25T21:33:25Z | OWNER | I do care about this, but I'm not hugely experienced with types yet so I'm open to suggestions about how to do it! | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | devrel/python api: Pylance type hinting 1393202060 | |
1291170072 | https://github.com/simonw/sqlite-utils/issues/496#issuecomment-1291170072 | https://api.github.com/repos/simonw/sqlite-utils/issues/496 | IC_kwDOCGYnMM5M9bEY | simonw 9599 | 2022-10-25T21:36:12Z | 2022-10-25T21:36:12Z | OWNER | I was going to suggest using `db.table(name)` instead of `db[name]` - but it looks like that method will have the same problem: https://github.com/simonw/sqlite-utils/blob/defa2974c6d3abc19be28d6b319649b8028dc966/sqlite_utils/db.py#L497-L506 I could change `sqlite-utils` so `db.table(name)` always returns a table and you need to call `db.view(name)` if you want to access a view - that would require bumping to 4.0 though. I'm not convinced that's the best approach here either. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | devrel/python api: Pylance type hinting 1393202060 | |
1291149509 | https://github.com/simonw/sqlite-utils/issues/495#issuecomment-1291149509 | https://api.github.com/repos/simonw/sqlite-utils/issues/495 | IC_kwDOCGYnMM5M9WDF | simonw 9599 | 2022-10-25T21:12:11Z | 2022-10-25T21:12:11Z | OWNER | This makes sense to me. There are other places in the codebase where JSON is automatically stringified: https://github.com/simonw/sqlite-utils/blob/c7e4308e6f49d929704163531632e558f9646e4a/sqlite_utils/db.py#L2759-L2766 I don't see why the return value from a convert function shouldn't do the same thing. Since this will result in previous errors working, I don't think it warrants a major version bump either. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Support JSON values returned from .convert() functions 1392690202 | |
1291152433 | https://github.com/simonw/sqlite-utils/issues/495#issuecomment-1291152433 | https://api.github.com/repos/simonw/sqlite-utils/issues/495 | IC_kwDOCGYnMM5M9Wwx | simonw 9599 | 2022-10-25T21:14:54Z | 2022-10-25T21:14:54Z | OWNER | There is a case where the function can return a dictionary at the moment: `multi=True` ```python table.convert( "title", lambda v: {"upper": v.upper(), "lower": v.lower()}, multi=True ) ``` But I think this change is still compatible with that. if you don't use `multi=True` then the return value will be stringified. If you DO use `multi=True` then something like this could work: ```python table.convert( "title", lambda v: {"upper": {"str": v.upper()}, "lower": {"str": v.lower()}}, multi=True ) ``` This would result in a `upper` and `lower` column, each containing the JSON string `{"str": "UPPERCASE"}`. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Support JSON values returned from .convert() functions 1392690202 | |
1291159549 | https://github.com/simonw/sqlite-utils/issues/495#issuecomment-1291159549 | https://api.github.com/repos/simonw/sqlite-utils/issues/495 | IC_kwDOCGYnMM5M9Yf9 | simonw 9599 | 2022-10-25T21:23:01Z | 2022-10-25T21:23:01Z | OWNER | I've decided not to explicitly document this, since it's consistent with how other parts of the library work already. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Support JSON values returned from .convert() functions 1392690202 | |
1258516872 | https://github.com/simonw/sqlite-utils/issues/494#issuecomment-1258516872 | https://api.github.com/repos/simonw/sqlite-utils/issues/494 | IC_kwDOCGYnMM5LA3GI | simonw 9599 | 2022-09-26T19:28:36Z | 2022-09-26T19:28:36Z | OWNER | New documentation: https://sqlite-utils.datasette.io/en/latest/contributing.html#using-just-and-pipenv | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Document how to use Just 1386593843 | |
1258521333 | https://github.com/simonw/sqlite-utils/issues/494#issuecomment-1258521333 | https://api.github.com/repos/simonw/sqlite-utils/issues/494 | IC_kwDOCGYnMM5LA4L1 | simonw 9599 | 2022-09-26T19:32:36Z | 2022-09-26T19:32:36Z | OWNER | Tweeted about it too: https://twitter.com/simonw/status/1574481628507668480 | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Document how to use Just 1386593843 | |
1258476455 | https://github.com/simonw/sqlite-utils/issues/493#issuecomment-1258476455 | https://api.github.com/repos/simonw/sqlite-utils/issues/493 | IC_kwDOCGYnMM5LAtOn | simonw 9599 | 2022-09-26T19:01:49Z | 2022-09-26T19:01:49Z | OWNER | I tried the tips in https://stackoverflow.com/questions/15258831/how-to-handle-two-dashes-in-rest (not the settings change though, because I might want smart quotes elsewhere) and they didn't work. Maybe I should disable smart quotes entirely? I feel like there should be an escaping trick that works here though. I tried `insert -\\-convert` but it didn't help. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Tiny typographical error in install/uninstall docs 1386562662 | |
1291166273 | https://github.com/simonw/sqlite-utils/issues/493#issuecomment-1291166273 | https://api.github.com/repos/simonw/sqlite-utils/issues/493 | IC_kwDOCGYnMM5M9aJB | simonw 9599 | 2022-10-25T21:31:15Z | 2022-10-25T21:31:15Z | OWNER | Based on the docs here I tried the following too: https://docutils.sourceforge.io/docs/user/smartquotes.html#description - `\--` - `\\--` - `\\-\\-` - `\-\-` But none of them had the desired effect in this particular piece of markup: the :ref:`insert \--convert <cli_insert_convert>` I think because this is text inside a `:ref:` block, not regular text. Consider the following: The \--convert and the :ref:`insert \--convert <cli_insert_convert>` and It's rendered like this: <img width="328" alt="image" src="https://user-images.githubusercontent.com/9599/197885893-b859f2bd-619b-4406-9688-3a8e592267f5.png"> | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Tiny typographical error in install/uninstall docs 1386562662 | |
1258446128 | https://github.com/simonw/sqlite-utils/issues/492#issuecomment-1258446128 | https://api.github.com/repos/simonw/sqlite-utils/issues/492 | IC_kwDOCGYnMM5LAl0w | simonw 9599 | 2022-09-26T18:32:14Z | 2022-09-26T18:33:19Z | OWNER | This idea would make more sense if there was a good mechanism to say "run the conversion script held in this file" as opposed to passing it as an option. This would also make having to remember bash escaping rules ([see tip](https://til.simonwillison.net/zsh/argument-heredoc)) much easier! `shot-scraper` has that for `--javascript`, using the `--input` option: https://shot-scraper.datasette.io/en/stable/javascript.html#shot-scraper-javascript-help Maybe `--convert-script` would work here? Or `--convert-file`? It should accept `-` for stdin too. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Idea: ability to pass extra variables to `--convert` scripts 1386530156 | |
1258449887 | https://github.com/simonw/sqlite-utils/issues/491#issuecomment-1258449887 | https://api.github.com/repos/simonw/sqlite-utils/issues/491 | IC_kwDOCGYnMM5LAmvf | simonw 9599 | 2022-09-26T18:35:50Z | 2022-09-26T18:35:50Z | OWNER | This is a really interesting idea. I'm nervous about needing to set the rules for how duplicate tables should be merged though. This feels like a complex topic - one where there isn't necessarily an obviously "correct" way of doing it, but where different problems that people are solving might need different merging approaches. Likewise, merging isn't just a database-to-database thing at that point - I could see a need for merging two tables using similar rules to those used for merging two databases. So I think I'd want to have some good concrete use-cases in mind before trying to design how something like this should work. Will leave this thread open for people to drop those in! | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Ability to merge databases and tables 1383646615 | |
1258450447 | https://github.com/simonw/sqlite-utils/issues/491#issuecomment-1258450447 | https://api.github.com/repos/simonw/sqlite-utils/issues/491 | IC_kwDOCGYnMM5LAm4P | simonw 9599 | 2022-09-26T18:36:23Z | 2022-09-26T18:36:23Z | OWNER | This is also the kind of feature that would need to express itself in both the Python library and the CLI utility. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Ability to merge databases and tables 1383646615 | |
1258697384 | https://github.com/simonw/sqlite-utils/issues/491#issuecomment-1258697384 | https://api.github.com/repos/simonw/sqlite-utils/issues/491 | IC_kwDOCGYnMM5LBjKo | simonw 9599 | 2022-09-26T22:12:45Z | 2022-09-26T22:12:45Z | OWNER | That feels like a slightly different command to me - maybe `sqlite-utils backup data.db data-backup.db`? It doesn't have any of the mechanics for merging tables together. Could be a useful feature separately though. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Ability to merge databases and tables 1383646615 | |
1256428818 | https://github.com/simonw/sqlite-utils/issues/490#issuecomment-1256428818 | https://api.github.com/repos/simonw/sqlite-utils/issues/490 | IC_kwDOCGYnMM5K45US | simonw 9599 | 2022-09-23T16:37:58Z | 2022-09-23T16:38:35Z | OWNER | It should be possible to achieve this with the `--text` option: https://sqlite-utils.datasette.io/en/stable/cli.html?highlight=text#convert-with-text Given an example like this in `multiline.log`: ``` 2022-03-01T12:04:52: Here is a log message that spans multiple lines 2022-03-01T12:04:52: This is a single line 2022-03-01T12:04:52: Here is another message that spans multiple lines ``` You should be able to run something like this: ``` sqlite-utils insert /tmp/log.db log multiline.log --text --convert " import re r = re.compile(r'^(?P<datetime>\d{4}-\d{2}-\d{2}T\d{2}:\d{2}:\d{2}):(?P<log>.*)', re.MULTILINE) def convert(text): return [m.groupdict() for m in r.finditer(text)] " ``` After running this I get: ``` sqlite-utils rows /tmp/log.db log [{"datetime": "2022-03-01T12:04:52", "log": " Here is a log message"}, {"datetime": "2022-03-01T12:04:52", "log": " This is a single line"}, {"datetime": "2022-03-01T12:04:52", "log": " Here is another message"}] ``` | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Ability to insert multi-line files 1382457780 | |
1258437060 | https://github.com/simonw/sqlite-utils/issues/490#issuecomment-1258437060 | https://api.github.com/repos/simonw/sqlite-utils/issues/490 | IC_kwDOCGYnMM5LAjnE | simonw 9599 | 2022-09-26T18:24:44Z | 2022-09-26T18:24:44Z | OWNER | Just saw your great write-up on this: https://jeqo.github.io/notes/2022-09-24-ingest-logs-sqlite/ | {"total_count": 1, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 1, "rocket": 0, "eyes": 0} | Ability to insert multi-line files 1382457780 | |
514509307 | https://github.com/simonw/sqlite-utils/issues/49#issuecomment-514509307 | https://api.github.com/repos/simonw/sqlite-utils/issues/49 | MDEyOklzc3VlQ29tbWVudDUxNDUwOTMwNw== | simonw 9599 | 2019-07-24T07:09:43Z | 2019-07-24T07:10:21Z | OWNER | This syntax should be shared with #42 as much as possible. Maybe something based on a namedtuple would work, since those are already used in the library. ```python workouts = db.table("workouts", extracts=[Extract( columns=["source", "source_version"], table="Sources" )]) ``` Since namedtuples cannot have default values this should probably be a class instead. Actually it looks like there is a trick for defaults here: https://stackoverflow.com/a/18348004 | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | extracts= should support multiple-column extracts 472115381 | |
710346830 | https://github.com/simonw/sqlite-utils/issues/49#issuecomment-710346830 | https://api.github.com/repos/simonw/sqlite-utils/issues/49 | MDEyOklzc3VlQ29tbWVudDcxMDM0NjgzMA== | simonw 9599 | 2020-10-16T18:08:52Z | 2020-10-16T18:09:21Z | OWNER | The new `.extract()` method can handle multiple columns: https://github.com/simonw/sqlite-utils/blob/2c541fac352632e23e40b0d21e3f233f7a744a57/tests/test_extract.py#L70-L87 | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | extracts= should support multiple-column extracts 472115381 | |
710359724 | https://github.com/simonw/sqlite-utils/issues/49#issuecomment-710359724 | https://api.github.com/repos/simonw/sqlite-utils/issues/49 | MDEyOklzc3VlQ29tbWVudDcxMDM1OTcyNA== | simonw 9599 | 2020-10-16T18:15:31Z | 2020-10-16T18:15:31Z | OWNER | Using a tuple would work: ```python fresh_db.table("tree", extracts=[("common_name", "latin_name")]) ``` Or to define a custom name: ```python fresh_db.table("tree", extracts={("common_name", "latin_name"): "names"}) ``` | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | extracts= should support multiple-column extracts 472115381 | |
710363789 | https://github.com/simonw/sqlite-utils/issues/49#issuecomment-710363789 | https://api.github.com/repos/simonw/sqlite-utils/issues/49 | MDEyOklzc3VlQ29tbWVudDcxMDM2Mzc4OQ== | simonw 9599 | 2020-10-16T18:18:05Z | 2020-10-16T18:18:05Z | OWNER | I wonder if there's value in extending the `extracts=` option at all given the existence of `table.extract()`. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | extracts= should support multiple-column extracts 472115381 | |
710364942 | https://github.com/simonw/sqlite-utils/issues/49#issuecomment-710364942 | https://api.github.com/repos/simonw/sqlite-utils/issues/49 | MDEyOklzc3VlQ29tbWVudDcxMDM2NDk0Mg== | simonw 9599 | 2020-10-16T18:18:48Z | 2020-10-16T18:18:48Z | OWNER | I think there is. It's a nice existing feature, and I don't think adding tuple support to it would be a huge lift. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | extracts= should support multiple-column extracts 472115381 | |
710390915 | https://github.com/simonw/sqlite-utils/issues/49#issuecomment-710390915 | https://api.github.com/repos/simonw/sqlite-utils/issues/49 | MDEyOklzc3VlQ29tbWVudDcxMDM5MDkxNQ== | simonw 9599 | 2020-10-16T18:34:26Z | 2020-10-16T18:34:50Z | OWNER | Here's the most complex example of `.extracts()`: ```python db["Trees"].extract( ["CommonName", "LatinName"], table="Species", fk_column="species_id", rename={"CommonName": "name", "LatinName": "latin"} ) ``` Resulting in: ```sql CREATE TABLE [Species] ( [id] INTEGER PRIMARY KEY, [name] TEXT, [latin] TEXT ) ``` From https://sqlite-utils.readthedocs.io/en/stable/python-api.html#extracting-columns-into-a-separate-table | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | extracts= should support multiple-column extracts 472115381 | |
710393550 | https://github.com/simonw/sqlite-utils/issues/49#issuecomment-710393550 | https://api.github.com/repos/simonw/sqlite-utils/issues/49 | MDEyOklzc3VlQ29tbWVudDcxMDM5MzU1MA== | simonw 9599 | 2020-10-16T18:35:57Z | 2020-10-16T18:36:39Z | OWNER | If I want to support that most complicated example, I think the option to pass a `Extracts()` object to `extracts=` is the best way to do it: ```python fresh_db.table("tree", extracts=[Extract( columns=("CommonName", "LatinName"), table="Species", fk_column="species_id", rename={"CommonName": "name", "LatinName": "latin"} )]) ``` | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | extracts= should support multiple-column extracts 472115381 | |
710395444 | https://github.com/simonw/sqlite-utils/issues/49#issuecomment-710395444 | https://api.github.com/repos/simonw/sqlite-utils/issues/49 | MDEyOklzc3VlQ29tbWVudDcxMDM5NTQ0NA== | simonw 9599 | 2020-10-16T18:37:10Z | 2020-10-16T18:37:10Z | OWNER | But this begins to feel too complicated, given that `table.extract()` can already be used to achieve the same thing. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | extracts= should support multiple-column extracts 472115381 | |
710397574 | https://github.com/simonw/sqlite-utils/issues/49#issuecomment-710397574 | https://api.github.com/repos/simonw/sqlite-utils/issues/49 | MDEyOklzc3VlQ29tbWVudDcxMDM5NzU3NA== | simonw 9599 | 2020-10-16T18:38:21Z | 2020-10-16T18:38:21Z | OWNER | I'm not going to implement this. I'll leave `extract=...` as it is right now, suitable for quick simple single-column operations on input, but if users want to do something more complicated involving multiple columns they should use the `table.extract()` method after the initial insert instead. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | extracts= should support multiple-column extracts 472115381 | |
710461468 | https://github.com/simonw/sqlite-utils/issues/49#issuecomment-710461468 | https://api.github.com/repos/simonw/sqlite-utils/issues/49 | MDEyOklzc3VlQ29tbWVudDcxMDQ2MTQ2OA== | simonw 9599 | 2020-10-16T19:18:19Z | 2020-10-16T19:18:19Z | OWNER | Reconsidering: #89 was a feature request that relates to this, so maybe this is worth implementing after all. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | extracts= should support multiple-column extracts 472115381 | |
1248474806 | https://github.com/simonw/sqlite-utils/issues/489#issuecomment-1248474806 | https://api.github.com/repos/simonw/sqlite-utils/issues/489 | IC_kwDOCGYnMM5Kaja2 | simonw 9599 | 2022-09-15T18:48:09Z | 2022-09-15T18:48:09Z | OWNER | Built a prototype of this that works really well: ```diff diff --git a/sqlite_utils/utils.py b/sqlite_utils/utils.py index c0b7bf1..f9a482c 100644 --- a/sqlite_utils/utils.py +++ b/sqlite_utils/utils.py @@ -272,7 +272,19 @@ def rows_from_file( if format == Format.JSON: decoded = json.load(fp) if isinstance(decoded, dict): - decoded = [decoded] + # TODO: Solve for if this isn't what people want + # Does it have just one key that is a list of dicts? + list_keys = [ + k + for k in decoded + if isinstance(decoded[k], list) + and decoded[k] + and all(isinstance(o, dict) for o in decoded[k]) + ] + if len(list_keys) == 1: + decoded = decoded[list_keys[0]] + else: + decoded = [decoded] if not isinstance(decoded, list): raise RowsFromFileBadJSON("JSON must be a list or a dictionary") return decoded, Format.JSON ``` I used that to build this: https://gist.github.com/simonw/0e6901974a14ab7d56c2746a04d72c8c One problem though: right now, if you do this `sqlite-utils` treats it as a single object and adds a `tags` column with JSON in it: ``` echo '{"title": "Hi", "tags": [{"t": "one"}]}` | sqlite-utils insert db.db t - ``` If I implement this new mechanism the above line would behave differently - which would be a backwards incompatible change. So I probably need some kind of opt-in mechanism for this. And I need a good name for it. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Ability to load JSON records held in a file with a single top level key that is a list of objects 1374939463 | |
1248475718 | https://github.com/simonw/sqlite-utils/issues/489#issuecomment-1248475718 | https://api.github.com/repos/simonw/sqlite-utils/issues/489 | IC_kwDOCGYnMM5KajpG | simonw 9599 | 2022-09-15T18:49:05Z | 2022-09-15T18:49:53Z | OWNER | Here's how I used my prototype to build [that Gist](https://gist.github.com/simonw/0e6901974a14ab7d56c2746a04d72c8c): sqlite-utils memory ~/Downloads/CVR_Export_20220908084311/*.json --schema > database.sql | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Ability to load JSON records held in a file with a single top level key that is a list of objects 1374939463 | |
1248479485 | https://github.com/simonw/sqlite-utils/issues/489#issuecomment-1248479485 | https://api.github.com/repos/simonw/sqlite-utils/issues/489 | IC_kwDOCGYnMM5Kakj9 | simonw 9599 | 2022-09-15T18:52:52Z | 2022-09-15T18:53:45Z | OWNER | The most similar option I have at the moment is probably `--flatten`. What would good names for this option be? - `--auto-list` - `--auto-key` - `--inner-key` - `--auto-json` - `--find-list` - `--find-key` Those are all bad. Another option: introduce a new explicit format for it. Right now the explicit formats you can use are: https://github.com/simonw/sqlite-utils/blob/d9b9e075f07a20f1137cd2e34ed5d3f1a3db4ad8/docs/cli-reference.rst#L153-L158 So I could add a `:autojson` format. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Ability to load JSON records held in a file with a single top level key that is a list of objects 1374939463 | |
1248481303 | https://github.com/simonw/sqlite-utils/issues/489#issuecomment-1248481303 | https://api.github.com/repos/simonw/sqlite-utils/issues/489 | IC_kwDOCGYnMM5KalAX | simonw 9599 | 2022-09-15T18:54:30Z | 2022-09-15T18:55:14Z | OWNER | Maybe this would make more sense as a mechanism where you can say "Use the data in the key called X" - but there's a special option for "figure out that key automatically". The syntax then could be: `--list-key List` Or for automatic detection: `--list-key-auto` Could also go with `--key List` and `--key-auto` - but would that be as obvious as `--list-key`? | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Ability to load JSON records held in a file with a single top level key that is a list of objects 1374939463 | |
1248484094 | https://github.com/simonw/sqlite-utils/issues/489#issuecomment-1248484094 | https://api.github.com/repos/simonw/sqlite-utils/issues/489 | IC_kwDOCGYnMM5Kalr- | simonw 9599 | 2022-09-15T18:56:31Z | 2022-09-15T18:56:31Z | OWNER | Actually I quite like `--key X` - it could work for single nested objects too. You could insert a single record like this: ```json { "record" { "id": 1 } } ``` ``` sqlite-utils insert db.db records record.json --key record ``` | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Ability to load JSON records held in a file with a single top level key that is a list of objects 1374939463 | |
1248501824 | https://github.com/simonw/sqlite-utils/issues/489#issuecomment-1248501824 | https://api.github.com/repos/simonw/sqlite-utils/issues/489 | IC_kwDOCGYnMM5KaqBA | simonw 9599 | 2022-09-15T19:10:48Z | 2022-09-15T19:10:48Z | OWNER | This feels pretty good: ``` % sqlite-utils memory ~/Downloads/CVR_Export_20220908084311/*.json --schema --auto-key CREATE TABLE [BallotTypeContestManifest] ( [BallotTypeId] INTEGER, [ContestId] INTEGER ); CREATE VIEW t1 AS select * from [BallotTypeContestManifest]; CREATE VIEW t AS select * from [BallotTypeContestManifest]; CREATE TABLE [BallotTypeManifest] ( [Description] TEXT, [Id] INTEGER, [ExternalId] TEXT ); ``` | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Ability to load JSON records held in a file with a single top level key that is a list of objects 1374939463 | |
1248512739 | https://github.com/simonw/sqlite-utils/issues/489#issuecomment-1248512739 | https://api.github.com/repos/simonw/sqlite-utils/issues/489 | IC_kwDOCGYnMM5Kasrj | simonw 9599 | 2022-09-15T19:18:24Z | 2022-09-15T19:21:01Z | OWNER | Why doesn't `sqlite-utils insert` use the `rows_from_file` function I wonder? https://github.com/simonw/sqlite-utils/issues/279#issuecomment-864207841 says: > I can refactor `sqlite-utils insert` to use this new code too. Maybe I forgot to do that? | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Ability to load JSON records held in a file with a single top level key that is a list of objects 1374939463 | |
1248522618 | https://github.com/simonw/sqlite-utils/issues/489#issuecomment-1248522618 | https://api.github.com/repos/simonw/sqlite-utils/issues/489 | IC_kwDOCGYnMM5KavF6 | simonw 9599 | 2022-09-15T19:29:20Z | 2022-09-15T19:29:20Z | OWNER | I think refactoring `sqlite-utils insert` to use `rows_from_file` needs to happen as part of this work. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Ability to load JSON records held in a file with a single top level key that is a list of objects 1374939463 | |
1248621072 | https://github.com/simonw/sqlite-utils/issues/489#issuecomment-1248621072 | https://api.github.com/repos/simonw/sqlite-utils/issues/489 | IC_kwDOCGYnMM5KbHIQ | simonw 9599 | 2022-09-15T20:56:09Z | 2022-09-15T20:56:09Z | OWNER | Prototype so far: ```diff diff --git a/sqlite_utils/cli.py b/sqlite_utils/cli.py index 767b170..d96c507 100644 --- a/sqlite_utils/cli.py +++ b/sqlite_utils/cli.py @@ -1762,6 +1762,17 @@ def query( is_flag=True, help="Analyze resulting tables and output results", ) +@click.option("--key", help="read data from this key of the root object") +@click.option( + "--auto-key", + is_flag=True, + help="Find a key in the root object that is a list of objects", +) +@click.option( + "--analyze", + is_flag=True, + help="Analyze resulting tables and output results", +) @load_extension_option def memory( paths, @@ -1784,6 +1795,8 @@ def memory( schema, dump, save, + key, + auto_key, analyze, load_extension, ): @@ -1838,7 +1851,9 @@ def memory( csv_table = stem stem_counts[stem] = stem_counts.get(stem, 1) + 1 csv_fp = csv_path.open("rb") - rows, format_used = rows_from_file(csv_fp, format=format, encoding=encoding) + rows, format_used = rows_from_file( + csv_fp, format=format, encoding=encoding, key=key, auto_key=auto_key + ) tracker = None if format_used in (Format.CSV, Format.TSV) and not no_detect_types: tracker = TypeTracker() diff --git a/sqlite_utils/utils.py b/sqlite_utils/utils.py index 8754554..2e69c26 100644 --- a/sqlite_utils/utils.py +++ b/sqlite_utils/utils.py @@ -231,6 +231,8 @@ def rows_from_file( encoding: Optional[str] = None, ignore_extras: Optional[bool] = False, extras_key: Optional[str] = None, + key: Optional[str] = None, + auto_key: Optional[bool] = False, ) -> Tuple[Iterable[dict], Format]: """ Load a sequence of dictionaries from a file-like object containing one of four different formats. @@ -271,13 +273,31 @@ def rows_from_file( :param encoding: the character encoding to use when reading CSV/TSV data :param ignore_extras: ignore any … | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Ability to load JSON records held in a file with a single top level key that is a list of objects 1374939463 | |
1246971764 | https://github.com/simonw/sqlite-utils/issues/488#issuecomment-1246971764 | https://api.github.com/repos/simonw/sqlite-utils/issues/488 | IC_kwDOCGYnMM5KU0d0 | simonw 9599 | 2022-09-14T15:52:14Z | 2022-09-14T15:52:14Z | OWNER | Frustratingly I think this counts as a backwards-incompatible change. Could have it be opt-in with a new option / method parameter, and then change it to the default if I release a `sqlite-utils 4`. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | `sqlite-utils transform` should set empty strings to null when converting text columns to integer/float 1373224657 | |
1254029808 | https://github.com/simonw/sqlite-utils/issues/488#issuecomment-1254029808 | https://api.github.com/repos/simonw/sqlite-utils/issues/488 | IC_kwDOCGYnMM5Kvvnw | simonw 9599 | 2022-09-21T17:45:20Z | 2022-09-21T17:45:41Z | OWNER | No, I'm going to say that this is a bug - it's WEIRD having a `integer` or `float` column containing an empty string. I'm OK changing that - I very much doubt anyone is relying on this functionality. So no need for a new option here - just fixing the bug is sensible. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | `sqlite-utils transform` should set empty strings to null when converting text columns to integer/float 1373224657 | |
1254032378 | https://github.com/simonw/sqlite-utils/issues/488#issuecomment-1254032378 | https://api.github.com/repos/simonw/sqlite-utils/issues/488 | IC_kwDOCGYnMM5KvwP6 | simonw 9599 | 2022-09-21T17:47:54Z | 2022-09-21T17:47:54Z | OWNER | New tests should go in: https://github.com/simonw/sqlite-utils/blob/main/tests/test_transform.py I think the implementation fix needs to go near here: https://github.com/simonw/sqlite-utils/blob/0b315d3fa83c1584eaeec32f24912898621e437a/sqlite_utils/db.py#L1770-L1775 The trick is going to be teaching that generated SQL to know which columns are `integer` or `float` and to convert `""` to `null` as part of that operation. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | `sqlite-utils transform` should set empty strings to null when converting text columns to integer/float 1373224657 | |
1254033981 | https://github.com/simonw/sqlite-utils/issues/488#issuecomment-1254033981 | https://api.github.com/repos/simonw/sqlite-utils/issues/488 | IC_kwDOCGYnMM5Kvwo9 | simonw 9599 | 2022-09-21T17:49:32Z | 2022-09-21T17:50:10Z | OWNER | It looks like SQLite has a `SELECT NULLIF(value, '')` function which returns `null` if that value is equal to `''`. We need to only apply that function to columns that we know to be of type integer or float though - text columns containing empty strings should not be rewritten to null. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | `sqlite-utils transform` should set empty strings to null when converting text columns to integer/float 1373224657 | |
1242409766 | https://github.com/simonw/sqlite-utils/issues/487#issuecomment-1242409766 | https://api.github.com/repos/simonw/sqlite-utils/issues/487 | IC_kwDOCGYnMM5KDasm | simonw 9599 | 2022-09-09T20:04:30Z | 2022-09-09T20:04:30Z | OWNER | This isn't supported yet - there's an older issue for that here: - #117 | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Specify foreign key against compound key in other table 1367835380 | |
1248565396 | https://github.com/simonw/sqlite-utils/pull/486#issuecomment-1248565396 | https://api.github.com/repos/simonw/sqlite-utils/issues/486 | IC_kwDOCGYnMM5Ka5iU | simonw 9599 | 2022-09-15T20:12:50Z | 2022-09-15T20:12:50Z | OWNER | Annoying `mypy` test failure: ``` /Users/runner/hostedtoolcache/Python/3.10.7/x64/lib/python3.10/site-packages/numpy/__init__.pyi:636: error: Positional-only parameters are only supported in Python 3.8 and greater ``` Looks like this: - https://github.com/python/mypy/issues/13627 | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | progressbar for inserts/upserts of all fileformats, closes #485 1366512990 | |
1248567323 | https://github.com/simonw/sqlite-utils/pull/486#issuecomment-1248567323 | https://api.github.com/repos/simonw/sqlite-utils/issues/486 | IC_kwDOCGYnMM5Ka6Ab | simonw 9599 | 2022-09-15T20:14:45Z | 2022-09-15T20:14:45Z | OWNER | There's a fix for `mypy` that has landed but isn't out in a release yet: - https://github.com/python/mypy/issues/13385 For the moment looks like pinning to Python 3.10.6 could help. Need to figure out how to do that in GitHub Actions though. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | progressbar for inserts/upserts of all fileformats, closes #485 1366512990 | |
1248568775 | https://github.com/simonw/sqlite-utils/pull/486#issuecomment-1248568775 | https://api.github.com/repos/simonw/sqlite-utils/issues/486 | IC_kwDOCGYnMM5Ka6XH | simonw 9599 | 2022-09-15T20:16:14Z | 2022-09-15T20:16:14Z | OWNER | https://github.com/actions/setup-python/blob/main/docs/advanced-usage.md#using-the-python-version-input says can set the full version: ``` - uses: actions/setup-python@v4 with: python-version: "3.10.6" ``` I'll try that. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | progressbar for inserts/upserts of all fileformats, closes #485 1366512990 | |
1248582147 | https://github.com/simonw/sqlite-utils/pull/486#issuecomment-1248582147 | https://api.github.com/repos/simonw/sqlite-utils/issues/486 | IC_kwDOCGYnMM5Ka9oD | simonw 9599 | 2022-09-15T20:29:17Z | 2022-09-15T20:29:17Z | OWNER | This looks good to me. I need to run some manual tests before merging (it's a good sign that the automated tests pass though). | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | progressbar for inserts/upserts of all fileformats, closes #485 1366512990 | |
1248591268 | https://github.com/simonw/sqlite-utils/pull/486#issuecomment-1248591268 | https://api.github.com/repos/simonw/sqlite-utils/issues/486 | IC_kwDOCGYnMM5Ka_2k | simonw 9599 | 2022-09-15T20:36:02Z | 2022-09-15T20:40:03Z | OWNER | I had a big CSV file lying around, I converted it to other formats like this: sqlite-utils insert /tmp/t.db t /tmp/en.openfoodfacts.org.products.csv --csv sqlite-utils rows /tmp/t.db t --nl > /tmp/big.nl sqlite-utils rows /tmp/t.db t > /tmp/big.json Then tested the progress bar like this: sqlite-utils insert /tmp/t2.db t /tmp/big.nl --nl Output: ``` sqlite-utils insert /tmp/t2.db t /tmp/big.nl --nl [------------------------------------] 0% [#######-----------------------------] 20% 00:00:20 ``` With `--silent` it is silent. And for regular JSON: ``` sqlite-utils insert /tmp/t3.db t /tmp/big.json [####################################] 100% ``` This is actually not doing the right thing. The problem is that `sqlite-utils` doesn't include a streaming JSON parser, so it instead reads that entire JSON file into memory first (exhausting the progress bar to 100% instantly) and then does the rest of the work in-memory while the bar sticks at 100%. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | progressbar for inserts/upserts of all fileformats, closes #485 1366512990 | |
1248593835 | https://github.com/simonw/sqlite-utils/pull/486#issuecomment-1248593835 | https://api.github.com/repos/simonw/sqlite-utils/issues/486 | IC_kwDOCGYnMM5KbAer | simonw 9599 | 2022-09-15T20:37:14Z | 2022-09-15T20:37:14Z | OWNER | I'm going to land this anyway. The lack of a streaming JSON parser is a separate issue, I don't think it should block landing this improvement. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | progressbar for inserts/upserts of all fileformats, closes #485 1366512990 | |
1248597643 | https://github.com/simonw/sqlite-utils/issues/485#issuecomment-1248597643 | https://api.github.com/repos/simonw/sqlite-utils/issues/485 | IC_kwDOCGYnMM5KbBaL | simonw 9599 | 2022-09-15T20:39:39Z | 2022-09-15T20:39:52Z | OWNER | A note from PR #486: https://github.com/simonw/sqlite-utils/issues/486#issuecomment-1248591268_ > ``` > sqlite-utils insert /tmp/t3.db t /tmp/big.json > [####################################] 100% > ``` > This is actually not doing the right thing. The problem is that `sqlite-utils` doesn't include a streaming JSON parser, so it instead reads that entire JSON file into memory first (exhausting the progress bar to 100% instantly) and then does the rest of the work in-memory while the bar sticks at 100%. I decided to land this anyway. If a streaming JSON parser is added later it will start to work. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Progressbar not shown when inserting/upserting jsonlines file 1366423176 | |
1238607591 | https://github.com/simonw/sqlite-utils/issues/484#issuecomment-1238607591 | https://api.github.com/repos/simonw/sqlite-utils/issues/484 | IC_kwDOCGYnMM5J06bn | simonw 9599 | 2022-09-06T20:16:39Z | 2022-09-06T20:16:39Z | OWNER | Here's the implementation for recipes at the moment: https://github.com/simonw/sqlite-utils/blob/5b969273f1244b1bcf3e4dc071cdf17dab35d5f8/sqlite_utils/utils.py#L434-L441 And here's the `--functions` implementation that doesn't expose them: https://github.com/simonw/sqlite-utils/blob/5b969273f1244b1bcf3e4dc071cdf17dab35d5f8/sqlite_utils/cli.py#L3029-L3040 | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Expose convert recipes to `sqlite-utils --functions` 1363766973 | |
1239697643 | https://github.com/simonw/sqlite-utils/issues/484#issuecomment-1239697643 | https://api.github.com/repos/simonw/sqlite-utils/issues/484 | IC_kwDOCGYnMM5J5Ejr | simonw 9599 | 2022-09-07T17:48:00Z | 2022-09-07T17:48:00Z | OWNER | Will also need to update documentation here: https://sqlite-utils.datasette.io/en/stable/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} | Expose convert recipes to `sqlite-utils --functions` 1363766973 | |
1239699276 | https://github.com/simonw/sqlite-utils/issues/484#issuecomment-1239699276 | https://api.github.com/repos/simonw/sqlite-utils/issues/484 | IC_kwDOCGYnMM5J5E9M | simonw 9599 | 2022-09-07T17:49:49Z | 2022-09-07T17:49:49Z | OWNER | This feature is a tiny bit weird though: the recipe functions are not exposed to SQL by default, they are instead designed to be used with `sqlite-utils convert`. I guess with `--functions` support you could do something like this: sqlite-utils data.db "update mytable set col1 = parsedate(col1)" --functions "parsedate = r.parsedate" | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Expose convert recipes to `sqlite-utils --functions` 1363766973 | |
1239759022 | https://github.com/simonw/sqlite-utils/issues/484#issuecomment-1239759022 | https://api.github.com/repos/simonw/sqlite-utils/issues/484 | IC_kwDOCGYnMM5J5Tiu | simonw 9599 | 2022-09-07T18:52:08Z | 2022-09-07T18:52:08Z | OWNER | It's not quite that simple. I tried applying this patch: ```diff diff --git a/sqlite_utils/cli.py b/sqlite_utils/cli.py index c51b101..33e4d90 100644 --- a/sqlite_utils/cli.py +++ b/sqlite_utils/cli.py @@ -30,6 +30,7 @@ from .utils import ( Format, TypeTracker, ) +from . import recipes CONTEXT_SETTINGS = dict(help_option_names=["-h", "--help"]) @@ -3029,7 +3030,7 @@ def _load_extensions(db, load_extension): def _register_functions(db, functions): # Register any Python functions as SQL functions: sqlite3.enable_callback_tracebacks(True) - globals = {} + globals = {"r": recipes, "recipes": recipes} try: exec(functions, globals) except SyntaxError as ex: ``` Then got this: ``` % sqlite-utils memory --functions 'parsedate = r.parsedate' 'select parsedate("1st jan")' Error: wrong number of arguments to function parsedate() % sqlite-utils memory --functions 'parsedate = r.parsedate' 'select parsedate("1st jan", 0, 0, 0)' [{"parsedate(\"1st jan\", 0, 0, 0)": "2022-01-01"}] ``` The problem here is that the `parsedate` function signature looks like this: https://github.com/simonw/sqlite-utils/blob/d9b9e075f07a20f1137cd2e34ed5d3f1a3db4ad8/sqlite_utils/recipes.py#L8 But the code that register SQL functions introspects that signature, so creates a SQL function that requires four arguments. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Expose convert recipes to `sqlite-utils --functions` 1363766973 | |
1239760001 | https://github.com/simonw/sqlite-utils/issues/484#issuecomment-1239760001 | https://api.github.com/repos/simonw/sqlite-utils/issues/484 | IC_kwDOCGYnMM5J5TyB | simonw 9599 | 2022-09-07T18:53:17Z | 2022-09-07T18:53:17Z | OWNER | So you would need to do this instead: ``` sqlite-utils memory 'select parsedate("1st jan")' --functions ' def parsedate(s): return r.parsedate(s) ' ``` | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Expose convert recipes to `sqlite-utils --functions` 1363766973 | |
1239761280 | https://github.com/simonw/sqlite-utils/issues/484#issuecomment-1239761280 | https://api.github.com/repos/simonw/sqlite-utils/issues/484 | IC_kwDOCGYnMM5J5UGA | simonw 9599 | 2022-09-07T18:54:51Z | 2022-09-07T18:54:51Z | OWNER | I could teach this code here to only register the function using arguments that don't have default parameters: https://github.com/simonw/sqlite-utils/blob/d9b9e075f07a20f1137cd2e34ed5d3f1a3db4ad8/sqlite_utils/cli.py#L3037-L3040 Or even this code here: https://github.com/simonw/sqlite-utils/blob/d9b9e075f07a20f1137cd2e34ed5d3f1a3db4ad8/sqlite_utils/db.py#L398-L418 | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Expose convert recipes to `sqlite-utils --functions` 1363766973 | |
1239762031 | https://github.com/simonw/sqlite-utils/issues/484#issuecomment-1239762031 | https://api.github.com/repos/simonw/sqlite-utils/issues/484 | IC_kwDOCGYnMM5J5URv | simonw 9599 | 2022-09-07T18:55:30Z | 2022-09-07T18:55:30Z | OWNER | That would be a breaking change though - existing code that registers functions with default parameters should continue to work unchanged (unless I want to ship `sqlite-utils` 4.0). | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Expose convert recipes to `sqlite-utils --functions` 1363766973 |
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]);
author_association 1 ✖