Adding a row to SQLite.jl database

Specific Domains Data
1

How do you add a row to a SQLite.jl database?

julia> using SQLite
julia> db = SQLite.DB("temp.sqlite")
SQLite.DB("temp.sqlite")
julia> SQLite.createtable!(db, "Primes", Tables.Schema(("index", "prime"), (Int, Int)))

Now I want to add a row to the "Primes" table, with the entries ("index" = 1, "prime" = 2), and write the changes to the temp.sqlite file.

How can I do that?

2 
using DBInterface, SQLite

stmt = DBInterface.prepare(db, "INSERT INTO Primes (index, prime) VALUES (?, ?)")
row = (index=1, prime=2)
DBInterface.execute(stmt, (row.index, row.prime))

FYI, there are lots of great tutorials on the web for working w/ sqlite, like this one: SQLite Insert Into - Inserting New Rows Into a Table.

3 Likes
3

Something’s wrong

julia> stmt = DBInterface.prepare(db, "INSERT INTO Primes (index, prime) VALUES (?, ?)")
ERROR: SQLite.SQLiteException("near \"index\": syntax error")
Stacktrace:
 [1] sqliteerror(::SQLite.DB) at /home/cossio/.julia/packages/SQLite/LjMUn/src/SQLite.jl:17
 [2] macro expansion at /home/cossio/.julia/packages/SQLite/LjMUn/src/consts.jl:24 [inlined]
 [3] sqliteprepare at /home/cossio/.julia/packages/SQLite/LjMUn/src/SQLite.jl:109 [inlined]
 [4] SQLite.Stmt(::SQLite.DB, ::String) at /home/cossio/.julia/packages/SQLite/LjMUn/src/SQLite.jl:94
 [5] prepare(::SQLite.DB, ::String) at /home/cossio/.julia/packages/SQLite/LjMUn/src/tables.jl:78
 [6] top-level scope at REPL[8]:1

Edit: It seems I cannot use index as the name of a column. If I use idx instead (say), it works.

4

You can also “quote” the column name, so:

stmt = DBInterface.prepare(db, "INSERT INTO Primes (\"index\", prime) VALUES (?, ?)")

should work too

3 Likes
5

I think you are asking for trouble when you use a SQL keyword “index” as a column name. It can be done, but you’ll have to remember to quote it everywhere when it appears. Very troublesome.
Change the name to something else. You’ll make your coding life easier.

6

list of SQLITE reserved words.
https://www.sqlite.org/lang_keywords.html

avoid those. one tick wrong and you have a nice afternoon finding the error.

i usually use rid as index column
create table if not exists primes(rid integer primary key autoincrement,prime integer)
this way you only care about inserting the prime nuber, indexing is done by sqlite dtb