github
html_url | issue_url | id | node_id | user | created_at | updated_at | author_association | body | reactions | issue | performed_via_github_app |
---|---|---|---|---|---|---|---|---|---|---|---|
https://github.com/simonw/sqlite-utils/issues/402#issuecomment-1030902102 | https://api.github.com/repos/simonw/sqlite-utils/issues/402 | 1030902102 | IC_kwDOCGYnMM49clFW | 9599 | 2022-02-06T19:53:34Z | 2022-02-08T07:40:34Z | OWNER | I like the idea that the contract for `Conversion` (or rather for its subclasses) is that it can wrap a Python value and then return both the SQL fragment - e.g. `GeomFromText(?, 4326)` - and the values that should be used as the SQL parameters. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 1125297737 | |
https://github.com/simonw/sqlite-utils/issues/402#issuecomment-1030904948 | https://api.github.com/repos/simonw/sqlite-utils/issues/402 | 1030904948 | IC_kwDOCGYnMM49clx0 | 9599 | 2022-02-06T20:09:42Z | 2022-02-08T07:40:44Z | OWNER | I think this is the code that needs to become aware of this system: https://github.com/simonw/sqlite-utils/blob/fea8c9bcc509bcae75e99ae8870f520103b9aa58/sqlite_utils/db.py#L2453-L2469 There's an earlier branch that runs for upserts which needs to be modified too: https://github.com/simonw/sqlite-utils/blob/fea8c9bcc509bcae75e99ae8870f520103b9aa58/sqlite_utils/db.py#L2417-L2440 | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 1125297737 | |
https://github.com/simonw/sqlite-utils/issues/402#issuecomment-1032294365 | https://api.github.com/repos/simonw/sqlite-utils/issues/402 | 1032294365 | IC_kwDOCGYnMM49h4_d | 9599 | 2022-02-08T07:32:09Z | 2022-02-08T07:34:41Z | OWNER | I have an idea for how that third option could work - the one that creates a new column using values from the existing ones: ```python db["places"].insert( { "name": "London", "lng": -0.118092, "lat": 51.509865, }, conversions={"point": LongitudeLatitude("lng", "lat")}, ) ``` How about specifying that the values in that `conversion=` dictionary can be: - A SQL string fragment (as currently implemented) - A subclass of `Conversion` as described above - Or... a callable function that takes the row as an argument and returns either a `Conversion` subclass instance or a literal value to be jnserted into the database (a string, int or float) Then you could do this: ```python db["places"].insert( { "name": "London", "lng": -0.118092, "lat": 51.509865, }, conversions={ "point": lambda row: LongitudeLatitude( row["lng"], row["lat"] ) } ) ``` Something I really like about this is that it expands the abilities of `conversions=` beyond the slightly obscure need to customize the SQL fragment into something that can solve other data insertion cleanup problems too. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 1125297737 | |
https://github.com/simonw/sqlite-utils/issues/402#issuecomment-1032296717 | https://api.github.com/repos/simonw/sqlite-utils/issues/402 | 1032296717 | IC_kwDOCGYnMM49h5kN | 9599 | 2022-02-08T07:35:46Z | 2022-02-08T07:35:46Z | OWNER | I'm going to write the documentation for this first, before the implementation, so I can see if it explains cleanly enough that the design appears to be sound. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 1125297737 | |
https://github.com/simonw/sqlite-utils/issues/26#issuecomment-1032120014 | https://api.github.com/repos/simonw/sqlite-utils/issues/26 | 1032120014 | IC_kwDOCGYnMM49hObO | 536941 | 2022-02-08T01:32:34Z | 2022-02-08T01:32:34Z | CONTRIBUTOR | if you are curious about prior art, https://github.com/jsnell/json-to-multicsv is really good! | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 455486286 | |
https://github.com/simonw/sqlite-utils/issues/403#issuecomment-1032126353 | https://api.github.com/repos/simonw/sqlite-utils/issues/403 | 1032126353 | IC_kwDOCGYnMM49hP-R | 536941 | 2022-02-08T01:45:15Z | 2022-02-08T01:45:31Z | CONTRIBUTOR | you can hack something like this to achieve this result: `sqlite-utils convert my_database my_table rowid "{'id': value}" --multi` | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 1126692066 | |
https://github.com/simonw/sqlite-utils/issues/402#issuecomment-1032732242 | https://api.github.com/repos/simonw/sqlite-utils/issues/402 | 1032732242 | IC_kwDOCGYnMM49jj5S | 25778 | 2022-02-08T15:26:59Z | 2022-02-08T15:26:59Z | CONTRIBUTOR | What if you did something like this: ```python class Conversion: def __init__(self, *args, **kwargs): "Put whatever settings you need here" def python(self, row, column, value): # not sure on args here "Python step to transform value" return value def sql(self, row, column, value): "Return the actual sql that goes in the insert/update step, and maybe params" # value is the return of self.python() return value, [] ``` This way, you're always passing an instance, which has methods that do the conversion. (Or you're passing a SQL string, as you would now.) The `__init__` could take column names, or SRID, or whatever other setup state you need per row, but the row is getting processed with the `python` and `sql` methods (or whatever you want to call them). This is pretty rough, so do what you will with names and args and such. You'd then use it like this: ```python # subclass might be unneeded here, if methods are present class LngLatConversion(Conversion): def __init__(self, x="longitude", y="latitude"): self.x = x self.y = y def python(self, row, column, value): x = row[self.x] y = row[self.y] return x, y def sql(self, row, column, value): # value is now a tuple, returned above s = "GeomFromText(POINT(? ?))" return s, value table.insert_all(rows, conversions={"point": LngLatConversion("lng", "lat"))} ``` I haven't thought through all the implementation details here, and it'll probably break in ways I haven't foreseen, but wanted to get this idea out of my head. Hope it helps. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 1125297737 | |
https://github.com/simonw/sqlite-utils/issues/403#issuecomment-1032976720 | https://api.github.com/repos/simonw/sqlite-utils/issues/403 | 1032976720 | IC_kwDOCGYnMM49kflQ | 9599 | 2022-02-08T19:23:05Z | 2022-02-08T19:23:27Z | OWNER | This is already possible using `sqlite-utils transform` like so: ``` % echo '[{"name": "Barry"}, {"name": "Sandra"}]' | sqlite-utils insert rowid.db records - % sqlite-utils schema rowid.db CREATE TABLE [records] ( [name] TEXT ); % sqlite-utils rows rowid.db records [{"name": "Barry"}, {"name": "Sandra"}] % sqlite-utils transform rowid.db records --pk id % sqlite-utils rows rowid.db records [{"id": 1, "name": "Barry"}, {"id": 2, "name": "Sandra"}] % sqlite-utils schema rowid.db CREATE TABLE "records" ( [id] INTEGER PRIMARY KEY, [name] TEXT ); % echo '[{"name": "Barry 2"}, {"name": "Sandra 2"}]' | sqlite-utils insert rowid.db records - % sqlite-utils rows rowid.db records [{"id": 1, "name": "Barry"}, {"id": 2, "name": "Sandra"}, {"id": 3, "name": "Barry 2"}, {"id": 4, "name": "Sandra 2"}] ``` It's not covered in the documentation though: https://sqlite-utils.datasette.io/en/3.23/cli.html#transforming-tables | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 1126692066 | |
https://github.com/simonw/sqlite-utils/issues/403#issuecomment-1032987901 | https://api.github.com/repos/simonw/sqlite-utils/issues/403 | 1032987901 | IC_kwDOCGYnMM49kiT9 | 9599 | 2022-02-08T19:36:06Z | 2022-02-08T19:36:06Z | OWNER | New documentation: https://sqlite-utils.datasette.io/en/latest/cli.html#adding-a-primary-key-to-a-rowid-table | {"total_count": 3, "+1": 3, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 1126692066 |