issues
565 rows where repo = 140912432 sorted by pull_request
This data as json, CSV (advanced)
Suggested facets: state, milestone, comments, author_association, type, draft, created_at (date), updated_at (date), closed_at (date)
id | node_id | number | title | user | state | locked | assignee | milestone | comments | created_at | updated_at | closed_at | author_association | pull_request ▼ | body | repo | type | active_lock_reason | performed_via_github_app | reactions | draft | state_reason |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
349850687 | MDU6SXNzdWUzNDk4NTA2ODc= | 2 | Mechanism for adding foreign keys to an existing table | simonw 9599 | closed | 0 | 1 | 2018-08-12T22:50:56Z | 2019-02-24T21:34:41Z | 2019-02-24T21:34:41Z | OWNER | SQLite does not have ALTER TABLE support for adding new foreign keys... but it turns out it's possible to make these changes without having to duplicate the entire table by carefully running `UPDATE sqlite_master SET sql=... WHERE type='table' AND name='X';` Here's how Django does it: https://github.com/django/django/blob/d3449faaa915a08c275b35de01e66a7ef6bdb2dc/django/db/backends/sqlite3/schema.py#L103-L125 And here's the official documentation about this: https://sqlite.org/lang_altertable.html#otheralter (scroll to the very bottom of the page) | sqlite-utils 140912432 | issue | {"url": "https://api.github.com/repos/simonw/sqlite-utils/issues/2/reactions", "total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | completed | ||||||
351845423 | MDU6SXNzdWUzNTE4NDU0MjM= | 3 | Experiment with contentless FTS tables | simonw 9599 | closed | 0 | 1 | 2018-08-18T19:31:01Z | 2019-07-22T20:58:55Z | 2019-07-22T20:58:55Z | OWNER | Could greatly reduce size of resulting database for large datasets: http://cocoamine.net/blog/2015/09/07/contentless-fts4-for-large-immutable-documents/ | sqlite-utils 140912432 | issue | {"url": "https://api.github.com/repos/simonw/sqlite-utils/issues/3/reactions", "total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | completed | ||||||
403624090 | MDU6SXNzdWU0MDM2MjQwOTA= | 6 | "sqlite-utils insert" should support newline-delimited JSON | simonw 9599 | closed | 0 | 1 | 2019-01-28T02:00:02Z | 2019-01-28T02:17:45Z | 2019-01-28T02:17:45Z | OWNER | We can already export newline delimited JSON. We should learn to import it as well. The neat thing about importing it is that you can import GBs of data without having to read the whole lot into memory in order to decode the wrapping JSON array. Datasette can export it now: https://github.com/simonw/datasette/issues/405 Demo: https://latest.datasette.io/fixtures/facetable.json?_shape=array&_nl=on It should be possible to do this: $ curl "https://latest.datasette.io/fixtures/facetable.json?_shape=array&_nl=on" \ | sqlite-utils insert data.db facetable - --nl | sqlite-utils 140912432 | issue | {"url": "https://api.github.com/repos/simonw/sqlite-utils/issues/6/reactions", "total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | completed | ||||||
403625674 | MDU6SXNzdWU0MDM2MjU2NzQ= | 7 | .insert_all() should accept a generator and process it efficiently | simonw 9599 | closed | 0 | 3 | 2019-01-28T02:11:58Z | 2019-01-28T06:26:53Z | 2019-01-28T06:26:53Z | OWNER | Right now you have to load every record into memory before passing the list to `.insert_all()` and friends. If you want to process millions of rows, this is inefficient. Python has generators - we should use them! The only catch here is that part of the magic of `sqlite-utils` is that it guesses the column types and creates the table for you. This code will need to be updated to notice if the table needs creating and, if it does, create it using the first X (where x=1,000 but can be customized) records. If a record outside of those first 1,000 has a rogue column, we can crash with an error. This will free us up to make the `--nl` option added in #6 much more efficient. | sqlite-utils 140912432 | issue | {"url": "https://api.github.com/repos/simonw/sqlite-utils/issues/7/reactions", "total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | completed | ||||||
403922644 | MDU6SXNzdWU0MDM5MjI2NDQ= | 8 | Problems handling column names containing spaces or - | psychemedia 82988 | closed | 0 | 3 | 2019-01-28T17:23:28Z | 2019-04-14T15:29:33Z | 2019-02-23T21:09:03Z | NONE | Irrrespective of whether using column names containing a space or - character is good practice, SQLite does allow it, but `sqlite-utils` throws an error in the following cases: ```python from sqlite_utils import Database dbname = 'test.db' DB = Database(sqlite3.connect(dbname)) import pandas as pd df = pd.DataFrame({'col1':range(3), 'col2':range(3)}) #Convert pandas dataframe to appropriate list/dict format DB['test1'].insert_all( df.to_dict(orient='records') ) #Works fine ``` However: ```python df = pd.DataFrame({'col 1':range(3), 'col2':range(3)}) DB['test1'].insert_all(df.to_dict(orient='records')) ``` throws: ``` --------------------------------------------------------------------------- OperationalError Traceback (most recent call last) <ipython-input-27-070b758f4f92> in <module>() 1 import pandas as pd 2 df = pd.DataFrame({'col 1':range(3), 'col2':range(3)}) ----> 3 DB['test1'].insert_all(df.to_dict(orient='records')) /usr/local/lib/python3.7/site-packages/sqlite_utils/db.py in insert_all(self, records, pk, foreign_keys, upsert, batch_size, column_order) 327 jsonify_if_needed(record.get(key, None)) for key in all_columns 328 ) --> 329 result = self.db.conn.execute(sql, values) 330 self.db.conn.commit() 331 self.last_id = result.lastrowid OperationalError: near "1": syntax error ``` and: ```python df = pd.DataFrame({'col-1':range(3), 'col2':range(3)}) DB['test1'].upsert_all(df.to_dict(orient='records')) ``` results in: ``` --------------------------------------------------------------------------- OperationalError Traceback (most recent call last) <ipython-input-28-654523549d20> in <module>() 1 import pandas as pd 2 df = pd.DataFrame({'col-1':range(3), 'col2':range(3)}) ----> 3 DB['test1'].insert_all(df.to_dict(orient='records')) /usr/local/lib/python3.7/site-packages/sqlite_… | sqlite-utils 140912432 | issue | {"url": "https://api.github.com/repos/simonw/sqlite-utils/issues/8/reactions", "total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | completed | ||||||
411066700 | MDU6SXNzdWU0MTEwNjY3MDA= | 10 | Error in upsert if column named 'order' | psychemedia 82988 | closed | 0 | 1 | 2019-02-16T12:05:18Z | 2019-02-24T16:55:38Z | 2019-02-24T16:55:37Z | NONE | The following works fine: ``` connX = sqlite3.connect('DELME.db', timeout=10) dfX=pd.DataFrame({'col1':range(3),'col2':range(3)}) DBX = Database(connX) DBX['test'].upsert_all(dfX.to_dict(orient='records')) ``` But if a column is named `order`: ``` connX = sqlite3.connect('DELME.db', timeout=10) dfX=pd.DataFrame({'order':range(3),'col2':range(3)}) DBX = Database(connX) DBX['test'].upsert_all(dfX.to_dict(orient='records')) ``` it throws an error: ``` --------------------------------------------------------------------------- OperationalError Traceback (most recent call last) <ipython-input-130-7dba33cd806c> in <module> 3 dfX=pd.DataFrame({'order':range(3),'col2':range(3)}) 4 DBX = Database(connX) ----> 5 DBX['test'].upsert_all(dfX.to_dict(orient='records')) /usr/local/lib/python3.7/site-packages/sqlite_utils/db.py in upsert_all(self, records, pk, foreign_keys, column_order) 347 foreign_keys=foreign_keys, 348 upsert=True, --> 349 column_order=column_order, 350 ) 351 /usr/local/lib/python3.7/site-packages/sqlite_utils/db.py in insert_all(self, records, pk, foreign_keys, upsert, batch_size, column_order) 327 jsonify_if_needed(record.get(key, None)) for key in all_columns 328 ) --> 329 result = self.db.conn.execute(sql, values) 330 self.db.conn.commit() 331 self.last_id = result.lastrowid OperationalError: near "order": syntax error ``` | sqlite-utils 140912432 | issue | {"url": "https://api.github.com/repos/simonw/sqlite-utils/issues/10/reactions", "total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | completed | ||||||
413740684 | MDU6SXNzdWU0MTM3NDA2ODQ= | 11 | Detect numpy types when creating tables | simonw 9599 | closed | 0 | 2 | 2019-02-23T21:09:35Z | 2019-02-24T04:02:20Z | 2019-02-24T04:02:20Z | OWNER | Inspired by #8 | sqlite-utils 140912432 | issue | {"url": "https://api.github.com/repos/simonw/sqlite-utils/issues/11/reactions", "total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | completed | ||||||
413779210 | MDU6SXNzdWU0MTM3NzkyMTA= | 13 | Ability to automatically create IDs from content hash of row | simonw 9599 | closed | 0 | 1 | 2019-02-24T04:07:08Z | 2019-02-24T04:36:48Z | 2019-02-24T04:36:48Z | OWNER | Sometimes when you are importing data the underlying source provides records without IDs that can be uniquely identified by their contents. A utility mechanism for calculating a sha1 hash of the contents and using that as a unique ID would be useful. | sqlite-utils 140912432 | issue | {"url": "https://api.github.com/repos/simonw/sqlite-utils/issues/13/reactions", "total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | completed | ||||||
413842611 | MDU6SXNzdWU0MTM4NDI2MTE= | 14 | Utilities for adding indexes | simonw 9599 | closed | 0 | 3 | 2019-02-24T16:57:28Z | 2019-02-24T19:11:28Z | 2019-02-24T19:11:28Z | OWNER | Both in the Python API and the CLI tool. For the CLI tool this should work: $ sqlite-utils create-index mydb.db mytable col1 col2 This will create a compound index across col1 and col2. The name of the index will be automatically chosen unless you use the `--name=...` option. Support a `--unique` option too. | sqlite-utils 140912432 | issue | {"url": "https://api.github.com/repos/simonw/sqlite-utils/issues/14/reactions", "total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | completed | ||||||
413857257 | MDU6SXNzdWU0MTM4NTcyNTc= | 15 | Ability to add columns to tables | simonw 9599 | closed | 0 | 0 | 2019-02-24T19:20:51Z | 2019-02-24T20:04:40Z | 2019-02-24T20:04:40Z | OWNER | Makes sense to do this before foreign keys in #2 Python: db["table"].add_column("new_column", int) CLI: $ sqlite-utils add-column table new_column INTEGER | sqlite-utils 140912432 | issue | {"url": "https://api.github.com/repos/simonw/sqlite-utils/issues/15/reactions", "total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | completed | ||||||
413867537 | MDU6SXNzdWU0MTM4Njc1Mzc= | 16 | add_column() should support REFERENCES {other_table}({other_column}) | simonw 9599 | closed | 0 | 4 | 2019-02-24T21:00:45Z | 2019-05-29T05:17:59Z | 2019-05-29T04:56:18Z | OWNER | Related to #2 | sqlite-utils 140912432 | issue | {"url": "https://api.github.com/repos/simonw/sqlite-utils/issues/16/reactions", "total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | completed | ||||||
413868452 | MDU6SXNzdWU0MTM4Njg0NTI= | 17 | Improve and document foreign_keys=... argument to insert/create/etc | simonw 9599 | closed | 0 | 7 | 2019-02-24T21:09:11Z | 2019-02-24T23:45:48Z | 2019-02-24T23:45:48Z | OWNER | The `foreign_keys=` argument to `table.insert_all()` and friends can be used to specify foreign key relationships that should be created. It is not yet documented. It also requires you to specify the SQLite type of each column, even though this can be detected by introspecting the referenced table: cols = [c for c in self.db[other_table].columns if c.name == other_column] cols[0].type Relates to #2 | sqlite-utils 140912432 | issue | {"url": "https://api.github.com/repos/simonw/sqlite-utils/issues/17/reactions", "total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | completed | ||||||
413871266 | MDU6SXNzdWU0MTM4NzEyNjY= | 18 | .insert/.upsert/.insert_all/.upsert_all should add missing columns | simonw 9599 | closed | 0 | 1.0 4348046 | 2 | 2019-02-24T21:36:11Z | 2019-05-25T00:42:11Z | 2019-05-25T00:42:11Z | OWNER | This is a larger change, but it would be incredibly useful: if you attempt to insert or update a document with a field that does not currently exist in the underlying table, sqlite-utils should add the appropriate column for you. | sqlite-utils 140912432 | issue | {"url": "https://api.github.com/repos/simonw/sqlite-utils/issues/18/reactions", "total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | completed | |||||
432217625 | MDU6SXNzdWU0MzIyMTc2MjU= | 19 | Incorrect help text for enable-fts command | simonw 9599 | closed | 0 | 1.0 4348046 | 0 | 2019-04-11T19:46:44Z | 2019-05-25T00:44:31Z | 2019-05-25T00:44:31Z | OWNER | I clearly copied-and-pasted this from the `tables` command without updating it: https://github.com/simonw/sqlite-utils/blob/0b1af42ead3b3902347951180b3364ce1942da6e/sqlite_utils/cli.py#L216-L222 | sqlite-utils 140912432 | issue | {"url": "https://api.github.com/repos/simonw/sqlite-utils/issues/19/reactions", "total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | completed | |||||
432727685 | MDU6SXNzdWU0MzI3Mjc2ODU= | 20 | JSON column values get extraneously quoted | mhalle 649467 | closed | 0 | 1.0 4348046 | 1 | 2019-04-12T20:15:30Z | 2019-05-25T00:57:19Z | 2019-05-25T00:57:19Z | NONE | If the input to `sqlite-utils insert` includes a column that is a JSON array or object, `sqlite-utils query` will introduce an extra level of quoting on output: ``` # echo '[{"key": ["one", "two", "three"]}]' | sqlite-utils insert t.db t - # sqlite-utils t.db 'select * from t' [{"key": "[\"one\", \"two\", \"three\"]"}] # sqlite3 t.db 'select * from t' ["one", "two", "three"] ``` This might require an imperfect solution, since sqlite3 doesn't have a JSON type. Perhaps fields that start with `["` or `{"` and end with `"]` or `"}` could be detected, with a flag to turn off that behavior for weird text fields (or vice versa). | sqlite-utils 140912432 | issue | {"url": "https://api.github.com/repos/simonw/sqlite-utils/issues/20/reactions", "total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | completed | |||||
448391492 | MDU6SXNzdWU0NDgzOTE0OTI= | 21 | Option to ignore inserts if primary key exists already | simonw 9599 | closed | 0 | 3 | 2019-05-25T00:17:12Z | 2019-05-29T05:09:01Z | 2019-05-29T04:18:26Z | OWNER | > I've just noticed that SQLite lets you IGNORE inserts that collide with a pre-existing key. This can be quite handy if you have a dataset that keeps changing in part, and you don't want to upsert and replace pre-existing PK rows but you do want to ignore collisions to existing PK rows. > > Do `sqlite_utils` support such (cavalier!) behaviour? _Originally posted by @psychemedia in https://github.com/simonw/sqlite-utils/issues/18#issuecomment-480621924_ | sqlite-utils 140912432 | issue | {"url": "https://api.github.com/repos/simonw/sqlite-utils/issues/21/reactions", "total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | completed | ||||||
448395665 | MDU6SXNzdWU0NDgzOTU2NjU= | 22 | Release notes for 1.0 | simonw 9599 | closed | 0 | 1.0 4348046 | 2 | 2019-05-25T00:58:03Z | 2019-05-25T01:18:27Z | 2019-05-25T01:06:52Z | OWNER | https://github.com/simonw/sqlite-utils/compare/0.14...251e473 | sqlite-utils 140912432 | issue | {"url": "https://api.github.com/repos/simonw/sqlite-utils/issues/22/reactions", "total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | completed | |||||
449565204 | MDU6SXNzdWU0NDk1NjUyMDQ= | 23 | Syntactic sugar for creating m2m records | simonw 9599 | closed | 0 | 10 | 2019-05-29T02:17:48Z | 2019-08-04T03:54:58Z | 2019-08-04T03:37:34Z | OWNER | Python library only. What would be a syntactically pleasant way of creating a m2m record? | sqlite-utils 140912432 | issue | {"url": "https://api.github.com/repos/simonw/sqlite-utils/issues/23/reactions", "total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | completed | ||||||
449818897 | MDU6SXNzdWU0NDk4MTg4OTc= | 24 | Additional Column Constraints? | IgnoredAmbience 98555 | closed | 0 | 6 | 2019-05-29T13:47:03Z | 2019-06-13T06:47:17Z | 2019-06-13T06:30:26Z | NONE | I'm looking to import data from XML with a pre-defined schema that maps fairly closely to a relational database. In particular, it has explicit annotations for when fields are required, optional, or when a default value should be inferred. Would there be value in adding the ability to define `NOT NULL` and `DEFAULT` column constraints to sqlite-utils? | sqlite-utils 140912432 | issue | {"url": "https://api.github.com/repos/simonw/sqlite-utils/issues/24/reactions", "total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | completed | ||||||
449848803 | MDU6SXNzdWU0NDk4NDg4MDM= | 25 | Allow .insert(..., foreign_keys=()) to auto-detect table and primary key | simonw 9599 | closed | 0 | 4 | 2019-05-29T14:39:22Z | 2019-06-13T05:32:32Z | 2019-06-13T05:32:32Z | OWNER | The `foreign_keys=` argument currently takes a list of triples: ```python db["usages"].insert_all( usages_to_insert, foreign_keys=( ("line_id", "lines", "id"), ("definition_id", "definitions", "id"), ), ) ``` As of #16 we have a mechanism for detecting the primary key column (the third item in this triple) - we should use that here too, so foreign keys can be optionally defined as a list of pairs. | sqlite-utils 140912432 | issue | {"url": "https://api.github.com/repos/simonw/sqlite-utils/issues/25/reactions", "total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | completed | ||||||
455486286 | MDU6SXNzdWU0NTU0ODYyODY= | 26 | Mechanism for turning nested JSON into foreign keys / many-to-many | simonw 9599 | open | 0 | 14 | 2019-06-13T00:52:06Z | 2022-06-29T23:35:29Z | OWNER | The GitHub JSON APIs have a really interesting convention with respect to related objects. Consider https://api.github.com/repos/simonw/sqlite-utils/issues - here's a truncated subset: ```json { "id": 449818897, "node_id": "MDU6SXNzdWU0NDk4MTg4OTc=", "number": 24, "title": "Additional Column Constraints?", "user": { "login": "IgnoredAmbience", "id": 98555, "node_id": "MDQ6VXNlcjk4NTU1", "avatar_url": "https://avatars0.githubusercontent.com/u/98555?v=4", "gravatar_id": "" }, "labels": [ { "id": 993377884, "node_id": "MDU6TGFiZWw5OTMzNzc4ODQ=", "url": "https://api.github.com/repos/simonw/sqlite-utils/labels/enhancement", "name": "enhancement", "color": "a2eeef", "default": true } ], "state": "open" } ``` The `user` column lists a complete user. The `labels` column has a list of labels. Since both user and label have populated `id` field this is actually enough information for us to create records for them AND set up the corresponding foreign key (for user) and m2m relationships (for labels). It would be really neat if `sqlite-utils` had some kind of mechanism for correctly processing these kind of patterns. Thanks to `jq` there's not much need for extra customization of the shape here - if we support a narrowly defined structure users can use `jq` to reshape arbitrary JSON to match. | sqlite-utils 140912432 | issue | {"url": "https://api.github.com/repos/simonw/sqlite-utils/issues/26/reactions", "total_count": 4, "+1": 4, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | ||||||||
455496504 | MDU6SXNzdWU0NTU0OTY1MDQ= | 27 | sqlite-utils create-table command | simonw 9599 | closed | 0 | 8 | 2019-06-13T01:43:30Z | 2020-05-03T15:26:15Z | 2020-05-03T15:26:15Z | OWNER | Spun off from #24 - it would be useful if CLI users could create new tables (with explicit column types, not null rules and defaults) without having to insert an example record. - [x] Get it working - [x] Support `--pk` - [x] Support `--not-null` - [x] Support `--default` - [x] Support `--fk colname othertable othercol` - [x] Support `--replace` and `--ignore` - [x] Documentation | sqlite-utils 140912432 | issue | {"url": "https://api.github.com/repos/simonw/sqlite-utils/issues/27/reactions", "total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | completed | ||||||
455996809 | MDU6SXNzdWU0NTU5OTY4MDk= | 28 | Rearrange the docs by area, not CLI vs Python | simonw 9599 | closed | 0 | 1 | 2019-06-13T23:33:35Z | 2019-07-15T02:37:20Z | 2019-07-15T02:37:20Z | OWNER | The docs for eg inserting data should live on the same page, rather than being split across the API and CLI pages. | sqlite-utils 140912432 | issue | {"url": "https://api.github.com/repos/simonw/sqlite-utils/issues/28/reactions", "total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | completed | ||||||
458941203 | MDU6SXNzdWU0NTg5NDEyMDM= | 29 | Prevent accidental add-foreign-key with invalid column | simonw 9599 | closed | 0 | 0 | 2019-06-20T23:57:24Z | 2019-06-20T23:58:26Z | 2019-06-20T23:58:26Z | OWNER | You can corrupt your database by running: $ sqlite-utils add-foreign-key my.db table non_existent_column other_table other_column | sqlite-utils 140912432 | issue | {"url": "https://api.github.com/repos/simonw/sqlite-utils/issues/29/reactions", "total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | completed | ||||||
461215118 | MDU6SXNzdWU0NjEyMTUxMTg= | 30 | Option to open database in read-only mode | simonw 9599 | closed | 0 | 1 | 2019-06-26T22:50:38Z | 2020-05-11T19:17:17Z | 2020-05-11T19:17:17Z | OWNER | Would this make it 100% safe to run reads against a database file that is being written to by another process? | sqlite-utils 140912432 | issue | {"url": "https://api.github.com/repos/simonw/sqlite-utils/issues/30/reactions", "total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | completed | ||||||
461237618 | MDU6SXNzdWU0NjEyMzc2MTg= | 31 | Mechanism for adding multiple foreign key constraints at once | simonw 9599 | closed | 0 | 0 | 2019-06-27T00:04:30Z | 2019-06-29T06:27:40Z | 2019-06-29T06:27:40Z | OWNER | Needed by [db-to-sqlite](https://github.com/simonw/db-to-sqlite). It currently works by collecting all of the foreign key relationships it can find and then applying them at the end of the process. The problem is, the `add_foreign_key()` method looks like this: https://github.com/simonw/sqlite-utils/blob/86bd2bba689e25f09551d611ccfbee1e069e5b66/sqlite_utils/db.py#L498-L516 That means it's doing a full `VACUUM` for every single relationship it sets up - and if you have hundreds of foreign key relationships in your database this can take hours. I think the right solution is to have a `.add_foreign_keys(list_of_args)` method which does the bulk operation and then a single `VACUUM`. `.add_foreign_key(...)` can then call the bulk action with a single list item. | sqlite-utils 140912432 | issue | {"url": "https://api.github.com/repos/simonw/sqlite-utils/issues/31/reactions", "total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | completed | ||||||
462423839 | MDU6SXNzdWU0NjI0MjM4Mzk= | 33 | index_foreign_keys / index-foreign-keys utilities | simonw 9599 | closed | 0 | 2 | 2019-06-30T16:42:03Z | 2019-06-30T23:54:11Z | 2019-06-30T23:50:55Z | OWNER | Sometimes it's good to have indices on all columns that are foreign keys, to allow for efficient reverse lookups. This would be a useful utility: $ sqlite-utils index-foreign-keys database.db | sqlite-utils 140912432 | issue | {"url": "https://api.github.com/repos/simonw/sqlite-utils/issues/33/reactions", "total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | completed | ||||||
462430920 | MDU6SXNzdWU0NjI0MzA5MjA= | 35 | table.update(...) method | simonw 9599 | closed | 0 | 2 | 2019-06-30T18:06:15Z | 2019-07-28T15:43:52Z | 2019-07-28T15:43:52Z | OWNER | Spun off from #23 - this method will allow a user to update a specific row. Currently the only way to do that it is to call `.upsert({full record})` with the primary key field matching an existing record - but this does not support partial updates. ```python db["events"].update(3, {"name": "Renamed"}) ``` This method only works on an existing table, so there's no need for a `pk="id"` specifier - it can detect the primary key by looking at the table. If the primary key is compound the first argument can be a tuple: ```python db["events_venues"].update((3, 2), {"custom_label": "Label"}) ``` The method can be called without the second dictionary argument. Doing this selects the row specified by the primary key (throwing an error if it does not exist) and remembers it so that chained operations can be carried out - see proposal in https://github.com/simonw/sqlite-utils/issues/23#issuecomment-507055345 | sqlite-utils 140912432 | issue | {"url": "https://api.github.com/repos/simonw/sqlite-utils/issues/35/reactions", "total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | completed | ||||||
462817589 | MDU6SXNzdWU0NjI4MTc1ODk= | 36 | Support compound primary keys | simonw 9599 | closed | 0 | 0 | 2019-07-01T17:00:07Z | 2019-07-15T04:28:52Z | 2019-07-15T04:28:52Z | OWNER | This should work: ```python table = db["dog_breeds"].insert({ "dog_id": 1, "breed_id": 2 }, pk=("dog_id", "breed_id")) ``` Needed for m2m work in #23 | sqlite-utils 140912432 | issue | {"url": "https://api.github.com/repos/simonw/sqlite-utils/issues/36/reactions", "total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | completed | ||||||
465815372 | MDU6SXNzdWU0NjU4MTUzNzI= | 37 | Experiment with type hints | simonw 9599 | closed | 0 | 6 | 2019-07-09T14:30:34Z | 2021-08-18T21:48:57Z | 2021-08-18T21:48:57Z | OWNER | Since it's designed to be used in Jupyter or for rapid prototyping in an IDE (and it's still pretty small) `sqlite-utils` feels like a great candidate for me to finally try out Python type hints. https://veekaybee.github.io/2019/07/08/python-type-hints/ is good. It suggests the mypy docs for getting started: https://mypy.readthedocs.io/en/latest/existing_code.html plus this tutorial: https://pymbook.readthedocs.io/en/latest/typehinting.html | sqlite-utils 140912432 | issue | {"url": "https://api.github.com/repos/simonw/sqlite-utils/issues/37/reactions", "total_count": 1, "+1": 1, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | completed | ||||||
467864071 | MDU6SXNzdWU0Njc4NjQwNzE= | 39 | table.get(...) method | simonw 9599 | closed | 0 | 0 | 2019-07-14T17:20:51Z | 2019-07-15T04:28:53Z | 2019-07-15T04:28:53Z | OWNER | Utility method for fetching a record by its primary key. Accepts a single value (for primary key / rowid tables) or a list/tuple of values (for compound primary keys, refs #36). Raises a `NotFoundError` if the record cannot be found. | sqlite-utils 140912432 | issue | {"url": "https://api.github.com/repos/simonw/sqlite-utils/issues/39/reactions", "total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | completed | ||||||
470131537 | MDU6SXNzdWU0NzAxMzE1Mzc= | 41 | sqlite-utils insert --tsv option | simonw 9599 | closed | 0 | 0 | 2019-07-19T04:27:21Z | 2019-07-19T04:50:47Z | 2019-07-19T04:50:47Z | OWNER | Right now we only support ingesting CSV, but sometimes interesting data is released as TSV. https://www.washingtonpost.com/national/2019/07/18/how-download-use-dea-pain-pills-database/ for example. | sqlite-utils 140912432 | issue | {"url": "https://api.github.com/repos/simonw/sqlite-utils/issues/41/reactions", "total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | completed | ||||||
470345929 | MDU6SXNzdWU0NzAzNDU5Mjk= | 42 | table.extract(...) method and "sqlite-utils extract" command | simonw 9599 | closed | 0 | 2.20 5897911 | 21 | 2019-07-19T14:09:36Z | 2020-09-22T23:39:31Z | 2020-09-22T23:37:49Z | OWNER | One of my favourite features of [csvs-to-sqlite](https://github.com/simonw/csvs-to-sqlite) is that it can "extract" columns into a separate lookup table - for example: csvs-to-sqlite big_csv_file.csv -c country output.db This will turn the `country` column in the resulting table into a integer foreign key against a new `country` table. You can see an example of what that looks like here: https://san-francisco.datasettes.com/registered-business-locations-3d50679/Business+Corridor was extracted from https://san-francisco.datasettes.com/registered-business-locations-3d50679/Registered_Business_Locations_-_San_Francisco?Business%20Corridor=1 I'd like to have the same capability in `sqlite-utils` - but with the ability to run it against an existing SQLite table rather than just against a CSV. | sqlite-utils 140912432 | issue | {"url": "https://api.github.com/repos/simonw/sqlite-utils/issues/42/reactions", "total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | completed | |||||
470691999 | MDU6SXNzdWU0NzA2OTE5OTk= | 43 | .add_column() doesn't match indentation of initial creation | simonw 9599 | closed | 0 | 3 | 2019-07-20T16:33:10Z | 2019-07-23T13:09:11Z | 2019-07-23T13:09:05Z | OWNER | I spotted a table which was created once and then had columns added to it and the formatted SQL looks like this: ```sql CREATE TABLE [records] ( [type] TEXT, [sourceName] TEXT, [sourceVersion] TEXT, [unit] TEXT, [creationDate] TEXT, [startDate] TEXT, [endDate] TEXT, [value] TEXT, [metadata_Health Mate App Version] TEXT, [metadata_Withings User Identifier] TEXT, [metadata_Modified Date] TEXT, [metadata_Withings Link] TEXT, [metadata_HKWasUserEntered] TEXT , [device] TEXT, [metadata_HKMetadataKeyHeartRateMotionContext] TEXT, [metadata_HKDeviceManufacturerName] TEXT, [metadata_HKMetadataKeySyncVersion] TEXT, [metadata_HKMetadataKeySyncIdentifier] TEXT, [metadata_HKSwimmingStrokeStyle] TEXT, [metadata_HKVO2MaxTestType] TEXT, [metadata_HKTimeZone] TEXT, [metadata_Average HR] TEXT, [metadata_Recharge] TEXT, [metadata_Lights] TEXT, [metadata_Asleep] TEXT, [metadata_Rating] TEXT, [metadata_Energy Threshold] TEXT, [metadata_Deep Sleep] TEXT, [metadata_Nap] TEXT, [metadata_Edit Slots] TEXT, [metadata_Tags] TEXT, [metadata_Daytime HR] TEXT) ``` It would be nice if the columns that were added later matched the indentation of the initial columns. | sqlite-utils 140912432 | issue | {"url": "https://api.github.com/repos/simonw/sqlite-utils/issues/43/reactions", "total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | completed | ||||||
471628483 | MDU6SXNzdWU0NzE2Mjg0ODM= | 44 | Utilities for building lookup tables | simonw 9599 | closed | 0 | 2 | 2019-07-23T10:59:58Z | 2019-07-23T13:07:01Z | 2019-07-23T13:07:01Z | OWNER | While building https://github.com/dogsheep/healthkit-to-sqlite I found a need for a neat mechanism for easily building lookup tables - tables where each unique value in a column is replaced by a foreign key to a separate table. csvs-to-sqlite currently creates those with its "extract" mechanism - but that's written as custom code against Pandas. I'd like to eventually replace Pandas with sqlite-utils there. See also #42 | sqlite-utils 140912432 | issue | {"url": "https://api.github.com/repos/simonw/sqlite-utils/issues/44/reactions", "total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | completed | ||||||
471780443 | MDU6SXNzdWU0NzE3ODA0NDM= | 46 | extracts= option for insert/update/etc | simonw 9599 | closed | 0 | 3 | 2019-07-23T15:55:46Z | 2020-03-01T16:53:40Z | 2019-07-23T17:00:44Z | OWNER | Relates to #42 and #44. I want the ability to extract values out into lookup tables during bulk insert/upsert operations. `db.insert_all(rows, extracts=["species"])` - creates species table for values in the species column `db.insert_all(rows, extracts={"species": "Species"})` - as above but the new table is called `Species`. | sqlite-utils 140912432 | issue | {"url": "https://api.github.com/repos/simonw/sqlite-utils/issues/46/reactions", "total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | completed | ||||||
471818939 | MDU6SXNzdWU0NzE4MTg5Mzk= | 48 | Jupyter notebook demo of the library, launchable on Binder | simonw 9599 | closed | 0 | 2 | 2019-07-23T17:05:05Z | 2022-01-26T02:08:46Z | 2022-01-26T02:08:39Z | OWNER | sqlite-utils 140912432 | issue | {"url": "https://api.github.com/repos/simonw/sqlite-utils/issues/48/reactions", "total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | completed | |||||||
472115381 | MDU6SXNzdWU0NzIxMTUzODE= | 49 | extracts= should support multiple-column extracts | simonw 9599 | open | 0 | 10 | 2019-07-24T07:06:41Z | 2020-10-16T19:18:19Z | OWNER | Lookup tables can be constructed on compound columns, but the `extracts=` option doesn't currently support that. Right now extracts can be defined in two ways: ```python # Extract these columns into tables with the same name: dogs = db.table("dogs", extracts=["breed", "most_recent_trophy"]) # Same as above but with custom table names: dogs = db.table("dogs", extracts={"breed": "Breeds", "most_recent_trophy": "Trophies"}) ``` Need some kind of syntax for much more complicated extractions, like when two columns (say "source" and "source_version") are extracted into a single table. | sqlite-utils 140912432 | issue | {"url": "https://api.github.com/repos/simonw/sqlite-utils/issues/49/reactions", "total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | ||||||||
473083260 | MDU6SXNzdWU0NzMwODMyNjA= | 50 | "Too many SQL variables" on large inserts | simonw 9599 | closed | 0 | 4 | 2019-07-25T21:43:31Z | 2022-11-04T14:38:36Z | 2019-07-28T11:59:33Z | OWNER | Reported here: https://github.com/dogsheep/healthkit-to-sqlite/issues/9 It looks like there's a default limit of 999 variables - we need to be smart about that, maybe dynamically lower the batch size based on the number of columns. | sqlite-utils 140912432 | issue | {"url": "https://api.github.com/repos/simonw/sqlite-utils/issues/50/reactions", "total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | completed | ||||||
476413293 | MDU6SXNzdWU0NzY0MTMyOTM= | 52 | Throws error if .insert_all() / .upsert_all() called with empty list | simonw 9599 | closed | 0 | 1 | 2019-08-03T04:09:00Z | 2019-11-07T04:32:39Z | 2019-11-07T04:32:39Z | OWNER | See also https://github.com/simonw/db-to-sqlite/issues/18 | sqlite-utils 140912432 | issue | {"url": "https://api.github.com/repos/simonw/sqlite-utils/issues/52/reactions", "total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | completed | ||||||
480961330 | MDU6SXNzdWU0ODA5NjEzMzA= | 54 | Ability to list views, and to access db["view_name"].rows / rows_where / etc | ftrain 20264 | closed | 0 | 5 | 2019-08-15T02:00:28Z | 2019-08-23T12:41:09Z | 2019-08-23T12:20:15Z | NONE | The docs show me how to create a view via `db.create_view()` but I can't seem to get back to that view post-creation; if I query it as a table it returns `None`, and it doesn't appear in the table listing, even though querying the view works fine from inside the sqlite3 command-line. It'd be great to have the view as a pseudo-table, or if the python/sqlite3 module makes that hard to pull off (I couldn't figure it out), to have that edge-case documented next to the `db.create_view()` docs. | sqlite-utils 140912432 | issue | {"url": "https://api.github.com/repos/simonw/sqlite-utils/issues/54/reactions", "total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | completed | ||||||
488293926 | MDU6SXNzdWU0ODgyOTM5MjY= | 58 | Support enabling FTS on views | amjith 49260 | closed | 0 | 1 | 2019-09-02T18:56:36Z | 2020-10-16T18:39:36Z | 2020-10-16T18:39:31Z | CONTRIBUTOR | Right now enable_fts() is only implemented for Table(). Technically sqlite supports enabling fts on views. But it requires deeper thought since views don't have `rowid` and the current implementation of enable_fts() relies on the presence of `rowid` column. It is possible to provide an alternative rowid using the `content_rowid` option to the FTS5() function. Ref: https://sqlite.org/fts5.html#fts5_table_creation_and_initialization > The "content_rowid" option, used to set the rowid field of an external content table. This will further complicate `enable_fts()` function by adding an extra argument. I'm wondering if that is outside the scope of this tool or should I work on that feature and send a PR? | sqlite-utils 140912432 | issue | {"url": "https://api.github.com/repos/simonw/sqlite-utils/issues/58/reactions", "total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | completed | ||||||
488338965 | MDU6SXNzdWU0ODgzMzg5NjU= | 59 | Ability to introspect triggers | simonw 9599 | closed | 0 | 0 | 2019-09-02T23:47:16Z | 2019-09-03T01:52:36Z | 2019-09-03T00:09:42Z | OWNER | Now that we're creating triggers (thanks to @amjith in #57) it would be neat if we could introspect them too. I'm thinking: `db.triggers` - lists all triggers for the database `db["tablename"].triggers` - lists triggers for that table The underlying query for this is `select * from sqlite_master where type = 'trigger'` I'll return the trigger information in a new namedtuple, similar to how Indexes and ForeignKeys work. | sqlite-utils 140912432 | issue | {"url": "https://api.github.com/repos/simonw/sqlite-utils/issues/59/reactions", "total_count": 1, "+1": 1, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | completed | ||||||
491219910 | MDU6SXNzdWU0OTEyMTk5MTA= | 61 | importing CSV to SQLite as library | witeshadow 17739 | closed | 0 | 2 | 2019-09-09T17:12:40Z | 2019-11-04T16:25:01Z | 2019-11-04T16:25:01Z | NONE | CSV can be imported to SQLite when used CLI, but I don't see documentation for when using as library. | sqlite-utils 140912432 | issue | {"url": "https://api.github.com/repos/simonw/sqlite-utils/issues/61/reactions", "total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | completed | ||||||
500783373 | MDU6SXNzdWU1MDA3ODMzNzM= | 62 | [enhancement] Method to delete a row in python | Sergeileduc 4454869 | closed | 0 | 5 | 2019-10-01T09:45:47Z | 2019-11-04T16:30:34Z | 2019-11-04T16:18:18Z | NONE | Hi ! Thanks for the lib ! Obviously, every possible sql queries won't have a dedicated method. But I was thinking : a method to delete a row (I'm terrible with names, maybe `delete_where()` or something, would be useful. I have a Database, with primary key. For the moment, I use : ```Python3 db.conn.execute(f"DELETE FROM table WHERE key = {key_id}") db.conn.commit() ``` to delete a row I don't need anymore, giving his primary key. Works like a charm. Just an idea : ```Python3 table.delete_where_pkey({'key': key_id}) ``` or something (I know, I'm terrible at naming methods...). Pros : well, no need to write SQL query. Cons : WHERE normally allows to do many more things (operators =, <>, >, <, BETWEEN), not to mention AND, OR, etc... Method is maybe to specific, and/or a pain to render more flexible. Again, just a thought. Writing his own sql works too, so... Thanks again. See yah. | sqlite-utils 140912432 | issue | {"url": "https://api.github.com/repos/simonw/sqlite-utils/issues/62/reactions", "total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | completed | ||||||
517241040 | MDU6SXNzdWU1MTcyNDEwNDA= | 63 | ensure_index() method | simonw 9599 | closed | 0 | 1 | 2019-11-04T15:51:22Z | 2019-11-04T16:20:36Z | 2019-11-04T16:20:35Z | OWNER | ```python db["table"].ensure_index(["col1", "col2"]) ``` This will do the following: - if the specified table or column does not exist, do nothing - if they exist and already have an index, do nothing - otherwise, create the index I want this for tools like [twitter-to-sqlite search](https://github.com/dogsheep/twitter-to-sqlite/blob/801c0c2daf17d8abce9dcb5d8d610410e7e25dbe/README.md#running-searches) where the `search_runs` table may or not have been created yet but, if it IS created, I want to put an index on the `hash` column. | sqlite-utils 140912432 | issue | {"url": "https://api.github.com/repos/simonw/sqlite-utils/issues/63/reactions", "total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | completed | ||||||
521868864 | MDU6SXNzdWU1MjE4Njg4NjQ= | 66 | The ".upsert()" method is misnamed | simonw 9599 | closed | 0 | 15 | 2019-11-12T23:48:28Z | 2019-12-31T01:30:21Z | 2019-12-31T01:30:20Z | OWNER | This thread here is illuminating: https://stackoverflow.com/questions/3634984/insert-if-not-exists-else-update The term `UPSERT` in SQLite has a specific meaning as-of 3.24.0 (2018-06-04): https://www.sqlite.org/lang_UPSERT.html It means "behave as an UPDATE or a no-op if the INSERT would violate a uniqueness constraint". The syntax in 3.24.0+ looks like this (confusingly it does not use the term "upsert"): ```sql INSERT INTO phonebook(name,phonenumber) VALUES('Alice','704-555-1212') ON CONFLICT(name) DO UPDATE SET phonenumber=excluded.phonenumber ``` Here's the problem: the `sqlite-utils` `.upsert()` and `.upsert_all()` methods don't do this. They use the following SQL: ```sql INSERT OR REPLACE INTO [{table}] ({columns}) VALUES {rows}; ``` If the record already exists, it will be entirely replaced by a new record - as opposed to updating any specified fields but leaving existing fields as they are (the behaviour of "upsert" in SQLite itself). | sqlite-utils 140912432 | issue | {"url": "https://api.github.com/repos/simonw/sqlite-utils/issues/66/reactions", "total_count": 1, "+1": 1, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | completed | ||||||
531583658 | MDU6SXNzdWU1MzE1ODM2NTg= | 68 | Add support for porter stemming in FTS | simonw 9599 | closed | 0 | 1 | 2019-12-02T22:35:52Z | 2020-09-20T04:25:53Z | 2020-09-20T04:25:47Z | OWNER | FTS5 can have porter stemming enabled. | sqlite-utils 140912432 | issue | {"url": "https://api.github.com/repos/simonw/sqlite-utils/issues/68/reactions", "total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | completed | ||||||
534507142 | MDU6SXNzdWU1MzQ1MDcxNDI= | 69 | Feature request: enable extensions loading | aborruso 30607 | closed | 0 | 3 | 2019-12-08T08:06:25Z | 2022-02-05T00:04:25Z | 2020-10-16T18:42:49Z | NONE | Hi, it would be great to add a parameter that enables the load of a sqlite extension you need. Something like "-ext modspatialite". In this way your great tool would be even more comfortable and powerful. Thank you very much | sqlite-utils 140912432 | issue | {"url": "https://api.github.com/repos/simonw/sqlite-utils/issues/69/reactions", "total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | completed | ||||||
539204432 | MDU6SXNzdWU1MzkyMDQ0MzI= | 70 | Implement ON DELETE and ON UPDATE actions for foreign keys | LucasElArruda 26292069 | open | 0 | 2 | 2019-12-17T17:19:10Z | 2020-02-27T04:18:53Z | NONE | Hi! I did not find any mention on the library about ON DELETE and ON UPDATE actions for foreign keys. Are those expected to be implemented? If not, it would be a nice thing to include! | sqlite-utils 140912432 | issue | {"url": "https://api.github.com/repos/simonw/sqlite-utils/issues/70/reactions", "total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | ||||||||
542814756 | MDU6SXNzdWU1NDI4MTQ3NTY= | 71 | Tests are failing due to missing FTS5 | simonw 9599 | closed | 0 | 3 | 2019-12-27T09:41:16Z | 2019-12-27T09:49:37Z | 2019-12-27T09:49:37Z | OWNER | https://travis-ci.com/simonw/sqlite-utils/jobs/268436167 This is a recent change: 2 months ago they worked fine. I'm not sure what changed here. Maybe something to do with https://launchpad.net/~jonathonf/+archive/ubuntu/backports ? | sqlite-utils 140912432 | issue | {"url": "https://api.github.com/repos/simonw/sqlite-utils/issues/71/reactions", "total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | completed | ||||||
545407916 | MDU6SXNzdWU1NDU0MDc5MTY= | 73 | upsert_all() throws issue when upserting to empty table | psychemedia 82988 | closed | 0 | 6 | 2020-01-05T11:58:57Z | 2020-01-31T14:21:09Z | 2020-01-05T17:20:18Z | NONE | If I try to add a list of `dict`s to an empty table using `upsert_all`, I get an error: ```python import sqlite3 from sqlite_utils import Database import pandas as pd conx = sqlite3.connect(':memory') cx = conx.cursor() cx.executescript('CREATE TABLE "test" ("Col1" TEXT);') q="SELECT * FROM test;" pd.read_sql(q, conx) #shows empty table db = Database(conx) db['test'].upsert_all([{'Col1':'a'},{'Col1':'b'}]) --------------------------------------------------------------------------- TypeError Traceback (most recent call last) <ipython-input-74-8c26d93d7587> in <module> 1 db = Database(conx) ----> 2 db['test'].upsert_all([{'Col1':'a'},{'Col1':'b'}]) /usr/local/lib/python3.7/site-packages/sqlite_utils/db.py in upsert_all(self, records, pk, foreign_keys, column_order, not_null, defaults, batch_size, hash_id, alter, extracts) 1157 alter=alter, 1158 extracts=extracts, -> 1159 upsert=True, 1160 ) 1161 /usr/local/lib/python3.7/site-packages/sqlite_utils/db.py in insert_all(self, records, pk, foreign_keys, column_order, not_null, defaults, batch_size, hash_id, alter, ignore, replace, extracts, upsert) 1040 sql = "INSERT OR IGNORE INTO [{table}]({pks}) VALUES({pk_placeholders});".format( 1041 table=self.name, -> 1042 pks=", ".join(["[{}]".format(p) for p in pks]), 1043 pk_placeholders=", ".join(["?" for p in pks]), 1044 ) TypeError: 'NoneType' object is not iterable ``` A hacky workaround in use is: ```python try: db['test'].upsert_all([{'Col1':'a'},{'Col1':'b'}]) except: db['test'].insert_all([{'Col1':'a'},{'Col1':'b'}]) ``` | sqlite-utils 140912432 | issue | {"url": "https://api.github.com/repos/simonw/sqlite-utils/issues/73/reactions", "total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | completed | ||||||
546073980 | MDU6SXNzdWU1NDYwNzM5ODA= | 74 | Test failures on openSUSE 15.1: AssertionError: Explicit other_table and other_column | jayvdb 15092 | open | 0 | 3 | 2020-01-07T04:35:50Z | 2020-01-12T07:21:17Z | CONTRIBUTOR | openSUSE 15.1 is using python 3.6.5 and click-7.0 , however it has test failures while openSUSE Tumbleweed on py37 passes. Most fail on the cli exit code like ```py [ 74s] =================================== FAILURES =================================== [ 74s] _________________________________ test_tables __________________________________ [ 74s] [ 74s] db_path = '/tmp/pytest-of-abuild/pytest-0/test_tables0/test.db' [ 74s] [ 74s] def test_tables(db_path): [ 74s] result = CliRunner().invoke(cli.cli, ["tables", db_path]) [ 74s] > assert '[{"table": "Gosh"},\n {"table": "Gosh2"}]' == result.output.strip() [ 74s] E assert '[{"table": "...e": "Gosh2"}]' == '' [ 74s] E - [{"table": "Gosh"}, [ 74s] E - {"table": "Gosh2"}] [ 74s] [ 74s] tests/test_cli.py:28: AssertionError ``` packaging project at https://build.opensuse.org/package/show/home:jayvdb:py-new/python-sqlite-utils I'll keep digging into this after I have github-to-sqlite working on Tumbleweed, as I'll need openSUSE Leap 15.1 working before I can submit this into the main python repo. | sqlite-utils 140912432 | issue | {"url": "https://api.github.com/repos/simonw/sqlite-utils/issues/74/reactions", "total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | ||||||||
549287310 | MDU6SXNzdWU1NDkyODczMTA= | 76 | order_by mechanism | metab0t 10501166 | closed | 0 | 4 | 2020-01-14T02:06:03Z | 2020-04-16T06:23:29Z | 2020-04-16T03:13:06Z | NONE | In some cases, I want to iterate rows in a table with `ORDER BY` clause. It would be nice to have a `rows_order_by` function similar to `rows_where`. In a more general case, `rows_filter` function might be added to allow more customized filtering to iterate rows. | sqlite-utils 140912432 | issue | {"url": "https://api.github.com/repos/simonw/sqlite-utils/issues/76/reactions", "total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | completed | ||||||
557825032 | MDU6SXNzdWU1NTc4MjUwMzI= | 77 | Ability to insert data that is transformed by a SQL function | simonw 9599 | closed | 0 | 2 | 2020-01-30T23:45:55Z | 2022-02-05T00:04:25Z | 2020-01-31T00:24:32Z | OWNER | I want to be able to run the equivalent of this SQL insert: ```python # Convert to "Well Known Text" format wkt = shape(geojson['geometry']).wkt # Insert and commit the record conn.execute("INSERT INTO places (id, name, geom) VALUES(null, ?, GeomFromText(?, 4326))", ( "Wales", wkt )) conn.commit() ``` From the Datasette SpatiaLite docs: https://datasette.readthedocs.io/en/stable/spatialite.html To do this, I need a way of telling `sqlite-utils` that a specific column should be wrapped in `GeomFromText(?, 4326)`. | sqlite-utils 140912432 | issue | {"url": "https://api.github.com/repos/simonw/sqlite-utils/issues/77/reactions", "total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | completed | ||||||
557842245 | MDU6SXNzdWU1NTc4NDIyNDU= | 79 | Helper methods for working with SpatiaLite | simonw 9599 | closed | 0 | 8 | 2020-01-31T00:39:19Z | 2022-02-05T00:04:25Z | 2022-02-04T05:55:11Z | OWNER | As demonstrated by this piece of documentation, using SpatiaLite with sqlite-utils requires a fair bit of boilerplate: https://github.com/simonw/sqlite-utils/blob/f7289174e66ae4d91d57de94bbd9d09fabf7aff4/docs/python-api.rst#L880-L909 | sqlite-utils 140912432 | issue | {"url": "https://api.github.com/repos/simonw/sqlite-utils/issues/79/reactions", "total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | completed | ||||||
558600274 | MDU6SXNzdWU1NTg2MDAyNzQ= | 81 | Remove .detect_column_types() from table, make it a documented API | simonw 9599 | closed | 0 | 4 | 2020-02-01T21:25:54Z | 2020-02-01T21:55:35Z | 2020-02-01T21:55:35Z | OWNER | I used it in `geojson-to-sqlite` here: https://github.com/simonw/geojson-to-sqlite/blob/f10e44264712dd59ae7dfa2e6fd5a904b682fb33/geojson_to_sqlite/utils.py#L45-L50 It would make more sense for this method to live on the Database rather than the Table - or even to exist as a separate utility method entirely. Then it should be documented. | sqlite-utils 140912432 | issue | {"url": "https://api.github.com/repos/simonw/sqlite-utils/issues/81/reactions", "total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | completed | ||||||
559197745 | MDU6SXNzdWU1NTkxOTc3NDU= | 82 | Tutorial command no longer works | petey284 10350886 | closed | 0 | 3 | 2020-02-03T16:36:11Z | 2020-02-27T04:16:43Z | 2020-02-27T04:16:30Z | NONE | Issue with command on [tutorial](https://simonwillison.net/2019/Feb/25/sqlite-utils/) on Simon's site. The following command no longer works, and breaks with the previous too many variables error: #50 ``` cmd > curl "https://data.nasa.gov/resource/y77d-th95.json" | \ sqlite-utils insert meteorites.db meteorites - --pk=id ``` Output: ``` cmd Traceback (most recent call last): File "continuum\miniconda3\envs\main\lib\runpy.py", line 193, in _run_module_as_main "__main__", mod_spec) File "continuum\miniconda3\envs\main\lib\runpy.py", line 85, in _run_code exec(code, run_globals) File "Continuum\miniconda3\envs\main\Scripts\sqlite-utils.exe\__main__.py", line 9, in <module> File "continuum\miniconda3\envs\main\lib\site-packages\click\core.py", line 764, in __call__ return self.main(*args, **kwargs) File "continuum\miniconda3\envs\main\lib\site-packages\click\core.py", line 717, in main rv = self.invoke(ctx) File "continuum\miniconda3\envs\main\lib\site-packages\click\core.py", line 1137, in invoke return _process_result(sub_ctx.command.invoke(sub_ctx)) File "continuum\miniconda3\envs\main\lib\site-packages\click\core.py", line 956, in invoke return ctx.invoke(self.callback, **ctx.params) File "continuum\miniconda3\envs\main\lib\site-packages\click\core.py", line 555, in invoke return callback(*args, **kwargs) File "continuum\miniconda3\envs\main\lib\site-packages\sqlite_utils\cli.py", line 434, in insert default=default, File "continuum\miniconda3\envs\main\lib\site-packages\sqlite_utils\cli.py", line 384, in insert_upsert_implementation docs, pk=pk, batch_size=batch_size, alter=alter, **extra_kwargs File "continuum\miniconda3\envs\main\lib\site-packages\sqlite_utils\db.py", line 1081, in insert_all result = self.db.conn.execute(query, params) sqlite3.OperationalError: too many SQL variables ``` My thought is that maybe the dataset grew over the last few years and so didn't run into this issue before. No error… | sqlite-utils 140912432 | issue | {"url": "https://api.github.com/repos/simonw/sqlite-utils/issues/82/reactions", "total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | completed | ||||||
559374410 | MDU6SXNzdWU1NTkzNzQ0MTA= | 83 | Make db["table"].exists a documented API | simonw 9599 | closed | 0 | 1 | 2020-02-03T22:31:44Z | 2020-02-08T23:58:35Z | 2020-02-08T23:56:23Z | OWNER | Right now it's a static thing which might get out-of-sync with the database. It should probably be a live check. Maybe call it `.exists()` instead? | sqlite-utils 140912432 | issue | {"url": "https://api.github.com/repos/simonw/sqlite-utils/issues/83/reactions", "total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | completed | ||||||
561460274 | MDU6SXNzdWU1NjE0NjAyNzQ= | 84 | .upsert() with hash_id throws error | simonw 9599 | closed | 0 | 0 | 2020-02-07T07:08:19Z | 2020-02-07T07:17:11Z | 2020-02-07T07:17:11Z | OWNER | ```python db[table_name].upsert_all(rows, hash_id="pk") ``` This throws an error: `PrimaryKeyRequired('upsert() requires a pk')` The problem is, if you try this: ```python db[table_name].upsert_all(rows, hash_id="pk", pk="pk") ``` You get this error: `AssertionError('Use either pk= or hash_id=')` `hash_id=` should imply that `pk=` that column. | sqlite-utils 140912432 | issue | {"url": "https://api.github.com/repos/simonw/sqlite-utils/issues/84/reactions", "total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | completed | ||||||
562911863 | MDU6SXNzdWU1NjI5MTE4NjM= | 85 | Create index doesn't work for columns containing spaces | simonw 9599 | closed | 0 | 1 | 2020-02-11T00:34:46Z | 2020-02-11T05:13:20Z | 2020-02-11T05:13:20Z | OWNER | sqlite-utils 140912432 | issue | {"url": "https://api.github.com/repos/simonw/sqlite-utils/issues/85/reactions", "total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | completed | |||||||
564579430 | MDU6SXNzdWU1NjQ1Nzk0MzA= | 86 | Problem with square bracket in CSV column name | foscoj 8149512 | closed | 0 | 7 | 2020-02-13T10:19:57Z | 2020-02-27T04:16:08Z | 2020-02-27T04:16:07Z | NONE | testing some data from european power information (entsoe.eu), the title of the csv contains square brackets. as I am playing with glitch, sqlite-utils are used for creating the db. Traceback (most recent call last): File "/app/.local/bin/sqlite-utils", line 8, in <module> sys.exit(cli()) File "/app/.local/lib/python3.7/site-packages/click/core.py", line 764, in __call__ return self.main(*args, **kwargs) File "/app/.local/lib/python3.7/site-packages/click/core.py", line 717, in main rv = self.invoke(ctx) File "/app/.local/lib/python3.7/site-packages/click/core.py", line 1137, in invoke return _process_result(sub_ctx.command.invoke(sub_ctx)) File "/app/.local/lib/python3.7/site-packages/click/core.py", line 956, in invoke return ctx.invoke(self.callback, **ctx.params) File "/app/.local/lib/python3.7/site-packages/click/core.py", line 555, in invoke return callback(*args, **kwargs) File "/app/.local/lib/python3.7/site-packages/sqlite_utils/cli.py", line 434, in insert default=default, File "/app/.local/lib/python3.7/site-packages/sqlite_utils/cli.py", line 384, in insert_upsert_implementation docs, pk=pk, batch_size=batch_size, alter=alter, **extra_kwargs File "/app/.local/lib/python3.7/site-packages/sqlite_utils/db.py", line 997, in insert_all extracts=extracts, File "/app/.local/lib/python3.7/site-packages/sqlite_utils/db.py", line 618, in create extracts=extracts, File "/app/.local/lib/python3.7/site-packages/sqlite_utils/db.py", line 310, in create_table self.conn.execute(sql) sqlite3.OperationalError: unrecognized token: "]" entsoe_2016.csv renamed to txt for uploading compatibility [entsoe_2016.txt](https://github.com/simonw/sqlite-utils/files/4197688/entsoe_2016.txt) code is remixed directly from your https://glitch.com/edit/#!/datasette-csvs repo | sqlite-utils 140912432 | issue | {"url": "https://api.github.com/repos/simonw/sqlite-utils/issues/86/reactions", "total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | completed | ||||||
565837965 | MDU6SXNzdWU1NjU4Mzc5NjU= | 87 | Should detect collections.OrderedDict as a regular dictionary | simonw 9599 | closed | 0 | 2 | 2020-02-16T02:06:34Z | 2020-02-16T02:20:59Z | 2020-02-16T02:20:59Z | OWNER | ``` File "...python3.7/site-packages/sqlite_utils/db.py", line 292, in create_table column_type=COLUMN_TYPE_MAPPING[column_type], KeyError: <class 'collections.OrderedDict'> ``` | sqlite-utils 140912432 | issue | {"url": "https://api.github.com/repos/simonw/sqlite-utils/issues/87/reactions", "total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | completed | ||||||
571805300 | MDU6SXNzdWU1NzE4MDUzMDA= | 88 | table.disable_fts() method and "sqlite-utils disable-fts ..." command | simonw 9599 | closed | 0 | 5 | 2020-02-27T04:00:50Z | 2020-02-27T04:40:44Z | 2020-02-27T04:40:44Z | OWNER | This would make it easier to iterate on the FTS configuration for a database without having to wipe and recreate the database each time. | sqlite-utils 140912432 | issue | {"url": "https://api.github.com/repos/simonw/sqlite-utils/issues/88/reactions", "total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | completed | ||||||
573578548 | MDU6SXNzdWU1NzM1Nzg1NDg= | 89 | Ability to customize columns used by extracts= feature | simonw 9599 | open | 0 | 3 | 2020-03-01T16:54:48Z | 2020-10-16T19:17:50Z | OWNER | @simonw any thoughts on allow extracts to specify the lookup column name? If I'm understanding the documentation right, `.lookup()` allows you to define the "value" column (the documentation uses name), but when you use `extracts` keyword as part of `.insert()`, `.upsert()` etc. the lookup must be done against a column named "value". I have an existing lookup table that I've populated with columns "id" and "name" as opposed to "id" and "value", and seems I can't use `extracts=`, unless I'm missing something... Initial thought on how to do this would be to allow the dictionary value to be a tuple of table name column pair... so: ``` table = db.table("trees", extracts={"species_id": ("Species", "name"}) ``` I haven't dug too much into the existing code yet, but does this make sense? Worth doing? _Originally posted by @chrishas35 in https://github.com/simonw/sqlite-utils/issues/46#issuecomment-592999503_ | sqlite-utils 140912432 | issue | {"url": "https://api.github.com/repos/simonw/sqlite-utils/issues/89/reactions", "total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | ||||||||
573740712 | MDU6SXNzdWU1NzM3NDA3MTI= | 90 | Cannot .enable_fts() for columns with spaces in their names | simonw 9599 | closed | 0 | 0 | 2020-03-02T06:06:03Z | 2020-03-02T06:10:49Z | 2020-03-02T06:10:49Z | OWNER | ``` import sqlite_utils db = sqlite_utils.Database(memory=True) db["test"].insert({"space in name": "hello"}) db["test"].enable_fts(["space in name"]) --------------------------------------------------------------------------- OperationalError Traceback (most recent call last) <ipython-input-8-ce4b87dd1c7a> in <module> ----> 1 db['test'].enable_fts(["space in name"]) /usr/local/lib/python3.7/site-packages/sqlite_utils/db.py in enable_fts(self, columns, fts_version, create_triggers) 755 ) 756 self.db.conn.executescript(sql) --> 757 self.populate_fts(columns) 758 759 if create_triggers: /usr/local/lib/python3.7/site-packages/sqlite_utils/db.py in populate_fts(self, columns) 787 table=self.name, columns=", ".join(columns) 788 ) --> 789 self.db.conn.executescript(sql) 790 return self 791 OperationalError: near "in": syntax error ``` | sqlite-utils 140912432 | issue | {"url": "https://api.github.com/repos/simonw/sqlite-utils/issues/90/reactions", "total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | completed | ||||||
577302229 | MDU6SXNzdWU1NzczMDIyMjk= | 91 | Enable ordering FTS results by rank | gfrmin 416374 | closed | 0 | 3.0 6079500 | 1 | 2020-03-07T08:43:51Z | 2020-11-06T23:53:26Z | 2020-11-06T23:53:25Z | NONE | According to https://www.sqlite.org/fts5.html (not sure about FTS4) results can be sorted by relevance. At the moment results are returned by default by `rowid`. Perhaps a flag can be added to the `search` method? | sqlite-utils 140912432 | issue | {"url": "https://api.github.com/repos/simonw/sqlite-utils/issues/91/reactions", "total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | completed | |||||
581339961 | MDU6SXNzdWU1ODEzMzk5NjE= | 92 | .columns_dict doesn't work for all possible column types | simonw 9599 | closed | 0 | 7 | 2020-03-14T19:30:35Z | 2020-03-15T18:37:43Z | 2020-03-14T20:04:14Z | OWNER | Got this error: ``` File ".../python3.7/site-packages/sqlite_utils/db.py", line 462, in <dictcomp> for column in self.columns KeyError: 'REAL' ``` `.columns_dict` uses `REVERSE_COLUMN_TYPE_MAPPING`: https://github.com/simonw/sqlite-utils/blob/43f1c6ab4e3a6b76531fb6f5447adb83d26f3971/sqlite_utils/db.py#L457-L463 `REVERSE_COLUMN_TYPE_MAPPING` defines `FLOAT` not `REAL`A https://github.com/simonw/sqlite-utils/blob/43f1c6ab4e3a6b76531fb6f5447adb83d26f3971/sqlite_utils/db.py#L68-L74 | sqlite-utils 140912432 | issue | {"url": "https://api.github.com/repos/simonw/sqlite-utils/issues/92/reactions", "total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | completed | ||||||
581795570 | MDU6SXNzdWU1ODE3OTU1NzA= | 93 | Support more string values for types in .add_column() | simonw 9599 | open | 0 | 0 | 2020-03-15T19:32:49Z | 2020-09-24T20:36:46Z | OWNER | https://sqlite-utils.readthedocs.io/en/2.4.2/python-api.html#adding-columns says: > SQLite types you can specify are "TEXT", "INTEGER", "FLOAT" or "BLOB". As discovered in #92 this isn't the right list of values. I should expand this to match https://www.sqlite.org/datatype3.html | sqlite-utils 140912432 | issue | {"url": "https://api.github.com/repos/simonw/sqlite-utils/issues/93/reactions", "total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | ||||||||
586477757 | MDU6SXNzdWU1ODY0Nzc3NTc= | 94 | If column data is a mixture of integers and nulls, detected type should be INTEGER | simonw 9599 | closed | 0 | 0 | 2020-03-23T19:51:46Z | 2020-03-23T19:57:10Z | 2020-03-23T19:57:10Z | OWNER | It looks like detected type for that case is TEXT at the moment. | sqlite-utils 140912432 | issue | {"url": "https://api.github.com/repos/simonw/sqlite-utils/issues/94/reactions", "total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | completed | ||||||
586486367 | MDU6SXNzdWU1ODY0ODYzNjc= | 95 | Columns with only null values are no longer created in the database | simonw 9599 | closed | 0 | 0 | 2020-03-23T20:07:42Z | 2020-03-23T20:31:15Z | 2020-03-23T20:31:15Z | OWNER | Bug introduced in #94, and released in `2.4.3`. | sqlite-utils 140912432 | issue | {"url": "https://api.github.com/repos/simonw/sqlite-utils/issues/95/reactions", "total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | completed | ||||||
593751293 | MDU6SXNzdWU1OTM3NTEyOTM= | 97 | Adding a "recreate" flag to the `Database` constructor | betatim 1448859 | closed | 0 | 4 | 2020-04-04T05:41:10Z | 2020-04-15T14:29:31Z | 2020-04-13T03:52:29Z | NONE | I have a [script](https://github.com/betatim/binder-datasette/blob/master/create-db.ipynb) that imports data into a sqlite DB. When I re-run that script I'd like to remove the existing sqlite DB, instead of adding to it. The pragmatic answer is to add the check and file deletion to my script. However I thought it would be easy and useful for others to add a `recreate=True` flag to `db = sqlite_utils.Database("binder-launches.db")`. After taking a look at the code for it I am not so sure any more. This is because the connection string could be a URL (or "connection string") like `"file:///tmp/foo.db"`. I don't know what the equivalent of `os.path.exists()` is for a connection string or how to detect that something is a connection string and raise an error "can't use recreate=True and conn_string at the same time". Does anyone have an idea/suggestion where to start investigating? | sqlite-utils 140912432 | issue | {"url": "https://api.github.com/repos/simonw/sqlite-utils/issues/97/reactions", "total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | completed | ||||||
597671518 | MDU6SXNzdWU1OTc2NzE1MTg= | 98 | Only set .last_rowid and .last_pk for single update/inserts, not for .insert_all()/.upsert_all() with multiple records | simonw 9599 | closed | 0 | 7 | 2020-04-10T03:19:40Z | 2021-09-28T04:38:44Z | 2020-04-13T03:29:15Z | OWNER | sqlite-utils 140912432 | issue | {"url": "https://api.github.com/repos/simonw/sqlite-utils/issues/98/reactions", "total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | completed | |||||||
598640234 | MDU6SXNzdWU1OTg2NDAyMzQ= | 99 | .upsert_all() should maybe error if dictionaries passed to it do not have the same keys | simonw 9599 | closed | 0 | 2 | 2020-04-13T03:02:25Z | 2020-04-13T03:05:20Z | 2020-04-13T03:05:04Z | OWNER | While investigating #98 I stumbled across this: ``` def test_upsert_compound_primary_key(fresh_db): table = fresh_db["table"] table.upsert_all( [ {"species": "dog", "id": 1, "name": "Cleo", "age": 4}, {"species": "cat", "id": 1, "name": "Catbag"}, ], pk=("species", "id"), ) table.upsert_all( [ {"species": "dog", "id": 1, "age": 5}, {"species": "dog", "id": 2, "name": "New Dog", "age": 1}, ], pk=("species", "id"), ) > assert [ {"species": "dog", "id": 1, "name": "Cleo", "age": 5}, {"species": "cat", "id": 1, "name": "Catbag", "age": None}, {"species": "dog", "id": 2, "name": "New Dog", "age": 1}, ] == list(table.rows) E AssertionError: assert [{'age': 5, '...cies': 'dog'}] == [{'age': 5, '...cies': 'dog'}] E At index 0 diff: {'species': 'dog', 'id': 1, 'name': 'Cleo', 'age': 5} != {'species': 'dog', 'id': 1, 'name': None, 'age': 5} E Full diff: E - [{'age': 5, 'id': 1, 'name': 'Cleo', 'species': 'dog'}, E ? ^^^ -- E + [{'age': 5, 'id': 1, 'name': None, 'species': 'dog'}, E ? ^^^ E {'age': None, 'id': 1, 'name': 'Catbag', 'species': 'cat'}, E {'age': 1, 'id': 2, 'name': 'New Dog', 'species': 'dog'}] ``` If you run `.upsert_all()` with multiple dictionaries it doesn't quite have the effect you might expect. | sqlite-utils 140912432 | issue | {"url": "https://api.github.com/repos/simonw/sqlite-utils/issues/99/reactions", "total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | completed | ||||||
601358649 | MDU6SXNzdWU2MDEzNTg2NDk= | 100 | Mechanism for forcing column-type, over-riding auto-detection | simonw 9599 | closed | 0 | 3 | 2020-04-16T19:12:52Z | 2020-04-17T23:53:32Z | 2020-04-17T23:53:32Z | OWNER | As seen in https://github.com/dogsheep/github-to-sqlite/issues/27#issuecomment-614843406 - there's a problem where you insert a record with a `None` value for a column and that column is created as `TEXT` - but actually you intended it to be an `INT` (as later examples will demonstrate). Some kind of mechanism for over-riding the detected types of columns would be useful here. | sqlite-utils 140912432 | issue | {"url": "https://api.github.com/repos/simonw/sqlite-utils/issues/100/reactions", "total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | completed | ||||||
601392318 | MDU6SXNzdWU2MDEzOTIzMTg= | 101 | README should include an example of CLI data insertion | simonw 9599 | closed | 0 | 0 | 2020-04-16T19:45:37Z | 2020-04-17T23:59:49Z | 2020-04-17T23:59:49Z | OWNER | Maybe using `curl` from the GitHub API. | sqlite-utils 140912432 | issue | {"url": "https://api.github.com/repos/simonw/sqlite-utils/issues/101/reactions", "total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | completed | ||||||
602569315 | MDU6SXNzdWU2MDI1NjkzMTU= | 102 | Can't store an array or dictionary containing a bytes value | simonw 9599 | closed | 0 | 0 | 2020-04-18T22:49:21Z | 2020-05-01T20:45:45Z | 2020-05-01T20:45:45Z | OWNER | ``` In [1]: import sqlite_utils In [2]: db = sqlite_utils.Database(memory=True) In [3]: db["t"].insert({"id": 1, "data": {"foo": b"bytes"}}) --------------------------------------------------------------------------- TypeError Traceback (most recent call last) <ipython-input-3-a8ab1f72c72c> in <module> ----> 1 db["t"].insert({"id": 1, "data": {"foo": b"bytes"}}) ~/Dropbox/Development/sqlite-utils/sqlite_utils/db.py in insert(self, record, pk, foreign_keys, column_order, not_null, defaults, hash_id, alter, ignore, replace, extracts, conversions, columns) 950 extracts=extracts, 951 conversions=conversions, --> 952 columns=columns, 953 ) 954 ~/Dropbox/Development/sqlite-utils/sqlite_utils/db.py in insert_all(self, records, pk, foreign_keys, column_order, not_null, defaults, batch_size, hash_id, alter, ignore, replace, extracts, conversions, columns, upsert) 1052 for key in all_columns: 1053 value = jsonify_if_needed( -> 1054 record.get(key, None if key != hash_id else _hash(record)) 1055 ) 1056 if key in extracts: ~/Dropbox/Development/sqlite-utils/sqlite_utils/db.py in jsonify_if_needed(value) 1318 def jsonify_if_needed(value): 1319 if isinstance(value, (dict, list, tuple)): -> 1320 return json.dumps(value) 1321 elif isinstance(value, (datetime.time, datetime.date, datetime.datetime)): 1322 return value.isoformat() /usr/local/Cellar/python/3.7.4_1/Frameworks/Python.framework/Versions/3.7/lib/python3.7/json/__init__.py in dumps(obj, skipkeys, ensure_ascii, check_circular, allow_nan, cls, indent, separators, default, sort_keys, **kw) 229 cls is None and indent is None and separators is None and 230 default is None… | sqlite-utils 140912432 | issue | {"url": "https://api.github.com/repos/simonw/sqlite-utils/issues/102/reactions", "total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | completed | ||||||
610517472 | MDU6SXNzdWU2MTA1MTc0NzI= | 103 | sqlite3.OperationalError: too many SQL variables in insert_all when using rows with varying numbers of columns | b0b5h4rp13 32605365 | closed | 0 | 8 | 2020-05-01T02:26:14Z | 2020-05-14T00:18:57Z | 2020-05-14T00:18:57Z | CONTRIBUTOR | If using insert_all to put in 1000 rows of data with varying number of columns, it comes up with this message `sqlite3.OperationalError: too many SQL variables` if the number of columns is larger in later records (past the first row) I've reduced `SQLITE_MAX_VARS` by 100 to 899 at the top of `db.py` to add wiggle room, so that if the column count increases it wont go past SQLite's batch limit as calculated by this line of code based on the count of the first row's dict keys batch_size = max(1, min(batch_size, SQLITE_MAX_VARS // num_columns)) | sqlite-utils 140912432 | issue | {"url": "https://api.github.com/repos/simonw/sqlite-utils/issues/103/reactions", "total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | completed | ||||||
610853393 | MDU6SXNzdWU2MTA4NTMzOTM= | 104 | --schema option to "sqlite-utils tables" | simonw 9599 | closed | 0 | 0 | 2020-05-01T16:55:49Z | 2020-05-01T17:12:37Z | 2020-05-01T17:12:37Z | OWNER | Adds output showing the table schema. | sqlite-utils 140912432 | issue | {"url": "https://api.github.com/repos/simonw/sqlite-utils/issues/104/reactions", "total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | completed | ||||||
610853576 | MDU6SXNzdWU2MTA4NTM1NzY= | 105 | "sqlite-utils views" command | simonw 9599 | closed | 0 | 1 | 2020-05-01T16:56:11Z | 2020-05-01T20:40:07Z | 2020-05-01T20:38:36Z | OWNER | Similar to `sqlite-utils tables`. See also #104. | sqlite-utils 140912432 | issue | {"url": "https://api.github.com/repos/simonw/sqlite-utils/issues/105/reactions", "total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | completed | ||||||
611216862 | MDU6SXNzdWU2MTEyMTY4NjI= | 106 | create_view(..., ignore=True, replace=True) parameters | simonw 9599 | closed | 0 | 1 | 2020-05-02T15:45:21Z | 2020-05-02T16:04:51Z | 2020-05-02T16:02:10Z | OWNER | Two new parameters which specify what should happen if the view already exists. I want this for https://github.com/dogsheep/github-to-sqlite/issues/37 Here's the current `create_view()` implementation: https://github.com/simonw/sqlite-utils/blob/b4d953d3ccef28bb81cea40ca165a647b59971fa/sqlite_utils/db.py#L325-L332 `ignore=True` will not do anything if the view exists already. `replace=True` will drop and redefine the view - but only if its SQL definition differs, otherwise it will be left alone. | sqlite-utils 140912432 | issue | {"url": "https://api.github.com/repos/simonw/sqlite-utils/issues/106/reactions", "total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | completed | ||||||
611222968 | MDU6SXNzdWU2MTEyMjI5Njg= | 107 | sqlite-utils create-view CLI command | simonw 9599 | closed | 0 | 2 | 2020-05-02T16:15:13Z | 2020-05-03T15:36:58Z | 2020-05-03T15:36:37Z | OWNER | Can go with #27 - `sqlite-utils create-table`. | sqlite-utils 140912432 | issue | {"url": "https://api.github.com/repos/simonw/sqlite-utils/issues/107/reactions", "total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | completed | ||||||
611326701 | MDU6SXNzdWU2MTEzMjY3MDE= | 108 | Documentation unit tests for CLI commands | simonw 9599 | closed | 0 | 2 | 2020-05-03T03:58:42Z | 2020-05-03T04:13:57Z | 2020-05-03T04:13:57Z | OWNER | Have a test that ensures all CLI commands are documented. | sqlite-utils 140912432 | issue | {"url": "https://api.github.com/repos/simonw/sqlite-utils/issues/108/reactions", "total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | completed | ||||||
612658444 | MDU6SXNzdWU2MTI2NTg0NDQ= | 109 | table.create_index(..., ignore=True) | simonw 9599 | closed | 0 | 1 | 2020-05-05T14:44:21Z | 2020-05-05T14:46:53Z | 2020-05-05T14:46:53Z | OWNER | Option to silently do nothing if the index already exists. | sqlite-utils 140912432 | issue | {"url": "https://api.github.com/repos/simonw/sqlite-utils/issues/109/reactions", "total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | completed | ||||||
613755043 | MDU6SXNzdWU2MTM3NTUwNDM= | 110 | Support decimal.Decimal type | dvhthomas 134771 | closed | 0 | 6 | 2020-05-07T03:57:19Z | 2020-05-11T01:58:20Z | 2020-05-11T01:50:11Z | NONE | Decimal types in Postgres cause a failure in db.py data type selection --- I have a Django app using a MoneyField, which uses a `numeric(14,0)` data type in Postgres (https://www.postgresql.org/docs/9.3/datatype-numeric.html). When attempting to export that table I get the following error: ```bash $ db-to-sqlite --table isaweb_proposal "postgres://connection" test.db .... column_type=COLUMN_TYPE_MAPPING[column_type], KeyError: <class 'decimal.Decimal'> ``` Looking at `sql_utils.db.py` at 292-ish it's clear that there is no matching type for what I assume SQLAlchemy interprets as Python decimal.Decimal. From the [SQLite docs](https://www.sqlite.org/datatype3.html#affinity_name_examples) it looks like DECIMAL in other DBs are considered numeric. I'm not quite sure if it's as simple as adding a data type to that list or if there are repercussions beyond it. Thanks for a great tool! | sqlite-utils 140912432 | issue | {"url": "https://api.github.com/repos/simonw/sqlite-utils/issues/110/reactions", "total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | completed | ||||||
615477131 | MDU6SXNzdWU2MTU0NzcxMzE= | 111 | sqlite-utils drop-table and drop-view commands | simonw 9599 | closed | 0 | 2 | 2020-05-10T21:10:42Z | 2020-05-11T01:58:36Z | 2020-05-11T00:44:26Z | OWNER | Would be useful to be able to drop views and tables from the CLI. | sqlite-utils 140912432 | issue | {"url": "https://api.github.com/repos/simonw/sqlite-utils/issues/111/reactions", "total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | completed | ||||||
616271236 | MDU6SXNzdWU2MTYyNzEyMzY= | 112 | add_foreign_key(...., ignore=True) | simonw 9599 | closed | 0 | 2.19 5896742 | 4 | 2020-05-12T00:24:00Z | 2020-09-20T22:17:34Z | 2020-09-20T22:17:34Z | OWNER | When using this library I often find myself wanting to "add this foreign key, but only if it doesn't exist yet". The `ignore=True` parameter is increasingly being used for this else where in the library (e.g. in `create_view()`). | sqlite-utils 140912432 | issue | {"url": "https://api.github.com/repos/simonw/sqlite-utils/issues/112/reactions", "total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | completed | |||||
621286870 | MDU6SXNzdWU2MjEyODY4NzA= | 113 | Syntactic sugar for ATTACH DATABASE | simonw 9599 | closed | 0 | 2 | 2020-05-19T21:10:00Z | 2021-02-19T05:09:12Z | 2021-02-19T04:56:36Z | OWNER | https://www.sqlite.org/lang_attach.html Maybe something like this: ```python db.attach("other_db", "other_db.db") ``` | sqlite-utils 140912432 | issue | {"url": "https://api.github.com/repos/simonw/sqlite-utils/issues/113/reactions", "total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | completed | ||||||
621989740 | MDU6SXNzdWU2MjE5ODk3NDA= | 114 | table.transform() method for advanced alter table | simonw 9599 | closed | 0 | 2.20 5897911 | 26 | 2020-05-20T18:20:46Z | 2020-09-22T07:51:37Z | 2020-09-22T04:20:02Z | OWNER | SQLite's `ALTER TABLE` can only do the following: * Rename a table * Rename a column * Add a column Notably, it cannot drop columns - so tricks like "add a float version of this text column, populate it, then drop the old one and rename" won't work. The docs here https://www.sqlite.org/lang_altertable.html#making_other_kinds_of_table_schema_changes describe a way of implementing full alters safely within a transaction, but it's fiddly. 1. Create new table 2. Copy data 3. Drop old table 4. Rename new into old It would be great if `sqlite-utils` provided an abstraction to help make these kinds of changes safely. | sqlite-utils 140912432 | issue | {"url": "https://api.github.com/repos/simonw/sqlite-utils/issues/114/reactions", "total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | completed | |||||
637889964 | MDU6SXNzdWU2Mzc4ODk5NjQ= | 115 | Ability to execute insert/update statements with the CLI | simonw 9599 | closed | 0 | 1 | 2020-06-12T17:01:17Z | 2020-06-12T17:51:11Z | 2020-06-12T17:41:10Z | OWNER | ``` $ sqlite-utils github.db "update stars set starred_at = ''" Traceback (most recent call last): File "/Users/simon/.local/bin/sqlite-utils", line 8, in <module> sys.exit(cli()) File "/Users/simon/.local/pipx/venvs/sqlite-utils/lib/python3.8/site-packages/click/core.py", line 829, in __call__ return self.main(*args, **kwargs) File "/Users/simon/.local/pipx/venvs/sqlite-utils/lib/python3.8/site-packages/click/core.py", line 782, in main rv = self.invoke(ctx) File "/Users/simon/.local/pipx/venvs/sqlite-utils/lib/python3.8/site-packages/click/core.py", line 1259, in invoke return _process_result(sub_ctx.command.invoke(sub_ctx)) File "/Users/simon/.local/pipx/venvs/sqlite-utils/lib/python3.8/site-packages/click/core.py", line 1066, in invoke return ctx.invoke(self.callback, **ctx.params) File "/Users/simon/.local/pipx/venvs/sqlite-utils/lib/python3.8/site-packages/click/core.py", line 610, in invoke return callback(*args, **kwargs) File "/Users/simon/.local/pipx/venvs/sqlite-utils/lib/python3.8/site-packages/sqlite_utils/cli.py", line 673, in query headers = [c[0] for c in cursor.description] TypeError: 'NoneType' object is not iterable ``` | sqlite-utils 140912432 | issue | {"url": "https://api.github.com/repos/simonw/sqlite-utils/issues/115/reactions", "total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | completed | ||||||
644122661 | MDU6SXNzdWU2NDQxMjI2NjE= | 116 | Documentation for table.pks introspection property | simonw 9599 | closed | 0 | 2 | 2020-06-23T20:27:24Z | 2020-06-23T21:21:33Z | 2020-06-23T21:03:14Z | OWNER | https://github.com/simonw/sqlite-utils/blob/4d9a3204361d956440307a57bd18c829a15861db/sqlite_utils/db.py#L535-L540 | sqlite-utils 140912432 | issue | {"url": "https://api.github.com/repos/simonw/sqlite-utils/issues/116/reactions", "total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | completed | ||||||
644161221 | MDU6SXNzdWU2NDQxNjEyMjE= | 117 | Support for compound (composite) foreign keys | simonw 9599 | open | 0 | 3 | 2020-06-23T21:33:42Z | 2020-06-23T21:40:31Z | OWNER | It turns out SQLite supports composite foreign keys: https://www.sqlite.org/foreignkeys.html#fk_composite Their example looks like this: ```sql CREATE TABLE album( albumartist TEXT, albumname TEXT, albumcover BINARY, PRIMARY KEY(albumartist, albumname) ); CREATE TABLE song( songid INTEGER, songartist TEXT, songalbum TEXT, songname TEXT, FOREIGN KEY(songartist, songalbum) REFERENCES album(albumartist, albumname) ); ``` Here's what that looks like in sqlite-utils: ``` In [1]: import sqlite_utils In [2]: import sqlite3 In [3]: conn = sqlite3.connect(":memory:") In [4]: conn Out[4]: <sqlite3.Connection at 0x1087186c0> In [5]: conn.executescript(""" ...: CREATE TABLE album( ...: albumartist TEXT, ...: albumname TEXT, ...: albumcover BINARY, ...: PRIMARY KEY(albumartist, albumname) ...: ); ...: ...: CREATE TABLE song( ...: songid INTEGER, ...: songartist TEXT, ...: songalbum TEXT, ...: songname TEXT, ...: FOREIGN KEY(songartist, songalbum) REFERENCES album(albumartist, albumname) ...: ); ...: """) Out[5]: <sqlite3.Cursor at 0x1088def10> In [6]: db = sqlite_utils.Database(conn) In [7]: db.tables … | sqlite-utils 140912432 | issue | {"url": "https://api.github.com/repos/simonw/sqlite-utils/issues/117/reactions", "total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | ||||||||
652700770 | MDU6SXNzdWU2NTI3MDA3NzA= | 119 | Ability to remove a foreign key | simonw 9599 | closed | 0 | 3 | 2020-07-07T22:31:37Z | 2020-09-24T20:36:59Z | 2020-09-24T20:36:59Z | OWNER | Useful if you add one but make a mistake and need to undo it without recreating the database from scratch. | sqlite-utils 140912432 | issue | {"url": "https://api.github.com/repos/simonw/sqlite-utils/issues/119/reactions", "total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | completed | ||||||
652961907 | MDU6SXNzdWU2NTI5NjE5MDc= | 121 | Improved (and better documented) support for transactions | simonw 9599 | open | 0 | 3 | 2020-07-08T04:56:51Z | 2020-09-24T20:36:46Z | OWNER | _Originally posted by @simonw in https://github.com/simonw/sqlite-utils/pull/118#issuecomment-655283393_ We should put some thought into how this library supports and encourages smart use of transactions. | sqlite-utils 140912432 | issue | {"url": "https://api.github.com/repos/simonw/sqlite-utils/issues/121/reactions", "total_count": 1, "+1": 1, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | ||||||||
665700495 | MDU6SXNzdWU2NjU3MDA0OTU= | 122 | CLI utility for inserting binary files into SQLite | simonw 9599 | closed | 0 | 10 | 2020-07-26T03:27:39Z | 2020-07-27T07:10:41Z | 2020-07-27T07:09:03Z | OWNER | SQLite BLOB columns can store entire binary files. The challenge is inserting them, since they don't neatly fit into JSON objects. It would be great if the `sqlite-utils` CLI had a trick for helping with this. Inspired by https://github.com/simonw/datasette-media/issues/14 | sqlite-utils 140912432 | issue | {"url": "https://api.github.com/repos/simonw/sqlite-utils/issues/122/reactions", "total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | completed | ||||||
665701216 | MDU6SXNzdWU2NjU3MDEyMTY= | 123 | --raw option for outputting binary content | simonw 9599 | closed | 0 | 0 | 2020-07-26T03:35:39Z | 2020-07-26T16:44:11Z | 2020-07-26T16:44:11Z | OWNER | Related to the `insert-files` work in #122 - it should be easy to get binary data back out of the database again. One way to do that could be: sqlite-utils files.db "select content from files where key = 'foo.jpg'" --raw The `--raw` option would cause just the contents of the first column to be output directly to stdout. | sqlite-utils 140912432 | issue | {"url": "https://api.github.com/repos/simonw/sqlite-utils/issues/123/reactions", "total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | completed | ||||||
665802405 | MDU6SXNzdWU2NjU4MDI0MDU= | 124 | sqlite-utils query should support named parameters | simonw 9599 | closed | 0 | 1 | 2020-07-26T15:25:10Z | 2020-07-30T22:57:51Z | 2020-07-27T03:53:58Z | OWNER | To help out with escaping - so you can run this: sqlite-utils query "insert into foo (blah) values (:blah)" --param blah `something here` | sqlite-utils 140912432 | issue | {"url": "https://api.github.com/repos/simonw/sqlite-utils/issues/124/reactions", "total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | completed | ||||||
665817570 | MDU6SXNzdWU2NjU4MTc1NzA= | 125 | Output binary columns in "sqlite-utils query" JSON | simonw 9599 | closed | 0 | 4 | 2020-07-26T16:47:02Z | 2020-07-27T00:49:41Z | 2020-07-27T00:48:45Z | OWNER | You get an error if you try to run a query that returns data from a BLOB. | sqlite-utils 140912432 | issue | {"url": "https://api.github.com/repos/simonw/sqlite-utils/issues/125/reactions", "total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | completed | ||||||
665819048 | MDU6SXNzdWU2NjU4MTkwNDg= | 126 | Ability to insert binary data on the CLI using JSON | simonw 9599 | closed | 0 | 2 | 2020-07-26T16:54:14Z | 2020-07-27T04:00:33Z | 2020-07-27T03:59:45Z | OWNER | > I could solve round tripping (at least a bit) by allowing insert to be run with a flag that says "these columns are base64 encoded, store the decoded data in a BLOB". > > That would solve inserting binary data using JSON too. _Originally posted by @simonw in https://github.com/simonw/sqlite-utils/issues/125#issuecomment-664012247_ | sqlite-utils 140912432 | issue | {"url": "https://api.github.com/repos/simonw/sqlite-utils/issues/126/reactions", "total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | completed | ||||||
666040390 | MDU6SXNzdWU2NjYwNDAzOTA= | 127 | Ability to insert files piped to insert-files stdin | simonw 9599 | closed | 0 | 3 | 2020-07-27T07:09:33Z | 2020-07-30T03:08:52Z | 2020-07-30T03:08:18Z | OWNER | > Inserting files by piping them in should work - but since a filename cannot be derived this will need a `--name blah.gif` option. > > cat blah.gif | sqlite-utils insert-files files.db files - --name=blah.gif > _Originally posted by @simonw in https://github.com/simonw/sqlite-utils/issues/122#issuecomment-664128071_ | sqlite-utils 140912432 | issue | {"url": "https://api.github.com/repos/simonw/sqlite-utils/issues/127/reactions", "total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | completed |
Advanced export
JSON shape: default, array, newline-delimited, object
CREATE TABLE [issues] ( [id] INTEGER PRIMARY KEY, [node_id] TEXT, [number] INTEGER, [title] TEXT, [user] INTEGER REFERENCES [users]([id]), [state] TEXT, [locked] INTEGER, [assignee] INTEGER REFERENCES [users]([id]), [milestone] INTEGER REFERENCES [milestones]([id]), [comments] INTEGER, [created_at] TEXT, [updated_at] TEXT, [closed_at] TEXT, [author_association] TEXT, [pull_request] TEXT, [body] TEXT, [repo] INTEGER REFERENCES [repos]([id]), [type] TEXT , [active_lock_reason] TEXT, [performed_via_github_app] TEXT, [reactions] TEXT, [draft] INTEGER, [state_reason] TEXT); CREATE INDEX [idx_issues_repo] ON [issues] ([repo]); CREATE INDEX [idx_issues_milestone] ON [issues] ([milestone]); CREATE INDEX [idx_issues_assignee] ON [issues] ([assignee]); CREATE INDEX [idx_issues_user] ON [issues] ([user]);