issue_comments: 1336094381

This data as json

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/datasette/issues/1878#issuecomment-1336094381	https://api.github.com/repos/simonw/datasette/issues/1878	1336094381	IC_kwDOBm6k_c5Poy6t	9599	2022-12-03T06:26:25Z	2022-12-03T06:26:25Z	OWNER	Initial prototype: ```diff diff --git a/datasette/app.py b/datasette/app.py index 125b4969..282c0984 100644 --- a/datasette/app.py +++ b/datasette/app.py @@ -40,7 +40,7 @@ from .views.special import ( PermissionsDebugView, MessagesDebugView, ) -from .views.table import TableView, TableInsertView, TableDropView +from .views.table import TableView, TableInsertView, TableUpsertView, TableDropView from .views.row import RowView, RowDeleteView, RowUpdateView from .renderer import json_renderer from .url_builder import Urls @@ -1292,6 +1292,10 @@ class Datasette: TableInsertView.as_view(self), r"/(?P<database>[^\/\.]+)/(?P<table>[^\/\.]+)/-/insert$", ) + add_route( + TableUpsertView.as_view(self), + r"/(?P<database>[^\/\.]+)/(?P<table>[^\/\.]+)/-/upsert$", + ) add_route( TableDropView.as_view(self), r"/(?P<database>[^\/\.]+)/(?P<table>[^\/\.]+)/-/drop$", diff --git a/datasette/views/table.py b/datasette/views/table.py index 7ba78c11..ae0d6366 100644 --- a/datasette/views/table.py +++ b/datasette/views/table.py @@ -1074,9 +1074,15 @@ class TableInsertView(BaseView): def __init__(self, datasette): self.ds = datasette - async def _validate_data(self, request, db, table_name): + async def _validate_data(self, request, db, table_name, pks, upsert): errors = [] + pks_list = [] + if isinstance(pks, str): + pks_list = [pks] + else: + pks_list = list(pks) + def _errors(errors): return None, errors, {} @@ -1135,6 +1141,15 @@ class TableInsertView(BaseView): # Validate columns of each row columns = set(await db.table_columns(table_name)) for i, row in enumerate(rows): + if upsert: + # It MUST have the primary key + missing_pks = [pk for pk in pks_list if pk not in row] + if missing_pks: + errors.append( + 'Row {} is missing primary key column(s): "{}"'.format( + i, '", "'.join(missing_pks) + ) + ) invalid_columns = set(row.keys()) - columns if invalid_columns: errors.append( @@ -1146,7 +1161,7 @@ class TableInsertView(BaseView): return _errors(errors) return rows, errors, extras - async def post(self, request): + async def post(self, request, upsert=False): try: resolved = await self.ds.resolve_table(request) except NotFound as e: @@ -1164,7 +1179,12 @@ class TableInsertView(BaseView): request.actor, "insert-row", resource=(database_name, table_name) ): return _error(["Permission denied"], 403) - rows, errors, extras = await self._validate_data(request, db, table_name) + + pks = await db.primary_keys(table_name) + + rows, errors, extras = await self._validate_data( + request, db, table_name, pks, upsert + ) if errors: return _error(errors, 400) @@ -1172,15 +1192,19 @@ class TableInsertView(BaseView): replace = extras.get("replace") should_return = bool(extras.get("return", False)) - # Insert rows - def insert_rows(conn): + + def insert_or_upsert_rows(conn): table = sqlite_utils.Database(conn)[table_name] + kwargs = {} + if upsert: + kwargs["pk"] = pks[0] if len(pks) == 1 else pks + else: + kwargs = {"ignore": ignore, "replace": replace} if should_return: rowids = [] + method = table.upsert if upsert else table.insert for row in rows: - rowids.append( - table.insert(row, ignore=ignore, replace=replace).last_rowid - ) + rowids.append(method(row, kwargs).last_rowid) return list( table.rows_where( "rowid in ({})".format(",".join("?" for _ in rowids)), @@ -1188,10 +1212,11 @@ class TableInsertView(BaseView): ) ) else: - table.insert_all(rows, ignore=ignore, replace=replace) + method_all = table.upsert_all if upsert else table.insert_all + method_all(rows, kwargs) try: - rows = await db.execute_write_fn(insert_rows) + rows = await db.execute_write_fn(insert_or_upsert_rows) except Exception as e: return _error([str(e)]) result = {"ok": True} @@ -1200,6 +1225,13 @@ class TableInsertView(BaseView): return Response.json(result, status=201) +class TableUpsertView(TableInsertView): + name = "table-upsert" + + async def post(self, request): + return await super().post(request, upsert=True) + + class TableDropView(BaseView): name = "table-drop" ``` Manual testing reveals that this mostly works... but it's not doing the right thing for `"return": true` - it always returns an empty list.	{"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0}	1432013704