Closed simonw closed 1 year ago
https://sqlite-utils.datasette.io/en/stable/cli-reference.html#create-table
sqlite-utils create-table ... --replace
That also has --ignore
:
--ignore If table already exists, do nothing
--replace If table already exists, replace it
--transform If table already exists, try to transform the schema
Here's where those are implemented for the create-table
CLI command: https://github.com/simonw/sqlite-utils/blob/f7af23837deab5c98dae9441d1f68318065d7d8c/sqlite_utils/cli.py#L1543-L1564
I think this is replace=True
and ignore=True
to match the CLI. And refactoring the CLI to use them.
Demo:
>>> from sqlite_utils import Database
>>> db = Database(memory=True)
>>> db["foo"].create({"id": int})
<Table foo (id)>
>>> db["foo"].create({"id": int})
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
File "/Users/simon/Dropbox/Development/sqlite-utils/sqlite_utils/db.py", line 1647, in create
self.db.create_table(
File "/Users/simon/Dropbox/Development/sqlite-utils/sqlite_utils/db.py", line 1030, in create_table
self.execute(sql)
File "/Users/simon/Dropbox/Development/sqlite-utils/sqlite_utils/db.py", line 510, in execute
return self.conn.execute(sql)
^^^^^^^^^^^^^^^^^^^^^^
sqlean.dbapi2.OperationalError: table [foo] already exists
>>> db["foo"].create({"id": int}, ignore=True)
<Table foo (id)>
>>> db["foo"].create({"id": int, "name": str}, replace=True)
<Table foo (id, name)>
>>> db["foo"].create({"id": int, "name": str, "age": int}, transform=True)
<Table foo (id, name, age)>
Manual testing of CLI command as well:
$ sqlite-utils create-table /tmp/f.db foo id integer
$ sqlite-utils create-table /tmp/f.db foo id integer
Error: Table "foo" already exists. Use --replace to delete and replace it.
$ sqlite-utils create-table /tmp/f.db foo id integer --replace
$ sqlite-utils create-table /tmp/f.db foo id
$ sqlite-utils schema /tmp/f.db
CREATE TABLE [foo] (
[id] INTEGER
);
$ sqlite-utils create-table /tmp/f.db foo id integer name str --transform
Error: column types must be one of ('INTEGER', 'TEXT', 'FLOAT', 'BLOB')
$ sqlite-utils create-table /tmp/f.db foo id integer name text --transform
$ sqlite-utils schema /tmp/f.db
CREATE TABLE "foo" (
[id] INTEGER,
[name] TEXT
);
$ sqlite-utils create-table /tmp/f.db foo id integer name text --ignore
$ sqlite-utils create-table /tmp/f.db foo id integer name text --replace
$ sqlite-utils schema /tmp/f.db
CREATE TABLE [foo] (
[id] INTEGER,
[name] TEXT
);
I think this broke a test:
$ pytest tests/test_tracer.py
=============================================== test session starts ================================================
platform darwin -- Python 3.11.4, pytest-7.2.2, pluggy-1.0.0
rootdir: /Users/simon/Dropbox/Development/sqlite-utils
plugins: icdiff-0.6, hypothesis-6.68.2
collected 2 items
tests/test_tracer.py F. [100%]
===================================================== FAILURES =====================================================
___________________________________________________ test_tracer ____________________________________________________
def test_tracer():
collected = []
db = Database(
memory=True, tracer=lambda sql, params: collected.append((sql, params))
)
db["dogs"].insert({"name": "Cleopaws"})
db["dogs"].enable_fts(["name"])
db["dogs"].search("Cleopaws")
> assert collected == [
("PRAGMA recursive_triggers=on;", None),
("select name from sqlite_master where type = 'view'", None),
("select name from sqlite_master where type = 'table'", None),
("select name from sqlite_master where type = 'view'", None),
("CREATE TABLE [dogs] (\n [name] TEXT\n);\n ", None),
("select name from sqlite_master where type = 'view'", None),
("INSERT INTO [dogs] ([name]) VALUES (?);", ["Cleopaws"]),
("select name from sqlite_master where type = 'view'", None),
(
"CREATE VIRTUAL TABLE [dogs_fts] USING FTS5 (\n [name],\n content=[dogs]\n)",
None,
),
(
"INSERT INTO [dogs_fts] (rowid, [name])\n SELECT rowid, [name] FROM [dogs];",
None,
),
("select name from sqlite_master where type = 'view'", None),
]
E assert equals failed
E [ [
E ('PRAGMA recursive_triggers=on;', None), ('PRAGMA recursive_triggers=on;', None),
E (
E "select name from sqlite_master where type =
E 'view'",
E None, ...
E
E ...Full output truncated (13 lines hidden), use '-vv' to show
tests/test_tracer.py:12: AssertionError
============================================= short test summary info ==============================================
FAILED tests/test_tracer.py::test_tracer - assert equals failed
=========================================== 1 failed, 1 passed in 0.05s ============================================
Here's why that test broke: https://github.com/simonw/sqlite-utils/blob/58b577279fcd5ef6ce88f88b28668dffebfe7f44/sqlite_utils/db.py#L960-L964
I added an extra if self[name].exists()
check to the db.create_table()
method.
Found myself using this pattern to quickly prototype a schema:
Using
replace=True
to drop and then recreate the table would be neat here, and would be consistent with other places that usereplace=True
.