Как перенести данные из Google Sheets в базу PostgreSQL?

Question

Предположим имеется таблица студентов в Google Sheets с такими данными:

№   ФИО   Курс   Пол ...
1   Иван   1     М
2   Петя   2     М
3   Оля    3     Ж
................................

Как правильно перенести эти данные в базу Postgres, чтобы в таблице столбцы имели такой же порядок и обновлялось/удалялось при изменении google-таблицы?:

Для работы с таблицей использую Python библиотеку gspread

Пример кода:

...
import psycopg2

conn = psycopg2.connect(
    database="test", user='test', password='test',
    host='127.0.0.1', port='5432'
)
cursor = conn.cursor()

cursor.execute("DROP TABLE IF EXISTS students")

# Creating table as per requirement
sql = '''CREATE TABLE students(
   ID SERIAL,
   FULLNAME CHAR(20) NOT NULL,
   GRAD INT,
   SEX CHAR(1),
)'''

cursor.execute(sql)

postgres_insert_query = """ INSERT INTO students (ID, FULLNAME, GRAD, SEX) VALUES 
(%s,%s,%s)"""

record_to_insert = # данные из таблицы

cursor.execute(postgres_insert_query, record_to_insert)

conn.commit()
conn.close()

Answer 1

Воспользуемся Apps Script (вкладка Расширения):

При добавлении новой строки в таблицу (через gspread) в 5 (в примере данных его нет) столбец автоматически будет добавляется выпадающий список (New, Edit, Delete) по умолчанию устанавливаем New если второй столбец (ФИО) содержит данные:

function setStatus(e) {
 var sh = SpreadsheetApp.getActiveSheet();
 var row = sh.getActiveRange().getRow();

 if (e.changeType === 'EDIT') {
   if (sh.getRange(row, 5).isBlank() && sh.getRange(row, 2).getValue().length > 0) {
     sh.getRange(row, 5).setValue('New')
     var cell = sh.getRange(row, 5);
     var rule = SpreadsheetApp.newDataValidation().requireValueInList(['Edit', 'Delete'], true).build();
     cell.setDataValidation(rule);
   }
  }
}

При выборе другого значения из выпадающего списка получим №, ФИО, курс и пол из этой строки и выполним запрос (функция api):

function api(num, initials, course, sex) {
  UrlFetchApp.fetch('http://' + ip_address + '/api?api=' + api_key + '&number=' + num + '&initials=' + initials + '&course=' + course + '&sex=' + sex);
}


function onEdit(e) {
  var range = e.range;
  var column = range.getColumn();
  var row = range.getRow();
  var value = e.value;

  var sheet = SpreadsheetApp.openById(spreadsheetId);
  var num = sheet.getRange('A' + row).getDisplayValues();
  var initials = sheet.getRange('B' + row).getDisplayValues();
  var course = sheet.getRange('C' + row).getDisplayValues();
  var sex = sheet.getRange('D' + row).getDisplayValues();

  if (column == 5 && value == 'Edit'){
    api(num, initials, course, sex);
  }

  else if (column == 5 && value == 'Delete'){
    api(num, initials, course, sex);
  }
}

Обязательно сделайте триггер для функций setStatus и onEdit глобальными:

В окне Apps Script слева Триггеры -> Добавить триггер

для функции setStatus - Из таблицы при изменении
для функции onEdit - Из таблицы при редактировании.

Запрос (функция api) будет передавать данные во Flask:

@app.route('/api', methods=['POST', 'GET'])
def result():
    if request.args.get('api') == api_key:
        print('func_api', f'{request.args.get("num")}, {request.args.get("initials")}, '
                          f'{request.args.get("course")}, {request.args.get("sex")}')
        return 'Actum est, ilicet', 200


app.run(host='0.0.0.0')

Ну а дальше, на основе полученных данных выполняйте нужный запрос в БД.

БЛОГ НА HUSL

Как перенести данные из Google Sheets в базу PostgreSQL?

Ответы (1 шт):