--sniff option for sniffing delimiters

[simonw]

I just spotted that csv.Sniffer in the Python standard library has a .has_header(sample) method which detects if the first row appears to be a header or not, which is interesting. https://docs.python.org/3/library/csv.html#csv.Sniffer

Originally posted by @simonw in #228 (comment)

[simonw]

Running this could take any CSV (or TSV) file and automatically detect the delimiter. If no header row is detected it could add unknown1,unknown2 headers:

sqlite-utils insert db.db data file.csv --sniff

(Using --sniff would imply --csv)

This could be called --sniffer instead but I like --sniff better.

[simonw]

The challenge here is how to read the first 2048 bytes and then reset the incoming file.

The Python docs example looks like this:

with open('example.csv', newline='') as csvfile:
    dialect = csv.Sniffer().sniff(csvfile.read(1024))
    csvfile.seek(0)
    reader = csv.reader(csvfile, dialect)

Here's the relevant code in sqlite-utils:

sqlite-utils/sqlite_utils/cli.py

Lines 671 to 679 in 726219c

	if csv or tsv:
	dialect = "excel-tab" if tsv else "excel"
	with file_progress(json_file, silent=silent) as json_file:
	csv_reader_args = {"dialect": dialect}
	if delimiter:
	csv_reader_args["delimiter"] = delimiter
	if quotechar:
	csv_reader_args["quotechar"] = quotechar
	reader = csv_std.reader(json_file, **csv_reader_args)

The challenge is going to be having the --sniff option work with the progress bar. Here's how file_progress() works:

sqlite-utils/sqlite_utils/utils.py

Lines 106 to 113 in 726219c

	@contextlib.contextmanager
	def file_progress(file, silent=False, **kwargs):
	if silent or file.raw.fileno() == 0: # 0 = stdin
	yield file
	else:
	file_length = os.path.getsize(file.raw.name)
	with click.progressbar(length=file_length, **kwargs) as bar:
	yield UpdateWrapper(file, bar.update)

If file.raw is stdin can I do the equivalent of csvfile.seek(0) on it?

[simonw]

No, you can't .seek(0) on stdin:

  File "/Users/simon/Dropbox/Development/sqlite-utils/sqlite_utils/cli.py", line 678, in insert_upsert_implementation
    json_file.raw.seek(0)
OSError: [Errno 29] Illegal seek

[simonw]

Types involved:

(Pdb) type(json_file.raw)
<class '_io.FileIO'>
(Pdb) type(json_file)
<class 'encodings.utf_8.StreamReader'>

[simonw]

Maybe I shouldn't be using StreamReader at all - https://www.python.org/dev/peps/pep-0400/ suggests that it should be deprecated in favour of io.TextIOWrapper. I'm using StreamReader due to this line:

sqlite-utils/sqlite_utils/cli.py

Lines 667 to 668 in 726219c

	encoding = encoding or "utf-8"
	json_file = codecs.getreader(encoding)(json_file)

[simonw]

There are two code paths here that matter:

• For a regular file, can read the first 2048 bytes, then .seek(0) before continuing. That's easy.
• stdin is harder. I need to read and buffer the first 2048 bytes, then pass an object to csv.reader() which will replay that chunk and then play the rest of stdin.

I'm a bit stuck on the second one. Ideally I could use something like itertools.chain() but I can't find an alternative for file-like objects.

[simonw]

I think I've got it. I can use io.BufferedReader() to get an object I can run .peek(2048) on, then wrap THAT in io.TextIOWrapper:

    encoding = encoding or "utf-8"
    buffered = io.BufferedReader(json_file, buffer_size=4096)
    decoded = io.TextIOWrapper(buffered, encoding=encoding, line_buffering=True)
    if pk and len(pk) == 1:
        pk = pk[0]
    if csv or tsv:
        if sniff:
            # Read first 2048 bytes and use that to detect
            first_bytes = buffered.peek(2048)
            print('first_bytes', first_bytes)

[simonw]

Here's the implementation in Python: https://github.com/python/cpython/blob/63298930fb531ba2bb4f23bc3b915dbf1e17e9e1/Lib/csv.py#L204-L225