ripgrep
yaml-to-sqlite/tests/test_cli.py
34 0
35 == CliRunner()
36 .invoke(cli.cli, [str(db_path), "items", "-"], input=TEST_YAML)
37 .exit_code
38 )
39 db = sqlite_utils.Database(str(db_path))
40 assert EXPECTED == list(db["items"].rows)
41 # Run it again should get double the rows
42 CliRunner().invoke(cli.cli, [str(db_path), "items", "-"], input=TEST_YAML)
43 assert EXPECTED + EXPECTED == list(db["items"].rows)
44
49 0
50 == CliRunner()
51 .invoke(cli.cli, [str(db_path), "items", "-", "--pk", "name"], input=TEST_YAML)
52 .exit_code
53 )
54 db = sqlite_utils.Database(str(db_path))
55 assert EXPECTED == list(db["items"].rows)
56 # Run it again should get same number of rows
57 CliRunner().invoke(cli.cli, [str(db_path), "items", "-"], input=TEST_YAML)
58 assert EXPECTED == list(db["items"].rows)
59
67 .invoke(
68 cli.cli,
69 [str(db_path), "numbers", "-", "--single-column", "name"],
70 input=test_yaml,
71 )
72 .exit_code
73 )
74 db = sqlite_utils.Database(str(db_path))
75 actual = list(db["numbers"].rows)
76 assert actual == [{"name": "One"}, {"name": "Two"}, {"name": "Three"}]
83 0
84 == CliRunner()
85 .invoke(cli.cli, [str(db_path), "items", "-"], input=TEST_YAML)
86 .exit_code
87 )
95 0
96 == CliRunner()
97 .invoke(cli.cli, [str(db_path), "items", "-"], input=more_input)
98 .exit_code
99 )
100 db = sqlite_utils.Database(str(db_path))
101 assert db["items"].columns_dict == {
102 "name": str,
twitter-to-sqlite/twitter_to_sqlite/utils.py
200 continue
201 else:
202 raise Exception(str(tweets["errors"]))
203 if key is not None:
204 tweets = tweets[key]
523 ATTACH DATABASE '{}' AS [{}];
524 """.format(
525 str(pathlib.Path(filepath).resolve()), alias
526 )
527 db.conn.execute(attach_sql)
twitter-to-sqlite/twitter_to_sqlite/cli.py
279
280 format_string = (
281 "@{:" + str(max(len(str(identifier)) for identifier in identifiers)) + "}"
282 )
283
twitter-to-sqlite/tests/utils.py
9 for filepath in path.glob("**/*"):
10 if filepath.is_file():
11 zf.write(filepath, str(filepath.relative_to(path)))
12 return zf
twitter-to-sqlite/tests/test_migrations.py
9
10 def test_no_migrations_on_first_run(tmpdir, zip_contents_path):
11 output = str(tmpdir / "output.db")
12 args = ["import", output, str(zip_contents_path / "follower.js")]
13 result = CliRunner().invoke(cli.cli, args)
14 assert 0 == result.exit_code, result.stdout
twitter-to-sqlite/tests/test_import.py
17 @pytest.fixture
18 def import_test_zip(tmpdir, zip_contents_path):
19 archive = str(tmpdir / "archive.zip")
20 buf = io.BytesIO()
21 zf = create_zip(zip_contents_path, buf)
39 def test_cli_import_zip_file(import_test_zip):
40 tmpdir, archive = import_test_zip
41 output = str(tmpdir / "output.db")
42 result = CliRunner().invoke(cli.cli, ["import", output, archive])
43 assert 0 == result.exit_code, result.stdout
47
48 def test_cli_import_folder(tmpdir, zip_contents_path):
49 output = str(tmpdir / "output.db")
50 result = CliRunner().invoke(cli.cli, ["import", output, str(zip_contents_path)])
51 assert 0 == result.exit_code, result.stdout
52 db = sqlite_utils.Database(output)
55
56 def test_cli_import_specific_files(tmpdir, zip_contents_path):
57 output = str(tmpdir / "output.db")
58 result = CliRunner().invoke(
59 cli.cli,
61 "import",
62 output,
63 str(zip_contents_path / "follower.js"),
64 str(zip_contents_path / "following.js"),
65 ],
66 )
111 def test_deletes_existing_archive_tables(import_test_zip):
112 tmpdir, archive = import_test_zip
113 output = str(tmpdir / "output.db")
114 db = sqlite_utils.Database(output)
115 # Create a table
til/zsh/argument-heredoc.md
7 ```
8 insert into documents select
9 substr(s3_ocr_etag, 2, 8) as id,
10 key as title,
11 key as path,
21 sqlite-utils sfms.db --attach index2 index.db "$(cat <<EOF
22 insert into documents select
23 substr(s3_ocr_etag, 2, 8) as id,
24 key as title,
25 key as path,
34 $(cat <<EOF
35 insert into documents select
36 substr(s3_ocr_etag, 2, 8) as id,
37 key as title,
38 key as path,
til/sqlite/unix-timestamp-milliseconds-sqlite.md
80 select
81 (1000 * (strftime('%s', 'now'))) + cast(
82 substr(
83 strftime('%f', 'now'),
84 instr(strftime('%f', 'now'), '.') + 1
85 ) as integer
86 ) as timestamp_ms
87 ```
88 [Try that here](https://latest.datasette.io/_memory?sql=select%0D%0A++(1000+*+(strftime(%27%25s%27%2C+%27now%27)))+%2B+cast(%0D%0A++++substr(%0D%0A++++++strftime(%27%25f%27%2C+%27now%27)%2C%0D%0A++++++instr(strftime(%27%25f%27%2C+%27now%27)%2C+%27.%27)+%2B+1%0D%0A++++)+as+integer%0D%0A++)+as+timestamp_ms).
89
90 The `substr('18.413', instr('18.413', '.') + 1)` part returns just the characters after the first `.` character, which are then cast to integer to get the milliseconds fraction of that second. These are added to `1000 * ` the unix timestamp in seconds.
til/sqlite/track-timestamped-changes-to-a-table.md
45 -- This is a recipe for timestamp in milliseconds, from
46 -- https://stackoverflow.com/a/56895050/6083
47 strftime('%s','now') || substr(strftime('%f','now'),4)
48 );
49 END;
55 (select id from _dqe_tables where [table] = 'foo'),
56 new.rowid,
57 strftime('%s','now') || substr(strftime('%f','now'),4)
58 );
59 END;
66 old.rowid,
67 1,
68 strftime('%s','now') || substr(strftime('%f','now'),4)
69 );
70 END;
131 foo.rowid,
132 null as deleted,
133 strftime('%s','now') || substr(strftime('%f','now'),4) as updated_ms
134 from foo, table_id;
135 ```
til/sqlite/substr-instr.md
12 I thought it would be fun to extract just the codes.
13
14 The [datasette-rure](https://datasette.io/plugins/datasette-rure) plugin adds regular expression support which can be used for this kind of thing, but in the absence of a plugin like that the only way to do it is with the SQLite [instr()](https://www.sqlite.org/lang_corefunc.html#instr) and [substr()](https://www.sqlite.org/lang_corefunc.html#substr) functions.
15
16 Here's the query I figured out:
19 with snippets as (
20 select
21 substr(body, instr(body, 'refcode=') + 8, 128) as snippet
22 from
23 emails
28 select
29 snippet,
30 substr(
31 snippet,
32 0,
33 min(
34 case
35 when instr(snippet, '&') > 0 then instr(snippet, '&')
36 else 128
37 end,
38 case
39 when instr(snippet, ']') > 0 then instr(snippet, ']')
40 else 128
41 end,
42 case
43 when instr(snippet, ' ') > 0 then instr(snippet, ' ')
44 else 128
45 end,
46 case
47 when instr(snippet, '.') > 0 then instr(snippet, '.')
48 else 128
49 end
66 I started by pulling out just the 128 characters following each `refcode=` - I picked 128 characters at random just to make the data easier to look at:
67 ```sql
68 substr(body, instr(body, 'refcode=') + 8, 128) as snippet
69 ```
70 `instr(body, 'refcode=') + 8` gives the character after the `=` sign, because `refcode=` is 8 characters long.
71
72 Next I needed to find the first character following the refcode that was either a `&`, a `]`, a ` space or a `.`. That's what this bit does:
73 ```sql
74 substr(
75 snippet,
76 0,
77 min(
78 case
79 when instr(snippet, '&') > 0 then instr(snippet, '&')
80 else 128
81 end,
82 case
83 when instr(snippet, ']') > 0 then instr(snippet, ']')
84 else 128
85 end,
86 case
87 when instr(snippet, ' ') > 0 then instr(snippet, ' ')
88 else 128
89 end,
90 case
91 when instr(snippet, '.') > 0 then instr(snippet, '.')
92 else 128
93 end
til/sqlite/splitting-commas-sqlite.md
1 # Splitting on commas in SQLite
2
3 I had an input string in `x,y,z` format and I needed to split it into three separate values in SQLite. I managed to do it using a confusing combination of the `instr()` and `substr()` functions.
4
5 Here's what I came up with:
7 ```sql
8 with comma_locations as (
9 select instr(:path, ',') as first_comma,
10 instr(:path, ',') + instr(substr(:path, instr(:path, ',') + 1), ',') as second_comma
11 ), variables as (
12 select
13 substr(:path, 0, first_comma) as first,
14 substr(:path, first_comma + 1, second_comma - first_comma - 1) as second,
15 substr(:path, second_comma + 1) as third
16 from comma_locations
17 )
til/sqlite/now-argument-stability.md
4
5 ```sql
6 select strftime('%s','now') || substr(strftime('%f','now'),4) as t1
7 union all
8 select strftime('%s','now') || substr(strftime('%f','now'),4)
9 union all
10 select strftime('%s','now') || substr(strftime('%f','now'),4)
11 union all
12 select strftime('%s','now') || substr(strftime('%f','now'),4)
13 union all
14 select strftime('%s','now') || substr(strftime('%f','now'),4)
15 union all
16 select strftime('%s','now') || substr(strftime('%f','now'),4)
17 ```
18 That `strftime()` pattern is [described here](https://stackoverflow.com/questions/17574784/sqlite-current-timestamp-with-milliseconds/56895050#56895050) and [in this TIL](https://til.simonwillison.net/sqlite/track-timestamped-changes-to-a-table), it returns the current Unix timestamp in milliseconds.
til/shot-scraper/social-media-cards.md
54 ```python
55 def jpeg_for_path(path):
56 page_html = str(TMP_PATH / "generate-screenshots-page.html")
57 # Use datasette to generate HTML
58 proc = subprocess.run(["datasette", ".", "--get", path], capture_output=True)
til/python/os-remove-windows.md
8 )
9 def test_recreate(tmpdir, use_path, file_exists):
10 filepath = str(tmpdir / "data.db")
11 if use_path:
12 filepath = pathlib.Path(filepath)
24 if recreate and os.path.exists(filename_or_conn):
25 os.remove(filename_or_conn)
26 self.conn = sqlite3.connect(str(filename_or_conn))
27 ```
28 On Windows I was getting the following exception:
til/python/find-local-variables-in-exception-traceback.md
43 raise click.ClickException(
44 "{}\n\nsql = {}\nparams={}".format(
45 str(e), variables["sql"], variables["params"]
46 )
47 )
til/python/cog-to-update-help-in-readme.md
58 sys.stdout = StringIO()
59 readme = pathlib.Path(__file__).parent.parent / "README.md"
60 result = Cog().main(["cog", str(readme)])
61 output = sys.stdout.getvalue()
62 sys.stdout = _stdout
67 The key line here is this one:
68 ```python
69 result = Cog().main(["cog", str(readme)])
70 ```
71 In cog's implementation, that code is called like this:
til/pytest/subprocess-server.md
til/pytest/pytest-mock-calls.md
11 with runner.isolated_filesystem():
12 result = runner.invoke(cli, ["create", "pytest-bucket-simonw-1", "-c"])
13 assert [str(c) for c in boto3.mock_calls] == [
14 "call('s3')",
15 "call('iam')",
22 ]
23 ```
24 I used the trick I describe in [How to cheat at unit tests with pytest and Black](https://simonwillison.net/2020/Feb/11/cheating-at-unit-tests-pytest-black/) where I run that comparison against an empty `[]` list, then use `pytest --pdb` to drop into a debugger and copy and paste the output of `[str(c) for c in boto3.mock_calls]` into my test code.
25
26 Initially I used a comparison directly against `boto3.mock_calls` - but this threw a surprising error. The calls sequence I baked into my tests looked like this:
55 It turns out `__str__()` calls do not play well with the `call()` constructor - see [this StackOverflow question](https://stackoverflow.com/questions/61926147/how-to-represent-unittest-mock-call-str).
56
57 My solution was to cast them all to `str()` using a list comprehension, which ended up fixing that problem.
58
59 ## Gotcha: parameter ordering
60
61 There's one major flaw to the `str()` trick I'm using here: the order in which parameters are displayed in the string representation of `call()` may differ between Python versions. I had to undo this trick in one place I was using it ([see here](https://github.com/simonw/s3-credentials/issues/8)) as a result due to the following test failure:
62
63 ```
til/plugins/template_vars.py
10 def first_paragraph(html):
11 soup = Soup(html, "html.parser")
12 return str(soup.find("p"))
13
14
til/macos/open-files-with-opensnoop.md
44 - `iosnoop`
45 - `fs_usage`
46 - `sudo dtrace -n 'syscall::open*:entry { printf("%s %s",execname,copyinstr(arg0)); }'`
47 - `strace` (Linux, not macOS)
til/llms/python-react-pattern.md
182 "sql": """
183 select
184 blog_entry.title || ': ' || substr(html_strip_tags(blog_entry.body), 0, 1000) as text,
185 blog_entry.created
186 from
til/llms/code-interpreter-expansions.md
67 deno_version_output = subprocess.check_output([deno_binary_path, '--version'], universal_newlines=True)
68 except Exception as e:
69 deno_version_output = str(e)
70
71 deno_version_output
til/jinja/custom-jinja-tags-with-attributes.md
132 "Invalid syntax for markdown attribute - got "
133 "'{}', should be name=\"value\"".format(
134 "".join([str(t.value) for t in gathered[i : i + 3]]),
135 )
136 ),
til/googlecloud/video-frame-ocr.md
131
132 for path in root.glob("*/*.jpg"):
133 relative = str(path.relative_to(root))
134 text = ocr_image(path, credentials_path)
135 db["results"].insert({
til/googlecloud/google-oauth-cli-application.md
77 ).json()
78 if "error" in data:
79 raise click.ClickException(str(data))
80 return data["access_token"]
81 ```
til/generate_screenshots.py
117
118 def jpeg_for_path(path):
119 page_html = str(TMP_PATH / "generate-screenshots-page.html")
120 # Use datasette to generate HTML
121 proc = subprocess.run(["datasette", ".", "--get", path], capture_output=True)
til/django/building-a-blog-in-django.md
167 def item_author_name(self, item):
168 return (
169 ", ".join([a.get_full_name() or str(a) for a in item.authors.all()]) or None
170 )
171
til/django/datasette-django.md
30 datasette_application = Datasette(
31 [
32 str(db["NAME"])
33 for db in connections.databases.values()
34 if db["ENGINE"] == "django.db.backends.sqlite3"
til/deno/pyodide-sandbox.md
111 except Exception as e:
112 # If the subprocess crashes, return an error message
113 return {"error": str(e)}
114 ```
115
til/datasette/serving-mbtiles.md
22 sql:
23 with comma_locations as (
24 select instr(:key, ',') as first_comma,
25 instr(:key, ',') + instr(substr(:key, instr(:key, ',') + 1), ',') as second_comma
26 ),
27 variables as (
28 select
29 substr(:key, 0, first_comma) as z,
30 substr(:key, first_comma + 1, second_comma - first_comma - 1) as x,
31 substr(:key, second_comma + 1) as y
32 from comma_locations
33 )
til/datasette/playwright-tests-datasette-plugin.md
45 def ds_server(tmp_path_factory):
46 tmpdir = tmp_path_factory.mktemp("tmp")
47 db_path = str(tmpdir / "data.db")
48 db = sqlite3.connect(db_path)
49 db.execute("""
60 "--port",
61 "8126",
62 str(db_path),
63 ],
64 stdout=PIPE,
til/cookiecutter/pytest-for-cookiecutter.md
13 import pathlib
14
15 TEMPLATE_DIRECTORY = str(pathlib.Path(__file__).parent.parent)
16
17
19 cookiecutter(
20 template=TEMPLATE_DIRECTORY,
21 output_dir=str(tmpdir),
22 no_input=True,
23 extra_context={
53 paths = list(pathlib.Path(directory).glob("**/*"))
54 paths = [r.relative_to(directory) for r in paths]
55 return {str(f) for f in paths if str(f) != "."}
56 ```
til/build_database.py
48 title = fp.readline().lstrip("#").strip()
49 body = fp.read().strip()
50 path = str(filepath.relative_to(root))
51 slug = filepath.stem
52 url = "https://github.com/simonw/til/blob/main/{}".format(path)
todomvc-datasette/node_modules/todomvc-common/base.js
132 function findRoot() {
133 var base = location.href.indexOf('examples/');
134 return location.href.substr(0, base);
135 }
136
209 // Correct link path
210 var href = sourceLink.getAttribute('href');
211 sourceLink.setAttribute('href', href.substr(href.lastIndexOf('http')));
212 sourceLinks.innerHTML = heading.outerHTML + sourceLink.outerHTML;
213 } else {
215 var demoLinks = aside.querySelectorAll('.demo-link');
216 Array.prototype.forEach.call(demoLinks, function (demoLink) {
217 if (demoLink.getAttribute('href').substr(0, 4) !== 'http') {
218 demoLink.setAttribute('href', findRoot() + demoLink.getAttribute('href'));
219 }
todomvc-datasette/js/controller.js
233 */
234 Controller.prototype._filter = function (force) {
235 var activeRoute = this._activeRoute.charAt(0).toUpperCase() + this._activeRoute.substr(1);
236
237 // Update the elements on the page, which change with each completed todo
tableau-to-sqlite/tests/test_tableau_to_sqlite.py
8
9
10 @vcr.use_cassette(str(fixtures / "cassette.yml"))
11 def test_run(tmpdir):
12 runner = CliRunner()
13 db_path = str(tmpdir / "tableau.db")
14 result = runner.invoke(
15 cli,
tableau-to-sqlite/tableau_to_sqlite/cli.py
37 ts.loads(url)
38 workboop = ts.getWorkbook()
39 conn = sqlite3.connect(str(db_path))
40 for worksheet in workboop.worksheets:
41 worksheet.data.to_sql(fix_name(worksheet.name), conn)
sqlite-utils-plugin/tests/test_cookiecutter_template.py
2 import pathlib
3
4 TEMPLATE_DIRECTORY = str(pathlib.Path(__file__).parent.parent)
5
6
32 cookiecutter(
33 template=TEMPLATE_DIRECTORY,
34 output_dir=str(directory),
35 no_input=True,
36 extra_context=context,
41 paths = list(pathlib.Path(directory).glob("**/*"))
42 paths = [r.relative_to(directory) for r in paths]
43 return {str(f) for f in paths if str(f) != "."}
sqlite-utils-shell/sqlite_utils_shell.py
78 db.conn.enable_load_extension(True)
79 for extension in load_extensions:
80 db.conn.load_extension(str(extension))
81
82 print_("Type 'exit' to exit.")
sqlite-utils-move-tables/tests/test_move_tables.py
7 @pytest.fixture
8 def databases(tmpdir):
9 origin = str(tmpdir / "origin.db")
10 origin_db = Database(origin)
11 origin_db.vacuum()
12 destination = str(tmpdir / "destination.db")
13 destination_db = Database(destination)
14 destination_db.vacuum()
sqlite-utils-fast-fks/tests/test_fast_fks_cli.py
8 @pytest.fixture
9 def db_and_db_path(tmpdir):
10 db_path = str(tmpdir / "data.db")
11 db = Database(db_path)
12 db["country"].insert_all([{"id": 1, "name": "United Kingdom"}])
sqlite-utils-jq/sqlite_utils_jq.py
8 return json.dumps(jq.first(expression, json.loads(value)))
9 except Exception as ex:
10 return json.dumps({"error": str(ex)})
11
12
sqlite-utils-dateutil/sqlite_utils_dateutil.py
42
43 def dateutil_easter(year):
44 year = str(year) if year else None
45 if not year or not year.isdigit():
46 return None
60 if dtstart:
61 kwargs["dtstart"] = dtstart
62 results = list(itertools.islice(rrulestr(rrule, **kwargs), 0, RRULE_MAX + 1))
63 if len(results) > RRULE_MAX:
64 raise TooManyError(
sqlite-utils/tests/test_wal.py
6 def db_path_tmpdir(tmpdir):
7 path = tmpdir / "test.db"
8 db = Database(str(path))
9 return db, path, tmpdir
10
sqlite-utils/tests/test_sniff.py
9 @pytest.mark.parametrize("filepath", sniff_dir.glob("example*"))
10 def test_sniff(tmpdir, filepath):
11 db_path = str(tmpdir / "test.db")
12 runner = CliRunner()
13 result = runner.invoke(
14 cli.cli,
15 ["insert", db_path, "creatures", str(filepath), "--sniff"],
16 catch_exceptions=False,
17 )
sqlite-utils/tests/test_register_function.py
20 return "".join(reversed(list(s)))
21
22 result = fresh_db.execute('select revstr("hello")').fetchone()[0]
23 assert result == "olleh"
24
sqlite-utils/tests/test_recreate.py
24 )
25 def test_recreate(tmp_path, use_path, create_file_first):
26 filepath = str(tmp_path / "data.db")
27 if use_path:
28 filepath = pathlib.Path(filepath)
sqlite-utils/tests/test_insert_files.py
12 with runner.isolated_filesystem():
13 tmpdir = pathlib.Path(".")
14 db_path = str(tmpdir / "files.db")
15 (tmpdir / "one.txt").write_text("This is file one", "utf-8")
16 (tmpdir / "two.txt").write_text("Two is shorter", "utf-8")
41 result = runner.invoke(
42 cli.cli,
43 ["insert-files", db_path, "files", str(tmpdir)]
44 + cols
45 + ["--pk", "path"]
121 with runner.isolated_filesystem():
122 tmpdir = pathlib.Path(".")
123 db_path = str(tmpdir / "files.db")
124 args = ["insert-files", db_path, "files", "-", "--name", "stdin-name"]
125 if use_text:
152 latin = tmpdir / "latin.txt"
153 latin.write_bytes(b"S\xe3o Paulo")
154 db_path = str(tmpdir / "files.db")
155 result = runner.invoke(
156 cli.cli,
157 ["insert-files", db_path, "files", str(latin), "--text"],
158 catch_exceptions=False,
159 )
160 assert result.exit_code == 1, result.output
161 assert result.output.strip().startswith(
162 "Error: Could not read file '{}' as text".format(str(latin.resolve()))
163 )
sqlite-utils/tests/test_gis.py
125 db_path = tmpdir / "created.db"
126 result = CliRunner().invoke(
127 cli, ["create-database", str(db_path), "--init-spatialite"]
128 )
129
132 assert db_path.read_binary()[:16] == b"SQLite format 3\x00"
133
134 db = Database(str(db_path))
135 assert "spatial_ref_sys" in db.table_names()
136
139 # create a rowid table with one column
140 db_path = tmpdir / "spatial.db"
141 db = Database(str(db_path))
142 db.init_spatialite()
143
148 [
149 "add-geometry-column",
150 str(db_path),
151 table.name,
152 "geometry",
171 # create a rowid table with one column
172 db_path = tmpdir / "spatial.db"
173 db = Database(str(db_path))
174 db.init_spatialite()
175 table = db["locations"].create({"name": str})
179 [
180 "add-geometry-column",
181 str(db_path),
182 table.name,
183 "geometry",
208 # create a rowid table with one column
209 db_path = tmpdir / "spatial.db"
210 db = Database(str(db_path))
211 db.init_spatialite()
212
217 [
218 "add-geometry-column",
219 str(db_path),
220 table.name,
221 "geometry",
231 # create a rowid table with one column
232 db_path = tmpdir / "spatial.db"
233 db = Database(str(db_path))
234 db.init_spatialite()
235
238
239 result = CliRunner().invoke(
240 cli, ["create-spatial-index", str(db_path), table.name, "geometry"]
241 )
242
sqlite-utils/tests/test_fts.py
322 # Recreating https://github.com/simonw/sqlite-utils/issues/149
323 path = tmpdir / "test.db"
324 db = Database(str(path), recursive_triggers=False)
325 licenses = [{"key": "apache2", "name": "Apache 2"}, {"key": "bsd", "name": "BSD"}]
326 db["licenses"].insert_all(licenses, pk="key", replace=True)
sqlite-utils/tests/test_enable_counts.py
86 @pytest.fixture
87 def counts_db_path(tmpdir):
88 path = str(tmpdir / "test.db")
89 db = Database(path)
90 db["foo"].insert({"name": "bar"})
sqlite-utils/tests/test_duplicate.py
21 "int_col": -255,
22 "bool_col": True,
23 "datetime_col": str(dt),
24 }
25 table1 = fresh_db["table1"]
sqlite-utils/tests/test_create.py
878 assert {"uuid"} == row.keys()
879 assert isinstance(row["uuid"], str)
880 assert row["uuid"] == str(uuid4)
881
882
1034 "float32",
1035 "float64",
1036 ] == [str(t) for t in df.dtypes]
1037 fresh_db["types"].insert_all(df.to_dict(orient="records"))
1038 assert [
sqlite-utils/tests/test_convert.py
79 with pytest.raises(AssertionError) as excinfo:
80 table.convert(["title", "other"], lambda v: v, output="out")
81 assert "output= can only be used with a single column" in str(excinfo.value)
82
83
sqlite-utils/tests/test_constructor.py
35 db = Database(memory=True)
36 else:
37 db = Database(str(tmpdir / "test.db"))
38 assert db.execute("select 1 + 1").fetchone()[0] == 2
39 db.close()
sqlite-utils/tests/test_cli_memory.py
24 sql_from = "stdin"
25 else:
26 csv_path = str(tmpdir / "test.csv")
27 with open(csv_path, "w") as fp:
28 fp.write(content)
47 else:
48 input = None
49 path = str(tmpdir / "chickens.tsv")
50 with open(path, "w") as fp:
51 fp.write(data)
73 else:
74 input = None
75 path = str(tmpdir / "chickens.json")
76 with open(path, "w") as fp:
77 fp.write(data)
99 else:
100 input = None
101 path = str(tmpdir / "chickens.json")
102 with open(path, "w") as fp:
103 fp.write(data)
127 sql_from = "stdin"
128 else:
129 csv_path = str(tmpdir / "test.csv")
130 with open(csv_path, "wb") as fp:
131 fp.write(latin1_csv)
202 @pytest.mark.parametrize("extra_args", ([], ["select 1"]))
203 def test_memory_save(tmpdir, extra_args):
204 save_to = str(tmpdir / "save.db")
205 result = CliRunner().invoke(
206 cli.cli,
282 one.write_text("id,name\n1,Cleo\n2,Bants", encoding="utf-8")
283 two.write_text("id,name\n3,Blue\n4,Lila", encoding="utf-8")
284 result = CliRunner().invoke(cli.cli, ["memory", str(one), str(two), "", "--schema"])
285 assert result.exit_code == 0
286 assert result.output == (
sqlite-utils/tests/test_cli_insert.py
9
10 def test_insert_simple(tmpdir):
11 json_path = str(tmpdir / "dog.json")
12 db_path = str(tmpdir / "dogs.db")
13 with open(json_path, "w") as fp:
14 fp.write(json.dumps({"name": "Cleo", "age": 4}))
24
25 def test_insert_from_stdin(tmpdir):
26 db_path = str(tmpdir / "dogs.db")
27 result = CliRunner().invoke(
28 cli.cli,
37
38 def test_insert_invalid_json_error(tmpdir):
39 db_path = str(tmpdir / "dogs.db")
40 result = CliRunner().invoke(
41 cli.cli,
51
52 def test_insert_json_flatten(tmpdir):
53 db_path = str(tmpdir / "flat.db")
54 result = CliRunner().invoke(
55 cli.cli,
62
63 def test_insert_json_flatten_nl(tmpdir):
64 db_path = str(tmpdir / "flat.db")
65 result = CliRunner().invoke(
66 cli.cli,
79
80 def test_insert_with_primary_key(db_path, tmpdir):
81 json_path = str(tmpdir / "dog.json")
82 with open(json_path, "w") as fp:
83 fp.write(json.dumps({"id": 1, "name": "Cleo", "age": 4}))
94
95 def test_insert_multiple_with_primary_key(db_path, tmpdir):
96 json_path = str(tmpdir / "dogs.json")
97 dogs = [{"id": i, "name": "Cleo {}".format(i), "age": i + 3} for i in range(1, 21)]
98 with open(json_path, "w") as fp:
108
109 def test_insert_multiple_with_compound_primary_key(db_path, tmpdir):
110 json_path = str(tmpdir / "dogs.json")
111 dogs = [
112 {"breed": "mixed", "id": i, "name": "Cleo {}".format(i), "age": i + 3}
134
135 def test_insert_not_null_default(db_path, tmpdir):
136 json_path = str(tmpdir / "dogs.json")
137 dogs = [
138 {"id": i, "name": "Cleo {}".format(i), "age": i + 3, "score": 10}
187 db = Database(db_path)
188 db["dogs"].insert({"id": 1, "name": "Cleo"}, pk="id")
189 json_path = str(tmpdir / "dogs.json")
190 with open(json_path, "w") as fp:
191 fp.write(json.dumps([{"id": 1, "name": "Bailey"}]))
218 def test_insert_csv_tsv(content, options, db_path, tmpdir):
219 db = Database(db_path)
220 file_path = str(tmpdir / "insert.csv-tsv")
221 with open(file_path, "w") as fp:
222 fp.write(content)
261 )
262 def test_insert_stop_after(tmpdir, input, args):
263 db_path = str(tmpdir / "data.db")
264 result = CliRunner().invoke(
265 cli.cli,
283 )
284 def test_only_allow_one_of_nl_tsv_csv(options, db_path, tmpdir):
285 file_path = str(tmpdir / "insert.csv-tsv")
286 with open(file_path, "w") as fp:
287 fp.write("foo")
295 def test_insert_replace(db_path, tmpdir):
296 test_insert_multiple_with_primary_key(db_path, tmpdir)
297 json_path = str(tmpdir / "insert-replace.json")
298 db = Database(db_path)
299 assert db["dogs"].count == 20
sqlite-utils/tests/test_cli_convert.py
25 @pytest.fixture
26 def fresh_db_and_path(tmpdir):
27 db_path = str(pathlib.Path(tmpdir) / "data.db")
28 db = sqlite_utils.Database(db_path)
29 return db, db_path
415 @pytest.mark.parametrize("delimiter", [None, ";", "-"])
416 def test_recipe_jsonsplit(tmpdir, delimiter):
417 db_path = str(pathlib.Path(tmpdir) / "data.db")
418 db = sqlite_utils.Database(db_path)
419 db["example"].insert_all(
531 "example",
532 "dt",
533 "str(value).upper()",
534 "--where",
535 "id = :id",
sqlite-utils/tests/test_cli_bulk.py
10 @pytest.fixture
11 def test_db_and_path(tmpdir):
12 db_path = str(pathlib.Path(tmpdir) / "data.db")
13 db = Database(db_path)
14 db["example"].insert_all(
sqlite-utils/tests/test_attach.py
3
4 def test_attach(tmpdir):
5 foo_path = str(tmpdir / "foo.db")
6 bar_path = str(tmpdir / "bar.db")
7 db = Database(foo_path)
8 with db.conn:
sqlite-utils/tests/test_analyze_tables.py
27 @pytest.fixture
28 def big_db_to_analyze_path(tmpdir):
29 path = str(tmpdir / "test.db")
30 db = Database(path)
31 categories = {
134 @pytest.fixture
135 def db_to_analyze_path(db_to_analyze, tmpdir):
136 path = str(tmpdir / "test.db")
137 db = sqlite3.connect(path)
138 sql = "\n".join(db_to_analyze.iterdump())
298 )
299 def test_analyze_table_validate_columns(tmpdir, args, expected_error):
300 path = str(tmpdir / "test_validate_columns.db")
301 db = Database(path)
302 db["one"].insert(
sqlite-utils/tests/test_cli.py
34
35
36 COMPILED_EXTENSION_PATH = str(Path(__file__).parent / "ext")
37
38
435 )
436 assert result.exit_code != 0
437 assert "table 'bobcats' does not exist" in str(result.exception)
438
439
838 )
839 result = CliRunner().invoke(cli.cli, [db_path, "select name, sz, data from files"])
840 assert result.exit_code == 0, str(result)
841 assert json.loads(result.output.strip()) == [
842 {
879 extra_args.extend(["-p", key, value])
880 result = CliRunner().invoke(cli.cli, [db_path, sql] + extra_args)
881 assert result.exit_code == 0, str(result)
882 assert json.loads(result.output.strip()) == [{"out": expected}]
883
927 assert result.stdout_bytes == content
928 else:
929 assert result.output == str(content)
930
931
942 assert result.stdout_bytes == b"\n".join(content for _ in range(3)) + b"\n"
943 else:
944 assert result.output == "\n".join(str(content) for _ in range(3)) + "\n"
945
946
1024
1025def test_upsert(db_path, tmpdir):
1026 json_path = str(tmpdir / "dogs.json")
1027 db = Database(db_path)
1028 insert_dogs = [
1057
1058def test_upsert_pk_required(db_path, tmpdir):
1059 json_path = str(tmpdir / "dogs.json")
1060 insert_dogs = [
1061 {"id": 1, "name": "Cleo", "age": 4},
1087
1088def test_upsert_flatten(tmpdir):
1089 db_path = str(tmpdir / "flat.db")
1090 db = Database(db_path)
1091 db["upsert_me"].insert({"id": 1, "name": "Example"}, pk="id")
1102
1103def test_upsert_alter(db_path, tmpdir):
1104 json_path = str(tmpdir / "dogs.json")
1105 db = Database(db_path)
1106 insert_dogs = [{"id": 1, "name": "Cleo"}]
1802
1803def test_insert_encoding(tmpdir):
1804 db_path = str(tmpdir / "test.db")
1805 latin1_csv = (
1806 b"date,name,latitude,longitude\n"
1810 )
1811 assert latin1_csv.decode("latin-1").split("\n")[2].split(",")[1] == "São Paulo"
1812 csv_path = str(tmpdir / "test.csv")
1813 with open(csv_path, "wb") as fp:
1814 fp.write(latin1_csv)
1866)
1867def test_search(tmpdir, fts, extra_arg, expected):
1868 db_path = str(tmpdir / "test.db")
1869 db = Database(db_path)
1870 db["articles"].insert_all(
1887
1888def test_search_quote(tmpdir):
1889 db_path = str(tmpdir / "test.db")
1890 db = Database(db_path)
1891 db["creatures"].insert({"name": "dog."}).enable_fts(["name"])
1906
1907def test_indexes(tmpdir):
1908 db_path = str(tmpdir / "test.db")
1909 db = Database(db_path)
1910 db.conn.executescript(
1916 result = CliRunner().invoke(
1917 cli.cli,
1918 ["indexes", str(db_path)],
1919 catch_exceptions=False,
1920 )
1944 result2 = CliRunner().invoke(
1945 cli.cli,
1946 ["indexes", str(db_path), "--aux"],
1947 catch_exceptions=False,
1948 )
1997)
1998def test_triggers(tmpdir, extra_args, expected):
1999 db_path = str(tmpdir / "test.db")
2000 db = Database(db_path)
2001 db["articles"].insert(
2066)
2067def test_schema(tmpdir, options, expected):
2068 db_path = str(tmpdir / "test.db")
2069 db = Database(db_path)
2070 db["dogs"].create({"id": int, "name": str})
2081
2082def test_long_csv_column_value(tmpdir):
2083 db_path = str(tmpdir / "test.db")
2084 csv_path = str(tmpdir / "test.csv")
2085 csv_file = open(csv_path, "w")
2086 long_string = "a" * 131073
2109)
2110def test_import_no_headers(tmpdir, args, tsv):
2111 db_path = str(tmpdir / "test.db")
2112 csv_path = str(tmpdir / "test.csv")
2113 csv_file = open(csv_path, "w")
2114 sep = "\t" if tsv else ","
2139
2140def test_attach(tmpdir):
2141 foo_path = str(tmpdir / "foo.db")
2142 bar_path = str(tmpdir / "bar.db")
2143 db = Database(foo_path)
2144 with db.conn:
2161
2162def test_csv_insert_bom(tmpdir):
2163 db_path = str(tmpdir / "test.db")
2164 bom_csv_path = str(tmpdir / "bom.csv")
2165 with open(bom_csv_path, "wb") as fp:
2166 fp.write(b"\xef\xbb\xbfname,age\nCleo,5")
2187@pytest.mark.parametrize("option_or_env_var", (None, "-d", "--detect-types"))
2188def test_insert_detect_types(tmpdir, option_or_env_var):
2189 db_path = str(tmpdir / "test.db")
2190 data = "name,age,weight\nCleo,6,45.5\nDori,1,3.5"
2191 extra = []
2217@pytest.mark.parametrize("option", ("-d", "--detect-types"))
2218def test_upsert_detect_types(tmpdir, option):
2219 db_path = str(tmpdir / "test.db")
2220 data = "id,name,age,weight\n1,Cleo,6,45.5\n2,Dori,1,3.5"
2221 result = CliRunner().invoke(
2234
2235def test_integer_overflow_error(tmpdir):
2236 db_path = str(tmpdir / "test.db")
2237 result = CliRunner().invoke(
2238 cli.cli,
2261 db_path = tmpdir / "test.db"
2262 assert not db_path.exists()
2263 args = ["create-database", str(db_path)]
2264 if enable_wal:
2265 args.append("--enable-wal")
2268 assert db_path.exists()
2269 assert db_path.read_binary()[:16] == b"SQLite format 3\x00"
2270 db = Database(str(db_path))
2271 if enable_wal:
2272 assert db.journal_mode == "wal"
2301)
2302def test_analyze(tmpdir, options, expected):
2303 db_path = str(tmpdir / "test.db")
2304 db = Database(db_path)
2305 db["one_index"].insert({"id": 1, "name": "Cleo"}, pk="id")
2314
2315def test_rename_table(tmpdir):
2316 db_path = str(tmpdir / "test.db")
2317 db = Database(db_path)
2318 db["one"].insert({"id": 1, "name": "Cleo"}, pk="id")
2346
2347def test_duplicate_table(tmpdir):
2348 db_path = str(tmpdir / "test.db")
2349 db = Database(db_path)
2350 db["one"].insert({"id": 1, "name": "Cleo"}, pk="id")
2421@pytest.mark.parametrize("strict", (False, True))
2422def test_insert_upsert_strict(tmpdir, method, strict):
2423 db_path = str(tmpdir / "test.db")
2424 result = CliRunner().invoke(
2425 cli.cli,
sqlite-utils/tests/conftest.py
36 @pytest.fixture
37 def db_path(tmpdir):
38 path = str(tmpdir / "test.db")
39 db = sqlite3.connect(path)
40 db.executescript(CREATE_TABLES)
sqlite-utils/sqlite_utils/db.py
343 self.conn = sqlite3.connect(":memory:")
344 raise
345 self.conn = sqlite3.connect(str(filename_or_conn))
346 else:
347 assert not recreate, "recreate cannot be used with connections, only paths"
428 @db.register_function
429 def upper(value):
430 return str(value).upper()
431
432 The decorator can take arguments::
434 @db.register_function(deterministic=True, replace=True)
435 def upper(value):
436 return str(value).upper()
437
438 See :ref:`python_api_register_function`.
487 ATTACH DATABASE '{}' AS [{}];
488 """.format(
489 str(pathlib.Path(filepath).resolve()), alias
490 ).strip()
491 self.execute(attach_sql)
587 if any(
588 [
589 str(value).startswith("'") and str(value).endswith("'"),
590 str(value).startswith('"') and str(value).endswith('"'),
591 ]
592 ):
593 return value
594
595 if str(value).upper() in ("CURRENT_TIME", "CURRENT_DATE", "CURRENT_TIMESTAMP"):
596 return value
597
598 if str(value).endswith(")"):
599 # Expr
600 return "({})".format(value)
3614 if value_truncate is None or isinstance(value, (float, int)):
3615 return value
3616 value = str(value)
3617 if len(value) > value_truncate:
3618 value = value[:value_truncate] + "..."
3797 return value.isoformat()
3798 elif isinstance(value, datetime.timedelta):
3799 return str(value)
3800 elif isinstance(value, uuid.UUID):
3801 return str(value)
3802 else:
3803 return value
sqlite-utils/sqlite_utils/cli.py
473 except OperationalError as ex:
474 if not ignore:
475 raise click.ClickException(str(ex))
476
477
1120 raise click.ClickException(
1121 "{}\n\nsql = {}\nparameters = {}".format(
1122 str(e), variables["sql"], variables["parameters"]
1123 )
1124 )
1443 )
1444 except (OperationalError, sqlite3.IntegrityError) as e:
1445 raise click.ClickException(str(e))
1446
1447
1637 if not ignore:
1638 raise click.ClickException(
1639 'Table "{}" could not be renamed. {}'.format(table, str(ex))
1640 )
1641
2031 cursor = db.execute(sql, dict(param))
2032 except OperationalError as e:
2033 raise click.ClickException(str(e))
2034 if cursor.description is None:
2035 # This was an update/insert
2043 sys.stdout.buffer.write(data)
2044 else:
2045 sys.stdout.write(str(data))
2046 elif raw_lines:
2047 for row in cursor:
2050 sys.stdout.buffer.write(data + b"\n")
2051 else:
2052 sys.stdout.write(str(data) + "\n")
2053 elif fmt or table:
2054 print(
2157 )
2158 except click.ClickException as e:
2159 if "malformed MATCH expression" in str(e) or "unterminated string" in str(e):
2160 raise click.ClickException(
2161 "{}\n\nTry running this again with the --quote option".format(str(e))
2162 )
2163 else:
2893 fn = getattr(recipes, name)
2894 help += "\n\nr.{}{}\n\n\b{}".format(
2895 name, str(inspect.signature(fn)), fn.__doc__.rstrip()
2896 )
2897 help += "\n\n"
2978 fn = _compile_code(code, imports)
2979 except SyntaxError as e:
2980 raise click.ClickException(str(e))
2981 if dry_run:
2982 # Pull first 20 values for first column and preview them
3003 )
3004 for row in db.conn.execute(sql, where_args).fetchall():
3005 click.echo(str(row[0]))
3006 click.echo(" --- becomes:")
3007 click.echo(str(row[1]))
3008 click.echo()
3009 count = db[table].count_where(
3183FILE_COLUMNS = {
3184 "name": lambda p: p.name,
3185 "path": lambda p: str(p),
3186 "fullpath": lambda p: str(p.resolve()),
3187 "sha256": lambda p: hashlib.sha256(p.resolve().read_bytes()).hexdigest(),
3188 "md5": lambda p: hashlib.md5(p.resolve().read_bytes()).hexdigest(),
sqlite-utils/docs/cli.rst
1669
1670 sqlite-utils convert content.db articles headline '
1671 value = str(value)
1672 return value.upper()'
1673
sqlite-transform/tests/test_jsonsplit.py
9 @pytest.mark.parametrize("delimiter", [None, ";", "-"])
10 def test_jsonsplit(tmpdir, delimiter):
11 db_path = str(pathlib.Path(tmpdir) / "data.db")
12 db = sqlite_utils.Database(db_path)
13 db["example"].insert_all(
sqlite-transform/tests/conftest.py
21 @pytest.fixture
22 def fresh_db_and_path(tmpdir):
23 db_path = str(pathlib.Path(tmpdir) / "data.db")
24 db = sqlite_utils.Database(db_path)
25 return db, db_path
sqlite-migrate/tests/test_sqlite_utils_migrate_command.py
31 def test_basic(two_migrations, arg):
32 path, _ = two_migrations
33 db_path = str(path / "test.db")
34
35 runner = CliRunner()
38 list_result = runner.invoke(
39 sqlite_utils.cli.cli,
40 ["migrate", db_path, "--list", arg.replace("TMPDIR", str(path))],
41 )
42 assert list_result.exit_code == 0
52
53 result = runner.invoke(
54 sqlite_utils.cli.cli, ["migrate", db_path, arg.replace("TMPDIR", str(path))]
55 )
56 assert result.exit_code == 0, result.output
89 "utf-8",
90 )
91 db_path = str(path / "test.db")
92 runner = CliRunner()
93 result = runner.invoke(
94 sqlite_utils.cli.cli, ["migrate", db_path, str(migrations_py)]
95 )
96 assert result.exit_code == 0
97 # Now run again with --verbose, should be no changes
98 result = runner.invoke(
99 sqlite_utils.cli.cli, ["migrate", db_path, str(migrations_py), "--verbose"]
100 )
101 assert result.exit_code == 0
132 # And run it
133 result = runner.invoke(
134 sqlite_utils.cli.cli, ["migrate", db_path, str(migrations_py), "--verbose"]
135 )
136 assert result.exit_code == 0
155 def test_stop_before(two_migrations):
156 path, _ = two_migrations
157 db_path = str(path / "test.db")
158 runner = CliRunner()
159 result = runner.invoke(
162 "migrate",
163 db_path,
164 str(path / "foo" / "migrations.py"),
165 "--stop-before",
166 "bar",
175 def test_stop_before_error(two_migrations):
176 path, _ = two_migrations
177 db_path = str(path / "test.db")
178 (path / "foo" / "migrations2.py").write_text(
179 """
194 "migrate",
195 db_path,
196 str(path / "foo" / "migrations.py"),
197 str(path / "foo" / "migrations2.py"),
198 "--stop-before",
199 "foo",
sqlite-migrate/sqlite_migrate/__init__.py
83 "migration_set": self.name,
84 "name": name,
85 "applied_at": str(datetime.datetime.now(datetime.timezone.utc)),
86 }
87 )
sqlite-transform/README.md
59 Here's how to convert a column to uppercase:
60
61 sqlite-transform lambda my.db mytable mycolumn --code='str(value).upper()'
62
63 The code you provide will be compiled into a function that takes `value` as a single argument. You can break your function body into multiple lines, provided the last line is a `return` statement:
64
65 sqlite-transform lambda my.db mytable mycolumn --code='value = str(value)
66 return value.upper()'
67
sqlite-history/tests/test_sqlite_history.py
111 def test_cli(tmpdir):
112 test = tmpdir / "test.db"
113 db = sqlite_utils.Database(str(test))
114 db["test"].insert({"id": 1, "name": "Alice", "order": 1})
115 db["test"].enable_fts(("name",), create_triggers=True)
122 "test_fts_config",
123 }
124 run([str(test), "--all"])
125 assert set(db.table_names()) == {
126 "test",
sqlite-generate/sqlite_generate/utils.py
27 (str, fake.zipcode),
28 (str, fake.text),
29 (str, lambda: str(fake.date_this_century())),
30 (float, fake.pyfloat),
31 (int, fake.pyint),
sqlite-fts4/sqlite_fts4/__init__.py
sqlite-dump/sqlite_dump/__init__.py
47 table_name_ident = table_name.replace('"', '""')
48 res = cu.execute('PRAGMA table_info("{0}")'.format(table_name_ident))
49 column_names = [str(table_info[1]) for table_info in res.fetchall()]
50 q = """SELECT 'INSERT INTO "{0}" VALUES({1})' FROM "{0}";""".format(
51 table_name_ident,
sqlite-dump/tests/test_sqlite_dump.py
7 @pytest.fixture()
8 def db_and_path(tmpdir):
9 path = str(tmpdir / "test.db")
10 db = sqlite_utils.Database(path)
11 db["dogs"].insert_all(
35 conn = sqlite3.connect(path)
36 lines = list(iterdump(conn))
37 db2_path = str(tmpdir / "restored.db")
38 conn2 = sqlite3.connect(db2_path)
39 conn2.executescript("\n".join(lines))
sqlite-diffable/tests/test_objects.py
28 output_dir = tmpdir / "out"
29 result = CliRunner().invoke(
30 cli.cli, ["dump", one_table_db, str(output_dir), "one_table"]
31 )
32 assert result.exit_code == 0, result.output
33 result2 = CliRunner().invoke(
34 cli.cli, ["objects", str(output_dir / "one_table.ndjson")] + options
35 )
36 assert result2.output == expected_output
sqlite-comprehend/tests/test_sqlite_comprehend.py
23 @pytest.mark.parametrize("compound_primary_key", (True, False))
24 def test_entities(mocker, tmpdir, compound_primary_key):
25 db_path = str(tmpdir / "data.db")
26 db = sqlite_utils.Database(db_path)
27 db["pages"].insert_all(
167
168 def test_entities_errors(mocker, tmpdir):
169 db_path = str(tmpdir / "data.db")
170 db = sqlite_utils.Database(db_path)
171 db["pages"].insert_all(
sqlite-diffable/tests/test_black.py
16 result = runner.invoke(
17 black.main,
18 [str(code_root / "tests"), str(code_root / "sqlite_diffable"), "--check"],
19 )
20 assert result.exit_code == 0, result.output
sqlite-diffable/tests/test_dump.py
8 output_dir = tmpdir / "out"
9 result = CliRunner().invoke(
10 cli.cli, ["dump", one_table_db, str(output_dir), "one_table"]
11 )
12 assert result.exit_code == 0, result.output
29 output_dir = tmpdir / "out"
30 result = CliRunner().invoke(
31 cli.cli, ["dump", two_tables_db, str(output_dir), "--all"]
32 )
33 assert result.exit_code == 0, result.output
42 restore_db = tmpdir / "restore.db"
43 result = CliRunner().invoke(
44 cli.cli, ["dump", str(two_tables_db), str(output_dir), "--all"]
45 )
46 assert result.exit_code == 0, result.output
47 # Now load it again
48 result2 = CliRunner().invoke(cli.cli, ["load", str(restore_db), str(output_dir)])
49 assert result2.exit_code == 0, result2.output
50 db = sqlite_utils.Database(str(restore_db))
51 assert set(db.table_names()) == {"second_table", "one_table"}
52 assert list(db["one_table"].rows) == [
59 ]
60 # Running load a second time should error
61 result3 = CliRunner().invoke(cli.cli, ["load", str(restore_db), str(output_dir)])
62 assert result3.exit_code == 1
63 assert (
69 )
70 result4 = CliRunner().invoke(
71 cli.cli, ["load", str(restore_db), str(output_dir), "--replace"]
72 )
73 assert result4.exit_code == 0
sqlite-diffable/tests/conftest.py
5 @pytest.fixture
6 def one_table_db(tmpdir):
7 path = str(tmpdir / "one_table.db")
8 db = sqlite_utils.Database(path)
9 db["one_table"].insert_all(
sqlite-comprehend/sqlite_comprehend/utils.py
101
102 def strip_tags(value):
103 value = str(value)
104 while "<" in value and ">" in value:
105 new_value = _strip_once(value)
sqlite-comprehend/README.md
115 import tempfile, pathlib
116 tmpdir = pathlib.Path(tempfile.mkdtemp())
117 db_path = str(tmpdir / "data.db")
118 db = sqlite_utils.Database(db_path)
119 db["pages"].insert_all(
sqlite-diffable/sqlite_diffable/cli.py
96 db.execute(schema)
97 except sqlite3.OperationalError as ex:
98 msg = str(ex)
99 if "already exists" in msg:
100 msg += "\n\nUse the --replace option to over-write existing tables"
shapefile-to-sqlite/tests/test_shapefile_to_sqlite.py
13
14 def test_missing(tmpdir):
15 db_path = str(tmpdir / "output.db")
16 result = CliRunner().invoke(
17 cli.cli, [db_path, str(testdir / "missing.shp")], catch_exceptions=False
18 )
19 assert 2 == result.exit_code
23 @pytest.mark.parametrize("table", [None, "customtable"])
24 def test_import_features(tmpdir, table):
25 db_path = str(tmpdir / "output.db")
26 args = [db_path, str(testdir / "features.shp")]
27 expected_table = "features"
28 if table:
56 @pytest.mark.skipif(not utils.find_spatialite(), reason="Could not find SpatiaLite")
57 def test_import_features_spatialite(tmpdir):
58 db_path = str(tmpdir / "output.db")
59 result = CliRunner().invoke(
60 cli.cli,
61 [db_path, str(testdir / "features.shp"), "--spatialite"],
62 catch_exceptions=False,
63 )
116 @pytest.mark.skipif(not utils.find_spatialite(), reason="Could not find SpatiaLite")
117 def test_import_features_spatialite_with_custom_crs(tmpdir):
118 db_path = str(tmpdir / "output.db")
119 result = CliRunner().invoke(
120 cli.cli,
121 [
122 db_path,
123 str(testdir / "features.shp"),
124 "--spatialite",
125 "--crs",
188 @pytest.mark.skipif(not utils.find_spatialite(), reason="Could not find SpatiaLite")
189 def test_import_features_spatial_index(tmpdir):
190 db_path = str(tmpdir / "output.db")
191 result = CliRunner().invoke(
192 cli.cli,
193 [db_path, str(testdir / "features.shp"), "--spatialite", "--spatial-index"],
194 catch_exceptions=False,
195 )
201
202 def test_import_features_extract_columns(tmpdir):
203 db_path = str(tmpdir / "output.db")
204 result = CliRunner().invoke(
205 cli.cli,
206 [db_path, str(testdir / "features.shp"), "-c", "slug"],
207 catch_exceptions=False,
208 )
214 @pytest.mark.skipif(not utils.find_spatialite(), reason="Could not find SpatiaLite")
215 def test_import_features_extract_columns_spatialite(tmpdir):
216 db_path = str(tmpdir / "output.db")
217 result = CliRunner().invoke(
218 cli.cli,
219 [db_path, str(testdir / "features.shp"), "-c", "slug", "--spatialite"],
220 catch_exceptions=False,
221 )
shapefile-to-sqlite/shapefile_to_sqlite/cli.py
22 return CRS.from_user_input(value)
23 except pyproj.exceptions.CRSError as e:
24 raise click.BadParameter(str(e))
25
26
85 for filepath in shapefile:
86 openpath = filepath
87 if str(filepath).endswith(".zip"):
88 openpath = "zip://{}".format(openpath)
89 with fiona.open(openpath) as collection:
pypi-to-sqlite/README.md
50 import tempfile, pathlib
51 tmpdir = pathlib.Path(tempfile.mkdtemp())
52 db_path = str(tmpdir / "pypi.db")
53 runner = CliRunner()
54 result = runner.invoke(cli.cli, [db_path, "-f", "tests/datasette-block.json"])
openai-to-sqlite/tests/test_openai_to_sqlite.py
83 json=MOCK_RESPONSE if batch_size is None else MOCK_RESPONSE_BATCH_SIZE_1
84 )
85 db_path = str(tmpdir / "embeddings.db")
86 runner = CliRunner()
87 args = ["embeddings", db_path]
98 else:
99 input = None
100 input_path = str(tmpdir / "input." + format)
101 with open(input_path, "w") as fp:
102 fp.write(data)
105 args.extend(["--format", format])
106 if batch_size:
107 args.extend(["--batch-size", str(batch_size)])
108 expected_token = "abc"
109 if use_token_option:
142 def test_sql(httpx_mock, tmpdir, use_other_db, table_option):
143 httpx_mock.add_response(json=MOCK_RESPONSE)
144 db_path = str(tmpdir / "embeddings.db")
145 db = sqlite_utils.Database(db_path)
146 extra_opts = []
147 other_table = "content"
148 if use_other_db:
149 db_path2 = str(tmpdir / "other.db")
150 db = sqlite_utils.Database(db_path2)
151 extra_opts = ["--attach", "other", db_path2]
180 def test_search(httpx_mock, tmpdir, table_option, count):
181 httpx_mock.add_response(json=MOCK_RESPONSE)
182 db_path = str(tmpdir / "embeddings.db")
183 db = sqlite_utils.Database(db_path)
184 table = "embeddings"
196 extra_opts.extend([table_option, "other_table"])
197 if count:
198 extra_opts.extend(["--count", str(count)])
199 runner = CliRunner()
200 result = runner.invoke(
268 @pytest.mark.parametrize("save_table", (None, "custom_save"))
269 def test_similar(httpx_mock, tmpdir, table_option, save, save_table):
270 db_path = str(tmpdir / "embeddings.db")
271 db = sqlite_utils.Database(db_path)
272 table = "embeddings"
302
303 def test_similar_recalculate_for_matches(tmpdir):
304 db_path = str(tmpdir / "embeddings.db")
305 db = sqlite_utils.Database(db_path)
306 db["embeddings"].insert_all(
openai-to-sqlite/openai_to_sqlite/cli.py
106 )
107 except json.JSONDecodeError as ex:
108 raise click.ClickException(str(ex))
109 # Use a click progressbar
110 total_tokens = 0
museums/plugins/sql_functions.py
4
5 def _render_markdown(markdown):
6 rendered = str(render_markdown(markdown))
7 prefix = '<div style="white-space: normal">'
8 suffix = "</div>"
museums/metadata.yaml
10 sql: |-
11 SELECT
12 'tag:niche-museums.com,' || substr(m.created, 0, 11) || ':' || m.id as atom_id,
13 m.name as atom_title,
14 m.created as atom_updated,
mbox-to-sqlite/tests/test_mbox_to_sqlite.py
12 runner = CliRunner()
13 with runner.isolated_filesystem():
14 args = ["mbox", "enron.db", str(tests_dir / "enron-sample.mbox")]
15 if table is not None:
16 args.extend(["--table", table])
markdown-to-sqlite/tests/test_cli.py
62 cli.cli,
63 [
64 str(tmpdir / "info.db"),
65 "talks",
66 str(tmpdir / "one.md"),
67 str(tmpdir / "two.md"),
68 str(tmpdir / "three.md"),
69 ],
70 )
71 db = Database(str(tmpdir / "info.db"))
72 assert ["talks"] == db.table_names()
73 assert 3 == db["talks"].count
iam-to-sqlite/iam_to_sqlite/cli.py
18 )
19 data = json.loads(process.stdout)
20 db = sqlite_utils.Database(str(db_path))
21 db["Users"].upsert_all(data["UserDetailList"], pk="UserId")
22 db["Groups"].upsert_all(data["GroupDetailList"], pk="GroupId")
healthkit-to-sqlite/tests/test_healthkit_to_sqlite.py
31 export_xml_filename = request.param
32 zip_contents_path = pathlib.Path(__file__).parent / "zip_contents"
33 archive = str(tmpdir / "export.zip")
34 buf = io.BytesIO()
35 zf = zipfile.ZipFile(buf, "w")
39 if arcname.name == "export.xml":
40 arcname = arcname.parent / export_xml_filename
41 zf.write(filepath, str(arcname))
42 zf.close()
43 with open(archive, "wb") as fp:
184
185 def test_cli_rejects_non_zip(xml_path, tmpdir):
186 result = CliRunner().invoke(cli.cli, [str(xml_path), str(tmpdir / "output.db")])
187 assert 1 == result.exit_code
188 assert (
193
194 def test_cli_parses_xml_file(xml_path, tmpdir):
195 output = str(tmpdir / "output.db")
196 result = CliRunner().invoke(cli.cli, [str(xml_path), output, "--xml"])
197 assert 0 == result.exit_code
198 db = sqlite_utils.Database(output)
208 def test_zip_file_with_gpx(zip_file_with_gpx):
209 tmpdir, export = zip_file_with_gpx
210 output = str(tmpdir / "output.db")
211 result = CliRunner().invoke(cli.cli, [export, output])
212 assert result.exit_code == 0, result.output
hacker-news-to-sqlite/tests/test_hacker_news_to_sqlite.py
7
8 def test_import_user(tmpdir, requests_mock):
9 db_path = str(tmpdir / "data.db")
10 requests_mock.get(
11 "https://hacker-news.firebaseio.com/v0/user/simonw.json",
google-drive-to-sqlite/google_drive_to_sqlite/utils.py
86 ).json()
87 if "error" in data:
88 raise self.Error(str(data))
89 self.access_token = data["access_token"]
90 return self.access_token
google-drive-to-sqlite/README.md
449 import tempfile, pathlib, sqlite_utils
450 tmpdir = pathlib.Path(tempfile.mkdtemp())
451 db_path = str(tmpdir / "docs.db")
452 result = runner.invoke(cli.cli, [
453 "files", db_path, "--import-json", "tests/folder-and-children.json"
google-takeout-to-sqlite/tests/utils.py
9 for filepath in path.glob("**/*"):
10 if filepath.is_file():
11 zf.write(filepath, str(filepath.relative_to(path)))
12 return zf
google-calendar-to-sqlite/google_calendar_to_sqlite/utils.py
35 ).json()
36 if "error" in data:
37 raise self.Error(str(data))
38 self.access_token = data["access_token"]
39 return self.access_token
geojson-to-sqlite/tests/test_geojson_to_sqlite.py
19
20 def test_invalid(tmpdir):
21 db_path = str(tmpdir / "output.db")
22 result = CliRunner().invoke(
23 cli.cli, [db_path, "features", str(testdir / "invalid.geojson")]
24 )
25 assert 1 == result.exit_code
31
32 def test_single_feature(tmpdir):
33 db_path = str(tmpdir / "output.db")
34 result = CliRunner().invoke(
35 cli.cli, [db_path, "features", str(testdir / "feature.geojson")]
36 )
37 assert 0 == result.exit_code, result.stdout
52 feature = json.loads((testdir / "feature.geojson").read_text())
53 feature["properties"] = {}
54 db_path = str(tmpdir / "output.db")
55 result = CliRunner().invoke(
56 cli.cli, [db_path, "features", "-", "--spatialite"], input=json.dumps(feature)
64 @pytest.mark.skipif(not find_spatialite(), reason="Could not find SpatiaLite")
65 def test_feature_collection_spatialite(tmpdir):
66 db_path = str(tmpdir / "output.db")
67 result = CliRunner().invoke(
68 cli.cli,
70 db_path,
71 "features",
72 str(testdir / "feature-collection.geojson"),
73 "--spatialite",
74 "--alter",
128 db_path,
129 "features",
130 str(testdir / "feature-collection.geojson"),
131 "--spatialite",
132 "--alter",
145 @pytest.mark.parametrize("use_spatial_index", (True, False))
146 def test_spatial_index(tmpdir, use_spatial_index):
147 db_path = str(tmpdir / "output.db")
148 result = CliRunner().invoke(
149 cli.cli,
151 db_path,
152 "features",
153 str(testdir / "feature-collection.geojson"),
154 ]
155 + (["--spatial-index"] if use_spatial_index else ["--spatialite"]),
169 @pytest.mark.skipif(not find_spatialite(), reason="Could not find SpatiaLite")
170 def test_spatial_index_twice(tmpdir):
171 db_path = str(tmpdir / "output.db")
172 args = [
173 db_path,
174 "features",
175 str(testdir / "feature-collection.geojson"),
176 "--spatialite",
177 "--spatial-index",
198
199 def test_feature_collection(tmpdir):
200 db_path = str(tmpdir / "output.db")
201 result = CliRunner().invoke(
202 cli.cli, [db_path, "features", str(testdir / "feature-collection.geojson")]
203 )
204 assert 0 == result.exit_code, result.stdout
224
225 def test_multiple_files(tmpdir):
226 db_path = str(tmpdir / "output.db")
227 result = CliRunner().invoke(
228 cli.cli,
230 db_path,
231 "features",
232 str(testdir / "feature-collection.geojson"),
233 str(testdir / "feature.geojson"),
234 ],
235 )
242
243 def test_feature_collection_pk_and_alter(tmpdir):
244 db_path = str(tmpdir / "output.db")
245 result = CliRunner().invoke(
246 cli.cli, [db_path, "features", str(testdir / "feature.geojson"), "--pk=slug"]
247 )
248 assert 0 == result.exit_code, result.stdout
259 db_path,
260 "features",
261 str(testdir / "feature-collection.geojson"),
262 "--pk=slug",
263 "--alter",
273
274 def test_feature_collection_id_as_pk(tmpdir):
275 db_path = str(tmpdir / "output.db")
276 result = CliRunner().invoke(
277 cli.cli, [db_path, "features", str(testdir / "feature-collection-ids.geojson")]
278 )
279 assert 0 == result.exit_code, result.stdout
300
301 def test_feature_collection_override_id(tmpdir):
302 db_path = str(tmpdir / "output.db")
303 result = CliRunner().invoke(
304 cli.cli,
306 db_path,
307 "features",
308 str(testdir / "feature-collection-ids.geojson"),
309 "--pk=slug",
310 ],
325 def test_ndjson(tmpdir):
326 ndjson = testdir / "quakes.ndjson"
327 db_path = str(tmpdir / "output.db")
328
329 with open(ndjson) as f:
330 data = [json.loads(line) for line in f]
331
332 result = CliRunner().invoke(cli.cli, [db_path, "features", str(ndjson), "--nl"])
333 assert 0 == result.exit_code, result.stdout
334
346 def test_ndjson_with_spatial_index(tmpdir):
347 ndjson = testdir / "quakes.ndjson"
348 db_path = str(tmpdir / "output.db")
349 result = CliRunner().invoke(
350 cli.cli, [db_path, "features", str(ndjson), "--nl", "--spatial-index"]
351 )
352 assert 0 == result.exit_code, result.stdout
364 def test_missing_geometry(tmpdir):
365 quakes = testdir / "quakes.geojson"
366 db_path = str(tmpdir / "output.db")
367 result = CliRunner().invoke(cli.cli, [db_path, "features", str(quakes)])
368
369 assert 0 == result.exit_code, result.stdout
381
382 def test_bundle_properties(tmpdir):
383 db_path = str(tmpdir / "output.db")
384 result = CliRunner().invoke(
385 cli.cli,
387 db_path,
388 "features",
389 str(testdir / "feature-collection.geojson"),
390 "--properties",
391 ],
402
403 def test_bundle_properties_colname(tmpdir):
404 db_path = str(tmpdir / "output.db")
405 result = CliRunner().invoke(
406 cli.cli,
408 db_path,
409 "features",
410 str(testdir / "feature-collection.geojson"),
411 "--properties",
412 "props",
425 @pytest.mark.skipif(not find_spatialite(), reason="Could not find SpatiaLite")
426 def test_bundle_properties_spatialite(tmpdir):
427 db_path = str(tmpdir / "output.db")
428 result = CliRunner().invoke(
429 cli.cli,
431 db_path,
432 "features",
433 str(testdir / "feature-collection.geojson"),
434 "--properties",
435 "--spatialite",
geojson-to-sqlite/geojson_to_sqlite/cli.py
github-to-sqlite/tests/test_repos.py
95 def _run_repos(tmpdir):
96 runner = CliRunner()
97 db_path = str(tmpdir / "test.db")
98 result = runner.invoke(
99 cli.cli,
github-to-sqlite/github_to_sqlite/utils.py
58 repos.html_url as repo,
59 releases.html_url as release,
60 substr(releases.published_at, 0, 11) as date,
61 releases.body as body_markdown,
62 releases.published_at,
genome-to-sqlite/tests/test_genome_to_sqlite.py
9 @pytest.fixture
10 def text_path():
11 return str(pathlib.Path(__file__).parent / "example.txt")
12
13
36
37 def test_cli_parses_zip(text_path, tmpdir):
38 db_path = str(tmpdir / "output.db")
39 # Put the example in a zip file
40 zip_path = str(tmpdir / "genome.zip")
41 with zipfile.ZipFile(str(zip_path), "w") as z:
42 z.write(text_path, "genome.txt")
43 # Now use CLI tool to import that zip file
49
50 def test_cli_parses_text(text_path, tmpdir):
51 db_path = str(tmpdir / "output.db")
52 result = CliRunner().invoke(cli.cli, [text_path, db_path])
53 assert 0 == result.exit_code
fec-to-sqlite/tests/test_save_filing.py
7
8 def test_save_filing(tmpdir):
9 db_path = str(tmpdir / "data.db")
10 db = sqlite_utils.Database(db_path)
11 fec_path = str(pathlib.Path(__file__).parent / "1146148.fec")
12 save_filing(fecfile.iter_file(fec_path), db)
13 assert {"itemizations", "filings"} == set(db.table_names())
fec-to-sqlite/fec_to_sqlite/utils.py
61 for key, value in d.items():
62 if isinstance(value, datetime.datetime):
63 d[key] = str(value)
64
65
fec-to-sqlite/fec_to_sqlite/cli.py
25 for filing_id in filing_ids:
26 fec_generator, total_bytes = start_iter_http(filing_id)
27 with tqdm.tqdm(total=total_bytes, desc=str(filing_id)) as pbar:
28 save_filing(fec_generator(callback=pbar.update), db)
29 db.index_foreign_keys()
evernote-to-sqlite/tests/test_evernote_to_sqlite.py
16
17 def test_enex(tmpdir):
18 output = str(tmpdir / "output.db")
19 result = CliRunner().invoke(
20 cli, ["enex", output, str(example_enex)], catch_exceptions=False
21 )
22 assert 0 == result.exit_code
dogsheep-beta/tests/test_plugin.py
355 index.callback(beta_path, beta_config_path, None, [])
356 ds = Datasette(
357 [str(beta_path), str(github_path), str(emails_path)],
358 metadata=parse_metadata(METADATA),
359 )
dogsheep-beta/tests/test_cli.py
62
63 runner = CliRunner()
64 args = ["index", str(beta_path), str(config_path)]
65 if not use_porter:
66 args.extend(["--tokenize", "none"])
dogsheep-beta/dogsheep_beta/__init__.py
180 raise
181 output = '<p style="color: red">{}</p><pre>{}</pre><p>Template:</p><pre>{}</pre>'.format(
182 html.escape(str(e)),
183 html.escape(json.dumps(result, default=repr, indent=4)),
184 html.escape(meta["display"]),
dogsheep-photos/dogsheep_photos/utils.py
113 8, "little", signed=True
114 )
115 return str(uuid.UUID(bytes=b)).upper()
116
117
127 keyname = "{}.{}".format(sha256, ext)
128 client.upload_file(
129 str(path),
130 creds["photos_s3_bucket"],
131 keyname,
dogsheep-photos/dogsheep_photos/cli.py
161 ext = path.suffix.lstrip(".")
162 uploads.upsert(
163 {"sha256": sha256, "filepath": str(path), "ext": ext, "size": size}
164 )
165 if bar:
316 labels_db_path = photosdb._dbfile_actual.parent / "search" / "psi.sqlite"
317 if labels_db_path.exists():
318 labels_db = sqlite3.connect(str(labels_db_path))
319 if db["labels"].exists():
320 db["labels"].drop()
379 sha256s = [r[0] for r in db.conn.execute(sql).fetchall()]
380 # Copy across apple_photos, apple_photos_scores, uploads
381 db.conn.execute("ATTACH DATABASE '{}' AS [{}]".format(str(new_db_path), "newdb"))
382 # First apple_photos
383 with db.conn:
dbf-to-sqlite/dbf_to_sqlite/cli.py
24 if verbose:
25 click.echo('Loading {} into table "{}"'.format(path, table_name))
26 table = dbf.Table(str(path))
27 table.open()
28 columns = table.field_names
django-sql-dashboard/test_project/test_widgets.py
45 soup = BeautifulSoup(html, "html5lib")
46 trs = soup.select("table tbody tr")
47 assert str(trs[0].find("td")) == (
48 '<td><pre class="json">{\n'
49 ' "hello": [\n'
django-sql-dashboard/django_sql_dashboard/views.py
206 )
207 else:
208 sql_query_parameter_errors.append(str(e))
209 parameter_values = {
210 parameter: request.POST.get(parameter, request.GET.get(parameter, ""))
219 sql = sql.strip().rstrip(";")
220 base_error_result = {
221 "index": str(results_index),
222 "sql": sql,
223 "textarea_rows": min(5, len(sql.split("\n"))),
257 rows = list(cursor.fetchmany(row_limit + 1))
258 except ProgrammingError as e:
259 rows = [{"statusmessage": str(cursor.statusmessage)}]
260 duration_ms = (time.perf_counter() - start) * 1000.0
261 except Exception as e:
262 query_results.append(dict(base_error_result, error=str(e)))
263 else:
264 templates = ["django_sql_dashboard/widgets/default.html"]
282 query_results.append(
283 {
284 "index": str(results_index),
285 "sql": sql,
286 "textarea_rows": len(sql.split("\n")),
344 "default": lambda o: o.isoformat()
345 if hasattr(o, "isoformat")
346 else str(o),
347 },
348 )
db-to-sqlite/tests/test_redact.py
8 @all_databases
9 def test_redact(connection, tmpdir, cli_runner):
10 db_path = str(tmpdir / "test_redact.db")
11 result = cli_runner(
12 [
db-to-sqlite/tests/test_db_to_sqlite.py
8 @all_databases
9 def test_db_to_sqlite(connection, tmpdir, cli_runner):
10 db_path = str(tmpdir / "test.db")
11 cli_runner([connection, db_path, "--all"])
12 db = sqlite_utils.Database(db_path)
51 @all_databases
52 def test_index_fks(connection, tmpdir, cli_runner):
53 db_path = str(tmpdir / "test_with_fks.db")
54 # With --no-index-fks should create no indexes
55 cli_runner([connection, db_path, "--all", "--no-index-fks"])
64 @all_databases
65 def test_specific_tables(connection, tmpdir, cli_runner):
66 db_path = str(tmpdir / "test_specific_tables.db")
67 result = cli_runner(
68 [connection, db_path, "--table", "categories", "--table", "products", "-p"]
79 @all_databases
80 def test_sql_query(connection, tmpdir, cli_runner):
81 db_path = str(tmpdir / "test_sql.db")
82 # Without --output it throws an error
83 result = cli_runner(
108 @pytest.mark.skipif(psycopg2 is None, reason="pip install psycopg2")
109 def test_postgres_schema(tmpdir, cli_runner):
110 db_path = str(tmpdir / "test_sql.db")
111 connection = POSTGRESQL_TEST_DB_CONNECTION
112 result = cli_runner(
datasette.io/templates/index.html
123 releases.html_url as release_url,
124 releases.tag_name as version,
125 substr(releases.published_at, 0, 11) as date,
126 releases.body as body_markdown,
127 releases.published_at as released_at,
137 null as release_url,
138 pypi_versions.name as version,
139 substr(min(pypi_releases.upload_time), 0, 11) as date,
140 null as body_markdown,
141 min(pypi_releases.upload_time) as released_at,