Python.sqlite3.row_factory.tuple.sqlite3row.namedtuple.dataclass.getitem

Thin wrapper that returns SQLite3 select result as tuple, sqlite3.Row, namedtuple, dataclass.

Requirement

2022-10-17
Raspbierry Pi 4 Model B Rev 1.2
Raspberry Pi OS buster 10.0 2020-08-20 setup
bash 5.0.3(1)-release
Python 3.10.5

$ uname -a
Linux raspberrypi 5.10.103-v7l+ #1529 SMP Tue Mar 8 12:24:00 GMT 2022 armv7l GNU/Linux

Installation

anyenv

git clone https://github.com/anyenv/anyenv ~/.anyenv
echo 'export PATH="$HOME/.anyenv/bin:$PATH"' >> ~/.bash_profile
echo 'eval "$(anyenv init -)"' >> ~/.bash_profile
anyenv install --init -y

pyenv

anyenv install pyenv
exec $SHELL -l

python

sudo apt install -y libsqlite3-dev libbz2-dev libncurses5-dev libgdbm-dev liblzma-dev libssl-dev tcl-dev tk-dev libreadline-dev

pyenv install -l

pyenv install 3.10.5

this works

git clone https://github.com/ytyaru/Python.sqlite3.row_factory.tuple.sqlite3row.namedtuple.dataclass.__getitem__.20221017181058
cd Python.sqlite3.row_factory.tuple.sqlite3row.namedtuple.dataclass.__getitem__.20221017181058/src

Usage

unit test

cd Python.sqlite3.row_factory.namedtuple.__getitem__.20221016095117/src
./test-ntlite.py

import

from ntlite import NtLite

new

db = NtLite() # :memory:

db = NtLite('./db/my.sqlite3')

db = NtLite('./db/my.sqlite3', RowTypes.dataclass)

db = NtLite(path='./db/my.sqlite3', row_type=RowTypes.dataclass)

RowTypes

RowTypes	column reference
`RowTypes.tuple`	`row[0]`
`RowTypes.sqlite3`(sqlite3.Row)	`row[0]`, `row['col_name']`
`RowTypes.namedtuple`	`row[0]`, `row.col_name`, `row['col_name']`
`RowTypes.dataclass`	`row[0]`, `row.col_name`, `row['col_name']`

By specifying RowTypes it is possible to specify the types of rows returned from the DB table. Implemented with row_factory.

RowTypes params

The following two have parameters.

RowTypes.namedtuple(not_getitem=True)

RowTypes.namedtuple(not_getitem=True, not_slot=True, not_frozen=True)

parameter	`namedtuple`	`dataclass`
`not_getitem`=`True`	Cannot be referenced by `['col_name']`	Cannot be referenced by `[0]` or `['col_name']`
`not_slots`=`True`	-	Cannot register new properties
`not_frozen`=`True`	-	Become mutable (be able to set values to properties)

All default to False.

When generating NtLite pass it as follows.

db = NtLite(row_type=RowTypes.namedtuple(not_getitem=True))
db = NtLite(row_type=RowTypes.dataclass(not_getitem=True, not_slots=True, not_frozen=True))

API

method	call sqlite3 method
`exec`	execute
`execm`	executemany
`execs`	executescript
`get`	execute + fetchone
`gets`	execute + fetchall

reference column

row = db.get("select id, name num from users where id=5;")
row.id    #=> 5
row['id'] #=> 5
row[0]    #=> 5

RowTypes	column reference
`RowTypes.tuple`	`row[0]`
`RowTypes.sqlite3row`(sqlite3.Row)	`row[0]`, `row['col_name']`
`RowTypes.namedtuple`	`row[0]`, `row.col_name`, `row['col_name']`
`RowTypes.dataclass`	`row[0]`, `row.col_name`, `row['col_name']`

example

#!/usr/bin/env python3
# coding: utf8
import os
from ntlite import NtLite
path = 'my.db'
if os.path.isfile(path): os.remove(path)
db = NtLite(path)
db.exec("create table users(id integer, name text);")
db.execm("insert into users values(?,?);", [(0,'A'),(1,'B')])
assert 2 == db.get("select count(*) num from users;").num
rows = db.gets("select * from users;")
assert 0   == rows[0].id
assert 'A' == rows[0].name
assert 1   == rows[1].id
assert 'B' == rows[1].name

assert 0   == rows[0]['id']
assert 'A' == rows[0]['name']
assert 1   == rows[1]['id']
assert 'B' == rows[1]['name']

assert 0   == rows[0][0]
assert 'A' == rows[0][1]
assert 1   == rows[1][0]
assert 'B' == rows[1][1]

Author

ytyaru

License

This software is CC0 licensed.