Single Table Database

[pollardd]

I have a problem where I want to read, write, update, delete data records on a raspberry pi pico using Micropython.
It’s just a single table style thing.
The data record is currently a json encoded string and the first field is unique.
I’m only ever likely to have one to three records in the table at a time at the absolute most.
It doesn’t need to survive a reboot so in memory is good.
I found details of btree functions in the Micropython doco but that requires me to compile my own version of the interpreter which seems insane and with all my existing code I’m not likely to have enough space.
How should I implement a simple data table that lets me read, write update and delete records.
There must be a simple way to do this?
Thanks for any ideas and points in the right direction.
David.

[peterhinch]

For an in-memory solution why not use a Python dict?

[pollardd]

Well I had better go learn about dict then :)
Thanks Peter!

[pollardd]

I have made some progress. I have a cut down example that includes all my data fields so not to make it meaningless (foo.bar).
I have followed the dict examples from w3schools.com and come up with the following code.
From what I can tell I'll need nested dictionaries so I can have one entry for each message number where message number is the unique key field. My problem is I cant see how to make this dictionary global so I can access and update the dictionary from any anywhere. Obviously a fundamental issue but I'm new to python.

def addDict(messageNumber, srcDevice, dstDevice, timeStamp, timeUpdate, batteryPercent, gateOpen,tempValue, acknowledge, timeToLive, textMessage):
    messageDict = {
        messageNumber : {
            "SrcDevice" : srcDevice,
            "DstDevice" : dstDevice,
            "TimeStamp" : timeStamp,
            "TimeUpdate" : timeUpdate,
            "BatteryPercent" : batteryPercent,
            "GateOpen" : gateOpen,
            "TempValue" : tempValue,
            "Acknowledge" : acknowledge,
            "TimeToLive" : timeToLive,
            "TextMessage" : textMessage        
        },
    }
    print (messageDict)

# Crete the first message 
messageNumber = "1"
srcDevice = "MainGate"
dstDevice = "MainHouse"
timeStamp = "(2023, 4, 2, 10, 43, 17, 6, 92)"
timeUpdate = "False"
batteryPercent = "99.99"
gateOpen = "False"
tempValue = ""
acknowledge = "False"
timeToLive = "5"
textMessage = "Heart Beat"

addDict(messageNumber, srcDevice, dstDevice, timeStamp, timeUpdate, batteryPercent, gateOpen,tempValue, acknowledge, timeToLive, textMessage)

# Create the second message
messageNumber = "2"
srcDevice = "MainGate"
dstDevice = "MainHouse"
timeStamp = "(2023, 4, 3, 10, 43, 17, 6, 92)"
timeUpdate = "False"
batteryPercent = "99.98"
gateOpen = "False"
tempValue = ""
acknowledge = "False"
timeToLive = "5"
textMessage = "Heart Beat"

addDict(messageNumber, srcDevice, dstDevice, timeStamp, timeUpdate, batteryPercent, gateOpen,tempValue, acknowledge, timeToLive, textMessage)

[Josverl]

I cant see how to make this dictionary global so I can access and update the dictionary from any anywhere.

If you put the dict in a seperate module you can import that in any other modules.
As a module is an singleton in Python, there will be one instance.

So from module mem_db with dict table_foo
You can access via :

Import mem_db as db

for rec in db.table_foo.keys():
    print(rec) 

[davefes]

What I did was I put this at the top of my main file:

# my data dictionary
my_data_dict = OrderedDict([('Time','00:00'), ('Battery voltage', 26.5), ('PV current', 10.00), ('Load current', 4.00), ('Daily WH in',  0.00), ('Daily WH out',  0.00), ('State of charge',  90), ('Battery temperature', 20.00), ('Error', 20480)])

then passed it to get_data.py updated it then passed it to process_data.py and passed it to save_data.py

Another thing I found was that I had to use OrderedDict or I got an error.

Good luck!

[peterhinch]

I'd do something like this (reduced number of fields for ease of understanding):

message_dict = {}  # Globally available message dict
record = {"src_device": "", "dst_device": "", "time_stamp": ()}  # Record template

def add_record(msg_no, src_device, dst_device, time_stamp):
    record["src_device"] = src_device
    record["dst_device"] = dst_device
    record["time_stamp"] = time_stamp
    message_dict[msg_no] = record.copy()  # Make a copy of the populated template.

This produces the following:

>>> add_record(1, 'test', 'dest', (1,2,3))
>>> add_record(2, 'test_2', 'dest_2', (4,5,6))
>>> message_dict[1]
{'dst_device': 'dest', 'src_device': 'test', 'time_stamp': (1, 2, 3)}
>>> message_dict[2]
{'dst_device': 'dest_2', 'src_device': 'test_2', 'time_stamp': (4, 5, 6)}
>>> message_dict[1]["time_stamp"]
(1, 2, 3)

Note that, if you want message_dict to be persistent you can encode it with json and save it in a file. This can be retrieved and decoded.

The purpose of btree is to cater for the case where the database is too big to be imported into RAM: it exists in a file with individual records being accessed.

[pollardd]

Hi Peter,
I'm already using json encoding to transmit a record between two pico's but I don't need persistence at this stage :)
I understand ( i think) how btree is supposed to work but absolute overkill for what I need.

I tried your example and it worked first go. I need to add all my fields back in and figure out how to minimize copying and pasting by including some common .py files. But I think I'm on the right track now.
Thanks to all who responded but Peter gets the tick this time :)
David

[pollardd]

Hi Peter,
I’m getting bizarre results with things being shown in a funny order when I try and iterate the dictionary.
If I read things back by message number and field name (keyword) it seems to work.
But that won’t work once I start deleting messages.
I also notice you have not used the dict keyword anywhere. Maybe the use of the keyword OrderedDict has thrown me off here.
Do you think I should post what I have as a new question or continue here.
Thanks
David
Ps
Maybe I’m trying to think of dict as a two dimensional array.
Perhaps I’d be better of just using an array instead?

[peterhinch]

If you iterate a dict the order of retrieval is not guaranteed. In my simple example, the following might retrieve records in any order:

for r in message_dict:
    print(f"key: {r} message: {message_dict[r]}")

I didn't use the dict keyword: the following lines of code are equivalent

message_dict = {}
message_dict = dict()

Using a dict is more general than using arrays because arrays require that all elements be of the same type.

I suggest you learn Python basics either from a book or by following an online course on a PC. These are elements of the Python language rather than MicroPython questions.

[pollardd]

Thanks 🤠