recent_releases (view)

• This tool is no longer maintained. The latest release of sqlite-utils introduces a new sqlite-utils convert command which provides a super-set of the functionality provided by sqlite-transform. #11

This release introduces the new sqlite-utils convert command (#251) and corresponding table.convert(...) Python method (#302). These tools can be used to apply a Python conversion function to one or more columns of a table, either updating the column in place or using transformed data from that column to populate one or more other columns.

This command-line example uses the Python standard library textwrap module to wrap the content of the content column in the articles table to 100 characters: $ sqlite-utils convert content.db articles content\ '"\n".join(textwrap.wrap(value, 100))'\ --import=textwrap The same operation in Python code looks like this: ```python import sqlite_utils, textwrap

db = sqlite_utils.Database("content.db") db["articles"].convert("content", lambda v: "\n".join(textwrap.wrap(v, 100))) ``` See the full documentation for the sqlite-utils convert command and the table.convert(...) Python method for more details.

Also in this release:

• The new table.count_where(...) method, for counting rows in a table that match a specific SQL WHERE clause. (#305)
• New --silent option for the sqlite-utils insert-files command to hide the terminal progress bar, consistent with the --silent option for sqlite-utils convert. (#301)

• Now also tested against Python 3.9.
• Fixed Jinja2 deprecation warnings. #7

• 1.0 release reflecting the stability of this plugin
• Extremely minor documentation improvements

• Now uses --json option to read existing application names from flyctl apps list. #5

• Quit Datasette on startup if remote metadata cannot be fetched. #1
• Documentation and tests for all configuration options. #2

• First working release.

• Handle string columns in addition to float columns. #1

• New --scope option for publishing to a different Vercel scope, for example a different team. #47

• sqlite-transform lambda --multi option allowing the function to return a Python dictionary defining multiple columns that will be created and populated. #4
• --drop option can be used with --output or --multi to drop the original column. #15
• All commands now require at least one column to be specified. #17

• sqlite-utils schema my.db table1 table2 command now accepts optional table names. (#299)
• sqlite-utils memory --help now describes the --schema option.

New -s or --silent option for hiding the progress bar. #14

New --output colname and --output-type integer options for all of the commands, see saving the result to a separate column. #13

• New "copy to clipboard" buttons next to each code example. #12
• Observable export code no longer needs to import d3. #10
• Better wrapping of code, especially on mobile screens. #11
• d3 export examples now use d3.autoType. #13

• Now uses the datasette.client mechanism for internal calls. #61
• Depends on datasette>=0.58.1

• Fix for an intermittent race condition caused by the refresh_schemas() internal function. (#1231)

• New datasette --uds /tmp/datasette.sock option for binding Datasette to a Unix domain socket, see proxy documentation. (#1388)
• "searchmode": "raw" table metadata option for defaulting a table to executing SQLite full-text search syntax without first escaping it, see Advanced SQLite search queries. (#1389)
• New plugin hook: get_metadata(datasette, key, database, table), for returning custom metadata for an instance, database or table. Thanks, Brandon Roberts! (#1384)
• New plugin hook: skip_csrf(datasette, scope), for opting out of CSRF protection based on the incoming request. (#1377)
• The menu_links(), table_actions() and database_actions() plugin hooks all gained a new optional request argument providing access to the current request. (#1371)
• Major performance improvement for Datasette faceting. (#1394)
• Improved documentation for Running Datasette behind a proxy to recommend using ProxyPreservehost On with Apache. (#1387)
• POST requests to endpoints that do not support that HTTP verb now return a 405 error.
• db.path can now be provided as a pathlib.Path object, useful when writing unit tests for plugins. Thanks, Chris Amico. (#1365)

• Fixed an error when running against PostgreSQL 10 - thanks, Ryan Cheley. #138
• This tool is now tested against PostgreSQL 10, 11, 12 and 13. #139

• Implemented a new column cog menu, with options for sorting, counting distinct items and counting by values. #57
• Fixed bug where columns named after PostgreSQL reserved words (such as on or group) produced invalid suggested SELECT queries. #134
• New Docker Compose configuration to support Docker development environments. Thanks, Atul Varma. #128
• Admin change list view now only shows dashboards the user has permission to edit. Thanks, Atul Varma. #130

• New db.query(sql, params) method, which executes a SQL query and returns the results as an iterator over Python dictionaries. (#290)
• This project now uses flake8 and has started to use mypy. (#291)
• New documentation on contributing to this project. (#292)

• New plugin hook: skip_csrf(datasette, scope), for opting out of CSRF protection based on the incoming request. (#1377)
• POST requests to endpoints that do not support that HTTP verb now return a 405 error.
• db.path can now be provided as a pathlib.Path object, useful when writing unit tests for plugins. Thanks, Chris Amico. (#1365)

• New sqlite-utils memory data.csv --schema option, for outputting the schema of the in-memory database generated from one or more files. See --schema, --dump and --save. (#288)
• Added installation instructions. (#286)

This release introduces the sqlite-utils memory command, which can be used to load CSV or JSON data into a temporary in-memory database and run SQL queries (including joins across multiple files) directly against that data.

Also new: sqlite-utils insert --detect-types, sqlite-utils dump, table.use_rowid plus some smaller fixes.

sqlite-utils memory

This example of sqlite-utils memory retrieves information about the all of the repositories in the Dogsheep organization on GitHub using this JSON API, sorts them by their number of stars and outputs a table of the top five (using -t):

``` $ curl -s 'https://api.github.com/users/dogsheep/repos'\ | sqlite-utils memory - ' select full_name, forks_count, stargazers_count from stdin order by stargazers_count desc limit 5 ' -t full_name forks_count stargazers_count

dogsheep/twitter-to-sqlite 12 225 dogsheep/github-to-sqlite 14 139 dogsheep/dogsheep-photos 5 116 dogsheep/dogsheep.github.io 7 90 dogsheep/healthkit-to-sqlite 4 85 ```

The tool works against files on disk as well. This example joins data from two CSV files:

$ cat creatures.csv species_id,name 1,Cleo 2,Bants 2,Dori 2,Azi $ cat species.csv id,species_name 1,Dog 2,Chicken $ sqlite-utils memory species.csv creatures.csv ' select * from creatures join species on creatures.species_id = species.id ' [{"species_id": 1, "name": "Cleo", "id": 1, "species_name": "Dog"}, {"species_id": 2, "name": "Bants", "id": 2, "species_name": "Chicken"}, {"species_id": 2, "name": "Dori", "id": 2, "species_name": "Chicken"}, {"species_id": 2, "name": "Azi", "id": 2, "species_name": "Chicken"}]

Here the species.csv file becomes the species table, the creatures.csv file becomes the creatures table and the output is JSON, the default output format.

You can also use the --attach option to attach existing SQLite database files to the in-memory database, in order to join data from CSV or JSON directly against your existing tables.

Full documentation of this new feature is available in Querying data directly using an in-memory database. (#272)

sqlite-utils insert --detect-types

The sqlite-utils insert command can be used to insert data from JSON, CSV or TSV files into a SQLite database file. The new --detect-types option (shortcut -d), when used in conjunction with a CSV or TSV import, will automatically detect if columns in the file are integers or floating point numbers as opposed to treating everything as a text column and create the new table with the corresponding schema. See Inserting CSV or TSV data for details. (#282)

Other changes

• Bug fix: table.transform(), when run against a table without explicit primary keys, would incorrectly create a new version of the table with an explicit primary key column called rowid. (#284)
• New table.use_rowid introspection property, see .use_rowid. (#285)
• The new sqlite-utils dump file.db command outputs a SQL dump that can be used to recreate a database. (#274)
• -h now works as a shortcut for --help, thanks Loren McIntyre. (#276)
• Now using pytest-cov and Codecov to track test coverage - currently at 96%. (#275)
• SQL errors that occur when using sqlite-utils query are now displayed as CLI errors.

• Fixed bug where searches consisting only of whitespace caused a server error. #33

• New feature: yaml-to-sqlite dogs.db dogs.yaml --single-column=name can be used to import YAML files consisting of a list of items into a single table with a single primary key column. #5
• Importing YAML files with new columns will now alter the table to add those columns. #4

• BREAKING CHANGE: The option order for this command is now markdown-to-sqlite database.db tablename file1.md file2.md - prior to version 1.0 the order was markdown-to-sqlite file1.md file2.md database.db tablename. This change was made for improved consistency with other x-to-sqlite tools.

• Fixed bug when using table.upsert_all() to create a table with only a single column that is treated as the primary key. (#271)

• Spatialite's geometry blob is now automatically parsed into GeoJSON, so the same query will output GeoJSON with and without Spatialite.

• New sqlite-utils schema command showing the full SQL schema for a database, see Showing the schema (CLI). (#268)
• db.schema introspection property exposing the same feature to the Python library, see Showing the schema (Python library).

• New --postgres-schema option for specifying a different PostgreSQL schema, thanks Jeremy Dormitzer. #29
• Correctly handles table names that need escaping, such as user in PostgreSQL. #25
• Empty tables are now created in the SQLite database with a matching schema, rather than being skipped. #28
• Connection strings starting with postgres:// are treated as if they started with postgresql://. #34

• No longer pins a specific version of Datasette.

• The menu_links(), table_actions() and database_actions() plugin hooks all gained a new optional request argument providing access to the current request. (#1371)

• First working version

• Fixed visual display glitch with global navigation menu. (#1367)
• No longer truncates the list of table columns displayed on the /database page. (#1364)

• Fixed bug using this plugin with Datasette 0.54 or higher. #19

• This release includes a small potentially backwards-incompatible change: the description field for a saved dashboard is now treated as Markdown and rendered as such when the saved dashboard is displayed. It is very unlikely that this will affect any of your existing dashboards but you should still check before applying the upgrade. Thanks, Atul Varma. #115

• Now depends on datasette>=0.56.1, to avoid a Datasette security vulnerability.

Warning: This release fixes a reflected cross-site scripting security hole with the ?_trace=1 feature. You should upgrade to this version, or to Datasette 0.56.1, as soon as possible. (#1360)

In addition to the security fix, this release includes ?_col= and ?_nocol= options for controlling which columns are displayed for a table, ?_facet_size= for increasing the number of facet results returned, re-display of your SQL query should an error occur and numerous bug fixes.

New features

• If an error occurs while executing a user-provided SQL query, that query is now re-displayed in an editable form along with the error message. (#619)
• New ?_col= and ?_nocol= parameters to show and hide columns in a table, plus an interface for hiding and showing columns in the column cog menu. (#615)
• A new ?_facet_size= parameter for customizing the number of facet results returned on a table or view page. (#1332)
• ?_facet_size=max sets that to the maximum, which defaults to 1,000 and is controlled by the the max_returned_rows setting. If facet results are truncated the ... at the bottom of the facet list now links to this parameter. (#1337)
• ?_nofacet=1 option to disable all facet calculations on a page, used as a performance optimization for CSV exports and ?_shape=array/object. (#1349, #263)
• ?_nocount=1 option to disable full query result counts. (#1353)
• ?_trace=1 debugging option is now controlled by the new trace_debug setting, which is turned off by default. (#1359)

Bug fixes and other improvements

• Custom pages now work correctly when combined with the base_url setting. (#1238)
• Fixed intermittent error displaying the index page when the user did not have permission to access one of the tables. Thanks, Guy Freeman. (#1305)
• Columns with the name "Link" are no longer incorrectly displayed in bold. (#1308)
• Fixed error caused by tables with a single quote in their names. (#1257)
• Updated dependencies: pytest-asyncio, Black, jinja2, aiofiles, click, and itsdangerous.
• The official Datasette Docker image now supports apt-get install. (#1320)
• The Heroku runtime used by datasette publish heroku is now python-3.8.10.

• Fix for a security hole: reflected cross-site scripting in the ?_trace=1 feature. #1360

• Fixed 500 error caused when markdown widget attempted to render a null value. #124

• New sqlite-utils indexes command to list indexes in a database, see Listing indexes. (#263)
• table.xindexes introspection property returning more details about that table's indexes, see .xindexes. (#261)

• New table.pks_and_rows_where() method returning (primary_key, row_dictionary) tuples - see Listing rows with their primary keys. (#240)
• Fixed bug with table.add_foreign_key() against columns containing spaces. (#238)
• table_or_view.drop(ignore=True) option for avoiding errors if the table or view does not exist. (#237)
• sqlite-utils drop-view --ignore and sqlite-utils drop-table --ignore options. (#237)
• Fixed a bug with inserts of nested JSON containing non-ascii strings - thanks, Dylan Wu. (#257)
• Suggest --alter if an error occurs caused by a missing column. (#259)
• Support creating indexes with columns in descending order, see API documentation and CLI documentation. (#260)
• Correctly handle CSV files that start with a UTF-8 BOM. (#250)

• Docker image now allows apt-get install. #1320
• ?_col= and ?_nocol= options to show and hide columns in a table, plus UI for doing this in the column cog menu. #615
• New ?_facet_size=max option, linked to from the … on truncated facets. #1337
• Heroku runtime used by datasette publish heroku is now python-3.8.10.
• Multi-arch support for built Docker images using Buildx - thanks, Blair Drummand. #1319

• Configuration instructions for people using dj-database-url and django-configurations - thanks, Paolo Melchiorre. #110
• Added a useful SQL pattern to the progress bar widget documentation.
• SQL queries on saved dashboards are now hidden behind a details/summary element. #117
• Documentation now covers the execute_sql permission. #112

Mainly dependency bumps, plus a new ?_facet_size= argument.

• Updated dependencies: pytest-asyncio, Black, jinja2, aiofiles, itsdangerous
• Fixed bug where columns called "Link" were incorrectly displayed in bold. (#1308)
• New ?_facet_size= argument for customizing the number of facet results returned on a page. (#1332)

• Fixed bug where the id column did not come first in the table schema. #20

• New --nl option for consuming newline-delimited GeoJSON - thanks, Chris Amico! #13
• New --spatial-index option which creates the table using SpatiaLite and adds a spatial index for the geometry column. #17

• Fixed a security and permissions flaw, where users without the execute_sql permission could still run custom queries by editing saved dashboards using the Django admin interface. #94
• Bar charts now preserve the order in which the data is returned by the query. #106
• Example select statements now include explicit columns. #105
• Columns on the dashboard page now respond to media queries, collapsing to a single column on narrow or mobile browser windows. #106
• Fixed hard-coded /dashboard/ URL, thanks Flávio Juvenal da Silva Junior. #99
• Fixed bug where ?_save- parameters could be accidentally reflected in the query string. #104
• Explicitly require at least Django 3.0. #101
• Fixed a warning about AutoField migrations with Django 3.2. #103
• Fixed a bug where users lacking permissions could end up in an infinite redirect. #30
• Configuration and security documentation now recommends using a read-only database replica as the most robust option. #95
• Added screenshots and demo links for all of the included widgets. #96

• New word cloud widget displayed when queries return wordcount_word and wordcount_count columns. #91
• All pages are now served with cache-control: private header if the user is logged in. #92
• Much improved README, including a detailed list of features. #40

• Fixed bug where saved dashboards relating to groups could be displayed multiple times. #90
• Removed duplicate "Run queries" button. #89
• HTML page titles now include named parameter values, if available. #88

• Documentation is now hosted at https://django-sql-dashboard.datasette.io/ #86
• Dashboard index link is no longer shown on saved dashboards to to users without permission to view it. #87

• Improved and updated documentation for saved dashboards. #84
• Added "Remove all queries" link for resetting interactive dashboard. #85

First non-alpha release! Django SQL Dashboard is now ready for people to use against their production Django applications.

• Saved dashboards can now be created from the interactive dashboard page. #44
• New progress bar widget for queries that return numeric columns total_count and completed_count. #77
• The list of available tables now better reflects your current permissions, and shows the columns for each listed table. #79, #80
• The dashboard index page now lists saved dashboards that the user is able to view or edit. #81
• "Edit" link for dashboards links to the Django Admin, which now respects the edit policy set for a dashboard. #44
• New documentation section covering security. #6
• Show row count on non-truncated results. #76
• More robust extraction of named parameteters from queries, fixing some errer-cases. #75
• Custom widgets can now extend a django_sql_dashboard/widgets/_base_widget.html base template. #78
• Fixed a bug caused by errors on saved dashboard pages being displayed as editable text. #74
• Unlisted public dashboards now include a robots tag to avoid being indexed by search engines. #42

• "Copy to clipboard" button on pretty-printed JSON results. #72
• Saved dashboards no longer display broken export buttons. #71

• Fixed bug where large SQL queries submitted by POST did not correctly evaluate any named parameters. #58

• Handle SQL queries too long for a GET. #58
• Export all results as CSV/TSV feature. #50

• Use separate styles for pre.sql and pre.json

• Do not show "[count]" links for unambiguous columns. #69
• Better HTML titles for pages, for better browser history. #70
• Pretty-print cells containing JSON. #62

• Correctly sign ?sql= on redirects created using DASHBOARD_UPGRADE_OLD_BASE64_LINKS. #68

• Fixed bug with DASHBOARD_UPGRADE_OLD_BASE64_LINKS setting. #67

• Make it easy to provide a custom base template. #7
• Content-Security-Policy: frame-ancestors header. #64
• Signing no longer uses base64/json. #45
• DASHBOARD_UPGRADE_OLD_BASE64_LINKS mechanism. #65

• SQL textarea now always has at least 5 rows
• Always strip trailing ; from SQL, fixing bug with "count" links. #56
• URLize links in results. #61

• Fixed bug with new --http-read-timeout feature

• Fix for bug with columns with identical names. #52
• DASHBOARD_ROW_LIMIT setting. #53
• Size SQL textarea to fit SQL. #51

• New --http-read-timeout option, thanks Eli Block! #14

• New --user-agent= option for passing a custom user-agent to Airtable. #12

Documentation improvements, bug fixes and support for SpatiaLite 5.

• The SQL editor can now be resized by dragging a handle. (#1236)
• Fixed a bug with JSON faceting and the __arraycontains filter caused by tables with spaces in their names. (#1239)
• Upgraded httpx dependency. (#1005)
• JSON faceting is now suggested even if a column contains blank strings. (#1246)
• New datasette.add_memory_database() method. (#1247)
• The Response.asgi_send() method is now documented. (#1266)
• The official Datasette Docker image now bundles SpatiaLite version 5. (#1278)
• Fixed a no such table: pragma_database_list bug when running Datasette against SQLite versions prior to SQLite 3.16.0. (#1276)
• HTML lists displayed in table cells are now styled correctly. Thanks, Bob Whitelock. (#1141, #1252)
• Configuration directory mode now correctly serves immutable databases that are listed in inspect-data.json. Thanks Campbell Allen and Frankie Robertson. (#1031, #1229)

• Better error message for --project that contains uppercase characters. #37
• Documentation on usage with GitHub Actions. #36

• --dayfirst and --yearfirst options for parsedate and parsedatetime commands. #12

• Link to dashboard from admin edit page. #48
• Better error message for queries with % that needs to be escaped. #41

• Pin underlying version of TableauScraper library to avoid problems caused by breaking changes. #4

• Removed unnecessary migration dependency, so this should now install on versions of Django prior to 3.1. Thanks, @jefftriplett. #46

• Serve cache-control: private HTTP header on private dashboards. #43

• Upgraded to latest @vercel/python builder, fixing a number of bugs involving incorrectly handled querystring characters. #28

• Optional support for HTTP Basic authentication. #15

• Documentation now lives at https://django-sql-dashboard.readthedocs.io/ (#36)
• Ability to copy and paste TSV from the default table display. (#29)
• Fixed two bugs with the way count links in column headers work. (#31, #32)
• New permissions system: a saved dashboard can now be made public, private, unlisted, group-only, staff-only or superuser-only. (#27)

• Changed default permission policy: saved dashboards are now inaccessible to the public by default (#37). This will change when permissions are implemented fully in #27.

• Added basic bar chart widget. #10
• Added markdown widget. #12
• Added html widget. #12

• Bundle widget templates in release.

• Fix for bug where templatetags were not bundled in the release package. #22

• Run tests against read-only 'dashboard' connection, refs #18
• Named SQL parameters are now extracted into form fields. #15
• Configure URLs using include() pattern. #14
• ?sql= params are now signed, must first be created using a POST request. #13
• New custom widgets framework #20
• Big number widget for displaying a big number. #20

Progress display now shows the total as well as the number currently in the queue. % hacker-news-to-sqlite trees hn.db 26440397 done: 1, in queue: 0, total: 1 done: 2, in queue: 28, total: 30 done: 3, in queue: 27, total: 30

• Include templates and migrations in the release. #4

• Workaround for psycopg2 dependency bug on macOS. #3

• Initial working alpha.

• Use white-space: pre-wrap when displaying lines, so lines now wrap. #21
• Long lines in results are now truncated, with a [...] link to expand them to full length. #22

• Support importing data from a full URL to a Tableau dashboard hosted somewhere other than public.tableau.com. #2

• No longer produces tables with single quotes in the name, which can cause bugs when viewed in Datasette. #1

First working version.

• Deployments that are configured to check organization membership now use read:org scope instead of the more permissive user scope. #73

• Fix ASGI lifespan bug. #1

• First working release

• Tool now also works against inputs that are JSON files consisting of a list of objects with identical keys, using --format=json. #12

• New sqlite-transform jsonsplit for converting foo,bar into ["foo", "bar"]. #10
• Added --dry-run option to sqlite-tranfsorm lambda for previewing the conversion that is about to be applied. #8

• New --sqlite database.db export option. #10

This release adds the ability to execute queries joining data from more than one database file - similar to the cross database querying feature introduced in Datasette 0.55.

• The db.attach(alias, filepath) Python method can be used to attach extra databases to the same connection, see db.attach() in the Python API documentation. (#113)
• The --attach option attaches extra aliased databases to run SQL queries against directly on the command-line, see attaching additional databases in the CLI documentation. (#236)

Support for cross-database SQL queries and built-in support for serving via HTTPS.

• The new --crossdb command-line option causes Datasette to attach up to ten database files to the same /_memory database connection. This enables cross-database SQL queries, including the ability to use joins and unions to combine data from tables that exist in different database files. See Cross-database queries for details. (#283)
• --ssl-keyfile and --ssl-certfile options can be used to specify a TLS certificate, allowing Datasette to serve traffic over https:// without needing to run it behind a separate proxy. (#1221)
• The /:memory: page has been renamed (and redirected) to /_memory for consistency with the new /_internal database introduced in Datasette 0.54. (#1205)
• Added plugin testing documentation on Using pdb for errors thrown inside Datasette. (#1207)
• The official Datasette Docker image now uses Python 3.7.10, applying the latest security fix for that Python version. (#1235)

• Fix broken tests against Datasette 0.54. #71
• Bundle JavaScript and CSS assets for GraphiQL, instead of loading them from a CDN. #73
• Pin to graphene dependency < 3.0. #72