issue_comments
9,947 rows sorted by html_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 |
---|---|---|---|---|---|---|---|---|---|---|---|
740796067 | https://github.com/simonw/sqlite-utils/pull/204#issuecomment-740796067 | https://api.github.com/repos/simonw/sqlite-utils/issues/204 | MDEyOklzc3VlQ29tbWVudDc0MDc5NjA2Nw== | simonw 9599 | 2020-12-08T17:49:22Z | 2020-12-08T17:49:22Z | OWNER | Great catch, thank you. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | use jsonify_if_need for sql updates 752888228 | |
774217792 | https://github.com/simonw/sqlite-utils/pull/203#issuecomment-774217792 | https://api.github.com/repos/simonw/sqlite-utils/issues/203 | MDEyOklzc3VlQ29tbWVudDc3NDIxNzc5Mg== | drkane 1049910 | 2021-02-05T18:44:13Z | 2021-02-05T18:44:13Z | NONE | Thanks for looking at this - home schooling kids has prevented me from replying. I'd struggled with how to adapt the API for the foreign keys too - I definitely tried the String/Tuple approach. I hadn't considered the breaking changes that would introduce though. I can take a look at this and try and make the change - see which of your options works best. I've got a workaround for the use-case I was looking at this for, so it wouldn't be a problem for me if it was put on the back burner until a hypothetical v4.0 anyway. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | changes to allow for compound foreign keys 743384829 | |
753567932 | https://github.com/simonw/sqlite-utils/pull/203#issuecomment-753567932 | https://api.github.com/repos/simonw/sqlite-utils/issues/203 | MDEyOklzc3VlQ29tbWVudDc1MzU2NzkzMg== | simonw 9599 | 2021-01-03T04:54:43Z | 2021-01-03T04:54:43Z | OWNER | Another option: expand the `ForeignKey` object to have `.columns` and `.other_columns` properties in addition to the existing `.column` and `.other_column` properties. These new plural properties would always return a tuple, which would be a one-item tuple for a non-compound-foreign-key. The question then is what should `.column` and `.other_column` return for compound foreign keys? I'd be inclined to say they should return `None` - which would trigger errors in code that encounters a compound foreign key for the first time, but those errors would at least be a strong indicator as to what had gone wrong. We can label `.column` and `.other_column` as deprecated and then remove them in `sqlite-utils 4.0`. Since this would still be a breaking change in some minor edge-cases I'm thinking maybe 4.0 needs to happen in order to land this feature. I'm not opposed to doing that, I was just hoping it might be avoidable. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | changes to allow for compound foreign keys 743384829 | |
753567744 | https://github.com/simonw/sqlite-utils/pull/203#issuecomment-753567744 | https://api.github.com/repos/simonw/sqlite-utils/issues/203 | MDEyOklzc3VlQ29tbWVudDc1MzU2Nzc0NA== | simonw 9599 | 2021-01-03T04:51:44Z | 2021-01-03T04:51:44Z | OWNER | One way that this could avoid a breaking change would be to have `fk.column` and `fk.other_column` remain as strings for non-compound-foreign-keys, but turn into tuples for a compound foreign key. This is a bit of an ugly API design, and it could still break existing code that encounters a compound foreign key for the first time - but it would leave code working for the more common case of a non-compound-foreign-key. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | changes to allow for compound foreign keys 743384829 | |
753567508 | https://github.com/simonw/sqlite-utils/pull/203#issuecomment-753567508 | https://api.github.com/repos/simonw/sqlite-utils/issues/203 | MDEyOklzc3VlQ29tbWVudDc1MzU2NzUwOA== | simonw 9599 | 2021-01-03T04:48:17Z | 2021-01-03T04:48:17Z | OWNER | Sorry for taking so long to review this! This approach looks great to me - being able to optionally pass a tuple anywhere the API currently expects a column is smart, and it's consistent with how the `pk=` parameter works elsewhere. There's just one problem I can see with this: the way it changes the `ForeignKey(...)` interface to always return a tuple for `.column` and `.other_column`, even if that tuple only contains a single item. This represents a breaking change to the existing API - any code that expects `ForeignKey.column` to be a single string (which is any code that has been written against that) will break. As such, I'd have to bump the major version of `sqlite-utils` to `4.0` in order to ship this. Ideally I'd like to make this change in a way that doesn't represent an API compatibility break. I need to think a bit harder about how that might be achieved. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | changes to allow for compound foreign keys 743384829 | |
743966289 | https://github.com/simonw/sqlite-utils/pull/203#issuecomment-743966289 | https://api.github.com/repos/simonw/sqlite-utils/issues/203 | MDEyOklzc3VlQ29tbWVudDc0Mzk2NjI4OQ== | simonw 9599 | 2020-12-13T07:20:51Z | 2020-12-13T07:20:51Z | OWNER | Sorry for not reviewing this yet! I'll try to carve out time to look at it in the next few days. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | changes to allow for compound foreign keys 743384829 | |
1404070841 | https://github.com/simonw/sqlite-utils/pull/203#issuecomment-1404070841 | https://api.github.com/repos/simonw/sqlite-utils/issues/203 | IC_kwDOCGYnMM5TsGu5 | fgregg 536941 | 2023-01-25T18:47:18Z | 2023-01-25T18:47:18Z | CONTRIBUTOR | i'll adopt this PR to make the changes @simonw suggested https://github.com/simonw/sqlite-utils/pull/203#issuecomment-753567932 | {"total_count": 1, "+1": 1, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | changes to allow for compound foreign keys 743384829 | |
1033641009 | https://github.com/simonw/sqlite-utils/pull/203#issuecomment-1033641009 | https://api.github.com/repos/simonw/sqlite-utils/issues/203 | IC_kwDOCGYnMM49nBwx | psychemedia 82988 | 2022-02-09T11:06:18Z | 2022-02-09T11:06:18Z | NONE | Is there any progress elsewhere on the handling of compound / composite foreign keys, or is this PR still effectively open? | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | changes to allow for compound foreign keys 743384829 | |
723148906 | https://github.com/simonw/sqlite-utils/pull/195#issuecomment-723148906 | https://api.github.com/repos/simonw/sqlite-utils/issues/195 | MDEyOklzc3VlQ29tbWVudDcyMzE0ODkwNg== | simonw 9599 | 2020-11-06T15:43:51Z | 2020-11-06T15:43:51Z | OWNER | Thanks to #198 (introducing a `rank_bm25()` custom function for FTS4) this feature will be able to offer relevance search for both FTS5 AND FTS4 tables. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | table.search() improvements plus sqlite-utils search command 735663855 | |
722542895 | https://github.com/simonw/sqlite-utils/pull/195#issuecomment-722542895 | https://api.github.com/repos/simonw/sqlite-utils/issues/195 | MDEyOklzc3VlQ29tbWVudDcyMjU0Mjg5NQ== | simonw 9599 | 2020-11-05T18:01:33Z | 2020-11-05T18:01:33Z | OWNER | Latest test failure: ``` 114 -> assert [("racoons are biting trash pandas", "USA", "bar")] == table.search( 115 "bite", order="rowid" 116 ) 117 118 119 def test_optimize_fts(fresh_db): (Pdb) table.search("bite") [(2, 'racoons are biting trash pandas', 'USA', 'bar', -9.641434262948206e-07)] ``` The problem here is that the `table.search()` method now behaves differently for FTS4 v.s. FTS5 tables. With FTS4 you get back just the table columns. With FTS5 you also get back the `rowid` as the first column and the `rank` score as the last column. This is weird. It also makes me question whether having `.search()` return a list of tuples is the right API design. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | table.search() improvements plus sqlite-utils search command 735663855 | |
721397665 | https://github.com/simonw/sqlite-utils/pull/195#issuecomment-721397665 | https://api.github.com/repos/simonw/sqlite-utils/issues/195 | MDEyOklzc3VlQ29tbWVudDcyMTM5NzY2NQ== | simonw 9599 | 2020-11-03T22:02:57Z | 2020-11-03T22:02:57Z | OWNER | Documentation so far: https://github.com/simonw/sqlite-utils/blob/6cadc6103ff1ba58c6409ce7fba74259e72965d9/docs/cli.rst#executing-searches | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | table.search() improvements plus sqlite-utils search command 735663855 | |
717361487 | https://github.com/simonw/sqlite-utils/pull/189#issuecomment-717361487 | https://api.github.com/repos/simonw/sqlite-utils/issues/189 | MDEyOklzc3VlQ29tbWVudDcxNzM2MTQ4Nw== | simonw 9599 | 2020-10-27T16:24:04Z | 2020-10-27T16:24:04Z | OWNER | This is great, thank you very much. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Allow iterables other than Lists in m2m records 729818242 | |
717359145 | https://github.com/simonw/sqlite-utils/pull/189#issuecomment-717359145 | https://api.github.com/repos/simonw/sqlite-utils/issues/189 | MDEyOklzc3VlQ29tbWVudDcxNzM1OTE0NQ== | adamwolf 35681 | 2020-10-27T16:20:32Z | 2020-10-27T16:20:32Z | CONTRIBUTOR | No problem. I added a test. Let me know if it looks sufficient or if you want me to to tweak something! If you don't mind, would you tag this PR as "hacktoberfest-accepted"? If you do mind, no problem and I'm sorry for asking :) My kiddos like the shirts. | {"total_count": 1, "+1": 1, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Allow iterables other than Lists in m2m records 729818242 | |
716756103 | https://github.com/simonw/sqlite-utils/pull/189#issuecomment-716756103 | https://api.github.com/repos/simonw/sqlite-utils/issues/189 | MDEyOklzc3VlQ29tbWVudDcxNjc1NjEwMw== | simonw 9599 | 2020-10-26T18:56:19Z | 2020-10-26T18:56:19Z | OWNER | This is a great fix, thanks! If you add a unit test somewhere in here I'll merge the PR: https://github.com/simonw/sqlite-utils/blob/main/tests/test_m2m.py | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Allow iterables other than Lists in m2m records 729818242 | |
701627158 | https://github.com/simonw/sqlite-utils/pull/178#issuecomment-701627158 | https://api.github.com/repos/simonw/sqlite-utils/issues/178 | MDEyOklzc3VlQ29tbWVudDcwMTYyNzE1OA== | simonw 9599 | 2020-09-30T20:29:11Z | 2020-09-30T20:29:11Z | OWNER | Thanks for the fix! | {"total_count": 1, "+1": 1, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Update README.md 709043182 | |
698400790 | https://github.com/simonw/sqlite-utils/pull/174#issuecomment-698400790 | https://api.github.com/repos/simonw/sqlite-utils/issues/174 | MDEyOklzc3VlQ29tbWVudDY5ODQwMDc5MA== | simonw 9599 | 2020-09-24T14:59:50Z | 2020-09-24T14:59:50Z | OWNER | For reusing the lookup table: I'm going to raise an error if a lookup table exists but without the correct columns. The caller can then add those columns and try again. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Much, much faster extract() implementation 707944044 | |
698184166 | https://github.com/simonw/sqlite-utils/pull/174#issuecomment-698184166 | https://api.github.com/repos/simonw/sqlite-utils/issues/174 | MDEyOklzc3VlQ29tbWVudDY5ODE4NDE2Ng== | simonw 9599 | 2020-09-24T08:01:07Z | 2020-09-24T08:01:07Z | OWNER | I may revert the now unnecessary undocumented tweaks to the `.update()` method made in 66d506587eba9f0715267d6560b97c1fa44cc781 as well. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Much, much faster extract() implementation 707944044 | |
698182656 | https://github.com/simonw/sqlite-utils/pull/174#issuecomment-698182656 | https://api.github.com/repos/simonw/sqlite-utils/issues/174 | MDEyOklzc3VlQ29tbWVudDY5ODE4MjY1Ng== | simonw 9599 | 2020-09-24T07:58:08Z | 2020-09-24T07:58:08Z | OWNER | The way the lookup table works here differs from the previous implementation. In the previous implementation the usage of `.lookup()` meant that an existing table would be modified to fit the new purpose. That no longer happens in this version. Need to make a design decision about how this should work. It should definitely be possible to use an existing lookup table - imagine a database where several tables have a "Departments" column and we want to extract all of those values out to a single shared "Departments" table. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Much, much faster extract() implementation 707944044 | |
698182037 | https://github.com/simonw/sqlite-utils/pull/174#issuecomment-698182037 | https://api.github.com/repos/simonw/sqlite-utils/issues/174 | MDEyOklzc3VlQ29tbWVudDY5ODE4MjAzNw== | simonw 9599 | 2020-09-24T07:56:50Z | 2020-09-24T07:56:50Z | OWNER | I could also be a bit smarter about transaction handling. I think it may be possible to run this entire operation in a single transaction now. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Much, much faster extract() implementation 707944044 | |
698181478 | https://github.com/simonw/sqlite-utils/pull/174#issuecomment-698181478 | https://api.github.com/repos/simonw/sqlite-utils/issues/174 | MDEyOklzc3VlQ29tbWVudDY5ODE4MTQ3OA== | simonw 9599 | 2020-09-24T07:55:45Z | 2020-09-24T07:55:45Z | OWNER | `import functools` is no longer needed. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Much, much faster extract() implementation 707944044 | |
698180705 | https://github.com/simonw/sqlite-utils/pull/174#issuecomment-698180705 | https://api.github.com/repos/simonw/sqlite-utils/issues/174 | MDEyOklzc3VlQ29tbWVudDY5ODE4MDcwNQ== | simonw 9599 | 2020-09-24T07:54:10Z | 2020-09-24T07:54:10Z | OWNER | After running through the steps in https://simonwillison.net/2020/Sep/23/sqlite-utils-extract/ I get a table that looks like this: <img width="1699" alt="salaries__salaries__683_558_rows" src="https://user-images.githubusercontent.com/9599/94116875-666b8900-fe00-11ea-9e97-2b9ccbfeae29.png"> The foreign key columns are all at the end of the table. It would be nicer if they were arranged in the same order as the columns they replaced. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Much, much faster extract() implementation 707944044 | |
698180113 | https://github.com/simonw/sqlite-utils/pull/174#issuecomment-698180113 | https://api.github.com/repos/simonw/sqlite-utils/issues/174 | MDEyOklzc3VlQ29tbWVudDY5ODE4MDExMw== | simonw 9599 | 2020-09-24T07:53:03Z | 2020-09-24T07:53:03Z | OWNER | This could do with a little bit more testing - I'm worried there may be column or table name edge cases that are not covered yet. I also need to remove the progress bar code since that no longer makes sense for this implementation. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Much, much faster extract() implementation 707944044 | |
696494070 | https://github.com/simonw/sqlite-utils/pull/161#issuecomment-696494070 | https://api.github.com/repos/simonw/sqlite-utils/issues/161 | MDEyOklzc3VlQ29tbWVudDY5NjQ5NDA3MA== | simonw 9599 | 2020-09-22T03:48:58Z | 2020-09-22T03:48:58Z | OWNER | One last thing. https://www.sqlite.org/lang_altertable.html#making_other_kinds_of_table_schema_change says that the first step should be: > If foreign key constraints are enabled, disable them using PRAGMA foreign_keys=OFF. And the last steps should be: > If foreign key constraints were originally enabled then run PRAGMA foreign_key_check to verify that the schema change did not break any foreign key constraints. > > Commit the transaction started in step 2. > > If foreign keys constraints were originally enabled, reenable them now. I need to implement that. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | table.transform() method 705975133 | |
696490851 | https://github.com/simonw/sqlite-utils/pull/161#issuecomment-696490851 | https://api.github.com/repos/simonw/sqlite-utils/issues/161 | MDEyOklzc3VlQ29tbWVudDY5NjQ5MDg1MQ== | simonw 9599 | 2020-09-22T03:33:54Z | 2020-09-22T03:33:54Z | OWNER | It would be neat if `.transform(pk=None)` converted a primary key table to a rowid table. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | table.transform() method 705975133 | |
696488201 | https://github.com/simonw/sqlite-utils/pull/161#issuecomment-696488201 | https://api.github.com/repos/simonw/sqlite-utils/issues/161 | MDEyOklzc3VlQ29tbWVudDY5NjQ4ODIwMQ== | simonw 9599 | 2020-09-22T03:21:16Z | 2020-09-22T03:21:16Z | OWNER | Just needs documentation now. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | table.transform() method 705975133 | |
696485791 | https://github.com/simonw/sqlite-utils/pull/161#issuecomment-696485791 | https://api.github.com/repos/simonw/sqlite-utils/issues/161 | MDEyOklzc3VlQ29tbWVudDY5NjQ4NTc5MQ== | simonw 9599 | 2020-09-22T03:10:15Z | 2020-09-22T03:10:15Z | OWNER | Design decision needed on foreign keys: what does the syntax look like for removing an existing foreign key? Since I already have a good implementation of `add_foreign_key()` I'm tempted to only support dropping them. Maybe like this: ```python table.transform(drop_foreign_keys=[("author_id", "author", "id")]) ``` It's a bit crufty but it's such a rare use-case that I think this will be good enough. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | table.transform() method 705975133 | |
696480925 | https://github.com/simonw/sqlite-utils/pull/161#issuecomment-696480925 | https://api.github.com/repos/simonw/sqlite-utils/issues/161 | MDEyOklzc3VlQ29tbWVudDY5NjQ4MDkyNQ== | simonw 9599 | 2020-09-22T02:45:47Z | 2020-09-22T02:45:47Z | OWNER | I'm not going to do `conversions=` because it would be inconsistent with how they work elsewhere. The SQL generated by this function looks like this: INSERT INTO dogs_new_tmp VALUES (a, b) SELECT a, b from dogs; So passing `conversions={"name": "upper(?)"})` wouldn't make sense, since we're not using arguments hence there is no-where for that `?` to go. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | table.transform() method 705975133 | |
696446658 | https://github.com/simonw/sqlite-utils/pull/161#issuecomment-696446658 | https://api.github.com/repos/simonw/sqlite-utils/issues/161 | MDEyOklzc3VlQ29tbWVudDY5NjQ0NjY1OA== | simonw 9599 | 2020-09-22T00:13:55Z | 2020-09-22T00:14:21Z | OWNER | Idea: allow a `conversions=` parameter, as seen on `.insert_all()` and friends, which lets you apply a SQL transformation function as part of the operation. E.g.: ```python table.transform({"age": int}, conversions={"name": "upper(?)"}) ``` https://sqlite-utils.readthedocs.io/en/stable/python-api.html#converting-column-values-using-sql-functions | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | table.transform() method 705975133 | |
696445766 | https://github.com/simonw/sqlite-utils/pull/161#issuecomment-696445766 | https://api.github.com/repos/simonw/sqlite-utils/issues/161 | MDEyOklzc3VlQ29tbWVudDY5NjQ0NTc2Ng== | simonw 9599 | 2020-09-22T00:10:50Z | 2020-09-22T00:11:12Z | OWNER | A less horrible interface might be the following: ```python # Ensure the 'age' column is not null: table.transform(not_null={"age"}) # The 'age' column is not null but I don't want it to be: table.transform(not_null={"age": False}) ``` So if the argument is a set it means "make sure these are all not null" - if the argument is a dictionary it means "set these to be null or not null depending on if their dictionary value is true or false". | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | table.transform() method 705975133 | |
696444842 | https://github.com/simonw/sqlite-utils/pull/161#issuecomment-696444842 | https://api.github.com/repos/simonw/sqlite-utils/issues/161 | MDEyOklzc3VlQ29tbWVudDY5NjQ0NDg0Mg== | simonw 9599 | 2020-09-22T00:07:43Z | 2020-09-22T00:09:05Z | OWNER | Syntax challenge: I could use `.transform(defaults={"age": None})` to indicate that the `age` column should have its default removed, but how would I tell `.transform()` that the `age` column, currently `not null`, should have the `not null` removed from it? I could do this: `.transform(not_not_null={"age"})` - it's a bit gross but it's also kind of funny. I actually like it! | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | table.transform() method 705975133 | |
696444353 | https://github.com/simonw/sqlite-utils/pull/161#issuecomment-696444353 | https://api.github.com/repos/simonw/sqlite-utils/issues/161 | MDEyOklzc3VlQ29tbWVudDY5NjQ0NDM1Mw== | simonw 9599 | 2020-09-22T00:06:12Z | 2020-09-22T00:06:12Z | OWNER | I should support `not_null=` and `default=` arguments to the `.transform()` method because it looks like you can't use `ALTER TABLE` to change those. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | table.transform() method 705975133 | |
696443845 | https://github.com/simonw/sqlite-utils/pull/161#issuecomment-696443845 | https://api.github.com/repos/simonw/sqlite-utils/issues/161 | MDEyOklzc3VlQ29tbWVudDY5NjQ0Mzg0NQ== | simonw 9599 | 2020-09-22T00:04:31Z | 2020-09-22T00:04:44Z | OWNER | Good news: the `.columns` introspection does tell me those things: ``` >>> import sqlite_utils >>> db = sqlite_utils.Database(memory=True) >>> db.create_table("foo", {"id": int, "name": str, "age": int}, defaults={"age": 1}, not_null={"name", "age"}) <Table foo (id, name, age)> >>> db["foo"] <Table foo (id, name, age)> >>> print(db["foo"].schema) CREATE TABLE [foo] ( [id] INTEGER, [name] TEXT NOT NULL, [age] INTEGER NOT NULL DEFAULT 1 ) >>> db["foo"].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=None, is_pk=0), Column(cid=2, name='age', type='INTEGER', notnull=1, default_value='1', is_pk=0)] ``` | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | table.transform() method 705975133 | |
696443190 | https://github.com/simonw/sqlite-utils/pull/161#issuecomment-696443190 | https://api.github.com/repos/simonw/sqlite-utils/issues/161 | MDEyOklzc3VlQ29tbWVudDY5NjQ0MzE5MA== | simonw 9599 | 2020-09-22T00:02:22Z | 2020-09-22T00:02:22Z | OWNER | How would I detect which columns are `not_null` and what their defaults are? I don`t think my introspection logic handles that yet. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | table.transform() method 705975133 | |
696443042 | https://github.com/simonw/sqlite-utils/pull/161#issuecomment-696443042 | https://api.github.com/repos/simonw/sqlite-utils/issues/161 | MDEyOklzc3VlQ29tbWVudDY5NjQ0MzA0Mg== | simonw 9599 | 2020-09-22T00:01:50Z | 2020-09-22T00:01:50Z | OWNER | When you transform a table, it should keep its primary key, foreign keys, not_null and defaults. I don't think it needs to care about `hash_id` or `extracts=` since those don't affect the structure of the table as it is being created - well, `hash_id` does but if we are transforming an existing table we will get the `hash_id` column for free. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | table.transform() method 705975133 | |
696442621 | https://github.com/simonw/sqlite-utils/pull/161#issuecomment-696442621 | https://api.github.com/repos/simonw/sqlite-utils/issues/161 | MDEyOklzc3VlQ29tbWVudDY5NjQ0MjYyMQ== | simonw 9599 | 2020-09-22T00:00:23Z | 2020-09-22T00:00:23Z | OWNER | I still need to figure out what to do about these various other table properties: https://github.com/simonw/sqlite-utils/blob/b34c9b40c206d7a9d7ee57a8c1f198ff1f522735/sqlite_utils/db.py#L775-L787 | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | table.transform() method 705975133 | |
693199392 | https://github.com/simonw/sqlite-utils/pull/158#issuecomment-693199392 | https://api.github.com/repos/simonw/sqlite-utils/issues/158 | MDEyOklzc3VlQ29tbWVudDY5MzE5OTM5Mg== | simonw 9599 | 2020-09-16T06:21:29Z | 2020-09-16T06:21:29Z | OWNER | Thanks! | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Fix accidental mega long line in docs 697203800 | |
689735140 | https://github.com/simonw/sqlite-utils/pull/156#issuecomment-689735140 | https://api.github.com/repos/simonw/sqlite-utils/issues/156 | MDEyOklzc3VlQ29tbWVudDY4OTczNTE0MA== | simonw 9599 | 2020-09-09T18:21:06Z | 2020-09-09T18:21:06Z | OWNER | Good spot, thanks. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Typos in tests 697030843 | |
689185393 | https://github.com/simonw/sqlite-utils/pull/146#issuecomment-689185393 | https://api.github.com/repos/simonw/sqlite-utils/issues/146 | MDEyOklzc3VlQ29tbWVudDY4OTE4NTM5Mw== | simonw 9599 | 2020-09-08T23:17:42Z | 2020-09-08T23:17:42Z | OWNER | That seems like a reasonable approach to me, especially since this is going to be a pretty rare edge-case. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Handle case where subsequent records (after first batch) include extra columns 688668680 | |
688573964 | https://github.com/simonw/sqlite-utils/pull/146#issuecomment-688573964 | https://api.github.com/repos/simonw/sqlite-utils/issues/146 | MDEyOklzc3VlQ29tbWVudDY4ODU3Mzk2NA== | simonwiles 96218 | 2020-09-08T01:55:07Z | 2020-09-08T01:55:07Z | CONTRIBUTOR | Okay, I've rewritten this PR to preserve the batching behaviour but still fix #145, and rebased the branch to account for the `db.execute()` api change. It's not terribly sophisticated -- if it attempts to insert a batch which has too many variables, the exception is caught, the batch is split in two and each half is inserted separately, and then it carries on as before with the same `batch_size`. In the edge case where this gets triggered, subsequent batches will all be inserted in two groups too if they continue to have the same number of columns (which is presumably reasonably likely). Do you reckon this is acceptable when set against the awkwardness of recalculating the `batch_size` on the fly? | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Handle case where subsequent records (after first batch) include extra columns 688668680 | |
688508510 | https://github.com/simonw/sqlite-utils/pull/146#issuecomment-688508510 | https://api.github.com/repos/simonw/sqlite-utils/issues/146 | MDEyOklzc3VlQ29tbWVudDY4ODUwODUxMA== | simonw 9599 | 2020-09-07T20:56:03Z | 2020-09-07T20:56:24Z | OWNER | The problem with this approach is that it requires us to consume the entire iterator before we can start inserting rows into the table - here on line 1052: https://github.com/simonw/sqlite-utils/blob/bb131793feac16bc7181ab997568f941b0220ef2/sqlite_utils/db.py#L1047-L1054 I designed the `.insert_all()` to avoid doing this, because I want to be able to pass it an iterator (or more likely a generator) that could produce potentially millions of records. Doing things one batch of 100 records at a time means that the Python process doesn't need to pull millions of records into memory at once. `db-to-sqlite` is one example of a tool that uses that characteristic, in https://github.com/simonw/db-to-sqlite/blob/63e4ee972f292de13bb11767c0fb64b35339d954/db_to_sqlite/cli.py#L94-L106 So we need to solve this issue without consuming the entire iterator with a `records = list(records)` call. I think one way to do this is to execute each chunk one at a time and watch out for an exception that indicates that we sent too many parameters - then adjust the chunk size down and try again. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Handle case where subsequent records (after first batch) include extra columns 688668680 | |
688481317 | https://github.com/simonw/sqlite-utils/pull/146#issuecomment-688481317 | https://api.github.com/repos/simonw/sqlite-utils/issues/146 | MDEyOklzc3VlQ29tbWVudDY4ODQ4MTMxNw== | simonwiles 96218 | 2020-09-07T19:18:55Z | 2020-09-07T19:18:55Z | CONTRIBUTOR | Just force-pushed to update d042f9c with more formatting changes to satisfy `black==20.8b1` and pass the GitHub Actions "Test" workflow. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Handle case where subsequent records (after first batch) include extra columns 688668680 | |
688479163 | https://github.com/simonw/sqlite-utils/pull/146#issuecomment-688479163 | https://api.github.com/repos/simonw/sqlite-utils/issues/146 | MDEyOklzc3VlQ29tbWVudDY4ODQ3OTE2Mw== | simonwiles 96218 | 2020-09-07T19:10:33Z | 2020-09-07T19:11:57Z | CONTRIBUTOR | @simonw -- I've gone ahead updated the documentation to reflect the changes introduced in this PR. IMO it's ready to merge now. In writing the documentation changes, I begin to wonder about the value and role of `batch_size` at all, tbh. May I assume it was originally intended to prevent using the entire row set to determine columns and column types, and that this was a performance consideration? If so, this PR entirely undermines its purpose. I've been passing in excess of 500,000 rows at a time to `insert_all()` with these changes and although I'm sure the performance difference is measurable it's not really noticeable; given #145, I don't know that any performance advantages outweigh the problems doing it this way removes. What do you think about just dropping the argument and defaulting to the maximum `batch_size` permissible given `SQLITE_MAX_VARS`? Are there other reasons one might want to restrict `batch_size` that I've overlooked? I could open a new issue to discuss/implement this. Of course the documentation will need to change again too if/when something is done about #147. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Handle case where subsequent records (after first batch) include extra columns 688668680 | |
683173375 | https://github.com/simonw/sqlite-utils/pull/142#issuecomment-683173375 | https://api.github.com/repos/simonw/sqlite-utils/issues/142 | MDEyOklzc3VlQ29tbWVudDY4MzE3MzM3NQ== | simonw 9599 | 2020-08-28T22:29:02Z | 2020-08-28T22:29:02Z | OWNER | Yeah I think that failure is actually because there's a brand new release of Black out and it subtly changes some of the formatting rules. I'll merge this and then run Black against the entire codebase. | {"total_count": 1, "+1": 1, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | insert_all(..., alter=True) should work for new columns introduced after the first 100 records 688386219 | |
683172829 | https://github.com/simonw/sqlite-utils/pull/142#issuecomment-683172829 | https://api.github.com/repos/simonw/sqlite-utils/issues/142 | MDEyOklzc3VlQ29tbWVudDY4MzE3MjgyOQ== | simonw 9599 | 2020-08-28T22:27:05Z | 2020-08-28T22:27:05Z | OWNER | Looks like it failed the "black" formatting test - possibly because there's a new release if black out. I'm going to merge despite that failure. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | insert_all(..., alter=True) should work for new columns introduced after the first 100 records 688386219 | |
683172082 | https://github.com/simonw/sqlite-utils/pull/142#issuecomment-683172082 | https://api.github.com/repos/simonw/sqlite-utils/issues/142 | MDEyOklzc3VlQ29tbWVudDY4MzE3MjA4Mg== | simonw 9599 | 2020-08-28T22:24:25Z | 2020-08-28T22:24:25Z | OWNER | Thanks very much! | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | insert_all(..., alter=True) should work for new columns introduced after the first 100 records 688386219 | |
655289686 | https://github.com/simonw/sqlite-utils/pull/120#issuecomment-655289686 | https://api.github.com/repos/simonw/sqlite-utils/issues/120 | MDEyOklzc3VlQ29tbWVudDY1NTI4OTY4Ng== | simonw 9599 | 2020-07-08T05:13:11Z | 2020-07-08T05:13:11Z | OWNER | This is an excellent fix, thanks! | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Fix query command's support for DML 652816158 | |
655653292 | https://github.com/simonw/sqlite-utils/pull/118#issuecomment-655653292 | https://api.github.com/repos/simonw/sqlite-utils/issues/118 | MDEyOklzc3VlQ29tbWVudDY1NTY1MzI5Mg== | simonw 9599 | 2020-07-08T17:26:02Z | 2020-07-08T17:26:02Z | OWNER | Awesome, thank you very much. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Add insert --truncate option 651844316 | |
655643078 | https://github.com/simonw/sqlite-utils/pull/118#issuecomment-655643078 | https://api.github.com/repos/simonw/sqlite-utils/issues/118 | MDEyOklzc3VlQ29tbWVudDY1NTY0MzA3OA== | tsibley 79913 | 2020-07-08T17:05:59Z | 2020-07-08T17:05:59Z | CONTRIBUTOR | > The only thing missing from this PR is updates to the documentation. Ah, yes, thanks for this reminder! I've repushed with doc bits added. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Add insert --truncate option 651844316 | |
655286864 | https://github.com/simonw/sqlite-utils/pull/118#issuecomment-655286864 | https://api.github.com/repos/simonw/sqlite-utils/issues/118 | MDEyOklzc3VlQ29tbWVudDY1NTI4Njg2NA== | simonw 9599 | 2020-07-08T05:05:27Z | 2020-07-08T05:05:36Z | OWNER | The only thing missing from this PR is updates to the documentation. Those need to go in two places: - In the Python API docs. I suggest adding a note to this section about bulk inserts: https://github.com/simonw/sqlite-utils/blob/d0cdaaaf00249230e847be3a3b393ee2689fbfe4/docs/python-api.rst#bulk-inserts - In the CLI docs, in this section: https://github.com/simonw/sqlite-utils/blob/d0cdaaaf00249230e847be3a3b393ee2689fbfe4/docs/cli.rst#inserting-json-data Here's an example of a previous commit that includes updates to both CLI and API documentation: https://github.com/simonw/sqlite-utils/commit/f9473ace14878212c1fa968b7bd2f51e4f064dba#diff-e3e2a9bfd88566b05001b02a3f51d286 | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Add insert --truncate option 651844316 | |
655284168 | https://github.com/simonw/sqlite-utils/pull/118#issuecomment-655284168 | https://api.github.com/repos/simonw/sqlite-utils/issues/118 | MDEyOklzc3VlQ29tbWVudDY1NTI4NDE2OA== | simonw 9599 | 2020-07-08T04:58:00Z | 2020-07-08T04:58:00Z | OWNER | Oops didn't mean to click "close" there. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Add insert --truncate option 651844316 | |
655284054 | https://github.com/simonw/sqlite-utils/pull/118#issuecomment-655284054 | https://api.github.com/repos/simonw/sqlite-utils/issues/118 | MDEyOklzc3VlQ29tbWVudDY1NTI4NDA1NA== | simonw 9599 | 2020-07-08T04:57:38Z | 2020-07-08T04:57:38Z | OWNER | Thoughts on transactions would be much appreciated in #121 | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Add insert --truncate option 651844316 | |
655283393 | https://github.com/simonw/sqlite-utils/pull/118#issuecomment-655283393 | https://api.github.com/repos/simonw/sqlite-utils/issues/118 | MDEyOklzc3VlQ29tbWVudDY1NTI4MzM5Mw== | simonw 9599 | 2020-07-08T04:55:18Z | 2020-07-08T04:55:18Z | OWNER | This is a really good idea - and thank you for the detailed discussion in the pull request. I'm keen to discuss how transactions can work better. I tend to use this pattern in my own code: with db.conn: db["table"].insert(...) But it's not documented and I've not though very hard about it! I like having inserts that handle 10,000+ rows commit on every chunk so I can watch their progress from another process, but the library should absolutely support people who want to commit all of the rows in a single transaction - or combine changes with DML. Lots to discuss here. I'll start a new issue. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Add insert --truncate option 651844316 | |
655239728 | https://github.com/simonw/sqlite-utils/pull/118#issuecomment-655239728 | https://api.github.com/repos/simonw/sqlite-utils/issues/118 | MDEyOklzc3VlQ29tbWVudDY1NTIzOTcyOA== | tsibley 79913 | 2020-07-08T02:16:42Z | 2020-07-08T02:16:42Z | CONTRIBUTOR | I fixed my original oops by moving the `DELETE FROM $table` out of the chunking loop and repushed. I think this change can be considered in isolation from issues around transactions, which I discuss next. I wanted to make the DELETE + INSERT happen all in the same transaction so it was robust, but that was more complicated than I expected. The transaction handling in the Database/Table classes isn't systematic, and this poses big hurdles to making `Table.insert_all` (or other operations) consistent and robust in the face of errors. For example, I wanted to do this (whitespace ignored in diff, so indentation change not highlighted): ```diff diff --git a/sqlite_utils/db.py b/sqlite_utils/db.py index d6b9ecf..4107ceb 100644 --- a/sqlite_utils/db.py +++ b/sqlite_utils/db.py @@ -1028,6 +1028,11 @@ class Table(Queryable): batch_size = max(1, min(batch_size, SQLITE_MAX_VARS // num_columns)) self.last_rowid = None self.last_pk = None + with self.db.conn: + # Explicit BEGIN is necessary because Python's sqlite3 doesn't + # issue implicit BEGINs for DDL, only DML. We mix DDL and DML + # below and might execute DDL first, e.g. for table creation. + self.db.conn.execute("BEGIN") if truncate and self.exists(): self.db.conn.execute("DELETE FROM [{}];".format(self.name)) for chunk in chunks(itertools.chain([first_record], records), batch_size): @@ -1038,7 +1043,11 @@ class Table(Queryable): # Use the first batch to derive the table names column_types = suggest_column_types(chunk) column_types.update(columns or {}) - self.create( + # Not self.create() because that is wrapped in its own + # transaction and Python's sqlite3 doesn't support + # nested transactions. + self.db.create_table( + … | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Add insert --truncate option 651844316 | |
655052451 | https://github.com/simonw/sqlite-utils/pull/118#issuecomment-655052451 | https://api.github.com/repos/simonw/sqlite-utils/issues/118 | MDEyOklzc3VlQ29tbWVudDY1NTA1MjQ1MQ== | tsibley 79913 | 2020-07-07T18:45:23Z | 2020-07-07T18:45:23Z | CONTRIBUTOR | Ah, I see the problem. The truncate is inside a loop I didn't realize was there. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Add insert --truncate option 651844316 | |
655018966 | https://github.com/simonw/sqlite-utils/pull/118#issuecomment-655018966 | https://api.github.com/repos/simonw/sqlite-utils/issues/118 | MDEyOklzc3VlQ29tbWVudDY1NTAxODk2Ng== | tsibley 79913 | 2020-07-07T17:41:06Z | 2020-07-07T17:41:06Z | CONTRIBUTOR | Hmm, while tests pass, this may not work as intended on larger datasets. Looking into it. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Add insert --truncate option 651844316 | |
612727814 | https://github.com/simonw/sqlite-utils/issues/99#issuecomment-612727814 | https://api.github.com/repos/simonw/sqlite-utils/issues/99 | MDEyOklzc3VlQ29tbWVudDYxMjcyNzgxNA== | simonw 9599 | 2020-04-13T03:05:04Z | 2020-04-13T03:05:04Z | OWNER | Bit trick from an implementation point of view this, since we want to be able to handle input that is a generator - so we can't scan through the input to validate that every dictionary has the same exact keys without consuming the entire iterator. The alternative would be to raise an error the first time we spot a dictionary with keys that differ... but that's weird because we commit changes in batches, so we may end up only applying half of the changes before exiting with the error. On that basis, I'm going to leave this as-is and mark this as wontfix. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | .upsert_all() should maybe error if dictionaries passed to it do not have the same keys 598640234 | |
612727400 | https://github.com/simonw/sqlite-utils/issues/99#issuecomment-612727400 | https://api.github.com/repos/simonw/sqlite-utils/issues/99 | MDEyOklzc3VlQ29tbWVudDYxMjcyNzQwMA== | simonw 9599 | 2020-04-13T03:03:09Z | 2020-04-13T03:03:09Z | OWNER | I think I'm going to leave this as intended behaviour. Or maybe passing multiple dictionaries to `.upsert_all()` with different numbers of keys should raise an error? | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | .upsert_all() should maybe error if dictionaries passed to it do not have the same keys 598640234 | |
928790381 | https://github.com/simonw/sqlite-utils/issues/98#issuecomment-928790381 | https://api.github.com/repos/simonw/sqlite-utils/issues/98 | IC_kwDOCGYnMM43XDdt | patricktrainer 36834097 | 2021-09-28T04:38:44Z | 2021-09-28T04:38:44Z | NONE | Hi @simonw - wondering if you might be able to shed some light here. I've seemed to reproduce this issue. Here's the stacktrace: ``` ... db["potholes"].insert(pothole, pk='id', alter=True, replace=True) ... Traceback (most recent call last): File "<stdin>", line 3, in <module> File "/Users/patricktrainer/.pyenv/versions/3.9.0/lib/python3.9/site-packages/sqlite_utils/db.py", line 2481, in insert return self.insert_all( File "/Users/patricktrainer/.pyenv/versions/3.9.0/lib/python3.9/site-packages/sqlite_utils/db.py", line 2596, in insert_all self.insert_chunk( File "/Users/patricktrainer/.pyenv/versions/3.9.0/lib/python3.9/site-packages/sqlite_utils/db.py", line 2424, in insert_chunk row = list(self.rows_where("rowid = ?", [self.last_rowid]))[0] IndexError: list index out of range ``` Interesting enough, I found that omitting the `pk` param does not throw the error. Let me know how I can help out! | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Only set .last_rowid and .last_pk for single update/inserts, not for .insert_all()/.upsert_all() with multiple records 597671518 | |
612728047 | https://github.com/simonw/sqlite-utils/issues/98#issuecomment-612728047 | https://api.github.com/repos/simonw/sqlite-utils/issues/98 | MDEyOklzc3VlQ29tbWVudDYxMjcyODA0Nw== | simonw 9599 | 2020-04-13T03:06:10Z | 2020-04-13T03:06:10Z | OWNER | Implementation plan: `.insert_all()` and `.upsert_all()` should only set `.last_rowid` and `last_pk` if they were called with a single item. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Only set .last_rowid and .last_pk for single update/inserts, not for .insert_all()/.upsert_all() with multiple records 597671518 | |
612708274 | https://github.com/simonw/sqlite-utils/issues/98#issuecomment-612708274 | https://api.github.com/repos/simonw/sqlite-utils/issues/98 | MDEyOklzc3VlQ29tbWVudDYxMjcwODI3NA== | simonw 9599 | 2020-04-13T01:25:59Z | 2020-04-13T01:26:11Z | OWNER | In mucking around with `sqlite3` it looks like `result.lastrowid` is indeed populated for `UPDATE` - in this case with the last inserted rowid in the table. This differs from the documented behaviour I linked to above. ``` In [1]: import sqlite3 In [2]: c = sqlite3.connect(":memory:") In [3]: c Out[3]: <sqlite3.Connection at 0x103c4d490> In [4]: c.execute('create table foo (bar integer);') Out[4]: <sqlite3.Cursor at 0x103f760a0> In [5]: c.execute('insert into foo (bar) values (1)') Out[5]: <sqlite3.Cursor at 0x103f76650> In [6]: c.execute('select * from foo').fetchall() Out[6]: [(1,)] In [7]: c.execute('insert into foo (bar) values (1)') Out[7]: <sqlite3.Cursor at 0x103f766c0> In [8]: c.execute('select * from foo').fetchall() Out[8]: [(1,), (1,)] In [9]: c.execute('insert into foo (bar) values (1)').lastrowid Out[9]: 3 In [10]: c.execute('select * from foo').fetchall() Out[10]: [(1,), (1,), (1,)] In [11]: c.execute('select rowid, bar from foo').fetchall() Out[11]: [(1, 1), (2, 1), (3, 1)] In [12]: c.execute('insert into foo (bar) values (1)').lastrowid Out[12]: 4 In [13]: c.execute('select rowid, bar from foo').fetchall() Out[13]: [(1, 1), (2, 1), (3, 1), (4, 1)] In [14]: r = c.execute('update foo set bar =2 where rowid = 1') In [15]: r.lastrowid Out[15]: 4 In [16]: c.execute('select rowid, bar from foo').fetchall() Out[16]: [(1, 2), (2, 1), (3, 1), (4, 1)] In [17]: r = c.execute('select rowid, bar from foo') In… | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Only set .last_rowid and .last_pk for single update/inserts, not for .insert_all()/.upsert_all() with multiple records 597671518 | |
612707828 | https://github.com/simonw/sqlite-utils/issues/98#issuecomment-612707828 | https://api.github.com/repos/simonw/sqlite-utils/issues/98 | MDEyOklzc3VlQ29tbWVudDYxMjcwNzgyOA== | simonw 9599 | 2020-04-13T01:24:05Z | 2020-04-13T01:24:16Z | OWNER | Why do I even care about `lastrowid` here? I'm trying to ensure that after you insert or upsert a row you can use `table.last_pk` to start doing things like building additional foreign key relationships. So maybe it doesn't make sense to make `.last_pk` available _at all_ for cases where you called `.upsert_all()` or `.insert_all()` - it should just be populated for `.upsert()` and `.insert()`. The documentation doesn't say it should work for `.upsert_all()` - it's only documented for the single actions. https://github.com/simonw/sqlite-utils/blob/6161ebf4de44411b3f33feeacaf4501e803d1116/sqlite_utils/db.py#L1113-L1124 | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Only set .last_rowid and .last_pk for single update/inserts, not for .insert_all()/.upsert_all() with multiple records 597671518 | |
612707293 | https://github.com/simonw/sqlite-utils/issues/98#issuecomment-612707293 | https://api.github.com/repos/simonw/sqlite-utils/issues/98 | MDEyOklzc3VlQ29tbWVudDYxMjcwNzI5Mw== | simonw 9599 | 2020-04-13T01:21:22Z | 2020-04-13T01:21:22Z | OWNER | I have a hunch that the root of the problem here is that accessing `result.lastrowid` during my version of an `.upsert()` doesn't actually make sense: https://github.com/simonw/sqlite-utils/blob/6161ebf4de44411b3f33feeacaf4501e803d1116/sqlite_utils/db.py#L1102-L1113 In the bug I'm seeing (which I still haven't reduced to a reproducible test) the debugger shows me this at that point: ``` (Pdb) query 'UPDATE [files] SET [createdAt] = ?, [ext] = ?, [updatedAt] = ?, [uri] = ?, [uriType] = ? WHERE [project] = ? AND [name] = ?' (Pdb) params ['2020-03-04T04:04:40.152000+00:00', 'csv', '2020-03-04T04:04:40.152000+00:00', 'https://storage.googleapis.com/bln_prod/...', 'download', 'UHJvamVjdDo4MTgyMjU2Ny01ZjI0LTQxM2ItYWZmNi05NTlmNGY3MjExMjI=', 'loans_to_documentation.csv'] (Pdb) result.lastrowid 100 ``` But here's the weird thing... there's no row in the table with a rowid of 100! ``` (Pdb) [r['rowid'] for r in self.db.execute_returning_dicts('select rowid, * from files')] [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99] ``` So what the heck is going on? The last SQL statement I executed here was an `UPDATE`. The `lastrowid` docs say: https://kite.com/python/docs/sqlite3.Cursor.lastrowid > This read-only attribute provides the rowid of the last modified row. It is only set if you issued a INSERT statement using the execute() method. For operations other than INSERT or when executemany() is called, lastrowid is set to None. So where did that `100` come from? It should be `None`! | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Only set .last_rowid and .last_pk for single update/inserts, not for .insert_all()/.upsert_all() with multiple records 597671518 | |
612258687 | https://github.com/simonw/sqlite-utils/issues/98#issuecomment-612258687 | https://api.github.com/repos/simonw/sqlite-utils/issues/98 | MDEyOklzc3VlQ29tbWVudDYxMjI1ODY4Nw== | simonw 9599 | 2020-04-10T23:08:48Z | 2020-04-10T23:08:48Z | OWNER | I need a test that reproduces this. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Only set .last_rowid and .last_pk for single update/inserts, not for .insert_all()/.upsert_all() with multiple records 597671518 | |
612173156 | https://github.com/simonw/sqlite-utils/issues/98#issuecomment-612173156 | https://api.github.com/repos/simonw/sqlite-utils/issues/98 | MDEyOklzc3VlQ29tbWVudDYxMjE3MzE1Ng== | simonw 9599 | 2020-04-10T19:03:32Z | 2020-04-10T23:08:28Z | OWNER | Investigate this traceback: ``` Traceback (most recent call last): File "fetch_projects.py", line 60, in <module> fetch_projects(db, token) File "fetch_projects.py", line 41, in fetch_projects db["projects"].upsert(project, pk="id") File "/Users/simonw/.local/share/virtualenvs/big-local-datasette-2jT6nJCT/lib/python3.7/site-packages/sqlite_utils/db.py", line 1139, in upsert conversions=conversions, File "/Users/simonw/.local/share/virtualenvs/big-local-datasette-2jT6nJCT/lib/python3.7/site-packages/sqlite_utils/db.py", line 1168, in upsert_all upsert=True, File "/Users/simonw/.local/share/virtualenvs/big-local-datasette-2jT6nJCT/lib/python3.7/site-packages/sqlite_utils/db.py", line 1107, in insert_all row = list(self.rows_where("rowid = ?", [self.last_rowid]))[0] IndexError: list index out of range ``` | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Only set .last_rowid and .last_pk for single update/inserts, not for .insert_all()/.upsert_all() with multiple records 597671518 | |
614073859 | https://github.com/simonw/sqlite-utils/issues/97#issuecomment-614073859 | https://api.github.com/repos/simonw/sqlite-utils/issues/97 | MDEyOklzc3VlQ29tbWVudDYxNDA3Mzg1OQ== | betatim 1448859 | 2020-04-15T14:29:30Z | 2020-04-15T14:29:30Z | NONE | Woah! Thanks a lot. Next time I will add a more obvious/explicit "if you like this idea let me know I'd love to work on it to get my feet wet here" :D | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Adding a "recreate" flag to the `Database` constructor 593751293 | |
612738311 | https://github.com/simonw/sqlite-utils/issues/97#issuecomment-612738311 | https://api.github.com/repos/simonw/sqlite-utils/issues/97 | MDEyOklzc3VlQ29tbWVudDYxMjczODMxMQ== | simonw 9599 | 2020-04-13T03:55:11Z | 2020-04-13T03:55:11Z | OWNER | Shipped in 2.5 - documentation is here: https://sqlite-utils.readthedocs.io/en/stable/python-api.html#connecting-to-or-creating-a-database | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Adding a "recreate" flag to the `Database` constructor 593751293 | |
612732453 | https://github.com/simonw/sqlite-utils/issues/97#issuecomment-612732453 | https://api.github.com/repos/simonw/sqlite-utils/issues/97 | MDEyOklzc3VlQ29tbWVudDYxMjczMjQ1Mw== | simonw 9599 | 2020-04-13T03:26:46Z | 2020-04-13T03:26:46Z | OWNER | I wonder if it should delete an recreate the file or if it would be safer to drop every table instead? Dropping tables gets messy: then you need to drop triggers and views, and you need to run `vacuum` to clean up the space. My worry with deleting and recreating the file is that it could trigger errors in other processes that are currently attached to that database file. But... if you know that's going to be likely, maybe you shouldn't use the `recreate=True` feature? | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Adding a "recreate" flag to the `Database` constructor 593751293 | |
612732129 | https://github.com/simonw/sqlite-utils/issues/97#issuecomment-612732129 | https://api.github.com/repos/simonw/sqlite-utils/issues/97 | MDEyOklzc3VlQ29tbWVudDYxMjczMjEyOQ== | simonw 9599 | 2020-04-13T03:25:29Z | 2020-04-13T03:25:29Z | OWNER | Interesting thought. I've run into this myself a lot - many of my scripts intend to create the database from scratch, so I end up running `!rm /tmp/blah.db` in Jupyter and occasionally getting errors if the file doesn't exist. I think adding `recreate=True` could make sense. It could throw an error if you attempt to use it after passing in something other than a path to a file on disk. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Adding a "recreate" flag to the `Database` constructor 593751293 | |
599247833 | https://github.com/simonw/sqlite-utils/issues/92#issuecomment-599247833 | https://api.github.com/repos/simonw/sqlite-utils/issues/92 | MDEyOklzc3VlQ29tbWVudDU5OTI0NzgzMw== | simonw 9599 | 2020-03-15T18:37:28Z | 2020-03-15T18:37:43Z | OWNER | Released in 2.4.2. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | .columns_dict doesn't work for all possible column types 581339961 | |
599128891 | https://github.com/simonw/sqlite-utils/issues/92#issuecomment-599128891 | https://api.github.com/repos/simonw/sqlite-utils/issues/92 | MDEyOklzc3VlQ29tbWVudDU5OTEyODg5MQ== | simonw 9599 | 2020-03-14T20:03:45Z | 2020-03-14T20:03:45Z | OWNER | I'm going to keep treating them as `str`. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | .columns_dict doesn't work for all possible column types 581339961 | |
599127453 | https://github.com/simonw/sqlite-utils/issues/92#issuecomment-599127453 | https://api.github.com/repos/simonw/sqlite-utils/issues/92 | MDEyOklzc3VlQ29tbWVudDU5OTEyNzQ1Mw== | simonw 9599 | 2020-03-14T19:50:08Z | 2020-03-14T19:50:08Z | OWNER | > If the declared type for a column contains the string "BLOB" or if no type is specified then the column has affinity BLOB I currently treat those as `str` - it sounds like I should treat them as `bytes`: https://github.com/simonw/sqlite-utils/blob/43f1c6ab4e3a6b76531fb6f5447adb83d26f3971/sqlite_utils/db.py#L68-L69 | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | .columns_dict doesn't work for all possible column types 581339961 | |
599127197 | https://github.com/simonw/sqlite-utils/issues/92#issuecomment-599127197 | https://api.github.com/repos/simonw/sqlite-utils/issues/92 | MDEyOklzc3VlQ29tbWVudDU5OTEyNzE5Nw== | simonw 9599 | 2020-03-14T19:48:06Z | 2020-03-14T19:48:06Z | OWNER | Actually it looks like I should implement the exact rules described in https://www.sqlite.org/datatype3.html#determination_of_column_affinity > The affinity of a column is determined by the declared type of the column, according to the following rules in the order shown: > > 1. If the declared type contains the string "INT" then it is assigned INTEGER affinity. > 2. If the declared type of the column contains any of the strings "CHAR", "CLOB", or "TEXT" then that column has TEXT affinity. Notice that the type VARCHAR contains the string "CHAR" and is thus assigned TEXT affinity. > 3. If the declared type for a column contains the string "BLOB" or if no type is specified then the column has affinity BLOB. > 4. If the declared type for a column contains any of the strings "REAL", "FLOA", or "DOUB" then the column has REAL affinity. > 5. Otherwise, the affinity is NUMERIC. > > Note that the order of the rules for determining column affinity is important. A column whose declared type is "CHARINT" will match both rules 1 and 2 but the first rule takes precedence and so the column affinity will be INTEGER. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | .columns_dict doesn't work for all possible column types 581339961 | |
599126831 | https://github.com/simonw/sqlite-utils/issues/92#issuecomment-599126831 | https://api.github.com/repos/simonw/sqlite-utils/issues/92 | MDEyOklzc3VlQ29tbWVudDU5OTEyNjgzMQ== | simonw 9599 | 2020-03-14T19:45:28Z | 2020-03-14T19:45:28Z | OWNER | Turns out there are a TON of valid column definitions that aren't being considered yet - https://www.sqlite.org/datatype3.html#affinity_name_examples - stuff like `VARYING CHARACTER(255)` and `DECIMAL(10,5)`. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | .columns_dict doesn't work for all possible column types 581339961 | |
599125557 | https://github.com/simonw/sqlite-utils/issues/92#issuecomment-599125557 | https://api.github.com/repos/simonw/sqlite-utils/issues/92 | MDEyOklzc3VlQ29tbWVudDU5OTEyNTU1Nw== | simonw 9599 | 2020-03-14T19:35:29Z | 2020-03-14T19:35:29Z | OWNER | Fixing that would technically constitute a breaking change for library consumers, so it should be a major version release. I'm not inclined to release `3.0` just for this one issue, so I'm going to hold back on fixing that and address the smaller issue in this bug as a dot release instead for the moment. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | .columns_dict doesn't work for all possible column types 581339961 | |
599125455 | https://github.com/simonw/sqlite-utils/issues/92#issuecomment-599125455 | https://api.github.com/repos/simonw/sqlite-utils/issues/92 | MDEyOklzc3VlQ29tbWVudDU5OTEyNTQ1NQ== | simonw 9599 | 2020-03-14T19:34:35Z | 2020-03-14T19:34:35Z | OWNER | From https://www.sqlite.org/datatype3.html it looks like `FLOAT` is a supported keyword for creating tables but `REAL` is the correct keyword. So actually `sqlite-utils` gets this wrong, because when we create a table we turn Python `float` values into a `FLOAT` column. Looks like the correct behaviour would be to turn them into a `REAL` column. https://github.com/simonw/sqlite-utils/blob/43f1c6ab4e3a6b76531fb6f5447adb83d26f3971/sqlite_utils/db.py#L28-L48 | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | .columns_dict doesn't work for all possible column types 581339961 | |
723350956 | https://github.com/simonw/sqlite-utils/issues/91#issuecomment-723350956 | https://api.github.com/repos/simonw/sqlite-utils/issues/91 | MDEyOklzc3VlQ29tbWVudDcyMzM1MDk1Ng== | simonw 9599 | 2020-11-06T23:53:25Z | 2020-11-06T23:53:25Z | OWNER | This is now possible, for both FTS4 and FTS5 - see #197. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Enable ordering FTS results by rank 577302229 | |
710460242 | https://github.com/simonw/sqlite-utils/issues/89#issuecomment-710460242 | https://api.github.com/repos/simonw/sqlite-utils/issues/89 | MDEyOklzc3VlQ29tbWVudDcxMDQ2MDI0Mg== | simonw 9599 | 2020-10-16T19:17:27Z | 2020-10-16T19:17:50Z | OWNER | I came up with potential syntax for that here: https://github.com/simonw/sqlite-utils/issues/49#issuecomment-710393550 - based on how `table.extract(...)` works: ```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} | Ability to customize columns used by extracts= feature 573578548 | |
615515867 | https://github.com/simonw/sqlite-utils/issues/89#issuecomment-615515867 | https://api.github.com/repos/simonw/sqlite-utils/issues/89 | MDEyOklzc3VlQ29tbWVudDYxNTUxNTg2Nw== | simonw 9599 | 2020-04-18T00:00:41Z | 2020-04-18T00:00:41Z | OWNER | Yes pleas, I'd love to see that pull request! | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Ability to customize columns used by extracts= feature 573578548 | |
593122605 | https://github.com/simonw/sqlite-utils/issues/89#issuecomment-593122605 | https://api.github.com/repos/simonw/sqlite-utils/issues/89 | MDEyOklzc3VlQ29tbWVudDU5MzEyMjYwNQ== | chrishas35 35075 | 2020-03-01T17:33:11Z | 2020-03-01T17:33:11Z | NONE | If you're happy with the proposed implementation, I have code & tests written that I'll get ready for a PR. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Ability to customize columns used by extracts= feature 573578548 | |
591769759 | https://github.com/simonw/sqlite-utils/issues/88#issuecomment-591769759 | https://api.github.com/repos/simonw/sqlite-utils/issues/88 | MDEyOklzc3VlQ29tbWVudDU5MTc2OTc1OQ== | simonw 9599 | 2020-02-27T04:08:29Z | 2020-02-27T04:08:29Z | OWNER | I think the method should be called `table.disable_fts()` - the opposite of `table.enable_fts(...)`. There should be a `sqlite-utils disable-fts database.db tablename` command to match it. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | table.disable_fts() method and "sqlite-utils disable-fts ..." command 571805300 | |
591769373 | https://github.com/simonw/sqlite-utils/issues/88#issuecomment-591769373 | https://api.github.com/repos/simonw/sqlite-utils/issues/88 | MDEyOklzc3VlQ29tbWVudDU5MTc2OTM3Mw== | simonw 9599 | 2020-02-27T04:06:47Z | 2020-02-27T04:06:47Z | OWNER | Looks like safest option is to loop through those trigger names and run `DROP TRIGGER IF EXISTS foo` on each one. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | table.disable_fts() method and "sqlite-utils disable-fts ..." command 571805300 | |
591769171 | https://github.com/simonw/sqlite-utils/issues/88#issuecomment-591769171 | https://api.github.com/repos/simonw/sqlite-utils/issues/88 | MDEyOklzc3VlQ29tbWVudDU5MTc2OTE3MQ== | simonw 9599 | 2020-02-27T04:05:58Z | 2020-02-27T04:26:31Z | OWNER | Strange - https://www.sqlite.org/lang_droptrigger.html says "Note that triggers are automatically dropped when the associated table is dropped" but that doesn't seem to be true in my experimenting. UPDATE: no that makes sense - the triggers are on `resources` which still exists, it was `resources_fts` that was dropped. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | table.disable_fts() method and "sqlite-utils disable-fts ..." command 571805300 | |
591769046 | https://github.com/simonw/sqlite-utils/issues/88#issuecomment-591769046 | https://api.github.com/repos/simonw/sqlite-utils/issues/88 | MDEyOklzc3VlQ29tbWVudDU5MTc2OTA0Ng== | simonw 9599 | 2020-02-27T04:05:15Z | 2020-02-27T04:05:15Z | OWNER | I can reliably get the list of triggers to delete from `select name from sqlite_master where type = 'trigger' and tbl_name = 'resources';` | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | table.disable_fts() method and "sqlite-utils disable-fts ..." command 571805300 | |
591768604 | https://github.com/simonw/sqlite-utils/issues/88#issuecomment-591768604 | https://api.github.com/repos/simonw/sqlite-utils/issues/88 | MDEyOklzc3VlQ29tbWVudDU5MTc2ODYwNA== | simonw 9599 | 2020-02-27T04:03:03Z | 2020-02-27T04:03:03Z | OWNER | `drop table resources_fts` drops the FTS table and the other ones that it created (resources_fts_data, resources_fts_idx, resources_fts_docsize, resources_fts_config) - but keeps the triggers. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | table.disable_fts() method and "sqlite-utils disable-fts ..." command 571805300 | |
586661276 | https://github.com/simonw/sqlite-utils/issues/87#issuecomment-586661276 | https://api.github.com/repos/simonw/sqlite-utils/issues/87 | MDEyOklzc3VlQ29tbWVudDU4NjY2MTI3Ng== | simonw 9599 | 2020-02-16T02:20:14Z | 2020-02-16T02:20:14Z | OWNER | `OrderedDict` is actually a subclass of `dict` - so a smart fix would be for this logic to check and see if the type `t` is a subclass of one of `list`, `tuple` or `dict`. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Should detect collections.OrderedDict as a regular dictionary 565837965 | |
586661250 | https://github.com/simonw/sqlite-utils/issues/87#issuecomment-586661250 | https://api.github.com/repos/simonw/sqlite-utils/issues/87 | MDEyOklzc3VlQ29tbWVudDU4NjY2MTI1MA== | simonw 9599 | 2020-02-16T02:19:33Z | 2020-02-16T02:19:33Z | OWNER | Here's the code: https://github.com/simonw/sqlite-utils/blob/5e0000609f9be6efafea1b96f610988eb18d6d89/sqlite_utils/utils.py#L18-L24 | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Should detect collections.OrderedDict as a regular dictionary 565837965 | |
591770623 | https://github.com/simonw/sqlite-utils/issues/86#issuecomment-591770623 | https://api.github.com/repos/simonw/sqlite-utils/issues/86 | MDEyOklzc3VlQ29tbWVudDU5MTc3MDYyMw== | simonw 9599 | 2020-02-27T04:12:39Z | 2020-02-27T04:12:39Z | OWNER | I pushed a branch with my experiment in it, but I'm going to fix this by throwing an error on `[` or `]` in a column name instead - I won't implement the changes from that branch. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Problem with square bracket in CSV column name 564579430 | |
586729798 | https://github.com/simonw/sqlite-utils/issues/86#issuecomment-586729798 | https://api.github.com/repos/simonw/sqlite-utils/issues/86 | MDEyOklzc3VlQ29tbWVudDU4NjcyOTc5OA== | simonw 9599 | 2020-02-16T17:11:02Z | 2020-02-16T17:11:02Z | OWNER | I filed a bug in the Python issue tracker here: https://bugs.python.org/issue39652 | {"total_count": 2, "+1": 2, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Problem with square bracket in CSV column name 564579430 | |
586683572 | https://github.com/simonw/sqlite-utils/issues/86#issuecomment-586683572 | https://api.github.com/repos/simonw/sqlite-utils/issues/86 | MDEyOklzc3VlQ29tbWVudDU4NjY4MzU3Mg== | foscoj 8149512 | 2020-02-16T09:03:54Z | 2020-02-16T09:03:54Z | NONE | Probably the best option to just throw the error. Is there any active dev chan where we could post the issue to python sqlite3? | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Problem with square bracket in CSV column name 564579430 | |
586676856 | https://github.com/simonw/sqlite-utils/issues/86#issuecomment-586676856 | https://api.github.com/repos/simonw/sqlite-utils/issues/86 | MDEyOklzc3VlQ29tbWVudDU4NjY3Njg1Ng== | simonw 9599 | 2020-02-16T07:20:34Z | 2020-02-16T07:20:34Z | OWNER | I'm not sure what to do about this one. I can't fix it: this bug in Python's `sqlite3` module means that even if I write a database out with column names that include `[]` I won't be able to read them back again. So... I could do one of the following: - Throw an error if a column name includes those characters. That's my preferred option I think. - Automatically replace `[` in column names with `(` and `]` with `)` - Do the automatic replacement but show a user-visible warning when I do it - Throw an error, but give the user an option to run with e.g. `--fix-column-names` which applies that automatic fix. Since this is likely to be an incredibly rare edge-case I think I'd rather minimize the amount of code that deals with it, so my preferred option is to just throw that error and stop. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Problem with square bracket in CSV column name 564579430 | |
586676640 | https://github.com/simonw/sqlite-utils/issues/86#issuecomment-586676640 | https://api.github.com/repos/simonw/sqlite-utils/issues/86 | MDEyOklzc3VlQ29tbWVudDU4NjY3NjY0MA== | simonw 9599 | 2020-02-16T07:16:31Z | 2020-02-16T07:16:31Z | OWNER | There's something weird about this. I created a test database file like so: ``` sqlite3 /tmp/demo.db <<EOF BEGIN TRANSACTION; CREATE TABLE "data" ( "MTU (CET)" TEXT, "Day-ahead Price [EUR/MWh]" TEXT ); INSERT INTO "data" VALUES('01.01.2016 00:00 - 01.01.2016 01:00','23.86'); COMMIT; EOF ``` Then confirmed that it works as expected in SQLite: ``` $ sqlite3 /tmp/demo.db SQLite version 3.24.0 2018-06-04 14:10:15 Enter ".help" for usage hints. sqlite> .schema CREATE TABLE IF NOT EXISTS "data" ( "MTU (CET)" TEXT, "Day-ahead Price [EUR/MWh]" TEXT ); sqlite> .headers on sqlite> select * from data; MTU (CET)|Day-ahead Price [EUR/MWh] 01.01.2016 00:00 - 01.01.2016 01:00|23.86 sqlite> ``` BUT... if I open the same database in Python, something weird happens: ``` In [1]: import sqlite3 In [2]: conn = sqlite3.connect("/tmp/demo.db") In [3]: cursor = conn.cursor() In [4]: cursor.execute("select * from data") Out[4]: <sqlite3.Cursor at 0x10c70a0a0> In [5]: cursor.fetchall() Out[5]: [('01.01.2016 00:00 - 01.01.2016 01:00', '23.86')] In [6]: cursor.description Out[6]: (('MTU (CET)', None, None, None, None, None, None), ('Day-ahead Price', None, None, None, None, None, None)) In [7]: conn.row_factory = sqlite3.Row In [8]: cursor = conn.cursor() … | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Problem with square bracket in CSV column name 564579430 | |
586662404 | https://github.com/simonw/sqlite-utils/issues/86#issuecomment-586662404 | https://api.github.com/repos/simonw/sqlite-utils/issues/86 | MDEyOklzc3VlQ29tbWVudDU4NjY2MjQwNA== | simonw 9599 | 2020-02-16T02:43:12Z | 2020-02-16T02:43:12Z | OWNER | https://stackoverflow.com/a/22694438 looks like the answer: > When using square brackets, it is not possible to have these characters in the identifier. > > When using double quotes, you can escape them in the name by doubling them: > > `CREATE TABLE "hello ""world"""(key INTEGER PRIMARY KEY);` | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Problem with square bracket in CSV column name 564579430 | |
586661934 | https://github.com/simonw/sqlite-utils/issues/86#issuecomment-586661934 | https://api.github.com/repos/simonw/sqlite-utils/issues/86 | MDEyOklzc3VlQ29tbWVudDU4NjY2MTkzNA== | simonw 9599 | 2020-02-16T02:33:07Z | 2020-02-16T02:33:07Z | OWNER | Thanks for the example file - looks like it can be trimmed down to just these two lines to replicate the bug: ```csv "MTU (CET)","Day-ahead Price [EUR/MWh]" "01.01.2016 00:00 - 01.01.2016 01:00","23.86" ``` | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Problem with square bracket in CSV column name 564579430 | |
584426938 | https://github.com/simonw/sqlite-utils/issues/85#issuecomment-584426938 | https://api.github.com/repos/simonw/sqlite-utils/issues/85 | MDEyOklzc3VlQ29tbWVudDU4NDQyNjkzOA== | simonw 9599 | 2020-02-11T00:35:09Z | 2020-02-11T00:35:09Z | OWNER | Here's why: https://github.com/simonw/sqlite-utils/blob/0c2451e0690c5f4e6463a2f339b0a280e30ed806/sqlite_utils/db.py#L627-L636 | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Create index doesn't work for columns containing spaces 562911863 | |
583789015 | https://github.com/simonw/sqlite-utils/issues/83#issuecomment-583789015 | https://api.github.com/repos/simonw/sqlite-utils/issues/83 | MDEyOklzc3VlQ29tbWVudDU4Mzc4OTAxNQ== | simonw 9599 | 2020-02-08T23:58:35Z | 2020-02-08T23:58:35Z | OWNER | Shipped as 2.3 | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Make db["table"].exists a documented API 559374410 | |
591771532 | https://github.com/simonw/sqlite-utils/issues/82#issuecomment-591771532 | https://api.github.com/repos/simonw/sqlite-utils/issues/82 | MDEyOklzc3VlQ29tbWVudDU5MTc3MTUzMg== | simonw 9599 | 2020-02-27T04:16:30Z | 2020-02-27T04:16:30Z | OWNER | Closing as can't reproduce. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Tutorial command no longer works 559197745 | |
581652388 | https://github.com/simonw/sqlite-utils/issues/82#issuecomment-581652388 | https://api.github.com/repos/simonw/sqlite-utils/issues/82 | MDEyOklzc3VlQ29tbWVudDU4MTY1MjM4OA== | simonw 9599 | 2020-02-03T22:35:44Z | 2020-02-03T22:35:44Z | OWNER | I can't replicate this problem: ``` /tmp $ sqlite-utils --version sqlite-utils, version 2.2 /tmp $ curl "https://data.nasa.gov/resource/y77d-th95.json" | sqlite-utils insert meteorites.db meteorites - --pk=id % Total % Received % Xferd Average Speed Time Time Time Current Dload Upload Total Spent Left Speed 100 240k 0 240k 0 0 185k 0 --:--:-- 0:00:01 --:--:-- 185k ``` Could you run `sqlite-utils --version` and tell me what you get? | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Tutorial command no longer works 559197745 | |
581651409 | https://github.com/simonw/sqlite-utils/issues/82#issuecomment-581651409 | https://api.github.com/repos/simonw/sqlite-utils/issues/82 | MDEyOklzc3VlQ29tbWVudDU4MTY1MTQwOQ== | simonw 9599 | 2020-02-03T22:32:41Z | 2020-02-03T22:32:41Z | OWNER | This should work - the data should be chunked automatically. It looks like this is a bug. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Tutorial command no longer works 559197745 | |
581071434 | https://github.com/simonw/sqlite-utils/issues/81#issuecomment-581071434 | https://api.github.com/repos/simonw/sqlite-utils/issues/81 | MDEyOklzc3VlQ29tbWVudDU4MTA3MTQzNA== | simonw 9599 | 2020-02-01T21:32:34Z | 2020-02-01T21:32:34Z | OWNER | While I'm at it I think I'll rename it to `suggest_column_types` - it's not really detecting them since the input is just a list of dictionaries. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Remove .detect_column_types() from table, make it a documented API 558600274 | |
581071235 | https://github.com/simonw/sqlite-utils/issues/81#issuecomment-581071235 | https://api.github.com/repos/simonw/sqlite-utils/issues/81 | MDEyOklzc3VlQ29tbWVudDU4MTA3MTIzNQ== | simonw 9599 | 2020-02-01T21:30:09Z | 2020-02-01T21:30:09Z | OWNER | Actually I'll put it in the `utils.py` module. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Remove .detect_column_types() from table, make it a documented API 558600274 |
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]);