And PostgreSQL would be a better one if you care about your data :)SN wrote:MySQL would be the obvious choice
Depends a bit on what exactly you mean by that. The aforementioned MySQL is an external software, a server, that of course can run on the Pi beside your app. What people sometimes get wrong is the word "server" which just stands for a system waiting for requests and answering to them. It's in fact always a software that does the job as a computer without software does nothing at all. Commonly machines dedicated to running such software are called servers, too. But that does not mean, that you have to have a dedicated machine as a database server for your app. Most web server setups run web, database and often mail servers on one and the same machine.gjs wrote:Runs locally on the RPi as part of the application, not on a server
Just delete the oldest row in your table when you consider it is "full" and you are about to insert a new row. You could use a serial number or a timestamp on your rows for that.gjs wrote:I also want to log the data on a round-robin basis so that it never gets full i.e. oldest data is overwritten when a preset size is reached. Does SQLite or the Python bindings have any built in mechanism for this?
Pirx-Danford wrote:I would go for SQLite on the Pi, because getting Postgres or MySQL running would require a massive amount of learning and tweaking the Pi.
So it depends on your usage ....BDB is twice the size of SQLite. A comparison between BDB and SQLite is similar to a comparison between assembly language and a dynamic language like Python or Tcl. BDB is probably much faster if you code it carefully. But it is much more difficult to use and considerably less flexible.
Code: Select all
import sqlite3 NSLOTS=10 def create_tables(dbname): db=sqlite3.connect(dbname) db.execute("CREATE TABLE log (slot INT PRIMARY KEY, data TEXT)") db.execute("CREATE TABLE metadata (metakey TEXT, metavalue INT)") db.execute("INSERT INTO metadata VALUES('nextslot',0)") db.commit() db.close() def addlogmessage(db, slot, msg): 'Add a message to the log in the given slot.' # slot is a value between 0 and NSLOTS db.execute('BEGIN TRANSACTION') db.execute('INSERT OR REPLACE INTO log VALUES(?,?)', (slot,msg)) nextslot = (slot+1) % NSLOTS db.execute("UPDATE metadata SET metavalue=? WHERE metakey='nextslot'", (nextslot,)) db.commit() return nextslot def appendlogmessage(db, msg): 'Add a message to the log in the next slot.' # get next slot cur = db.cursor() cur.execute("SELECT metavalue FROM metadata WHERE metakey='nextslot'") nextslot = cur.fetchone() # add the message at that slot location return addlogmessage(db, nextslot, msg)