Edit on GitHub

SQLite.jl Documentation

High-level interface

DBInterface.execute — Function
DBInterface.execute(db::SQLite.DB, sql::String, [params])
DBInterface.execute(stmt::SQLite.Stmt, [params])

Bind any positional (params as Vector or Tuple) or named (params as NamedTuple or Dict) parameters to an SQL statement, given by db and sql or as an already prepared statement stmt, execute the query and return an iterator of result rows.

Note that the returned result row iterator only supports a single-pass, forward-only iteration of the result rows. Calling SQLite.reset!(result) will re-execute the query and reset the iterator back to the beginning.

The resultset iterator supports the Tables.jl interface, so results can be collected in any Tables.jl-compatible sink, like DataFrame(results), CSV.write("results.csv", results), etc.

Passing strict=true to DBInterface.execute will cause the resultset iterator to return values of the exact type specified by SQLite.

source
SQLite.load! — Function
source |> SQLite.load!(db::SQLite.DB, tablename::String; temp::Bool=false, ifnotexists::Bool=false, replace::Bool=false, on_conflict::Union{String, Nothing} = nothing, analyze::Bool=false)
SQLite.load!(source, db, tablename; temp=false, ifnotexists=false, replace::Bool=false, on_conflict::Union{String, Nothing} = nothing, analyze::Bool=false)

Load a Tables.jl input source into an SQLite table that will be named tablename (will be auto-generated if not specified).

  • temp=true will create a temporary SQLite table that will be destroyed automatically when the database is closed
  • ifnotexists=false will throw an error if tablename already exists in db
  • on_conflict=nothing allows to specify an alternative constraint conflict resolution algorithm: "ABORT", "FAIL", "IGNORE", "REPLACE", or "ROLLBACK".
  • replace=false controls whether an INSERT INTO ... statement is generated or a REPLACE INTO .... This keyword argument exists for backward compatibility, and is overridden if an algorithm is selected using the on_conflict keyword.
  • analyze=true will execute ANALYZE at the end of the insert
source

Types/Functions

SQLite.DB — Type
`SQLite.DB()` => in-memory SQLite database
`SQLite.DB(file)` => file-based SQLite database

Constructors for a representation of an sqlite database, either backed by an on-disk file or in-memory.

SQLite.DB requires the file string argument in the 2nd definition as the name of either a pre-defined SQLite database to be opened, or if the file doesn't exist, a database will be created. Note that only sqlite 3.x version files are supported.

The SQLite.DB object represents a single connection to an SQLite database. All other SQLite.jl functions take an SQLite.DB as the first argument as context.

To create an in-memory temporary database, call SQLite.DB().

The SQLite.DB will be automatically closed/shutdown when it goes out of scope (i.e. the end of the Julia session, end of a function call wherein it was created, etc.)

source
SQLite.Stmt — Type
SQLite.Stmt(db, sql; register = true) => SQL.Stmt

Prepares an optimized internal representation of SQL statement in the context of the provided SQLite3 db and constructs the SQLite.Stmt Julia object that holds a reference to the prepared statement.

Note: the sql statement is not actually executed, but only compiled (mainly for usage where the same statement is executed multiple times with different parameters bound as values).

The SQLite.Stmt will be automatically closed/shutdown when it goes out of scope (i.e. the end of the Julia session, end of a function call wherein it was created, etc.). One can also call DBInterface.close!(stmt) to immediately close it.

The keyword argument register controls whether the created Stmt is registered in the provided SQLite3 database db. All registered and unclosed statements of a given DB connection are automatically closed when the DB is garbage collected or closed explicitly after calling close(db) or DBInterface.close!(db).

source
SQLite.bind! — Function
SQLite.bind!(stmt::SQLite.Stmt, values)

bind values to parameters in a prepared SQLite.Stmt. Values can be:

  • Vector or Tuple: where each element will be bound to an SQL parameter by index order
  • Dict or NamedTuple; where values will be bound to named SQL parameters by the Dict/NamedTuple key

Additional methods exist for working individual SQL parameters:

  • SQLite.bind!(stmt, name, val): bind a single value to a named SQL parameter
  • SQLite.bind!(stmt, index, val): bind a single value to a SQL parameter by index number

From the SQLite documentation:

Usually, though, it is not useful to evaluate exactly the same SQL statement more than once. More often, one wants to evaluate similar statements. For example, you might want to evaluate an INSERT statement multiple times though with different values to insert. To accommodate this kind of flexibility, SQLite allows SQL statements to contain parameters which are "bound" to values prior to being evaluated. These values can later be changed and the same prepared statement can be evaluated a second time using the new values.

In SQLite, wherever it is valid to include a string literal, one can use a parameter in one of the following forms:

  • ?
  • ?NNN
  • :AAA
  • $AAA
  • @AAA

In the examples above, NNN is an integer value and AAA is an identifier. A parameter initially has a value of NULL. Prior to calling sqlite3_step() for the first time or immediately after sqlite3_reset(), the application can invoke one of thesqlite3bind()interfaces to attach values to the parameters. Each call tosqlite3bind()` overrides prior bindings on the same parameter.

source
SQLite.createtable! — Function
SQLite.createtable!(db::SQLite.DB, table_name, schema::Tables.Schema; temp=false, ifnotexists=true)

Create a table in db with name table_name, according to schema, which is a set of column names and types, constructed like Tables.Schema(names, types) where names can be a vector or tuple of String/Symbol column names, and types is a vector or tuple of sqlite-compatible types (Int, Float64, String, or unions of Missing).

If temp=true, the table will be created temporarily, which means it will be deleted when the db is closed. If ifnotexists=true, no error will be thrown if the table already exists.

source
SQLite.drop! — Function
SQLite.drop!(db, table; ifexists::Bool=true)

drop the SQLite table table from the database db; ifexists=true will prevent an error being thrown if table doesn't exist

source
SQLite.dropindex! — Function
SQLite.dropindex!(db, index; ifexists::Bool=true)

drop the SQLite index index from the database db; ifexists=true will not return an error if index doesn't exist

source
SQLite.createindex! — Function
SQLite.createindex!(db, table, index, cols; unique=true, ifnotexists=false)

create the SQLite index index on the table table using cols, which may be a single column or vector of columns. unique specifies whether the index will be unique or not. ifnotexists=true will not throw an error if the index already exists

source
SQLite.removeduplicates! — Function
SQLite.removeduplicates!(db, table, cols)

Removes duplicate rows from table based on the values in cols, which is an array of column names.

A convenience method for the common task of removing duplicate rows in a dataset according to some subset of columns that make up a "primary key".

source
SQLite.tables — Function
SQLite.tables(db, sink=columntable)

returns a list of tables in db

source
SQLite.columns — Function
SQLite.columns(db, table, sink=columntable)

returns a list of columns in table

source
SQLite.indices — Function
SQLite.indices(db, sink=columntable)

returns a list of indices in db

source
SQLite.enable_load_extension — Function
SQLite.enable_load_extension(db, enable::Bool=true)

Enables extension loading (off by default) on the sqlite database db. Pass false as the second argument to disable.

source
SQLite.register — Function
SQLite.register(db, func)
SQLite.register(db, init, step_func, final_func; nargs=-1, name=string(step), isdeterm=true)

Register a scalar (first method) or aggregate (second method) function with a SQLite.DB.

source
SQLite.@register — Macro
SQLite.@register db function

User-facing macro for convenience in registering a simple function with no configurations needed

source
SQLite.@sr_str — Macro
sr"..."

This string literal is used to escape all special characters in the string, useful for using regex in a query.

source
SQLite.sqlreturn — Function

This function should never be called explicitly. Instead it is exported so that it can be overloaded when necessary, see below.

source
SQLite.transaction — Function
SQLite.transaction(db, mode="DEFERRED")
SQLite.transaction(func, db)

Begin a transaction in the specified mode, default = "DEFERRED".

If mode is one of "", "DEFERRED", "IMMEDIATE" or "EXCLUSIVE" then a transaction of that (or the default) mutable struct is started. Otherwise a savepoint is created whose name is mode converted to AbstractString.

In the second method, func is executed within a transaction (the transaction being committed upon successful execution)

source
SQLite.commit — Function
SQLite.commit(db)
SQLite.commit(db, name)

commit a transaction or named savepoint

source
SQLite.rollback — Function
SQLite.rollback(db)
SQLite.rollback(db, name)

rollback transaction or named savepoint

source

User Defined Functions

SQLite Regular Expressions

SQLite provides syntax for calling the regexp function from inside WHERE clauses. Unfortunately, however, sqlite does not provide a default implementation of the regexp function. It can be easily added, however, by calling SQLite.@register db SQLite.regexp

The function can be called in the following ways (examples using the Chinook Database)

julia> using SQLite

julia> db = SQLite.DB("Chinook_Sqlite.sqlite")

julia> # using SQLite's in-built syntax

julia> DBInterface.execute(db, "SELECT FirstName, LastName FROM Employee WHERE LastName REGEXP 'e(?=a)'") |> DataFrame
1x2 ResultSet
| Row | "FirstName" | "LastName" |
|-----|-------------|------------|
| 1   | "Jane"      | "Peacock"  |

julia> # explicitly calling the regexp() function

julia> DBInterface.execute(db, "SELECT * FROM Genre WHERE regexp('e[trs]', Name)") |> DataFrame
6x2 ResultSet
| Row | "GenreId" | "Name"               |
|-----|-----------|----------------------|
| 1   | 3         | "Metal"              |
| 2   | 4         | "Alternative & Punk" |
| 3   | 6         | "Blues"              |
| 4   | 13        | "Heavy Metal"        |
| 5   | 23        | "Alternative"        |
| 6   | 25        | "Opera"              |

julia> # you can even do strange things like this if you really want

julia> DBInterface.execute(db, "SELECT * FROM Genre ORDER BY GenreId LIMIT 2") |> DataFrame
2x2 ResultSet
| Row | "GenreId" | "Name" |
|-----|-----------|--------|
| 1   | 1         | "Rock" |
| 2   | 2         | "Jazz" |

julia> DBInterface.execute(db, "INSERT INTO Genre VALUES (regexp('^word', 'this is a string'), 'My Genre')") |> DataFrame
1x1 ResultSet
| Row | "Rows Affected" |
|-----|-----------------|
| 1   | 0               |

julia> DBInterface.execute(db, "SELECT * FROM Genre ORDER BY GenreId LIMIT 2") |> DataFrame
2x2 ResultSet
| Row | "GenreId" | "Name"     |
|-----|-----------|------------|
| 1   | 0         | "My Genre" |
| 2   | 1         | "Rock"     |

Due to the heavy use of escape characters, you may run into problems where julia parses out some backslashes in your query, for example "\y" simply becomes "y". For example, the following two queries are identical:

julia> DBInterface.execute(db, "SELECT * FROM MediaType WHERE Name REGEXP '-\d'") |> DataFrame
1x1 ResultSet
| Row | "Rows Affected" |
|-----|-----------------|
| 1   | 0               |

julia> DBInterface.execute(db, "SELECT * FROM MediaType WHERE Name REGEXP '-d'") |> DataFrame
1x1 ResultSet
| Row | "Rows Affected" |
|-----|-----------------|
| 1   | 0               |

This can be avoided in two ways. You can either escape each backslash yourself or you can use the sr"..." string literal that SQLite.jl exports. The previous query can then successfully be run like so:

julia> # manually escaping backslashes

julia> DBInterface.execute(db, "SELECT * FROM MediaType WHERE Name REGEXP '-\\d'") |> DataFrame
1x2 ResultSet
| Row | "MediaTypeId" | "Name"                        |
|-----|---------------|-------------------------------|
| 1   | 3             | "Protected MPEG-4 video file" |

julia> # using sr"..."

julia> DBInterface.execute(db, sr"SELECT * FROM MediaType WHERE Name REGEXP '-\d'") |> DataFrame
1x2 ResultSet
| Row | "MediaTypeId" | "Name"                        |
|-----|---------------|-------------------------------|
| 1   | 3             | "Protected MPEG-4 video file" |

The sr"..." currently escapes all special characters in a string but it may be changed in the future to escape only characters which are part of a regex.

Custom Scalar Functions

SQLite.jl also provides a way that you can implement your own Scalar Functions. This is done using the SQLite.register function and macro.

SQLite.@register takes a SQLite.DB and a function. The function can be in block syntax:

julia> SQLite.@register db function add3(x)
       x + 3
       end

inline function syntax:

julia> SQLite.@register db mult3(x) = 3 * x

and previously defined functions:

julia> SQLite.@register db sin

The SQLite.register function takes optional arguments; nargs which defaults to -1, name which defaults to the name of the function, isdeterm which defaults to true. In practice these rarely need to be used.

The SQLite.register function uses the SQLite.sqlreturn function to return your function's return value to SQLite. By default, sqlreturn maps the returned value to a native SQLite type or, failing that, serializes the julia value and stores it as a BLOB. To change this behaviour simply define a new method for sqlreturn which then calls a previously defined method for sqlreturn. Methods which map to native SQLite types are

sqlreturn(context, ::NullType)
sqlreturn(context, val::Int32)
sqlreturn(context, val::Int64)
sqlreturn(context, val::Float64)
sqlreturn(context, val::UTF16String)
sqlreturn(context, val::String)
sqlreturn(context, val::Any)

As an example, say you would like BigInts to be stored as TEXT rather than a BLOB. You would simply need to define the following method:

sqlreturn(context, val::BigInt) = sqlreturn(context, string(val))

Another example is the SQLite.sqlreturn used by the regexp function. For regexp to work correctly, it must return it must return an Int (more specifically a 0 or 1) but occursin (used by regexp) returns a Bool. For this reason the following method was defined:

sqlreturn(context, val::Bool) = sqlreturn(context, int(val))

Any new method defined for sqlreturn must take two arguments and must pass the first argument straight through as the first argument.

Custom Aggregate Functions

Using the SQLite.register function, you can also define your own aggregate functions with largely the same semantics.

The SQLite.register function for aggregates must take a SQLite.DB, an initial value, a step function and a final function. The first argument to the step function will be the return value of the previous function (or the initial value if it is the first iteration). The final function must take a single argument which will be the return value of the last step function.

julia> dsum(prev, cur) = prev + cur

julia> dsum(prev) = 2 * prev

julia> SQLite.register(db, 0, dsum, dsum)

If no name is given, the name of the first (step) function is used (in this case "dsum"). You can also use lambdas; the following does the same as the previous code snippet

julia> SQLite.register(db, 0, (p,c) -> p+c, p -> 2p, name="dsum")