remote/sqlite-utils
1
0
Fork 0
mirror of https://github.com/simonw/sqlite-utils.git synced 2026-07-23 09:24:31 +02:00
Code Issues Projects Releases Packages Wiki Activity
136 commits 70 branches 137 tags 7.2 MiB
Commit graph

72 commits

Author SHA1 Message Date
Simon Willison
208f56fbaf --not-null and --default options to insert/update, closes #24 2019-06-12 23:30:16 -07:00
Simon Willison
362359da7e not_null= and defaults= arguments to various Python methods, refs #24 2019-06-12 23:10:07 -07:00
Simon Willison
07a10bdb2c foreign_keys=[...] can now detect tables and keys - closes #25 2019-06-12 22:32:26 -07:00
Simon Willison
d645032cfa add_foreign_key can now detect table and pk, refs #25 2019-06-12 21:51:09 -07:00
Simon Willison
2fed87da6e --not-null-default and not_null_default=, refs #24 2019-06-12 18:35:02 -07:00
Simon Willison
50e2f94b58 Ability to add a column that is a foreign key reference 
Python API:

    db["dogs"].add_column("species_id", fk="species")
    # or
    db["dogs"].add_column("species_id", fk="species", fk_col="ref")

CLI:

    $ sqlite-utils add-column mydb.db dogs species_id --fk species
    # or
    $ sqlite-utils add-column mydb.db dogs species_id --fk species --fk-col ref

Closes #16
 2019-05-28 21:54:43 -07:00
Simon Willison
00c5a49a87 Option to ignore inserts if primary key exists already 
Support for SQLite's INSERT OR IGNORE

In the API layer it looks like this:

    fresh_db["test"].insert({"id": 1, "bar": 3}, ignore=True)

For the CLI layer it looks like this:

    $ sqlite-utils insert data.db dogs dogs.json --ignore

Closes #21
 2019-05-28 21:15:57 -07:00
Simon Willison
cec8c3c482 sqlite-utils rows now obeys --json-cols 2019-05-27 17:47:59 -07:00
Simon Willison
092e1368ed Fixed typo 2019-05-25 07:24:17 -07:00
Simon Willison
251e473041 --json-cols option for outputting nested JSON, closes #20 2019-05-24 17:56:44 -07:00
Simon Willison
eff52023c6 alter=True/--alter option to automatically add missing columns 
Closes #18
 2019-05-24 17:41:04 -07:00
Simon Willison
3cab079d3e add-column col_type now optional, defaults to str 2019-02-24 14:24:00 -08:00
Simon Willison
557dc3f9a7 Improved foreign_keys= argument, closes #17 2019-02-24 14:12:45 -08:00
Simon Willison
f8d3b7cfe5 sqlite-utils add-foreign-key books.db books author_id authors id 
Command for adding foreign keys to existing tables. Closes #2
 2019-02-24 13:33:45 -08:00
Simon Willison
e1ca938aa1 table.add_foreign_key(column, other_table, other_column) 
New mechanism for adding foreign key constraints to an existing SQLite
table.

SQLite ALTER TABLE does not support this out-of-the-box, so I instead
had to write some careful code that uses PRAGMA writable_schema = 1 to
directly modify the sqlite_master table.

Refs #2
 2019-02-24 13:10:51 -08:00
Simon Willison
9756310408 Black 2019-02-24 12:59:46 -08:00
Simon Willison
33df260b38 Fix for CLI test 2019-02-24 12:12:59 -08:00
Simon Willison
0bc49e938e 'sqlite-utils add-column name type' command, closes #15 2019-02-24 12:04:33 -08:00
Simon Willison
05a85b358f Don't assume -1 for error code 2019-02-24 11:50:56 -08:00
Simon Willison
6d25f648ed add_column now accepts explicit SQLite types, refs #15 2019-02-24 11:49:31 -08:00
Simon Willison
c5286d2c66 Black formatting 2019-02-24 11:40:58 -08:00
Simon Willison
dc2560879e table.add_column(name, type) method, refs #15 2019-02-24 11:40:26 -08:00
Simon Willison
8a5d0d80c3 sqlite-utils create-index command, closes #14 2019-02-24 11:11:21 -08:00
Simon Willison
7784b924a1 Added create_index(..., if_not_exists=True), refs #14 2019-02-24 10:46:44 -08:00
Simon Willison
e7ffbcdb36 Ability to create unique indexes, refs #14 2019-02-24 10:41:51 -08:00
Simon Willison
6c3f5e6479 Added --table and --fmt options for table output using tabulate 2019-02-23 22:45:17 -08:00
Simon Willison
dbf239e3fc hash_id= mechanism for setting ID using content hash, closes #13 2019-02-23 20:36:40 -08:00
Simon Willison
e615d22c55
 		Support for numpy types, closes #11 (#12) 2019-02-23 20:02:19 -08:00
Simon Willison
ca225b02c0 Prettier indentation for CREATE TABLE 2019-02-23 12:52:04 -08:00
Simon Willison
e3c0ed1b1a sqlite-utils tables now takes --counts, --columns, --csv, --nl 2019-02-22 18:12:53 -08:00
Simon Willison
c7dbb03a10 Added 'sqlite-utils rows db.db tablename' command 2019-02-22 17:52:17 -08:00
Simon Willison
f2ca48c0da Default command now executes queries, --csv or --json 
I replaced the following commands:

    sqlite-utils json db.db "select * from table"
    sqlite-utils csv db.db "select * from table"

With a unified 'query' command, which is now set as the default:

    sqlite-utils db.db "select * from table"
    sqlite-utils db.db "select * from table" --csv
 2019-02-22 17:40:21 -08:00
Simon Willison
3095f2e671 Added db[table].rows iterator 2019-02-21 21:29:50 -08:00
Simon Willison
3f8ef35b2a enable-fts and populate-fts CLI commands 2019-02-07 21:18:24 -08:00
Simon Willison
f0afa5646b insert_all() now accepts generator, closes #7 2019-01-27 22:12:18 -08:00
Simon Willison
5309c5c775 sqlite-utils insert ... --nl option, closes #6 2019-01-27 18:17:38 -08:00
Simon Willison
57fc8ce55e Database() constructor works with pathlib.Path 2019-01-27 15:53:41 -08:00
Simon Willison
ea17c63cc5 Tests for json returning single row 2019-01-26 10:59:01 -08:00
Simon Willison
5466c9745d sqlite-utils json dogs.db "select * from dogs" 2019-01-25 18:06:29 -08:00
Simon Willison
c83dd8a5eb sqlite-utils csv data.db "select ..." command 2019-01-25 07:50:20 -08:00
Simon Willison
9501ba4bf1 sqlite-utils table_names blah.db is now tables blah.db 
Travis tests were failing because on OS X the command was this:

   sqlite-utils table_names blah.db

But in Travis CI the command was this:

   sqlite-utils table-names blah.db

Renaming it to tables fixes this inconsistency.
 2019-01-24 23:04:52 -08:00
Simon Willison
b30f924c49 Implemented 'sqlite-utils upsert my.db tablename --pk=x' 2019-01-24 21:20:10 -08:00
Simon Willison
9e74289397 'sqlite-utils insert tablename file.json' command 2019-01-24 21:06:41 -08:00
Simon Willison
1c683076d3 Sanity check test for optimize --no-vacuum 2019-01-24 20:38:23 -08:00
Simon Willison
0a8194e730 sqlite-utils optimize command, .optimize() and .detect_fts() table methods 2019-01-24 20:35:51 -08:00
Simon Willison
015bd2a840 Test for .table_names(fts5=True) 2019-01-24 20:23:53 -08:00
Simon Willison
66fd63b119 Ability to list just FTS4 or FTS5 table names 2019-01-24 19:57:04 -08:00
Simon Willison
231224ba1a Added vacuum to CLI and Python API 2019-01-24 19:39:04 -08:00
Simon Willison
fd5829b27d First CLI command: sqlite-utils table_names test.db 2019-01-24 19:30:47 -08:00
Simon Willison
8437fe3091 Revert "No need to skip black under Python 3.7 any more" 
It turns out the black tests still fail in Travis against that version.

This reverts commit d18687a30a.
 2019-01-24 19:08:52 -08:00