Skip to main content

SQLite Library Reference

SQLite is a self‑contained, high‑reliability, embedded, full‑featured, public‑domain SQL database engine. Unlike client‑server databases, SQLite runs in the same process as your application and stores the entire database as a single file on disk. Because of its small footprint (under 600 KB of library code), zero‑configuration setup, and transactional integrity, SQLite is ideal for:

  • Embedded environments: Mobile apps, IoT devices, desktop utilities.
  • Prototyping & testing: Spin up a database instantly without installing or configuring a server.
  • Standalone tools: Command‑line utilities, small web services, local caches.

Key characteristics:​

  • Server‑less: No separate database server process; your code talks directly to the database file.
  • Zero‑configuration: No “installation”—just open the file.
  • Transactional: Full ACID compliance ensures data integrity, even on crashes or power failures.
  • Portable: Database files are cross‑platform and can be copied, shared, or checked into version control.
  • SQL support: Most of the SQL‑92 standard, including complex queries, joins, indexes, triggers, and views.

Why EasyBite Includes SQLite​

EasyBite is designed for beginners and hobbyists who want to build powerful data‑driven scripts without the overhead of installing and managing external database servers. By bundling SQLite support directly into the language core, EasyBite allows you to:

  • Get started immediately — no external dependencies or setup steps.
  • Store structured data — tables, rows, and indexes let you query and filter large datasets efficiently.
  • Use familiar SQL — leverage your existing SQL knowledge to interact with data.
  • Build applications that scale — start small with a local file and later migrate to a larger server‑based database if needed.
  • Ensure data safety — ACID transactions protect your data without additional coding.

Benefits for EasyBite Users​

  • Simplicity: A single import sqlite and you’re ready to create tables, insert records, and run queries.
  • Integration: Treat database operations like any other EasyBite function—use set, show, and value assignments.
  • Portability: Your .db file travels with your script—share it with teammates or run it on any machine.
  • Performance: For most small to medium workloads, SQLite is fast and efficient.
  • Learning: Gain hands‑on SQL experience directly in EasyBite without installing external tools.

SQLite Library Reference

The sqlite library provides functions for working with SQLite databases in EasyBite. You must import the module before using any functions:

import sqlite

Or import specific functions:

from sqlite import connect, query, fetchall

Available Functions​

FunctionParametersDescription
connect(dbname)dbname (String)Opens (or creates) the SQLite database file and returns a connection object.
create(dbname)dbname (String)Alias for connect—creates a new database file (or opens existing).
query(conn, sql)conn (Connection), sql (String)Executes the given SQL. For DML (INSERT/UPDATE/DELETE) returns true if rows affected > 0; for SELECT returns a SQLiteResult.
fetchall(result)result (SQLiteResult)Converts a SQLiteResult into an array of dictionaries (one per row).
fetchassoc(result)result (SQLiteResult)Removes and returns the next row as a dictionary, or null if none left.
fetchone(result)result (SQLiteResult)Equivalent to fetchrow(result, 0).
fetchrow(result, index)result (SQLiteResult), index (Number)Removes and returns the row at index as a numeric array, or error if out of bounds.
fetcharray(result, mode)result (SQLiteResult), mode (0/1/2)Removes and returns next row in three modes: <0: dict; 1: numeric array; 2: both combined.
numrows(result)result (SQLiteResult)Returns the number of rows remaining in the result set.
insertid()―Returns the last insert row ID from the most recent DML, on the global connection.
begin_transaction(conn)conn (Connection)Begins a new transaction.
commit(conn)conn (Connection)Commits the current transaction.
rollback(conn)conn (Connection)Rolls back the current transaction.
escape_string(input)input (String)Returns a version of input safe for embedding in SQL string literals (single quotes doubled).
error()―Returns the last error message (currently always "").
version(conn)conn (Connection)Returns the SQLite library version as a string.
close(conn)conn (Connection)Closes the given connection.

Examples​

All examples assume:

import sqlite

and that you have write access to the working directory.

1. connect(dbname) / create(dbname)​

set db to sqlite.connect("test.db")
show(db.typeof())
// Output:
// "function"  // Connection is a built-in SQLiteConnection

set newdb to sqlite.create("new.db")
show(newdb.typeof())
// Output:
// "function"

2. query(conn, sql) with DDL/DML​

set ok1 to sqlite.query(db, "CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT)")
show(ok1)
// Output:
// true

set ok2 to sqlite.query(db, "INSERT INTO users (name) VALUES ('Alice')")
show(ok2)
// Output:
// true

3. insertid()​

set lastId to sqlite.insertid()
show(lastId)
// Output (example):
// 1

4. Transactions: begin_transaction, commit, rollback​

set _ to sqlite.begin_transaction(db)
set ok to sqlite.query(db, "INSERT INTO users (name) VALUES ('Bob')")
show(ok)
// Output:
// true

set _ to sqlite.rollback(db)
show(sqlite.query(db, "SELECT COUNT(*) as cnt FROM users").typeof())
// Output:
// "function"

set _ to sqlite.begin_transaction(db)
set ok2 to sqlite.query(db, "INSERT INTO users (name) VALUES ('Carol')")
show(ok2)
// Output:
// true

set _ to sqlite.commit(db)

5. query(conn, sql) with SELECT​

set r to sqlite.query(db, "SELECT id, name FROM users")
show(r)
// Output:
// true

6. fetchall(result)​

set allRows to sqlite.fetchall(r)
show(allRows)
// Output (example):
// [{"id":1,"name":"Alice"},{"id":3,"name":"Carol"}]

7. numrows(result)​

set count to sqlite.numrows(r)
show(count)
// Output (example):
// 2

8. fetchassoc(result)​

set row1 to sqlite.fetchassoc(r)
show(row1)
// Output (example):
// {"id":1,"name":"Alice"}

set row2 to sqlite.fetchassoc(r)
show(row2)
// Output (example):
// {"id":3,"name":"Carol"}

set noneLeft to sqlite.fetchassoc(r)
show(noneLeft)
// Output:
// null

9. fetchone(result)​

set r2 to sqlite.query(db, "SELECT name FROM users")
set first to sqlite.fetchone(r2)
show(first)
// Output (example):
// ["Alice"]

10. fetchrow(result, index)​

set r3 to sqlite.query(db, "SELECT id FROM users")
set secondId to sqlite.fetchrow(r3, 1)
show(secondId)
// Output (example):
// [3]

11. fetcharray(result, mode)​

set r4 to sqlite.query(db, "SELECT id, name FROM users")
set asDict to sqlite.fetcharray(r4, 0)
show(asDict)
// Output:
// {"id":1,"name":"Alice"}

set asNum to sqlite.fetcharray(r4, 1)
show(asNum)
// Output:
// [1,"Alice"]

set asBoth to sqlite.fetcharray(r4, 2)
show(asBoth)
// Output:
// {"0":1,"1":"Alice","id":1,"name":"Alice"}

12. escape_string(input)​

set raw to "O'Reilly"
set safe to sqlite.escape_string(raw)
show(safe)
// Output:
// O''Reilly

13. error()​

set err to sqlite.error()
show(err)
// Output:
// ""

14. version(conn)​

set ver to sqlite.version(db)
show(ver)
// Output (example):
// 3.34.1

15. close(conn)​

set _ to sqlite.close(db)
show(db)
// Output:
// null

Conclusion​

The sqlite library in EasyBite gives you full control over embedded SQL databases:

  • Import module: import sqlite → sqlite.connect(...), sqlite.query(...)
  • Selective import: from sqlite import connect, query, fetchall → connect("db"), query(...), fetchall(...)

Use these functions to manage tables, transactions, and result sets directly from your EasyBite scripts.