![]() As per docs path also supports negative indexing where $ can be used to access last element of the array. Here $ is used to access the first element of the array. Then we can pass it to -> which also takes a path expression to its right but returns value as SQLite datatype like text, integer etc. Since the interests column is of JSON type we can use -> operator along with $.likes which is a path expression to get the likes attribute. ![]() ![]() In the below example we look for users who have reading as their first preference. > also provides a JSON representation in return so it can be chained in nested lookups. With -> we can have the left part as a JSON component and the right part as a path expression. With the above schema in place we can now use the JSON operators to query. sqlite > create table user ( id integer primary key, name text, interests json ) sqlite > insert into user values ( null, "John", ' Operator semantics Use ".open FILENAME" to reopen on a persistent database. Processing data with this involves two steps: first import it into a sqlite-utils query to run queries and output the results. Connected to a transient in - memory database. already offers a mechanism for importing CSV and JSON data into a SQLite database file, in the form of the sqlite-utils insert command. insert_all (, pk = "id" )Ĭheck out the full library documentation for everything else you can do with the Python library. Database ( "demo_database.db" ) # This line creates a "dogs" table if one does not already exist: db. You can also import sqlite_utils and use it as a Python library like this: import sqlite_utils db = sqlite_utils. See the full CLI documentation for comprehensive coverage of many more commands. Sqlite-utils memory lets you import CSV or JSON data into an in-memory database and run SQL queries against it in a single command: $ cat dogs.csv | sqlite-utils memory - "select name, age from stdin" Or for data in a CSV file: $ sqlite-utils insert dogs.db dogs dogs.csv -csv | sqlite-utils insert releases.db releases -pk id You can import JSON data into a new database table like this: $ curl \ $ sqlite-utils dogs.db "select * from dogs" -table $ sqlite-utils dogs.db "select * from dogs" -csv $ sqlite-utils dogs.db "select id, name from dogs" $ sqlite-utils insert dogs.db dogs dogs.csv -csv Now you can do things with the CLI utility like this: $ sqlite-utils memory dogs.csv "select * from t" Or if you use Homebrew for macOS: brew install sqlite-utils Read more on my blog, in this series of posts on New features in sqlite-utils and other entries tagged sqliteutils.
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |