# \#sqlite

**URL:** https://discourse.julialang.org/tag/sqlite/311.md

[Latest](https://discourse.julialang.org/latest.md) · [Categories](https://discourse.julialang.org/categories.md) · [Tags](https://discourse.julialang.org/tags.md)

---

## [Try to create table, but database is closed](https://discourse.julialang.org/t/try-to-create-table-but-database-is-closed/136271)

<div class="topic-metadata">

**Author:** [@phma](https://discourse.julialang.org/u/phma)\
**Replies:** 0\
**Last updated:** [March 19, 2026, 8:48am UTC](https://discourse.julialang.org/t/try-to-create-table-but-database-is-closed/136271 "2026-03-19T08:48:55Z")

</div>

I’ve created a database and am trying to create a table in it. mkpath(spoolDir\*"database") db=SQLite.DB(spoolDir\*"database/foo.db") function makeTables() global db SQLite.execute(db,"create table if not exists node…

---

## [Query on date doesn't return what expected](https://discourse.julialang.org/t/query-on-date-doesnt-return-what-expected/122512)

<div class="topic-metadata">

**Author:** [@andreh](https://discourse.julialang.org/u/andreh)\
**Replies:** 25\
**Last updated:** [November 16, 2024, 10:25pm UTC](https://discourse.julialang.org/t/query-on-date-doesnt-return-what-expected/122512 "2024-11-16T22:25:20Z")

</div>

I am using SQLite to perform queries on my Table “Todos” I run on julia the commands: julia\> using SQLite julia\> db = SQLite.DB(“db/dev.sqlite3”) julia\> using DataFrames From my “Todos” table I want to return valu…

---

## [Using an SQLite table as a Query.jl data source (or, best way to left-join a DataFrame or Vector with a very large sqlite table)](https://discourse.julialang.org/t/using-an-sqlite-table-as-a-query-jl-data-source-or-best-way-to-left-join-a-dataframe-or-vector-with-a-very-large-sqlite-table/116410)

<div class="topic-metadata">

**Author:** [@sleak](https://discourse.julialang.org/u/sleak)\
**Replies:** 2\
**Last updated:** [July 3, 2024, 10:50pm UTC](https://discourse.julialang.org/t/using-an-sqlite-table-as-a-query-jl-data-source-or-best-way-to-left-join-a-dataframe-or-vector-with-a-very-large-sqlite-table/116410 "2024-07-03T22:50:24Z")

</div>

I have a vector (of strings) that I want to left-join with an SQLite table (to find an “id” corresponding to each string). The SQLite table is too big to load into memory - but the left join should return only as many ro…

---

## [Cannot query user\_version from SQLite Database](https://discourse.julialang.org/t/cannot-query-user-version-from-sqlite-database/107829)

<div class="topic-metadata">

**Author:** [@guilhermebodin](https://discourse.julialang.org/u/guilhermebodin)\
**Replies:** 1\
**Last updated:** [December 19, 2023, 8:37pm UTC](https://discourse.julialang.org/t/cannot-query-user-version-from-sqlite-database/107829 "2023-12-19T20:37:34Z")

</div>

Hello, does anybody know how should I execute this query? using SQLite db = SQLite.DB() SQLite.execute(db, "PRAGMA user\_version = 3;") @show version = SQLite.execute(db, "PRAGMA user\_version;") # returns 100

---

## [Write a table into SQLite and DuckDB in efficient manner](https://discourse.julialang.org/t/write-a-table-into-sqlite-and-duckdb-in-efficient-manner/93417)

<div class="topic-metadata">

**Author:** [@sairus7](https://discourse.julialang.org/u/sairus7)\
**Replies:** 24\
**Last updated:** [December 8, 2023, 3:25am UTC](https://discourse.julialang.org/t/write-a-table-into-sqlite-and-duckdb-in-efficient-manner/93417 "2023-12-08T03:25:25Z")

</div>

How can I efficiently populate DB table from very long tables (say, millions rows)? I try to compare SQlite vs DuckDB in this scenario, but I think I’m doing this very inefficiently: using SQLite, DuckDB, Tables con\_sq…

---

## [Getting SQLite data without runtime dispatch](https://discourse.julialang.org/t/getting-sqlite-data-without-runtime-dispatch/91789)

<div class="topic-metadata">

**Author:** [@taotree](https://discourse.julialang.org/u/taotree)\
**Replies:** 1\
**Last updated:** [January 4, 2023, 4:26am UTC](https://discourse.julialang.org/t/getting-sqlite-data-without-runtime-dispatch/91789 "2023-01-04T04:26:42Z")

</div>

I can’t figure out how to avoid runtime dispatch when using SQLite (or understand if the profiler is indicating incorrectly). I ran @profview, and it’s showing most of the time in SQLite.getvalue, I think the call to sql…

---

## [SQLite backup](https://discourse.julialang.org/t/sqlite-backup/88654)

<div class="topic-metadata">

**Author:** [@sransome](https://discourse.julialang.org/u/sransome)\
**Replies:** 5\
**Last updated:** [October 19, 2022, 2:33pm UTC](https://discourse.julialang.org/t/sqlite-backup/88654 "2022-10-19T14:33:29Z")

</div>

Hi, Is there a way of using sqlite.jl to execute the API commands like sqlite3\_backup\_init() ?

---

## [Customising the legend display order in Plots.jl](https://discourse.julialang.org/t/customising-the-legend-display-order-in-plots-jl/86779)

<div class="topic-metadata">

**Author:** [@jmair](https://discourse.julialang.org/u/jmair)\
**Replies:** 1\
**Last updated:** [September 5, 2022, 1:59pm UTC](https://discourse.julialang.org/t/customising-the-legend-display-order-in-plots-jl/86779 "2022-09-05T13:59:56Z")

</div>

I am using Plots.jl with the pgfplotsx() backend and I want to have a somewhat custom legend, which has two columns and shows the marker for the expected curve next to actual samples on that curve. A MWE is given below: …

---

## [Enabling StatsBase functions in SQLite (Sqlite3Stats)](https://discourse.julialang.org/t/enabling-statsbase-functions-in-sqlite-sqlite3stats/73829)

<div class="topic-metadata">

**Author:** [@jbytecode](https://discourse.julialang.org/u/jbytecode)\
**Replies:** 1\
**Last updated:** [August 8, 2022, 7:15pm UTC](https://discourse.julialang.org/t/enabling-statsbase-functions-in-sqlite-sqlite3stats/73829 "2022-08-08T19:15:29Z")

</div>

Hi guys, I am not sure if it is really needed but it is useful (at least for me) to have aggregative functions such as correlation, skewness, kurtosis, covariance, median absolute deviations, median, geometric and harmo…

---

## [SQLite: query with duplicate column names |\> DataFrame](https://discourse.julialang.org/t/sqlite-query-with-duplicate-column-names-dataframe/64518)

<div class="topic-metadata">

**Author:** [@viraltux](https://discourse.julialang.org/u/viraltux)\
**Replies:** 15\
**Last updated:** [March 29, 2022, 5:36am UTC](https://discourse.julialang.org/t/sqlite-query-with-duplicate-column-names-dataframe/64518 "2022-03-29T05:36:08Z")

</div>

Hi guys, The following query with duplicate column names works however when converting to a DataFrame I have a "not unique" error. using SQLite db = SQLite.DB(); Q = q -\> DBInterface.execute(db, q); DataFrame(rand(10,…

---

## [Get the number of rows updated in SQLite?](https://discourse.julialang.org/t/get-the-number-of-rows-updated-in-sqlite/78023)

<div class="topic-metadata">

**Author:** [@taotree](https://discourse.julialang.org/u/taotree)\
**Replies:** 0\
**Last updated:** [March 17, 2022, 12:58pm UTC](https://discourse.julialang.org/t/get-the-number-of-rows-updated-in-sqlite/78023 "2022-03-17T12:58:20Z")

</div>

In other languages I’ve used, after running update SQL, it returns the number of rows updated. However, when I run DBInterface.execute("update Test set val='green' where id=1"), it seems to return nothing. Is there a way…

---

## [Sqlite database remains in use by Julia REPL](https://discourse.julialang.org/t/sqlite-database-remains-in-use-by-julia-repl/77194)

<div class="topic-metadata">

**Author:** [@Marie](https://discourse.julialang.org/u/Marie)\
**Replies:** 5\
**Last updated:** [February 28, 2022, 4:29pm UTC](https://discourse.julialang.org/t/sqlite-database-remains-in-use-by-julia-repl/77194 "2022-02-28T16:29:08Z")

</div>

I have a small program that creates sqlite database with a single table. Inserts some data into the table. In the end of program execution database file remains in use by Julia. I have to kill REPL terminal to release i…

---

## [SQLite.jl NOT NULL constraint failed](https://discourse.julialang.org/t/sqlite-jl-not-null-constraint-failed/74628)

<div class="topic-metadata">

**Author:** [@Jeremy](https://discourse.julialang.org/u/Jeremy)\
**Replies:** 0\
**Last updated:** [January 14, 2022, 2:52pm UTC](https://discourse.julialang.org/t/sqlite-jl-not-null-constraint-failed/74628 "2022-01-14T14:52:04Z")

</div>

Since updating Julia from v1.6.2 (SQLite v1.2.0) to v1.7.1 (SQLite v 1.3.0) I get an error while trying to add data to an existing table in my database. julia\> dataBiomet |\> SQLite.load!(db,"Biomet") ERROR: SQLiteExcept…

---

## [Is there an idiomatic way to select a single value from a SQL query?](https://discourse.julialang.org/t/is-there-an-idiomatic-way-to-select-a-single-value-from-a-sql-query/68962)

<div class="topic-metadata">

**Author:** [@gleyland](https://discourse.julialang.org/u/gleyland)\
**Replies:** 3\
**Last updated:** [October 1, 2021, 7:59am UTC](https://discourse.julialang.org/t/is-there-an-idiomatic-way-to-select-a-single-value-from-a-sql-query/68962 "2021-10-01T07:59:23Z")

</div>

Hi, Some SQL queries (like “SELECT COUNT(\*)…”) return a single value. The shortest way I’ve managed to come up with for obtaining the result of the query is: using SQLite db = SQLite.DB() SQLite.DBInterface.execute(db…

---

## [SQLite.jl execute with "pragma" not working](https://discourse.julialang.org/t/sqlite-jl-execute-with-pragma-not-working/46935)

<div class="topic-metadata">

**Author:** [@andrey2185](https://discourse.julialang.org/u/andrey2185)\
**Replies:** 1\
**Last updated:** [September 19, 2020, 10:04pm UTC](https://discourse.julialang.org/t/sqlite-jl-execute-with-pragma-not-working/46935 "2020-09-19T22:04:12Z")

</div>

Hello, why this not working? I do using SQLite db = SQLite.DB("SQLite\_test.db") SQLite.execute(db, "PRAGMA count\_changes=true;") but then count\_changes=false in my database

---

## [Threading usage patterns: Worker pools for Agents.jl and SQLite.jl](https://discourse.julialang.org/t/threading-usage-patterns-worker-pools-for-agents-jl-and-sqlite-jl/44232)

<div class="topic-metadata">

**Author:** [@dlakelan](https://discourse.julialang.org/u/dlakelan)\
**Replies:** 1\
**Last updated:** [August 4, 2020, 2:02pm UTC](https://discourse.julialang.org/t/threading-usage-patterns-worker-pools-for-agents-jl-and-sqlite-jl/44232 "2020-08-04T14:02:45Z")

</div>

I’ve got some code where I spawn off a thread to handle SQLite interaction, and then spawn off 4 threads to handle running Agents.jl simulations. My main function then goes and grabs a simulation description Tuple, put!…

---

## [Correct way to transparently use SQLite.jl responses or (legacy) text files as a data source](https://discourse.julialang.org/t/correct-way-to-transparently-use-sqlite-jl-responses-or-legacy-text-files-as-a-data-source/41825)

<div class="topic-metadata">

**Author:** [@mkarikom](https://discourse.julialang.org/u/mkarikom)\
**Replies:** 3\
**Last updated:** [June 24, 2020, 5:51pm UTC](https://discourse.julialang.org/t/correct-way-to-transparently-use-sqlite-jl-responses-or-legacy-text-files-as-a-data-source/41825 "2020-06-24T17:51:02Z")

</div>

I’ve really been enjoying SQLight.jl as a back end that just works. Is there any high level documentation on the relationship between SQLight.jl, Tables.jl, and DataFrames.jl? Background: Unfortunately, the package I’m…

---

## [Use julia array in SQLite Query](https://discourse.julialang.org/t/use-julia-array-in-sqlite-query/32049)

<div class="topic-metadata">

**Author:** [@kevbonham](https://discourse.julialang.org/u/kevbonham)\
**Replies:** 1\
**Last updated:** [December 9, 2019, 5:10pm UTC](https://discourse.julialang.org/t/use-julia-array-in-sqlite-query/32049 "2019-12-09T17:10:23Z")

</div>

I asked this before in Slack, but failed to write down the answer. As penance, I vow to write this up as a PR to the SQLite docs :laughing: I’d like to use an existing vector as part of a SQLite.Query. Eg: julia\> using…

---

## [Constant declaration in modules (SQLite)](https://discourse.julialang.org/t/constant-declaration-in-modules-sqlite/14801)

<div class="topic-metadata">

**Author:** [@moesphere](https://discourse.julialang.org/u/moesphere)\
**Replies:** 7\
**Last updated:** [September 11, 2018, 9:17am UTC](https://discourse.julialang.org/t/constant-declaration-in-modules-sqlite/14801 "2018-09-11T09:17:14Z")

</div>

Following minimal code worked fine with julia 0.6.4, but does not work with 1.0.0. Defining a minimal module: module TestModule using SQLite fp = "" const db = SQLite.DB(fp) end Loading the module and execute opera…