基于postgresql的熊猫实时数据处理

Question

我在PostgreSQL中有两个表：-

1. “学生”桌

student_id name   score
1         Adam     10
2         Brian    9

1. "student_log“表：-

log_id student_id score
1         1       10
2         2       9

我有一个python脚本，它获取包含"name“和”DataFrame“列的，然后将其填充到学生表中。

每当学生的“分数”发生变化时，我想更新学生和student_log表。另外，如果在dataframe中有一个新的学生名，我想要在学生表中为它添加另一行，并在"student_log“表中维护它的记录。有人能建议怎么做吗？

让我们考虑一下新获取的DataFrame如下所示：

name score
Adam  7
Lee   5

那么预期的结果是：

“学生”桌

student_id name   score
1         Adam     7
2         Brian    9
3         Lee      5

"student_log“表：-

log_id student_id score
1         1       10
2         2       9
3         1       7    
4         3       5

Answer 1

我终于找到了一个好的答案。我用了扳机，功能和CTE。

我创建一个函数来记录更改，并创建一个触发器来处理更新。下面是代码。

CREATE OR REPLACE FUNCTION log_last_changes()
  RETURNS TRIGGER 
  LANGUAGE PLPGSQL
  AS
$$
DECLARE
serial_num integer;
BEGIN
    IF NEW.name <> OLD.name OR NEW.score <> OLD.score
    THEN
         SELECT SETVAL('log_id_seq', (select max(id) from log)) into serial_num;
     INSERT INTO log(student_id,score)
     VALUES(NEW.id,NEW.score)
         ON CONFLICT DO NOTHING;
    END IF;
  RETURN NEW;
END;
$$;

CREATE TRIGGER log_student
  AFTER UPDATE
  ON student
  FOR EACH ROW
  EXECUTE PROCEDURE log_last_changes();

CTE表达式如下：

WITH new_values(id, name, score) AS ( 
    values
        (1,'Adam',7),
        (2,'Brian',9),
        (3,'Lee',5)      
),
upsert AS 
    ( 
        UPDATE student s 
        SET NAME = nv.name,
        SCORE = nv.score
        FROM new_values nv, student s2
        WHERE 
            s.id = nv.id and s.id = s2.id 
        Returning s.* 
    )
    INSERT INTO student select id, name, score 
    FROM 
        new_values 
    WHERE NOT EXISTS (
            SELECT 1  from upsert up where up.id=new_values.id
    );

Answer 2

我想你试着区分两个数据

下面是一个例子

#old student dataframe
old_pd:pd.DataFrame

#new student dataframe
new_pd:pd.DataFrame

joined_pd = new_pd.join(old_pd,on='name',lsuffix='_new',rsuffix='_old')
diff_pd = joined_pd[joined_pd['score_new']!=joined_pd['score_old']]

#then insert all diff_pd to student_log table.and update to student table