simonw/datasette-upload-csvs

Datasette plugin for uploading CSV files and converting them to database tables

datasette-upload-csvs

Datasette plugin for uploading CSV files and converting them to database tables

Installation

datasette install datasette-upload-csvs

Usage

The plugin adds an interface at /-/upload-csvs for uploading a CSV file and using it to create a new database table.

By default only the root actor can access the page - so you'll need to run Datasette with the --root option and click on the link shown in the terminal to sign in and access the page.

The upload-csvs permission governs access. You can use permission plugins such as datasette-permissions-sql to grant additional access to the write interface.

datasette-upload-csvs's Issues

Support drag-and-drop file uploads

https://www.smashingmagazine.com/2018/01/drag-drop-file-uploader-vanilla-js/ has some useful tips here.

Forbidden - No mutable databases available

I just installed datasette with pipx (Ubuntu 22.04), and I injected datasette-upload-csvs with:
pipx inject datasette datasette-upload-csvs

Then I launch datasette with root permissions
datasette --root
and click on the link with the token.

If I try to navigate to the /-/upload-csvs link I get the 403 error:
Forbidden - No mutable databases available

What am I doing wrong? Is there any setting I should change to upload csvs?

It is a fresh datasette instance, it has not database except _memory

Thanks,
Luigi

Should use block=False

On this line:

datasette-upload-csvs/datasette_upload_csvs/__init__.py

Line 127 in 7f2bacb

await db.execute_write_fn(insert_docs)

Require upload-csvs permission

For the 0.44 Datasette release: simonw/datasette#806

add primary key to (possibly) enable drill-down

After importing a small CSV, I used datasette enrichments to generate a full_address field, and an opencage_json field. The problem is that I can't see the full opencage_json data. I thought I could click on the rowid to drill into detail, but it is not linked. I'm guessing that that's because there is no primary key. If that's the case, it might be helpful to offer settings in datasette-upload-csvs, so that the primary key defaults to rowid unless another column is selected.

Ability to set permissions on a per-database basis

Need to consider permissions here too. Currently:

datasette-upload-csvs/datasette_upload_csvs/__init__.py

Lines 46 to 50 in a0b64f7

	async def upload_csvs(scope, receive, datasette, request):
	if not await datasette.permission_allowed(
	request.actor, "upload-csvs", default=False
	):
	raise Forbidden("Permission denied for upload-csvs")

That needs to take the database into consideration as well.

Good opportunity for me to revise (and maybe document) how Datasette permissions deal with changing an existing permission to now depend on a resource.

Originally posted by @simonw in #28 (comment)

UI to select name of table at time of upload

Picking a better name for the new table is too common a requirement to leave to other plugins such as https://github.com/simonw/datasette-edit-schema

Set up a live ephemeral demo

A demo would be neat. It could delete tables shortly after they are uploaded and run on Cloud Run.

Improved compatibility with Datasette 1.0a+

Error if CSV filename contains spaces

Redesign how this plugin handles parsing CSV and writing to the DB

The plugin currently works by kicking off a long-running operation in the single "write" thread and parsing the CSV file entirely within that operation:

datasette-upload-csvs/datasette_upload_csvs/__init__.py

Line 176 in a0b64f7

await db.execute_write_fn(insert_docs_catch_errors, block=False)

I'm having trouble getting the tests to pass against Datasette 1.0 - see #36 - which has made me think that this might not be the best approach.

I'd rather not tie up the write connection for so long - ideally I'd like the parsing to happen in a separate thread with rows written to the database 100 at a time or so.

I'm not entirely sure how to do that, so I'll likely get a good TIL out of it.

Allow selection of the database to add the table to

Currently the database that a file is uploaded to is hard-coded:

datasette-upload-csvs/datasette_upload_csvs/__init__.py

Lines 52 to 57 in c613805

	# For the moment just use the first database that's not immutable
	db = [
	db
	for db in datasette.databases.values()
	if db.is_mutable and db.name != "_internal"
	][0]

Upgrade for compatibility with Datasette 0.44+

Similar to simonw/datasette-configure-fts#9

Tests fail against latest dependencies

FAILED tests/test_datasette_upload_csvs.py::test_redirect - TypeError: AsyncClient.get() got an unexpected keyword argument 'allow_redirects'
FAILED tests/test_datasette_upload_csvs.py::test_upload - TypeError: Expected bytes or bytes-like object got: <class 'str'>
FAILED tests/test_datasette_upload_csvs.py::test_permissions - TypeError: AsyncClient.get() got an unexpected keyword argument 'allow_redirects'

Detect column types in uploaded data automatically

Like sqlite-utils does:

• simonw/sqlite-utils#282
• simonw/sqlite-utils#445

Rename /-/upload-csv to /-/upload-csvs and set up a redirect

Since the plugin is called datasette-upload-csvs it's confusing that the URL is /-/upload-csv. I'm going to fix that and redirect the old URL to the new one.

If a database has no tables, add a prompt to upload a CSV

Make it easier for people to start using this plugin by showing them a prompt if their database is empty.

Could show that prompt at the bottom of the table list too.

URL on final confirmation page needs to use tilde encoding

I introduced tilde encoding in Datasette 0.61: https://docs.datasette.io/en/stable/changelog.html#v0-61

This template needs to be fixed to use it:

datasette-upload-csvs/datasette_upload_csvs/templates/upload_csv_done.html

Lines 8 to 10 in 777cdaa

	<p>Importing rows into
	<a href="/{{ database|quote_plus }}/{{ table|quote_plus }}">{{ table }}</a>
	</p>

Make upload-csvs more discoverable by adding a link to Datasette's top level nav

It'd be lovely to not have to remember the path /-/upload-csvs, but to have somewhere in the app's UI that shows a way to navigate there if the plugin is enabled and available.

If table already exists, create a new table with _2

Follows:

• #17

Template extends extra_head without super()

This means that Datasette installations with a base.html that itself extends extra_head are incompatible with this plugin.

UnicodeDecodeError: 'utf-8' codec can't decode byte 0xa3 in position 105: invalid start byte

Got this uploading this CSV: https://static.simonwillison.net/static/2022/Animal%20Rescue%20incidents%20attended%20by%20LFB%20from%20Jan%202009.csv

Relevant TODO:

datasette-upload-csvs/datasette_upload_csvs/__init__.py

Lines 92 to 96 in 8127afc

	def insert_docs(conn):
	database = sqlite_utils.Database(conn)
	# TODO: Support other encodings:
	reader = csv_std.reader(codecs.iterdecode(csv.file, "utf-8"))
	headers = next(reader)

Defaults to uploading to _internal which is wrong

As-of Datasette 0.54 a new _internal database is available to the root user - if you are signed in as root you get this, which is bad because _internal is an in-memory database not designed to handle uploaded content:

Title of upload page is incorrect

Upload a CSV to ""

Show upload progress bar

This is tricky with fetch() but easy with XMLHttpRequest - https://www.smashingmagazine.com/2018/01/drag-drop-file-uploader-vanilla-js/ has a good recipe for this.

Don't hide errors in imports from the user

Related issue: if this happens an error message is shown in the logs but not to the user.

Originally posted by @simonw in #23 (comment)

Preview first 100 lines before importing

Importing CSV files is tricky when there's a chance they might actually be TSV, or may use an unexpected encoding or strange escaping rules.

I can address many of these issues by providing a "preview" of the CSV file before running the conversion to SQLite. The preview could operate against the first 100 rows (which can be efficiently loaded without consuming the whole file). The user can then confirm that it looks like the import is correct, or can switch to CSV or some other encoding/escaping mechanism and preview again.

This also provides the opportunity to allow the user to select the primary key, select columns that should be extracted into a separate table and select columns for indexing with FTS.

Switch CI to GitHub Actions

Don't crash if there are no writable databases available

This can happen when running plugins like this one:

• simonw/datasette-upload-dbs#5

Use a template for the upload interface

I can do this thanks to the new Datasette.render_template() feature released in Datasette 0.35. simonw/datasette#577

ascii codec Error uploading CSV

Got this uploading CSV of the Squirrel Census:

{
	"0": {
		"id": "f029f3d9-1d9c-43f7-9eaa-297d3e3e3f9d",
		"filename": "2018_Central_Park_Squirrel_Census_-_Squirrel_Data",
		"bytes_todo": 747760,
		"bytes_done": 7604,
		"rows_done": 30,
		"started": "2022-07-03 20:10:18.452435",
		"completed": null,
		"error": "'ascii' codec can't decode byte 0xe2 in position 170: ordinal not in range(128)"
	}
}

Don't hang the request while processing the CSV

The gif in #5 demonstrates how a large CSV upload results in a request that hangs for a while.

Starlette background tasks might help here: https://www.starlette.io/background/ - suggestion from encode/starlette#697 (comment)