Как записать данные из Google Sheets в вашу базу данных с помощью Python

Представьте себе: вы находитесь в процессе сбора источников данных для создания нового отчета и понимаете, что некоторые наборы данных все еще обновляются вручную вашими заинтересованными сторонами и хранятся в таблицах Google… звучит знакомо?

В этом случае у вас есть два варианта: либо вы запустите ускоренный курс, чтобы научить своих менее технических коллег работе с SQL и хранилищами данных, либо вы сами автоматизируете процесс с помощью Python.

В этом руководстве вы узнаете, как извлекать наборы данных из электронной таблицы Google с помощью Python, подключившись к API Google Диска, а затем сохранить их в таблице базы данных с помощью пакета SQLAlchemy.

Взаимодействие с Google Drive API

Чтобы извлечь данные из электронной таблицы Google без использования пользовательского интерфейса, вам необходимо вызвать API Google Диска через Python.

Есть много способов добиться этого, но шаги, описанные ниже, скорее всего, потребуют меньше времени и кода:

1. Перейдите в консоль Google API и создайте новый проект. Все API, которые вы включите с этого момента, станут частью этого проекта. При необходимости вы, конечно, можете создать несколько проектов или отредактировать текущий.
2. Теперь на верхней панели выберите «Включить API и службы» (Enable APIs and Services), затем найдите API Google Диска и API Google Таблиц. Оба они должны быть включены.
3. Если вы включили API Google Диска последним, оставайтесь в соответствующем разделе и выберите «Создать учетные данные» (Create Credentials). Кроме того, вы можете найти список включенных API в нижней части основной панели инструментов проекта.
4. На экране «Добавить учетные данные в свой проект» (Add Credentials To Your Project) просто выберите те же параметры, что и на снимке экрана ниже. Например, будьте осторожны при выборе «Other Non-UI», поскольку вы будете взаимодействовать с API через Python.
5. На последнем этапе создайте учетную запись службы, чтобы сделать возможным взаимодействие с API, и выберите «Владелец» (Owner) в качестве роли. Затем вам будет разрешено загрузить учетные данные в формате JSON.

6. Откройте загруженный файл и скопируйте адрес электронной почты клиента. Затем перейдите к электронной таблице, которую вы хотите прочитать с помощью Python, нажмите «Файл > Поделиться» и вставьте электронное адрес, чтобы предоставить доступ пользователю сервиса.

7. * Необязательно: переместите файл с учетными данными в ту же папку, где будет размещен ваш блокнот / скрипт Python. Этот шаг не является обязательным, но облегчит вам жизнь при получении учетных данных.

Вот и все со скучными шагами! Также хорошей новостью является то, что вам нужно выполнить эти шаги только один раз, после чего вы можете продолжать использовать один и тот же адрес электронной почты клиента для доступа к любому количеству электронных таблиц. Теперь напишем код.

Чтение данных из Google Sheets

Вам необходимо загрузить и импортировать следующие пакеты:

import gspread
from oauth2client.service_account import ServiceAccountCredentials as sac
import pandas as pd
import sqlalchemy as sa

Затем вы можете просто использовать следующую функцию для извлечения данных из электронной таблицы:

def gsheet2df(spreadsheet_name, sheet_num):
    scope = ['https://spreadsheets.google.com/feeds','https://www.googleapis.com/auth/drive']
    credentials_path = 'tutorial-sa-b04b423afd77.json'
    
    credentials = sac.from_json_keyfile_name(credentials_path, scope)
    client = gspread.authorize(credentials)
    
    sheet = client.open(spreadsheet_name).get_worksheet(sheet_num).get_all_records()
    df =  pd.DataFrame.from_dict(sheet)
    
    return df.head() 

Что делает эта функция:

1. В первых двух строках мы определяем объем вашего вызова, который в данном случае должен взаимодействовать с Google Диском, и указывает путь к файлу с учетными данными, загруженными ранее. В этом случае предполагается, что файл был перемещен в ту же папку, что и наш код.
2. В следующих двух строках мы извлекаем учетные данные по указанному пути и используем их для авторизации доступа к электронным таблицам Google через Python.
3. В последних двух строках авторизованный клиент используется для открытия электронной таблицы, выбора определенного листа и извлечения всех записей в виде словаря. В конце концов словарь преобразуется в DataFrame.

Функция принимает два аргумента (spreadsheet_name и sheet_num). Ниже он был протестирован на электронной таблице с именем Retail_Transactions, которая включает только один лист:

gsheet2df(‘Retail_Transactions’, 0)

Помните, что в Python индексация начинается с нуля, поэтому для выбора первого листа в электронной таблице вам необходимо установить sheet_num = 0. Запустив функцию, мы получим следующий результат:

Теперь вы можете применить все необходимые преобразования к этому df, прежде чем записывать его в целевую таблицу.

Запись данных в таблицу базы данных

Предположим, что после некоторых манипуляций вы, наконец, можете записать эти данные в таблицу хранилища данных, чтобы использовать их как часть вашего решения для создания отчетов. В этом руководстве показано, как подключиться к базе данных Redshift с помощью пакета SQLAlchemy, но тот же код применяется к другим базам данных (вам просто нужно немного изменить строку подключения).

В частности, вы должны использовать приведенный ниже фрагмент для создания строки подключения, а также для установления соединения с Redshift:

#### INPUT YOUR CREDENTIALS ###
DATABASE = "database_name"
USER = "your_user_name"
PASSWORD = "your_pw"
HOST = "company_name.eu-west-1.redshift.amazonaws.com"
PORT = "5439"  

### CREATE A CONNECTION TO REDSHIFT DB
connection_string = "postgresql+psycopg2://%s:%s@%s:%s/%s" % (USER,PASSWORD,HOST,str(PORT),DATABASE)
engine = sa.create_engine(connection_string)
connection = engine.connect()

Как только соединение установлено, вы можете создать таблицу в предпочтительной схеме, используя метод pandas .to_sql:

 fx_revenues.to_sql('table_name', con = engine, if_exists = 'replace', index= False, schema = "schema_name")

connection.execute('grant select on schema_name.table_name to your_username;')

Также не забудьте предоставить доступ к таблице вашему пользователю БД, чтобы вы могли запрашивать данные с помощью SQL.

Теперь весь сценарий можно запланировать с помощью Airflow или периодически запускать с помощью альтернативного инструмента планирования, чтобы полностью автоматизировать процесс!