python实现notion导入bi

太生气

已于 2022-03-28 21:15:42 修改

阅读量1.6k

点赞数

分类专栏： python 文章标签： python

于 2022-03-24 12:20:42 首次发布

本文链接：https://blog.youkuaiyun.com/qq_19757081/article/details/123707817

版权

本文介绍了一个Python脚本（notion/notion_to_bi.py），用于将Notion平台的数据按照指定模式（merge、append、overwrite）导入到Greenplum数据库。脚本需要Notion的accesstoken、database ID、数据库连接参数（如用户名、密码、主机、端口、数据库名和模式名）以及白名单列名。merge模式保留已有数据，overwrite模式保持一致，append模式仅添加新数据。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

#!/usr/bin/env python
#coding=utf-8
import subprocess
import json
import os
import psycopg2
import re
import sys
import psycopg2.extras
import requests
import threading
import time
import sys
import re
import copy


def if_database(base_url,databaseID,token):
    url = base_url + "databases/" + databaseID
    headers = {
        "Accept": "application/json",
        "Notion-Version": "2022-02-22",
        "Authorization": "Bearer %s" %token
    }
    response = requests.request("GET", url, headers=headers)
    return response.status_code


def get_page(base_url,databaseID,token):
    url = base_url + "databases/" + databaseID + "/query"

    payload = {"page_size": 100}
    headers = {
        "Accept": "application/json",
        "Notion-Version": "2022-02-22",
        "Content-Type": "application/json",
        "Authorization": "Bearer %s" %token
    }

    total_list = []
    response = requests.request("POST", url, json=payload, headers=headers)
    total_list.extend(response.json()["results"])
    while True:
        if response.json()["has_more"] == True:
            payload["start_cursor"] = response.json()["next_cursor"]
            response = requests.request("POST", url, json=payload, headers=headers)
            total_list.extend(response.json()["results"])
        else:
            break
    print(len(total_list))
    notion_list = []
    for i in total_list:
        i["properties"]["pageid"] = {}
        i["properties"]["pageid"]["pageid"] = i["id"]
        i["properties"]["pageid"]["type"] = "pageid"
        notion_list.append(i["properties"])

    return notion_list

class Pq_opration():
    def __init__(self, conn, cursor):
        self.conn = conn
        self.cursor = cursor

    def select(self, select_sth, from_sth, where_sth=None):
        self.cursor.execute("select %s from %s %s" % (select_sth, from_sth, where_sth))
        rows = self.cursor.fetchall()
        return rows

    def create(self, db_table_total, db_sql):
        self.cursor.execute("create table %s %s" % (db_table_total, db_sql))
        self.conn.commit()

    def insert(self, _dict, db_table_total):
        if len(list(_dict.keys())) == 1:
            self.cursor.execute('''insert into ''' + db_table_total + '(' + ','.join(
                list(_dict.keys())) + ''') values ''' + '(' + '\'' + list(_dict.values())[0] + '\'' + ')')
        else:
            self.cursor.execute(
                '''insert into ''' + db_table_total + '(' + ','.join(list(_dict.keys())) + ''') values ''' + str(
                    tuple(_dict.values())))
        self.conn.commit()

    def delete(self, main_key, value, db_table_total):
        self.cursor.execute('''delete from ''' + db_table_total + ' where ' + main_key + ' = ' + '\'' + value + '\'')
        self.conn.commit()

    def merge(self, list1, list2, db_table_total,main_key="pageid"):
        count = 0
        num = 0
        for i in list1:
            tag = 0
            for j in list2:

                if main_key in list(i.keys()) and i != {} and j != {} and i[main_key] == j[main_key.lower()]:
                    sql = self.dict_to_sql(i, main_key)
                    self.cursor.execute(
                        '''update ''' + db_table_total + ' set ' + sql + ' where ' + main