1

General overview

I have a `history.csv CSV file of urls formated like this:

<url>;<title>;2024-03-30T12:00:00;2024-03-30T12:00:00

(The 2024-03-30T12:00:00 is the same)

The goal is to get this urls in the history of Qutebrowser who is in sqlite3.

What I did

Prerequest

First I closed Qutebrowser.

Opening the DB

I run:

sqlite3 ~/.local/share/qutebrowser/history.sqlite

Adding the new urls

In the sqlite3 shell I did:

.mode csv
.separator ';'
.import urls.txt History

The things seams good, except just some bad formated lines like:

history.csv:11671: unescaped " character
history.csv:11671: unescaped " character
history.csv:11772: expected 4 columns but found 6 - extras ignored

Verificaton

I verified the changes took place with:

SELECT * FROM History;

And yes, I saw the urls.

Closing

Finally I closed the console with:

.quit

The problem

When I went back to Qutebrowser and try to open some urls to see if it works, so it doesn’t work. The urls seems not imported.

The question

What to do to import urls into Qutebrowser’s history? By the way I described or any other way.

Update

The links I added appear when I activate :history command inside Qutebrowser. But did not appear in history completion. So it’s not a question of database.

1 Answer 1

1

SQLite doesn't enforce column types, and the time columns in cutebrowser's history SQL are simple integers (probably seconds since epoch). Yours are full strings with ISO-formatted date-times! So, qutebrowser probably can't parse these.

The fact that you seem to have " or ; in your CSV is another indication that you're not pre-processing your data correctly; URLs need to be urlencoded first. The fact that sqlite tells you there's unescaped quotes really is a bad sign, though; usually their parser is somewhat tolerant, and you might really have data that isn't unambiguosly parseable as CSV. Check these lines in a text editor ((neo)vim shouldn't have any problem opening a file with a couple thousand lines).

So, I'm afraid directly importing them through the sqlite3 "extended" SQL shell won't work. It should be pretty straightforward to throw together a script in a language of your choice; mine here would be python3, because it brings, built-in by default

  • the sqlite3 module, with which you can very easily insert rows into your table,
  • datetime for date conversions,
  • and both the csv module for reading CSV files, as well as simple line-read handling. I'd tend to manual parsing here, because you seem to have quotes in unexpected places.

A stub of the program I'd come up with would roughly look like

#!/usr/bin/env python3
from datetime import datetime
# Need to install python3-requests:
from requests.utils import requote_uri
import sqlite3
from sys import argv

def parseline(line: str) -> tuple:
    components = line.split(";")
    url = requote_uri(components[0])

    raw_title = components[1]
    if raw_title[0] == '"' and raw_title[-1] == '"':
        raw_title = raw_title[1:-1]
    title = raw_title

    timestamp1 = int(datetime.fromisoformat(components[-2]).timestamp())
    return (url, title, timestamp1, 0)

assert(len(argv) == 3)

with sqlite3.Connection(argv[1]) as db:
    handle = db.cursor()
    with open(argv[2], "r", encoding="utf-8") as textfile:
        handle.executemany("INSERT INTO History VALUES(?, ?, ?, ?)", 
            (parseline(line) for line in textfile))

Note that the above script is wholly untested, and just written from the top of my head. You can make it executable as chmod a+x scriptname and run it from your .local/share/qutebrowser directory, with the path to the semicolon-separated file as argument, like cd ~/.local/share/qutebrowser; ~/bin/scriptname ~/urls.txt

Debuging by Fauve

I could run the following script

#!/usr/bin/env python3
from datetime import datetime
# Need to install python3-requests:
from requests.utils import requote_uri
import sqlite3
from sys import argv

def parseline(line: str) -> tuple:
    components = line.split(";")
    url = requote_uri(components[0])

    raw_title = components[1]
    if raw_title[0] == '"' and raw_title[-1] == '"':
        raw_title = raw_title[1:-1]
    title = raw_title

    timestamp1 = int(datetime.fromisoformat(components[-2]).timestamp())
    return (url, title, timestamp1, 0)

assert(len(argv) == 3)

with sqlite3.Connection(argv[1]) as db:
    handle = db.cursor()
    with open(argv[2], "r", encoding="utf-8") as textfile:
        handle.executemany("INSERT INTO History VALUES(?, ?, ?, ?)", 
            (parseline(line) for line in textfile))

With python3 importHistory.py ~/.local/share/qutebrowser/history.sqlite history.csv

In my script you should give the DB path.

6
  • I am debuging youre code right now. But why did you join both url and title? Is the sqlite database of Qutebrowser shaped this way?
    – fauve
    Mar 31 at 15:41
  • ah sorry, that was an oversight of me; you can see the actual database schema if you run .schema History on the sqlite3 shell. You can also get a few exemplary entries using SELECT * FROM History LIMIT 20;. Mar 31 at 15:48
  • And thanks for the bugfixing! Mar 31 at 15:48
  • 1
    I debuged the script, and basically, I get the same behaviour. The entries are added to the DB, but from Qutebrowser I have absolutly no completion.
    – fauve
    Mar 31 at 16:23
  • sad! But then I'm out of ideas. Mar 31 at 17:18

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .