Add a --write option that enables insert/update/etc

[simonw]

This could default to showing the SQL it's going to run and asking for confirmation.

A -y/--yes option could skip that confirmation step for people who are super confident.

[simonw]

I don't think this is as simple as just enabling a non-read-only connection and telling the model it can run insert/update/etc.

If it was I could do that mostly here and in an alternative system prompt:

sqlite-utils-ask/sqlite_utils_ask.py

Lines 126 to 132 in 91277ee

	@click.option("json_", "-j", "--json", is_flag=True, help="Output as JSON")
	def ask(path, question, model_id, verbose, examples, json_):
	"Ask a question of your data"
	# Open in read-only mode
	conn = sqlite3.connect("file:{}?mode=ro".format(str(path)), uri=True)
	db = sqlite_utils.Database(conn)
	_shared_ask(db, question, model_id, verbose, examples, json_)

But I need to consider a few other things:

• Write queries don't usually return rows, so JSON serializing the output won't necessarily make sense. I can display the number of rows affected but not much else.
• Often a write operation may want to execute multiple commands. The system is currently setup to run a single query (and maybe retry it a few times on an error).
• It might be good to execute a write and then a SELECT afterwards to help illustrate the difference.
• Some writes will want to insert multiple rows - imagine someone asking to insert rows for each of the names and addresses in an input document, for example - a task that begins to overlap with https://github.com/datasette/datasette-extract

[simonw]

Honestly write support feels to me like it makes more sense in a bit of a ChatGPT Code Interpreter style chat loop as opposed to a single CLI invocation.

[simonw]

I put together an initial prototype. Here's a transcript (after I first got it to drop the repos table):

% sqlite-utils ask content.db 'create a repos table and insert 10 fake rows' -w 
create table repos (
   id TEXT PRIMARY KEY,
   name TEXT,
   description TEXT,
   owner INTEGER REFERENCES users(id),
   full_name TEXT,
   created_at TEXT,
   updated_at TEXT,
   stargazers_count INTEGER,
   topics TEXT
);
Execute this SQL? [y/N]: y

Error:
    'NoneType' object is not iterable

insert into repos (id, name, description, owner, full_name, created_at, updated_at, stargazers_count, topics) values
('repo1', 'Fake Repo 1', 'This is a fake repository for testing purposes', 1, 'user1/Fake Repo 1', '2023-01-01', '2023-01-01', 10, '["testing", "fake"]'),
('repo2', 'Fake Repo 2', 'This is a fake repository for testing purposes', 2, 'user2/Fake Repo 2', '2023-01-02', '2023-01-02', 20, '["testing", "sample"]'),
('repo3', 'Fake Repo 3', 'This is a fake repository for testing purposes', 3, 'user3/Fake Repo 3', '2023-01-03', '2023-01-03', 15, '["tutorial", "example"]'),
('repo4', 'Fake Repo 4', 'This is a fake repository for testing purposes', 4, 'user4/Fake Repo 4', '2023-01-04', '2023-01-04', 25, '["demo", "test"]'),
('repo5', 'Fake Repo 5', 'This is a fake repository for testing purposes', 1, 'user1/Fake Repo 5', '2023-01-05', '2023-01-05', 5, '["example", "sample"]'),
('repo6', 'Fake Repo 6', 'This is a fake repository for testing purposes', 2, 'user2/Fake Repo 6', '2023-01-06', '2023-01-06', 12, '["fake", "repository"]'),
('repo7', 'Fake Repo 7', 'This is a fake repository for testing purposes', 3, 'user3/Fake Repo 7', '2023-01-07', '2023-01-07', 30, '["testing", "demo"]'),
('repo8', 'Fake Repo 8', 'This is a fake repository for testing purposes', 4, 'user4/Fake Repo 8', '2023-01-08', '2023-01-08', 8, '["sample"]'),
('repo9', 'Fake Repo 9', 'This is a fake repository for testing purposes', 1, 'user1/Fake Repo 9', '2023-01-09', '2023-01-09', 18, '["tutorial"]'),
('repo10', 'Fake Repo 10', 'This is a fake repository for testing purposes', 2, 'user2/Fake Repo 10', '2023-01-10', '2023-01-10', 22, '["example", "fake"]');
Execute this SQL? [y/N]: y

Error:
    'NoneType' object is not iterable

select * from repos;
create table repos (
   id TEXT PRIMARY KEY,
   name TEXT,
   description TEXT,
   owner INTEGER REFERENCES users(id),
   full_name TEXT,
   created_at TEXT,
   updated_at TEXT,
   stargazers_count INTEGER,
   topics TEXT
);

insert into repos (id, name, description, owner, full_name, created_at, updated_at, stargazers_count, topics) values
('repo1', 'Fake Repo 1', 'This is a fake repository for testing purposes', 1, 'user1/Fake Repo 1', '2023-01-01', '2023-01-01', 10, '["testing", "fake"]'),
('repo2', 'Fake Repo 2', 'This is a fake repository for testing purposes', 2, 'user2/Fake Repo 2', '2023-01-02', '2023-01-02', 20, '["testing", "sample"]'),
('repo3', 'Fake Repo 3', 'This is a fake repository for testing purposes', 3, 'user3/Fake Repo 3', '2023-01-03', '2023-01-03', 15, '["tutorial", "example"]'),
('repo4', 'Fake Repo 4', 'This is a fake repository for testing purposes', 4, 'user4/Fake Repo 4', '2023-01-04', '2023-01-04', 25, '["demo", "test"]'),
('repo5', 'Fake Repo 5', 'This is a fake repository for testing purposes', 1, 'user1/Fake Repo 5', '2023-01-05', '2023-01-05', 5, '["example", "sample"]'),
('repo6', 'Fake Repo 6', 'This is a fake repository for testing purposes', 2, 'user2/Fake Repo 6', '2023-01-06', '2023-01-06', 12, '["fake", "repository"]'),
('repo7', 'Fake Repo 7', 'This is a fake repository for testing purposes', 3, 'user3/Fake Repo 7', '2023-01-07', '2023-01-07', 30, '["testing", "demo"]'),
('repo8', 'Fake Repo 8', 'This is a fake repository for testing purposes', 4, 'user4/Fake Repo 8', '2023-01-08', '2023-01-08', 8, '["sample"]'),
('repo9', 'Fake Repo 9', 'This is a fake repository for testing purposes', 1, 'user1/Fake Repo 9', '2023-01-09', '2023-01-09', 18, '["tutorial"]'),
('repo10', 'Fake Repo 10', 'This is a fake repository for testing purposes', 2, 'user2/Fake Repo 10', '2023-01-10', '2023-01-10', 22, '["example", "fake"]');

select * from repos;

[
    {
        "id": "repo1",
        "name": "Fake Repo 1",
        "description": "This is a fake repository for testing purposes",
        "owner": 1,
        "full_name": "user1/Fake Repo 1",
        "created_at": "2023-01-01",
        "updated_at": "2023-01-01",
        "stargazers_count": 10,
        "topics": "[\"testing\", \"fake\"]"
    },
    {
        "id": "repo2",
        "name": "Fake Repo 2",
        "description": "This is a fake repository for testing purposes",
        "owner": 2,
        "full_name": "user2/Fake Repo 2",
        "created_at": "2023-01-02",
        "updated_at": "2023-01-02",
        "stargazers_count": 20,
        "topics": "[\"testing\", \"sample\"]"
    },
    {
        "id": "repo3",
        "name": "Fake Repo 3",
        "description": "This is a fake repository for testing purposes",
        "owner": 3,
        "full_name": "user3/Fake Repo 3",
        "created_at": "2023-01-03",
        "updated_at": "2023-01-03",
        "stargazers_count": 15,
        "topics": "[\"tutorial\", \"example\"]"
    },
    {
        "id": "repo4",
        "name": "Fake Repo 4",
        "description": "This is a fake repository for testing purposes",
        "owner": 4,
        "full_name": "user4/Fake Repo 4",
        "created_at": "2023-01-04",
        "updated_at": "2023-01-04",
        "stargazers_count": 25,
        "topics": "[\"demo\", \"test\"]"
    },
    {
        "id": "repo5",
        "name": "Fake Repo 5",
        "description": "This is a fake repository for testing purposes",
        "owner": 1,
        "full_name": "user1/Fake Repo 5",
        "created_at": "2023-01-05",
        "updated_at": "2023-01-05",
        "stargazers_count": 5,
        "topics": "[\"example\", \"sample\"]"
    },
    {
        "id": "repo6",
        "name": "Fake Repo 6",
        "description": "This is a fake repository for testing purposes",
        "owner": 2,
        "full_name": "user2/Fake Repo 6",
        "created_at": "2023-01-06",
        "updated_at": "2023-01-06",
        "stargazers_count": 12,
        "topics": "[\"fake\", \"repository\"]"
    },
    {
        "id": "repo7",
        "name": "Fake Repo 7",
        "description": "This is a fake repository for testing purposes",
        "owner": 3,
        "full_name": "user3/Fake Repo 7",
        "created_at": "2023-01-07",
        "updated_at": "2023-01-07",
        "stargazers_count": 30,
        "topics": "[\"testing\", \"demo\"]"
    },
    {
        "id": "repo8",
        "name": "Fake Repo 8",
        "description": "This is a fake repository for testing purposes",
        "owner": 4,
        "full_name": "user4/Fake Repo 8",
        "created_at": "2023-01-08",
        "updated_at": "2023-01-08",
        "stargazers_count": 8,
        "topics": "[\"sample\"]"
    },
    {
        "id": "repo9",
        "name": "Fake Repo 9",
        "description": "This is a fake repository for testing purposes",
        "owner": 1,
        "full_name": "user1/Fake Repo 9",
        "created_at": "2023-01-09",
        "updated_at": "2023-01-09",
        "stargazers_count": 18,
        "topics": "[\"tutorial\"]"
    },
    {
        "id": "repo10",
        "name": "Fake Repo 10",
        "description": "This is a fake repository for testing purposes",
        "owner": 2,
        "full_name": "user2/Fake Repo 10",
        "created_at": "2023-01-10",
        "updated_at": "2023-01-10",
        "stargazers_count": 22,
        "topics": "[\"example\", \"fake\"]"
    }
]

That 'NoneType' object is not iterable error is because I haven't yet solved for non-select queries not returning a cursor with results you can loop through.

Still needs a bunch more thought.

[simonw]

The code that loops through multiple sql queries instead of one is a total mess, I haven't reconsidered how retries should work yet - in particular when I prompt the model for a fixed version of a query that errors I need to specify WHICH of those queries needs fixing.