CODEWITHSUNDEEP
pythonsqlalchemy
Tinker
Tinker

Reputation: 4525

How to implement "History" for a database object?

I am currently using SQLAlchemy with Python on Postgres

I am interested to implement a "history" attribute for a model. This means that whenever a database row is modified, this change in value will be recorded somewhere.

For example,

I have a model Dog. Dog has two attributes: weight and owner.

Day 1: Dog is created, weight=5, owner=None

Day 2: Dog.weight=4

Day 3: Dog.owner=me

This will result in a history as such: 

[ [1, weight=5, owner=None], 
  [2, weight=4, owner=None], 
  [3, weight=4, owner=me] ]
  1. Is there any existing implementation in Sqlalchemy that does this automatically?
  2. If not, how should I go about implementing this elegantly?

Upvotes: 0

Views: 1281

Answers (1)

zebo zhuang
zebo zhuang

Reputation: 636

Actually, I do not think Postgresql has this feature to store history of a table though I do not use Postgresql but it's similar to MySQL.You can use two tables to implement your feature. Here is an example(just an example that uses MySQL syntax):

Table 1: 
CREATE TABLE `dog_now`(
    `id` int(11) AUTO_INCREMENT ,
    `dog_id` int(11) DEAFULT '0',   -- dog id
    `weight` float DEFAULT '0',
    `owner` varchar(32) DEFAULT '',
    PRIMARY KEY(`id`),
    UNIQUE KEY `idx_dog_id`(`dog_id`)
)

Table 1 is used to store the current state of a dog.

Table 2:
CREATE TABLE `dog_history`(
   `id` int(11) AUTO_INCREMENT,
   `dog_id` int(11) DEAFULT '0',   -- dog id
   `weight` float DEFAULT '0',
   `owner` varchar(32) DEFAULT '',
   PRIMARY KEY (`id`),
   KEY `idx_dog_id`(`dog_id`)
)

Table 2 is used to store the history of a dog.

Now maybe you want to know how to store the data. When you want to save the new dog data in database, please query the dog_now table to achieve the data by dog_id. And put the data into dog_history table, update new dog data in dog_now table.

Upvotes: 1

Related Questions