Skip to content

table.search(..., quote=True) parameter and sqlite-utils search --quote option #296

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom
Closed
deafmute1 opened this issue Jul 14, 2021 · 6 comments
Closed

table.search(..., quote=True) parameter and sqlite-utils search --quote option #296

deafmute1 opened this issue Jul 14, 2021 · 6 comments
Labels
enhancement New feature or request

Comments

@deafmute1
Copy link

Hi,
Recently got this error:

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/home/ethan/git/music-metadata-indexer/src/mmindexer/__init__.py", line 38, in <module>
    start("/home/ethan/git/music-metadata-indexer/sample", "/home/ethan/git/music-metadata-indexer/test.db")
  File "/home/ethan/git/music-metadata-indexer/src/mmindexer/__init__.py", line 23, in start
    scanner.build_database()
  File "/home/ethan/git/music-metadata-indexer/src/mmindexer/scan.py", line 79, in build_database
    _import_song(self.db, Path(dirpath).joinpath(f), self.logger) 
  File "/home/ethan/git/music-metadata-indexer/src/mmindexer/scan.py", line 23, in _import_song
    db.add_song(filepath)
  File "/home/ethan/git/music-metadata-indexer/src/mmindexer/index.py", line 166, in add_song
    for match in self.search("albums", album): 
  File "/home/ethan/git/music-metadata-indexer/env/lib/python3.9/site-packages/sqlite_utils/db.py", line 1625, in search
    cursor = self.db.execute(
  File "/home/ethan/git/music-metadata-indexer/env/lib/python3.9/site-packages/sqlite_utils/db.py", line 243, in execute
    return self.conn.execute(sql, parameters)
sqlite3.OperationalError: fts5: syntax error near "."

So, the error seems to suggest there was a "." character somewhere in the SQL command that was causing the error. I did a little digging and found this in the docs: https://www.sqlite.org/fts5.html#fts5_strings. "." is one of the many prohibited characters.

My solution was to just strip these out of the query using this line
query = query.translate({e: None for e in itertools.chain(range(0,26), range(27, 48), range(58,65), range(91,95), [96], range(123,128))})

Perhaps this could be included into the table.search() function?
@simonw
Copy link
Owner

simonw commented Aug 18, 2021

This is a good call. I have a fix for this in Datasette but it's not in sqlite-utils yet: https://github.com/simonw/datasette/blob/adb5b70de5cec3c3dd37184defe606a082c232cf/datasette/utils/__init__.py#L824-L835

@simonw simonw added the enhancement New feature or request label Aug 18, 2021
@simonw
Copy link
Owner

simonw commented Aug 18, 2021 •
edited
Loading

I think I'll do this as an optional table.search(..., escape=True) parameter.

Actually I'll do quote=True for consistency with the new db.quote_fts() method.

@simonw simonw mentioned this issue Aug 18, 2021
Merged
simonw pushed a commit that referenced this issue Aug 18, 2021
@DeNeutoy
db.quote_fts() method, thanks Mark Neumann
53fec0d 
Refs #296, closes #246.
@simonw simonw mentioned this issue Aug 18, 2021
Closed
@simonw simonw changed the title table.search() allows prohibited characters to be in the search query. table.search(..., quote=True) parameter Aug 18, 2021
@simonw simonw changed the title table.search(..., quote=True) parameter table.search(..., quote=True) parameter Aug 18, 2021
@simonw
Copy link
Owner

simonw commented Aug 18, 2021

Also add sqlite-utils search ... --quote option.

simonw added a commit that referenced this issue Aug 18, 2021
@simonw
table.search(quote=True) parameter, refs #296
8ae77a6
@simonw
Copy link
Owner

simonw commented Aug 18, 2021 

sqlite-utils search fixtures.db searchable 'dog"'
Error: malformed MATCH expression: [dog"]

This error message could suggest retrying with --quote.

@simonw simonw changed the title table.search(..., quote=True) parameter table.search(..., quote=True) parameter and sqlite-utils search --quote option Aug 18, 2021
@simonw simonw closed this as completed in ccf128c Aug 18, 2021
@simonw
Copy link
Owner

simonw commented Aug 18, 2021 

% sqlite-utils search fixtures.db searchable 'dog"'
Error: malformed MATCH expression: [dog"]

Try running this again with the --quote option

@simonw
Copy link
Owner

simonw commented Aug 18, 2021 •
edited
Loading

Documentation for table.search(..., quote=True): https://sqlite-utils.datasette.io/en/latest/python-api.html#searching-with-table-search

In the API reference: https://sqlite-utils.datasette.io/en/latest/reference.html#sqlite_utils.db.Table.search

And for the CLI --quote option: https://sqlite-utils.datasette.io/en/latest/cli.html#executing-searches

simonw added a commit that referenced this issue Aug 18, 2021
@simonw
Release 3.16
5912878 
Refs #37, #246, #294, #295, #296, #314, #316
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
enhancement New feature or request
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@simonw @deafmute1