tWcWas0F

1# Convert one or more Opera .ADR files to SQLite
2
3import glob
4import time
5import sqlite3
6
7db_file_name = 'opera.sqlite'
8
9drop_table = "DROP TABLE IF EXISTS {}"
10create_bookmarks_table = "create table bookmarks (CREATED integer, DATE text, NAME text, URL text,  DESC text)"
11create_notes_table = "create table notes (CREATED integer, DATE text, NAME text, URL text)"
12
13tables = ["bookmarks", "notes"]
14
15conn = sqlite3.connect(db_file_name)
16cursor = conn.cursor()
17
18for table in tables:
19    cursor.execute(drop_table.format(table))
20
21cursor.execute(create_bookmarks_table)
22cursor.execute(create_notes_table)
23
24files = glob.glob('*.adr')
25
26for file in files:
27    with open(file, encoding="utf8", newline='\r\n') as f:
28        lines = [line.strip() for line in f]
29
30        started = False
31        rowType = 0  #1 - URL, 2- NOTE, 0- OTHERS
32
33        for line in lines:
34                if line == '#URL' or line == '#NOTE':
35                    if line == '#URL': rowType = 1
36                    if line == '#NOTE': rowType = 2
37                    started = True
38                    row = {}
39                elif line == '':
40                    if started and 'CREATED' in row:
41                        created = row['CREATED']
42                        name = row.get('NAME','')
43                        url = row.get('URL','')
44                        desc = row.get('DESCRIPTION','')
45
46                        if rowType == 1:
47                            cursor.execute("""insert into bookmarks(CREATED, DATE, NAME, URL, DESC) values(?,?,?,?,?)""",
48                                           [created,
49                                            time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(int(created))),
50                                            name,
51                                            url,
52                                            desc])
53
54                        if rowType == 2 and (name or url):
55                            cursor.execute("""insert into notes(CREATED, DATE, NAME, URL) values(?,?,?,?)""",
56                                           [created,
57                                            time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(int(created))),
58                                            name,
59                                            url])
60
61                    started = False
62                    rowType = 0
63                elif line == "Opera Hotlist version 2.0" or line == "Options: encoding = utf8, version=3":
64                    continue
65                else:
66                    if started:
67                        k,v = line.split('=',1)
68                        row[k] = v
69
70# Remove duplicates
71cursor.execute("""delete from bookmarks
72                where rowid not in (select min(rowid)
73                from bookmarks
74                group by NAME, URL, DESC)""")
75
76cursor.execute("""delete from notes
77                where rowid not in (select min(rowid)
78                from notes
79                group by NAME, URL)""")
80
81cursor.close()
82conn.commit()
83conn.close()