μSQLite library module for MicroPython

Overview

μSQLite library module for MicroPython

WARNING: This project is in the beta development stage and may be subject to change.

usqlite is a SQL database library module for MicroPython built on the popular SQLite C library.

The usqlite SQL interface is designed to be a subset of the DB-API 2.0 specification as specified by PEP 249. The usqlite API interface is also highly compatible with the standard sqlite3 library for Python with a few extra features.

Using the usqlite module in a MicroPython application is relatively simple. The application imports the usqlite library, connects to a database and then executes SQL commands. For example:

import usqlite

if not usqlite.mem_status():
    usqlite.mem_status(True) # Enable memory usage monitoring

con = usqlite.connect("data.db")

con.executemany(
    "BEGIN TRANSACTION;"
    "CREATE TABLE IF NOT EXISTS data (name TEXT, year INT);"+
    "INSERT INTO data VALUES ('Larry', 1902);"+
    "INSERT INTO data VALUES ('Curly', 1903);"+
    "INSERT INTO data VALUES ('Moe', 1897);"+
    "INSERT INTO data VALUES ('Shemp', 1895);"+
    "COMMIT;")

with con.execute("SELECT * from data") as cur:
    for row in cur:
        print("stooge:", row)
        
con.close()

print("usqlite mem - current:", usqlite.mem_current(), "peak:", usqlite.mem_peak())

The database files created or used by usqlite are compatible with SQLite database files created by applications on other platforms and operating systems such as DB Browser for SQLite.


Getting Started

The usqlite module is designed so that it can be easily compiled and included in MicroPython builds alongside other external modules.

Version 3.36.0 of the SQLite amalgamated source files sqlite3.h and sqlite3.c are included with the usqlite source code. These may be replaced with alternate or custom amalgamated versions built from the canonical SQLite source code.

Project directory structure

The directory structure used in the develoment of this module is as shown below. The usqlite project can easily be modified to suit your own alternate project structure requirements with minor changes to the code.

~/<projectroot>
    /micropython            # MicroPython source code
    /modules
        micropython.cmake
        /usqlite            # μSQLite source code 
        ...
        ...
cd <projectroot>
mkdir modules
mkdir sqlite
git clone https://github.com/micropython/micropython.git
cd modules
git clone https://github.com/spatialdude/usqlite.git

Typical micropython.cmake

# This top-level micropython.cmake is responsible for listing
# the individual modules we want to include.
# Paths are absolute, and ${CMAKE_CURRENT_LIST_DIR} can be
# used to prefix subdirectories.

include(${CMAKE_CURRENT_LIST_DIR}/usqlite/micropython.cmake)

Compiling

Refer to the MicroPython's Getting Started wiki and documentation for more details on setting up a build environment.

Ports

Custom Configurations

The default configuration of usqlite is intended to suit typical project requirements. This includes which SQLite components are included, memory allocation configuration and debug options.

The usqlite configuration settings can be found in the C header file usqlite_config.h.

Memory allocation configuration

MicroPython builds often need to account for constrained memory enviroments. Fortunately the SQLite library is lightweight and has been designed so that it can be configured to accomodate many different memory environment needs.

SQLite does an excellent job of keeping memory usage as low as possible, so usqlite can be made to work well even in very tightly constrained memory spaces. The usqlite module provides functions that allow your application to monitor memory usage.

The default configuration of usqlite implements a custom dymanic memory allocator that uses MicroPython's GC heap. Memory demands placed on the heap will vary greatly depending on the complexity of the SQL of your application.

usqlite can be configured with an alternate memory configuration allocation that limits the memory to a fixed static heap size.


usqlite library API

As the usqlite API interface is highly compatible with the standard sqlite3 library for for Python, much of the sqlite3 documentation is also applicable to usqlite.

The details in this section will describe differences and API features unique to usqlite. Please also refer to the sqlite3 documentation as a general reference.

usqlite global object

Constants

Name Type Description
version str usqlite module version string e.g. "0.1.0"
version_info tuple usqlite module version tuple e.g (0,1,0)
sqlite_version str SQLite version string e.g. "3.36.0"
sqlite_version_info tuple SQLite version tuple e.g (3,36,0)
sqlite_version_number int SQLite version number e.g 3036000

Functions

Signature Return type Description
connect(<database>) Connection
statement_complete(<sql>) bool
mem_current() int Current usqlite module memory usage in bytes.
mem_peak() int Peak usqlite module memory usage in bytes. Include optional bool parameter True to reset peak memory usage statistics.
mem_status(<enable>) bool Set or returns current status of memory usage monitoring. The memory usage status monitoring can be enabled or disabled via an optional bool parameter. The status can only be set on initialisation before the execution of any SQL.

Connection object

A Connection object is returned by the usqlite.connect() function.

Attributes

Name Type Access Description
row_type str R/W Get/set row data type: tuple (default), dict, or row
total_changes int R

Functions

Name Return type Description
close() None Close the connection and all cursors associated with the connection.
execute(<sql>) Cursor
executemany(<sql>) Cursor
set_trace_callback(<callback>) None

Cursor object

Attributes

Name Type Access Description
arraysize int R/W
connection Connection R
description list R
lastrowid int R
rowcount int R

Functions

Name Return type Description
close() None
execute(<sql>) self
executemany(<sql>) self
fetchone() Any
fetchmany(<size>) list
fetchall() list

Data row objects

The data type of rows returned by SQL statments is determined by the Connection object's row_type property. The default row_type is tuple.

If the row_type is dict then each row of data is returnened in as a dict object with the column value key names set to each value's respective column name.

The row_type row is a specialised type of tuple object with an the addional keys property that returns a tuple of column names.

execute function parameter substitution

usqlite has an extended range of SQL expression parameter substitution methods available.

? and ?NNN indexed parameters

Indexed parameter values are be supplied as a tuple or list

For convenience, if the SQL statment contains a single ? parameter, the parameter value can also be supplied as a single value.

e.g.

con.execute("SELECT * FROM data WHERE year > ?", 1900)

is equivalent to

con.execute("SELECT * FROM data WHERE year > ?", (1900,))

:AAAA, @AAAA and $AAAA named parameters

Named parameters are passed as a dict object. The value keys must match the parameter names.

e.g.

con.execute("SELECT * FROM data WHERE name=:name", {"name":"Larry"})
Comments
  • Build failure esp32

    Build failure esp32

    First, my apologies for the verbose post(complete build output in zip file). As you can see below the build fails for esp32. Any direction will be appreciated. Thanks, Rick

    FYI, I can build esp32 successfully without usqlite.

    Ubuntu 20.04 Micropython 1.17 ESP-IDF v4.2

    Project Structure /Home ..../Micropython ..../Modules ......../usqlite .........micropython.cmake ... ..../sqlite ....sqlite3.c ....sqlite3.h

    micropython/ports/esp32$ make submodules git submodule update --init ../../lib/berkeley-db-1.xx micropython/ports/esp32$ make clean idf.py -D MICROPY_BOARD=GENERIC -B build-GENERIC fullclean Executing action: fullclean Done micropython/ports/esp32$ make USER_C_MODULES=/home/fredrick/micropython/modules/usqlite/micropython.cmake

    [1283/1284] Linking CXX executable micropython.elf FAILED: micropython.elf

    ... build_failure.zip ...

    collect2: error: ld returned 1 exit status ninja: build stopped: subcommand failed. ninja failed with exit code 1 make: *** [Makefile:34: all] Error 2 $

    bug good first issue 
    opened by palmtreefrb 7
  • usqlite does not compile

    usqlite does not compile

    Hi! I think there is a problem with the latest edits.. could you please check? [ 98%] Building C object esp-idf/main/CMakeFiles/idf_main.dir//modules/usqlite/usqlite_vfs.c.obj /home/ltomi91/Desktop/micro/micropythonrep/micropython/ports/esp32/modules/usqlite/usqlite_file.c: In function 'usqlite_file_exists': /home/ltomi91/Desktop/micro/micropythonrep/micropython/ports/esp32/modules/usqlite/usqlite_file.c:36:19: error: implicit declaration of function 'mp_module_get'; did you mean 'mp_locals_get'? [-Werror=implicit-function-declaration] mp_obj_t os = mp_module_get(MP_QSTR_uos); ^~~~~~~~~~~~~ mp_locals_get /home/ltomi91/Desktop/micro/micropythonrep/micropython/ports/esp32/modules/usqlite/usqlite_file.c:36:19: warning: initialization of 'mp_obj_t' {aka 'void *'} from 'int' makes pointer from integer without a cast [-Wint-conversion] /home/ltomi91/Desktop/micro/micropythonrep/micropython/ports/esp32/modules/usqlite/usqlite_file.c: In function 'usqlite_file_delete': /home/ltomi91/Desktop/micro/micropythonrep/micropython/ports/esp32/modules/usqlite/usqlite_file.c:237:38: warning: passing argument 1 of 'usqlite_method' makes pointer from integer without a cast [-Wint-conversion] mp_obj_t remove = usqlite_method(mp_module_get(MP_QSTR_uos), MP_QSTR_remove); ^~~~~~~~~~~~~~~~~~~~~~~~~~ In file included from /home/ltomi91/Desktop/micro/micropythonrep/micropython/ports/esp32/modules/usqlite/usqlite.h:35, from /home/ltomi91/Desktop/micro/micropythonrep/micropython/ports/esp32/modules/usqlite/usqlite_file.c:25: /home/ltomi91/Desktop/micro/micropythonrep/micropython/ports/esp32/modules/usqlite/usqlite_utils.h:47:48: note: expected 'const mp_obj_module_t *' {aka 'const struct _mp_obj_module_t *'} but argument is of type 'int' mp_obj_t usqlite_method(const mp_obj_module_t *module, qstr name); ~~~~~~~~~~~~~~~~~~~~~~~^~~~~~ cc1: some warnings being treated as errors make[3]: *** [esp-idf/main/CMakeFiles/__idf_main.dir/build.make:3431: esp-idf/main/CMakeFiles/idf_main.dir//modules/usqlite/usqlite_file.c.obj] Error 1 make[3]: *** Waiting for unfinished jobs.... make[3]: Leaving directory '/home/ltomi91/Desktop/micro/micropythonrep/micropython/ports/esp32/build-GENERIC' make[2]: *** [CMakeFiles/Makefile2:4672: esp-idf/main/CMakeFiles/__idf_main.dir/all] Error 2 make[2]: Leaving directory '/home/ltomi91/Desktop/micro/micropythonrep/micropython/ports/esp32/build-GENERIC' make[1]: *** [Makefile:149: all] Error 2 make[1]: Leaving directory '/home/ltomi91/Desktop/micro/micropythonrep/micropython/ports/esp32/build-GENERIC' make failed with exit code 2 make: *** [Makefile:34: all] Error 2

    bug 
    opened by tamaslukacs 1
  • Compile error

    Compile error

    Good day

    I am following the getting started section on https://github.com/spatialdude/usqlite

    I get the following error when following the instructions on https://github.com/spatialdude/usqlite/wiki/unix

    sqlite/usqlite_module.c:127:35: error: ‘mp_obj_type_t’ {aka ‘const struct _mp_obj_type_t’} has no member named ‘make_new’

    Ubuntu 22.04

    Regards

    opened by emile-cronje 0
  • Compile for ESP32

    Compile for ESP32

    I am trying to compile for esp32 target I am getting error:

    /micropython/modules/usqlite/usqlite_file.c:113:37: error: 'mp_module_io' undeclared (first use in this function); did you mean 'mp_module_sys'?
         mp_obj_t open = usqlite_method(&mp_module_io, MP_QSTR_open);
    
    opened by Phisatho 0
  • Cannot create table with UNIQUE or PRIMARY KEY

    Cannot create table with UNIQUE or PRIMARY KEY

    The following SQL

    CREATE TABLE IF NOT EXISTS login(
      key TEXT NOT NULL PRIMARY KEY,
      name TEXT
    );
    

    Gives the error:

    usqlite_Error: malformed database schema (sqlite_autoindex_login_1) - orphan index
    

    Same happens if I try to set a column as UNIQUE.

    opened by fermuch 0
  • insert failed

    insert failed

    esp32wroom32 micropython1.8 `Guru Meditation Error: Core 0 panic'ed (LoadProhibited). Exception was unhandled. Core 0 register dump: PC : 0x4023ffe7 PS : 0x00060130 A0 : 0x80166468 A1 : 0x3ffcdf40 A2 : 0x0015122d A3 : 0x3fff0198 A4 : 0x00000005 A5 : 0x00000001 A6 : 0x00000500 A7 : 0x3ffe4f18 A8 : 0x00000001 A9 : 0x00000001 A10 : 0x00000001 A11 : 0xffeaeddc A12 : 0x00151225 A13 : 0x00000000 A14 : 0x00000001 A15 : 0x00000544 SAR : 0x00000018 EXCCAUSE: 0x0000001c EXCVADDR: 0x0015122d LBEG : 0x400014fd LEND : 0x4000150d LCOUNT : 0xfffffffc

    ELF file SHA256: 79f4c1dccd211abb

    Backtrace: 0x4023ffe4:0x3ffcdf40 0x40166465:0x3ffcdf60 0x40166fae:0x3ffce010 0x401676f5:0x3ffce060 0x40162eb8:0x3ffce080 0x4015245d:0x3ffce0a0 0x40158c4d:0x3ffce0c0 0x40158d7a:0x3ffce0e0 0x40161811:0x3ffce100 0x40161b82:0x3ffce130 0x401524a9:0x3ffce160 0x40158c4d:0x3ffce190 0x40158d4e:0x3ffce1b0 0x400d174f:0x3ffce1e0 0x400d1c3d:0x3ffce230 0x400d4529:0x3ffce280 0x401049ea:0x3ffce2d0 0x400e16de:0x3ffce320 0x400e1993:0x3ffce360 0x400e1c6b:0x3ffce3a0 0x400ec1c1:0x3ffce3e0 0x400ef11d:0x3ffce440 0x400fe7b1:0x3ffce510 0x400f841f:0x3ffcecd0 0x400f8597:0x3ffced10 0x400d0819:0x3ffced60 0x400d0e00:0x3ffced90 0x400d0773:0x3ffcede0 0x400d038f:0x3ffcee30 0x401524b6:0x3ffcee80 0x40158c4d:0x3ffceeb0 0x40158d7a:0x3ffceed0 0x40084a69:0x3ffceef0 0x4015253c:0x3ffcef90 0x40158c4d:0x3ffceff0 0x40158d7a:0x3ffcf010 0x40084a69:0x3ffcf030 0x4015253c:0x3ffcf0d0 0x40158c4d:0x3ffcf140 0x40158d7a:0x3ffcf160 0x40084a69:0x3ffcf180 0x4015253c:0x3ffcf220 0x40158c4d:0x3ffcf290 0x40158d7a:0x3ffcf2b0 0x40084a69:0x3ffcf2d0 0x4015253c:0x3ffcf370 0x40158c4d:0x3ffcf3d0 0x40158c76:0x3ffcf3f0 0x401810ff:0x3ffcf410 0x401814bd:0x3ffcf4a0 0x40181525:0x3ffcf4c0 0x4016a360:0x3ffcf4e0`

    opened by tljk 1
  • not work on micropython 8266

    not work on micropython 8266

    xtensa-lx106-elf-ld: build-GENERIC/firmware.elf section `.text' will not fit in region `iram1_0_seg'
    xtensa-lx106-elf-ld: region `iram1_0_seg' overflowed by 398353 bytes
    build-GENERIC/usqlite/usqlite_cursor.o:(.text.usqlite_cursor_execute+0x44): undefined reference to `atoi'
    build-GENERIC/usqlite/usqlite_cursor.o: In function `usqlite_cursor_execute':
    usqlite_cursor.c:(.text.usqlite_cursor_execute+0x22c): undefined reference to `atoi'
    build-GENERIC/usqlite/usqlite.o: In function `sqlite3ShadowTableName':
    usqlite.c:(.text.sqlite3ShadowTableName+0x13): undefined reference to `strrchr'
    build-GENERIC/usqlite/usqlite_file.o:(.text.usqlite_file_exists+0xc): undefined reference to `strrchr'
    build-GENERIC/usqlite/usqlite_file.o: In function `usqlite_file_exists':
    usqlite_file.c:(.text.usqlite_file_exists+0x42): undefined reference to `strrchr'
    usqlite_file.c:(.text.usqlite_file_exists+0x5a): undefined reference to `strrchr'
    make: *** [Makefile:223: build-GENERIC/firmware.elf] Error 1
    
    
    opened by AiPacino 0
Owner
Elvin Slavik
Elvin Slavik
upstream module that allows nginx to communicate directly with PostgreSQL database.

About ngx_postgres is an upstream module that allows nginx to communicate directly with PostgreSQL database. Configuration directives postgres_server

RekGRpth 1 Apr 29, 2022
Simple constant key/value storage library, for read-heavy systems with infrequent large bulk inserts.

Sparkey is a simple constant key/value storage library. It is mostly suited for read heavy systems with infrequent large bulk inserts. It includes bot

Spotify 986 Nov 27, 2022
❤️ SQLite ORM light header only library for modern C++

SQLite ORM SQLite ORM light header only library for modern C++ Status Branch Travis Appveyor master dev Advantages No raw string queries Intuitive syn

Yevgeniy Zakharov 1.7k Nov 29, 2022
A type safe SQL template library for C++

sqlpp11 A type safe embedded domain specific language for SQL queries and results in C++ Documentation is found in the wiki So what is this about? SQL

Roland Bock 2.1k Nov 29, 2022
Kreon is a key-value store library optimized for flash-based storage

Kreon is a key-value store library optimized for flash-based storage, where CPU overhead and I/O amplification are more significant bottlenecks compared to I/O randomness.

Computer Architecture and VLSI Systems (CARV) Laboratory 24 Jul 14, 2022
SOCI - The C++ Database Access Library

Originally, SOCI was developed by Maciej Sobczak at CERN as abstraction layer for Oracle, a Simple Oracle Call Interface. Later, several database backends have been developed for SOCI, thus the long name has lost its practicality. Currently, if you like, SOCI may stand for Simple Open (Database) Call Interface or something similar.

SOCI 1.2k Nov 24, 2022
Velox is a new C++ vectorized database acceleration library aimed to optimizing query engines and data processing systems.

Velox is a C++ database acceleration library which provides reusable, extensible, and high-performance data processing components

Facebook Incubator 1.8k Nov 27, 2022
The fastest database-library on Android OS.

Android SQLite3 NDK 封装 Demo下载 (操作:按钮新增 按钮查询 点按编辑 长按删除) 写在前面 sqlite3 开源、集成简单(现在的版本只有2个文件 sqlite3.h sqlite3.c) 这个库抽离自 Telegram 的开源代码、作者:DrKLO 我个人感觉 Tele

水银灯、 2 Dec 27, 2021
A lightweight header-only C++11 library for quick and easy SQL querying with QtSql classes.

EasyQtSql EasyQtSql is a lightweight header-only C++11 library for quick and easy SQL querying with QtSql classes. Features: Header only C++11 library

null 51 Nov 17, 2022
LevelDB is a fast key-value storage library written at Google that provides an ordered mapping from string keys to string values.

LevelDB is a fast key-value storage library written at Google that provides an ordered mapping from string keys to string values. Authors: Sanjay Ghem

Google 31.3k Nov 24, 2022
C++11 wrapper for the LMDB embedded B+ tree database library.

lmdb++: a C++11 wrapper for LMDB This is a comprehensive C++ wrapper for the LMDB embedded database library, offering both an error-checked procedural

D.R.Y. C++ 260 Nov 14, 2022
A friendly and lightweight C++ database library for MySQL, PostgreSQL, SQLite and ODBC.

QTL QTL is a C ++ library for accessing SQL databases and currently supports MySQL, SQLite, PostgreSQL and ODBC. QTL is a lightweight library that con

null 167 Nov 22, 2022
redis-cpp is a header-only library in C++17 for Redis (and C++11 backport)

redis-cpp - lightweight C++ client library for Redis redis-cpp is a C++17 library for executing Redis commands with support for pipelines and the publ

null 76 Nov 22, 2022
C++ client library for PostgreSQL

Welcome to taoPQ taoPQ is a lightweight C++ client library for accessing a PostgreSQL➚ database. It has no dependencies beyond libpq➚, the C applicati

The Art of C++ 231 Nov 30, 2022
Trilogy is a client library for MySQL-compatible database servers, designed for performance, flexibility, and ease of embedding.

Trilogy is a client library for MySQL-compatible database servers, designed for performance, flexibility, and ease of embedding.

GitHub 475 Nov 15, 2022
Tntdb is a c++-class-library for easy access to databases

Tntdb is a c++-class-library for easy access to databases

Tommi Mäkitalo 31 Aug 1, 2022
The C++14 wrapper around sqlite library

sqlite modern cpp wrapper This library is a lightweight modern wrapper around sqlite C api . #include<iostream> #include <sqlite_modern_cpp.h> using n

null 713 Nov 12, 2022
dqlite is a C library that implements an embeddable and replicated SQL database engine with high-availability and automatic failover

dqlite dqlite is a C library that implements an embeddable and replicated SQL database engine with high-availability and automatic failover. The acron

Canonical 3.2k Nov 23, 2022
USB to interfaces implementing MicroPython "machine" module functionalities on a computer.

u2if project u2if(USB to interfaces) is an attempt to implement some of the MicroPython "machine" module functionalities on a computer. The goal is to

null 98 Nov 14, 2022
MicroPython - a lean and efficient Python implementation for microcontrollers and constrained systems

The MicroPython project This is the MicroPython project, which aims to put an implementation of Python 3.x on microcontrollers and small embedded syst

MicroPython 15.5k Nov 24, 2022