We are currently solving the following issue within our repository. Here is the issue text:
--- BEGIN ISSUE ---
SQLite databases with invalid unicode not supported
**Small description**

Opening a SQLite database will fail when a cell in a TEXT column has invalid unicode characters

**Expected result**

Either the specific cell that has invalid unicode is not imported, or it is imported but the invalid characters are stripped or slash-escaped.

**Actual result with screenshot**

![error screenshot](https://user-images.githubusercontent.com/5001092/137644252-3474128c-52be-40cb-89bb-6b13498169d7.png)

![error screenshot](https://user-images.githubusercontent.com/5001092/137644398-e3b388e3-ebfe-476e-94b3-ede0a418223f.png)

(those are errors from two different DBs that I've seen this problem on)

**Steps to reproduce with sample data and a .vd**

Here's a SQLite file that has this problem: https://hack.wesleyac.com/test.sqlite

**Additional context**

`vd --version`: `saul.pw/VisiData v2.4`

dbcli/litecli#89 is a similar issue in a different python project, which may have useful context for fixing this


--- END ISSUE ---

Below are some code segments, each from a relevant file. One or more of these files may contain bugs.
--- BEGIN FILES ---
Path: `visidata/loaders/sqlite.py`
Content:
```
1 import re
2 
3 from visidata import *
4 
5 @VisiData.api
6 def open_sqlite(vd, p):
7     return SqliteIndexSheet(p.name, source=p)
8 
9 VisiData.open_sqlite3 = VisiData.open_sqlite
10 VisiData.open_db = VisiData.open_sqlite
11 
12 # rowdef: list of values
13 class SqliteSheet(Sheet):
14     'Provide functionality for importing SQLite databases.'
15     savesToSource = True
16     defer = True
17 
18     def resolve(self):
19         'Resolve all the way back to the original source Path.'
20         return self.source.resolve()
21 
22     def conn(self):
23         import sqlite3
24         return sqlite3.connect(str(self.resolve()))
25 
26     def execute(self, conn, sql, parms=None):
27         parms = parms or []
28         vd.debug(sql)
29         return conn.execute(sql, parms)
30 
31     def iterload(self):
32         import sqlite3
33 
34         def parse_sqlite_type(t):
35             m = re.match(r'(\w+)(\((\d+)(,(\d+))?\))?', t.upper())
36             if not m: return anytype
37             typename, _, i, _, f = m.groups()
38             if typename == 'DATE': return date
39             if typename == 'INTEGER': return int
40             if typename == 'REAL': return float
41             if typename == 'NUMBER':
42                 return int if f == '0' else float
43             return anytype
44 
45         with self.conn() as conn:
46             tblname = self.tableName
47             if not isinstance(self, SqliteIndexSheet):
48                 self.columns = []
49                 self.addColumn(ColumnItem('rowid', 0, type=int, width=0))
50                 for r in self.execute(conn, 'PRAGMA TABLE_XINFO("%s")' % tblname):
51                     colnum, colname, coltype, nullable, defvalue, colkey, *_ = r
52                     c = ColumnItem(colname, colnum+1, type=parse_sqlite_type(coltype))
53                     self.addColumn(c)
54 
55                     if colkey:
56                         self.setKeys([c])
57 
58             try:
59                 r = self.execute(conn, 'SELECT rowid, * FROM "%s"' % tblname)
60             except sqlite3.OperationalError:
61                 vd.error('tables WITHOUT ROWID not supported')
62             yield from Progress(r, total=r.rowcount-1)
63 
64     @asyncthread
65     def putChanges(self):
66         adds, mods, dels = self.getDeferredChanges()
67         options_safe_error = options.safe_error
68         def value(row, col):
69             v = col.getTypedValue(row)
70             if isinstance(v, TypedWrapper):
71                 if isinstance(v, TypedExceptionWrapper):
72                     return options_safe_error
73                 else:
74                     return None
75             elif not isinstance(v, (int, float, str)):
76                 v = col.getDisplayValue(r)
77             return v
78 
79         def values(row, cols):
80             vals = []
81             for c in cols:
82                 vals.append(value(row, c))
83             return vals
84 
85         with self.conn() as conn:
86             wherecols = [self.columns[0]] # self.column("rowid")
87             for r in adds.values():
88                 cols = self.visibleCols
89                 sql = 'INSERT INTO "%s" ' % self.tableName
90                 sql += '(%s)' % ','.join(c.name for c in cols)
91                 sql += ' VALUES (%s)' % ','.join('?' for c in cols)
92                 res = self.execute(conn, sql, parms=values(r, cols))
93                 if res.rowcount != res.arraysize:
94                     vd.warning('not all rows inserted') # f'{res.rowcount}/{res.arraysize} rows inserted'
95 
96             for row, rowmods in mods.values():
97                 sql = 'UPDATE "%s" SET ' % self.tableName
98                 sql += ', '.join('%s=?' % c.name for c, _ in rowmods.items())
99                 sql += ' WHERE %s' % ' AND '.join('"%s"=?' % c.name for c in wherecols)
100                 newvals=values(row, [c for c, _ in rowmods.items()])
101                 # calcValue gets the 'previous' value (before update)
102                 wherevals=list(Column.calcValue(c, row) or '' for c in wherecols)
103                 res = self.execute(conn, sql, parms=newvals+wherevals)
104                 if res.rowcount != res.arraysize:
105                     vd.warning('not all rows updated') # f'{res.rowcount}/{res.arraysize} rows updated'
106 
107             for row in dels.values():
108                 sql = 'DELETE FROM "%s" ' % self.tableName
109                 sql += ' WHERE %s' % ' AND '.join('"%s"=?' % c.name for c in wherecols)
110                 wherevals=list(Column.calcValue(c, row) for c in wherecols)
111                 res = self.execute(conn, sql, parms=wherevals)
112                 if res.rowcount != res.arraysize:
113                     vd.warning('not all rows deleted') # f'{res.rowcount}/{res.arraysize} rows deleted'
114 
115             conn.commit()
116 
117         self.preloadHook()
118         self.reload()
119 
120 
121 class SqliteIndexSheet(SqliteSheet, IndexSheet):
122     rowtype = 'tables'
123     tableName = 'sqlite_master'
124     savesToSource = True
125     defer = True
126     def iterload(self):
127         for row in SqliteSheet.iterload(self):
128             if row[1] != 'index':
129                 tblname = row[2]
130                 yield SqliteSheet(tblname, source=self, tableName=tblname, row=row)
131 
132     def putChanges(self):
133         adds, mods, dels = self.getDeferredChanges()
134         with self.conn() as conn:
135             for r in adds.values():
136                 vd.warning('create a new table by saving a new sheet to this database file')
137 
138             for row, rowmods in mods.values():
139                 cname = self.column('name')
140                 if len(rowmods) == 1 and cname in rowmods:
141                     sql='ALTER TABLE "%s" RENAME TO "%s"' % (cname.calcValue(row), rowmods[cname])
142                     self.execute(conn, sql)
143                 else:
144                     vd.warning('can only modify table name')
145 
146             for row in dels.values():
147                 sql = 'DROP TABLE "%s"' % row.tableName
148                 self.execute(conn, sql)
149 
150             conn.commit()
151 
152         self.preloadHook()
153         self.reload()
154 
155 class SqliteQuerySheet(SqliteSheet):
156     def iterload(self):
157         with self.conn() as conn:
158             self.columns = []
159             for c in type(self).columns:
160                 self.addColumn(copy(c))
161             self.result = self.execute(conn, self.query, parms=getattr(self, 'parms', []))
162             for i, desc in enumerate(self.result.description):
163                 self.addColumn(ColumnItem(desc[0], i))
164 
165             for row in self.result:
166                 yield row
167 
168 
169 
170 @VisiData.api
171 def save_sqlite(vd, p, *vsheets):
172     import sqlite3
173     conn = sqlite3.connect(str(p))
174     conn.row_factory = sqlite3.Row
175     c = conn.cursor()
176 
177     sqltypes = {
178         int: 'INTEGER',
179         float: 'REAL',
180         currency: 'REAL'
181     }
182 
183     for vs in vsheets:
184         vs.ensureLoaded()
185     vd.sync()
186 
187     for vs in vsheets:
188         tblname = clean_to_id(vs.name)
189         sqlcols = []
190         for col in vs.visibleCols:
191             sqlcols.append('"%s" %s' % (col.name, sqltypes.get(col.type, 'TEXT')))
192         sql = 'CREATE TABLE IF NOT EXISTS "%s" (%s)' % (tblname, ', '.join(sqlcols))
193         c.execute(sql)
194 
195         for r in Progress(vs.rows, 'saving'):
196             sqlvals = []
197             for col in vs.visibleCols:
198                 v = col.getTypedValue(r)
199                 if isinstance(v, TypedWrapper):
200                     if isinstance(v, TypedExceptionWrapper):
201                         v = options.safe_error
202                     else:
203                         v = None
204                 elif not isinstance(v, (int, float, str)):
205                     v = col.getDisplayValue(r)
206                 sqlvals.append(v)
207             sql = 'INSERT INTO "%s" (%s) VALUES (%s)' % (tblname, ','.join(f'"{c.name}"' for c in vs.visibleCols), ','.join('?' for v in sqlvals))
208             c.execute(sql, sqlvals)
209 
210     conn.commit()
211 
212     vd.status("%s save finished" % p)
213 
214 
215 SqliteIndexSheet.addCommand('a', 'add-table', 'fail("create a new table by saving a sheet to this database file")', 'stub; add table by saving a sheet to the db file instead')
216 SqliteIndexSheet.bindkey('ga', 'add-table')
217 SqliteSheet.class_options.header = 0
218 VisiData.save_db = VisiData.save_sqlite
219
```
--- END FILES ---

Please first localize the bug based on the issue statement, and then generate a patch according to the `git diff` format fenced by three backticks.

Here is an example:

```diff
diff --git a/examples/server_async.py b/examples/server_async.py
--- a/examples/server_async.py
+++ b/examples/server_async.py
@@ -313,4 +313,4 @@
 
 
 if __name__ == "__main__":
-    asyncio.run(run_async_server("."), debug=True)
+    asyncio.run(run_async_server(), debug=True)
diff --git a/examples/server_sync.py b/examples/server_sync.py
--- a/examples/server_sync.py
+++ b/examples/server_sync.py
@@ -313,5 +313,5 @@
 
 
 if __name__ == "__main__":
-    server = run_sync_server(".")
+    server = run_sync_server()
     server.shutdown()

```

diff --git a/visidata/loaders/sqlite.py b/visidata/loaders/sqlite.py
--- a/visidata/loaders/sqlite.py
+++ b/visidata/loaders/sqlite.py
@@ -21,7 +21,9 @@
 
     def conn(self):
         import sqlite3
-        return sqlite3.connect(str(self.resolve()))
+        con = sqlite3.connect(str(self.resolve()))
+        con.text_factory = lambda s, enc=self.options.encoding: s.decode(enc)
+        return con
 
     def execute(self, conn, sql, parms=None):
         parms = parms or []
@@ -55,10 +57,7 @@
                     if colkey:
                         self.setKeys([c])
 
-            try:
-                r = self.execute(conn, 'SELECT rowid, * FROM "%s"' % tblname)
-            except sqlite3.OperationalError:
-                vd.error('tables WITHOUT ROWID not supported')
+            r = self.execute(conn, 'SELECT rowid, * FROM "%s"' % tblname)
             yield from Progress(r, total=r.rowcount-1)
 
     @asyncthread
@@ -171,6 +170,7 @@
 def save_sqlite(vd, p, *vsheets):
     import sqlite3
     conn = sqlite3.connect(str(p))
+    conn.text_factory = lambda s, enc=vsheets[0].options.encoding: s.decode(enc)
     conn.row_factory = sqlite3.Row
     c = conn.cursor()

{"golden_diff": "diff --git a/visidata/loaders/sqlite.py b/visidata/loaders/sqlite.py\n--- a/visidata/loaders/sqlite.py\n+++ b/visidata/loaders/sqlite.py\n@@ -21,7 +21,9 @@\n \n     def conn(self):\n         import sqlite3\n-        return sqlite3.connect(str(self.resolve()))\n+        con = sqlite3.connect(str(self.resolve()))\n+        con.text_factory = lambda s, enc=self.options.encoding: s.decode(enc)\n+        return con\n \n     def execute(self, conn, sql, parms=None):\n         parms = parms or []\n@@ -55,10 +57,7 @@\n                     if colkey:\n                         self.setKeys([c])\n \n-            try:\n-                r = self.execute(conn, 'SELECT rowid, * FROM \"%s\"' % tblname)\n-            except sqlite3.OperationalError:\n-                vd.error('tables WITHOUT ROWID not supported')\n+            r = self.execute(conn, 'SELECT rowid, * FROM \"%s\"' % tblname)\n             yield from Progress(r, total=r.rowcount-1)\n \n     @asyncthread\n@@ -171,6 +170,7 @@\n def save_sqlite(vd, p, *vsheets):\n     import sqlite3\n     conn = sqlite3.connect(str(p))\n+    conn.text_factory = lambda s, enc=vsheets[0].options.encoding: s.decode(enc)\n     conn.row_factory = sqlite3.Row\n     c = conn.cursor()\n", "issue": "SQLite databases with invalid unicode not supported\n**Small description**\r\n\r\nOpening a SQLite database will fail when a cell in a TEXT column has invalid unicode characters\r\n\r\n**Expected result**\r\n\r\nEither the specific cell that has invalid unicode is not imported, or it is imported but the invalid characters are stripped or slash-escaped.\r\n\r\n**Actual result with screenshot**\r\n\r\n![error screenshot](https://user-images.githubusercontent.com/5001092/137644252-3474128c-52be-40cb-89bb-6b13498169d7.png)\r\n\r\n![error screenshot](https://user-images.githubusercontent.com/5001092/137644398-e3b388e3-ebfe-476e-94b3-ede0a418223f.png)\r\n\r\n(those are errors from two different DBs that I've seen this problem on)\r\n\r\n**Steps to reproduce with sample data and a .vd**\r\n\r\nHere's a SQLite file that has this problem: https://hack.wesleyac.com/test.sqlite\r\n\r\n**Additional context**\r\n\r\n`vd --version`: `saul.pw/VisiData v2.4`\r\n\r\ndbcli/litecli#89 is a similar issue in a different python project, which may have useful context for fixing this\r\n\n", "before_files": [{"content": "import re\n\nfrom visidata import *\n\n@VisiData.api\ndef open_sqlite(vd, p):\n    return SqliteIndexSheet(p.name, source=p)\n\nVisiData.open_sqlite3 = VisiData.open_sqlite\nVisiData.open_db = VisiData.open_sqlite\n\n# rowdef: list of values\nclass SqliteSheet(Sheet):\n    'Provide functionality for importing SQLite databases.'\n    savesToSource = True\n    defer = True\n\n    def resolve(self):\n        'Resolve all the way back to the original source Path.'\n        return self.source.resolve()\n\n    def conn(self):\n        import sqlite3\n        return sqlite3.connect(str(self.resolve()))\n\n    def execute(self, conn, sql, parms=None):\n        parms = parms or []\n        vd.debug(sql)\n        return conn.execute(sql, parms)\n\n    def iterload(self):\n        import sqlite3\n\n        def parse_sqlite_type(t):\n            m = re.match(r'(\\w+)(\\((\\d+)(,(\\d+))?\\))?', t.upper())\n            if not m: return anytype\n            typename, _, i, _, f = m.groups()\n            if typename == 'DATE': return date\n            if typename == 'INTEGER': return int\n            if typename == 'REAL': return float\n            if typename == 'NUMBER':\n                return int if f == '0' else float\n            return anytype\n\n        with self.conn() as conn:\n            tblname = self.tableName\n            if not isinstance(self, SqliteIndexSheet):\n                self.columns = []\n                self.addColumn(ColumnItem('rowid', 0, type=int, width=0))\n                for r in self.execute(conn, 'PRAGMA TABLE_XINFO(\"%s\")' % tblname):\n                    colnum, colname, coltype, nullable, defvalue, colkey, *_ = r\n                    c = ColumnItem(colname, colnum+1, type=parse_sqlite_type(coltype))\n                    self.addColumn(c)\n\n                    if colkey:\n                        self.setKeys([c])\n\n            try:\n                r = self.execute(conn, 'SELECT rowid, * FROM \"%s\"' % tblname)\n            except sqlite3.OperationalError:\n                vd.error('tables WITHOUT ROWID not supported')\n            yield from Progress(r, total=r.rowcount-1)\n\n    @asyncthread\n    def putChanges(self):\n        adds, mods, dels = self.getDeferredChanges()\n        options_safe_error = options.safe_error\n        def value(row, col):\n            v = col.getTypedValue(row)\n            if isinstance(v, TypedWrapper):\n                if isinstance(v, TypedExceptionWrapper):\n                    return options_safe_error\n                else:\n                    return None\n            elif not isinstance(v, (int, float, str)):\n                v = col.getDisplayValue(r)\n            return v\n\n        def values(row, cols):\n            vals = []\n            for c in cols:\n                vals.append(value(row, c))\n            return vals\n\n        with self.conn() as conn:\n            wherecols = [self.columns[0]] # self.column(\"rowid\")\n            for r in adds.values():\n                cols = self.visibleCols\n                sql = 'INSERT INTO \"%s\" ' % self.tableName\n                sql += '(%s)' % ','.join(c.name for c in cols)\n                sql += ' VALUES (%s)' % ','.join('?' for c in cols)\n                res = self.execute(conn, sql, parms=values(r, cols))\n                if res.rowcount != res.arraysize:\n                    vd.warning('not all rows inserted') # f'{res.rowcount}/{res.arraysize} rows inserted'\n\n            for row, rowmods in mods.values():\n                sql = 'UPDATE \"%s\" SET ' % self.tableName\n                sql += ', '.join('%s=?' % c.name for c, _ in rowmods.items())\n                sql += ' WHERE %s' % ' AND '.join('\"%s\"=?' % c.name for c in wherecols)\n                newvals=values(row, [c for c, _ in rowmods.items()])\n                # calcValue gets the 'previous' value (before update)\n                wherevals=list(Column.calcValue(c, row) or '' for c in wherecols)\n                res = self.execute(conn, sql, parms=newvals+wherevals)\n                if res.rowcount != res.arraysize:\n                    vd.warning('not all rows updated') # f'{res.rowcount}/{res.arraysize} rows updated'\n\n            for row in dels.values():\n                sql = 'DELETE FROM \"%s\" ' % self.tableName\n                sql += ' WHERE %s' % ' AND '.join('\"%s\"=?' % c.name for c in wherecols)\n                wherevals=list(Column.calcValue(c, row) for c in wherecols)\n                res = self.execute(conn, sql, parms=wherevals)\n                if res.rowcount != res.arraysize:\n                    vd.warning('not all rows deleted') # f'{res.rowcount}/{res.arraysize} rows deleted'\n\n            conn.commit()\n\n        self.preloadHook()\n        self.reload()\n\n\nclass SqliteIndexSheet(SqliteSheet, IndexSheet):\n    rowtype = 'tables'\n    tableName = 'sqlite_master'\n    savesToSource = True\n    defer = True\n    def iterload(self):\n        for row in SqliteSheet.iterload(self):\n            if row[1] != 'index':\n                tblname = row[2]\n                yield SqliteSheet(tblname, source=self, tableName=tblname, row=row)\n\n    def putChanges(self):\n        adds, mods, dels = self.getDeferredChanges()\n        with self.conn() as conn:\n            for r in adds.values():\n                vd.warning('create a new table by saving a new sheet to this database file')\n\n            for row, rowmods in mods.values():\n                cname = self.column('name')\n                if len(rowmods) == 1 and cname in rowmods:\n                    sql='ALTER TABLE \"%s\" RENAME TO \"%s\"' % (cname.calcValue(row), rowmods[cname])\n                    self.execute(conn, sql)\n                else:\n                    vd.warning('can only modify table name')\n\n            for row in dels.values():\n                sql = 'DROP TABLE \"%s\"' % row.tableName\n                self.execute(conn, sql)\n\n            conn.commit()\n\n        self.preloadHook()\n        self.reload()\n\nclass SqliteQuerySheet(SqliteSheet):\n    def iterload(self):\n        with self.conn() as conn:\n            self.columns = []\n            for c in type(self).columns:\n                self.addColumn(copy(c))\n            self.result = self.execute(conn, self.query, parms=getattr(self, 'parms', []))\n            for i, desc in enumerate(self.result.description):\n                self.addColumn(ColumnItem(desc[0], i))\n\n            for row in self.result:\n                yield row\n\n\n\n@VisiData.api\ndef save_sqlite(vd, p, *vsheets):\n    import sqlite3\n    conn = sqlite3.connect(str(p))\n    conn.row_factory = sqlite3.Row\n    c = conn.cursor()\n\n    sqltypes = {\n        int: 'INTEGER',\n        float: 'REAL',\n        currency: 'REAL'\n    }\n\n    for vs in vsheets:\n        vs.ensureLoaded()\n    vd.sync()\n\n    for vs in vsheets:\n        tblname = clean_to_id(vs.name)\n        sqlcols = []\n        for col in vs.visibleCols:\n            sqlcols.append('\"%s\" %s' % (col.name, sqltypes.get(col.type, 'TEXT')))\n        sql = 'CREATE TABLE IF NOT EXISTS \"%s\" (%s)' % (tblname, ', '.join(sqlcols))\n        c.execute(sql)\n\n        for r in Progress(vs.rows, 'saving'):\n            sqlvals = []\n            for col in vs.visibleCols:\n                v = col.getTypedValue(r)\n                if isinstance(v, TypedWrapper):\n                    if isinstance(v, TypedExceptionWrapper):\n                        v = options.safe_error\n                    else:\n                        v = None\n                elif not isinstance(v, (int, float, str)):\n                    v = col.getDisplayValue(r)\n                sqlvals.append(v)\n            sql = 'INSERT INTO \"%s\" (%s) VALUES (%s)' % (tblname, ','.join(f'\"{c.name}\"' for c in vs.visibleCols), ','.join('?' for v in sqlvals))\n            c.execute(sql, sqlvals)\n\n    conn.commit()\n\n    vd.status(\"%s save finished\" % p)\n\n\nSqliteIndexSheet.addCommand('a', 'add-table', 'fail(\"create a new table by saving a sheet to this database file\")', 'stub; add table by saving a sheet to the db file instead')\nSqliteIndexSheet.bindkey('ga', 'add-table')\nSqliteSheet.class_options.header = 0\nVisiData.save_db = VisiData.save_sqlite\n", "path": "visidata/loaders/sqlite.py"}], "after_files": [{"content": "import re\n\nfrom visidata import *\n\n@VisiData.api\ndef open_sqlite(vd, p):\n    return SqliteIndexSheet(p.name, source=p)\n\nVisiData.open_sqlite3 = VisiData.open_sqlite\nVisiData.open_db = VisiData.open_sqlite\n\n# rowdef: list of values\nclass SqliteSheet(Sheet):\n    'Provide functionality for importing SQLite databases.'\n    savesToSource = True\n    defer = True\n\n    def resolve(self):\n        'Resolve all the way back to the original source Path.'\n        return self.source.resolve()\n\n    def conn(self):\n        import sqlite3\n        con = sqlite3.connect(str(self.resolve()))\n        con.text_factory = lambda s, enc=self.options.encoding: s.decode(enc)\n        return con\n\n    def execute(self, conn, sql, parms=None):\n        parms = parms or []\n        vd.debug(sql)\n        return conn.execute(sql, parms)\n\n    def iterload(self):\n        import sqlite3\n\n        def parse_sqlite_type(t):\n            m = re.match(r'(\\w+)(\\((\\d+)(,(\\d+))?\\))?', t.upper())\n            if not m: return anytype\n            typename, _, i, _, f = m.groups()\n            if typename == 'DATE': return date\n            if typename == 'INTEGER': return int\n            if typename == 'REAL': return float\n            if typename == 'NUMBER':\n                return int if f == '0' else float\n            return anytype\n\n        with self.conn() as conn:\n            tblname = self.tableName\n            if not isinstance(self, SqliteIndexSheet):\n                self.columns = []\n                self.addColumn(ColumnItem('rowid', 0, type=int, width=0))\n                for r in self.execute(conn, 'PRAGMA TABLE_XINFO(\"%s\")' % tblname):\n                    colnum, colname, coltype, nullable, defvalue, colkey, *_ = r\n                    c = ColumnItem(colname, colnum+1, type=parse_sqlite_type(coltype))\n                    self.addColumn(c)\n\n                    if colkey:\n                        self.setKeys([c])\n\n            r = self.execute(conn, 'SELECT rowid, * FROM \"%s\"' % tblname)\n            yield from Progress(r, total=r.rowcount-1)\n\n    @asyncthread\n    def putChanges(self):\n        adds, mods, dels = self.getDeferredChanges()\n        options_safe_error = options.safe_error\n        def value(row, col):\n            v = col.getTypedValue(row)\n            if isinstance(v, TypedWrapper):\n                if isinstance(v, TypedExceptionWrapper):\n                    return options_safe_error\n                else:\n                    return None\n            elif not isinstance(v, (int, float, str)):\n                v = col.getDisplayValue(r)\n            return v\n\n        def values(row, cols):\n            vals = []\n            for c in cols:\n                vals.append(value(row, c))\n            return vals\n\n        with self.conn() as conn:\n            wherecols = [self.columns[0]] # self.column(\"rowid\")\n            for r in adds.values():\n                cols = self.visibleCols\n                sql = 'INSERT INTO \"%s\" ' % self.tableName\n                sql += '(%s)' % ','.join(c.name for c in cols)\n                sql += ' VALUES (%s)' % ','.join('?' for c in cols)\n                res = self.execute(conn, sql, parms=values(r, cols))\n                if res.rowcount != res.arraysize:\n                    vd.warning('not all rows inserted') # f'{res.rowcount}/{res.arraysize} rows inserted'\n\n            for row, rowmods in mods.values():\n                sql = 'UPDATE \"%s\" SET ' % self.tableName\n                sql += ', '.join('%s=?' % c.name for c, _ in rowmods.items())\n                sql += ' WHERE %s' % ' AND '.join('\"%s\"=?' % c.name for c in wherecols)\n                newvals=values(row, [c for c, _ in rowmods.items()])\n                # calcValue gets the 'previous' value (before update)\n                wherevals=list(Column.calcValue(c, row) or '' for c in wherecols)\n                res = self.execute(conn, sql, parms=newvals+wherevals)\n                if res.rowcount != res.arraysize:\n                    vd.warning('not all rows updated') # f'{res.rowcount}/{res.arraysize} rows updated'\n\n            for row in dels.values():\n                sql = 'DELETE FROM \"%s\" ' % self.tableName\n                sql += ' WHERE %s' % ' AND '.join('\"%s\"=?' % c.name for c in wherecols)\n                wherevals=list(Column.calcValue(c, row) for c in wherecols)\n                res = self.execute(conn, sql, parms=wherevals)\n                if res.rowcount != res.arraysize:\n                    vd.warning('not all rows deleted') # f'{res.rowcount}/{res.arraysize} rows deleted'\n\n            conn.commit()\n\n        self.preloadHook()\n        self.reload()\n\n\nclass SqliteIndexSheet(SqliteSheet, IndexSheet):\n    rowtype = 'tables'\n    tableName = 'sqlite_master'\n    savesToSource = True\n    defer = True\n    def iterload(self):\n        for row in SqliteSheet.iterload(self):\n            if row[1] != 'index':\n                tblname = row[2]\n                yield SqliteSheet(tblname, source=self, tableName=tblname, row=row)\n\n    def putChanges(self):\n        adds, mods, dels = self.getDeferredChanges()\n        with self.conn() as conn:\n            for r in adds.values():\n                vd.warning('create a new table by saving a new sheet to this database file')\n\n            for row, rowmods in mods.values():\n                cname = self.column('name')\n                if len(rowmods) == 1 and cname in rowmods:\n                    sql='ALTER TABLE \"%s\" RENAME TO \"%s\"' % (cname.calcValue(row), rowmods[cname])\n                    self.execute(conn, sql)\n                else:\n                    vd.warning('can only modify table name')\n\n            for row in dels.values():\n                sql = 'DROP TABLE \"%s\"' % row.tableName\n                self.execute(conn, sql)\n\n            conn.commit()\n\n        self.preloadHook()\n        self.reload()\n\nclass SqliteQuerySheet(SqliteSheet):\n    def iterload(self):\n        with self.conn() as conn:\n            self.columns = []\n            for c in type(self).columns:\n                self.addColumn(copy(c))\n            self.result = self.execute(conn, self.query, parms=getattr(self, 'parms', []))\n            for i, desc in enumerate(self.result.description):\n                self.addColumn(ColumnItem(desc[0], i))\n\n            for row in self.result:\n                yield row\n\n\n\n@VisiData.api\ndef save_sqlite(vd, p, *vsheets):\n    import sqlite3\n    conn = sqlite3.connect(str(p))\n    conn.text_factory = lambda s, enc=vsheets[0].options.encoding: s.decode(enc)\n    conn.row_factory = sqlite3.Row\n    c = conn.cursor()\n\n    sqltypes = {\n        int: 'INTEGER',\n        float: 'REAL',\n        currency: 'REAL'\n    }\n\n    for vs in vsheets:\n        vs.ensureLoaded()\n    vd.sync()\n\n    for vs in vsheets:\n        tblname = clean_to_id(vs.name)\n        sqlcols = []\n        for col in vs.visibleCols:\n            sqlcols.append('\"%s\" %s' % (col.name, sqltypes.get(col.type, 'TEXT')))\n        sql = 'CREATE TABLE IF NOT EXISTS \"%s\" (%s)' % (tblname, ', '.join(sqlcols))\n        c.execute(sql)\n\n        for r in Progress(vs.rows, 'saving'):\n            sqlvals = []\n            for col in vs.visibleCols:\n                v = col.getTypedValue(r)\n                if isinstance(v, TypedWrapper):\n                    if isinstance(v, TypedExceptionWrapper):\n                        v = options.safe_error\n                    else:\n                        v = None\n                elif not isinstance(v, (int, float, str)):\n                    v = col.getDisplayValue(r)\n                sqlvals.append(v)\n            sql = 'INSERT INTO \"%s\" (%s) VALUES (%s)' % (tblname, ','.join(f'\"{c.name}\"' for c in vs.visibleCols), ','.join('?' for v in sqlvals))\n            c.execute(sql, sqlvals)\n\n    conn.commit()\n\n    vd.status(\"%s save finished\" % p)\n\n\nSqliteIndexSheet.addCommand('a', 'add-table', 'fail(\"create a new table by saving a sheet to this database file\")', 'stub; add table by saving a sheet to the db file instead')\nSqliteIndexSheet.bindkey('ga', 'add-table')\nSqliteSheet.class_options.header = 0\nVisiData.save_db = VisiData.save_sqlite\n", "path": "visidata/loaders/sqlite.py"}]}