写真Daniele Levis Pelusi 角恩Unsplash .
我々の角で非常にrecent article 私たちは自動化するのが簡単である一般的なタスクを議論したい.
Googleシートからデータをエクスポートし、MySQLに挿入するPythonスクリプトを書く方法を説明します.
いつ使いますか.
これは、Googleフォームを使用してフォームの並べ替えを作成すると、毎晩新しいデータを自動的にロードするときに便利です.これはGoogleシートにGoogleフォームに接続されています.理論上、Googleシートのスキーマへの変更はありません.
これは重要な点です.あなたがデータを抽出したいGoogleシートが常に変わっているならば、あなたはより複雑なシステムを開発する必要があります.


データスキーマがあまり変更されない限り、このタスクの自動化は非常に簡単です.これはgspread 図書館このライブラリは非常に簡単に様々なGoogle APIとの相互作用を行います.
それで、あなたのGoogleシート抽出物を自動化し始めましょう.

Googleシートからデータを抽出する

開始するには、Googleシートから直接データを抽出する機能を構築します.
まず、いくつかの設定をする必要があります.

サービスアカウント資格を設定する必要があります.そうするために、Googleプロジェクトに行く必要があります.

そこから、サービスアカウントを作成し、JSONファイルを作成できます.

また、GoogleシートAPIへのアクセスを可能にする必要があります.

すべてのセットで、今我々は抽出を開始することができます.
そうするために、我々は[open_by_url](https://gspread.readthedocs.io/en/latest/user-guide.html) . 以下のコードに示すように、正確なスプレッドシートにアクセスできます.

import gspread
import mysql.connector
from oauth2client.service_account import ServiceAccountCredentials

# This is the mysqlcredentials.py file containing your credentials.
import mysqlcredentials as mc

# The required variables for gspread:
scope = ['https://spreadsheets.google.com/feeds', \
       'https://www.googleapis.com/auth/drive']

# The credentials created for the service account in your Google project
# is stored in a .json file after you click 'Create Key'
# I renamed this file to sheetstodb.json.
creds = ServiceAccountCredentials.from_json_keyfile_name('creds.json', scope)
client = gspread.authorize(creds)

def GetSpreadsheetData(sheetName, worksheetIndex):
   sheet = client.open_by_url(sheetName).get_worksheet(worksheetIndex)
   return sheet.get_all_values()[1:]

我々のGetSpreadsheetData 関数は、Googleシートからデータを返します.基本的に、配列の配列を取り戻します.
次のようになります.

#Array of arrays
x = [
["username1","2020-01-01","A","B","D","C"],
["username2","2020-01-01","A","B","D","C"],
["username3","2020-01-01","A","B","D","C"]]

これは、このデータセットを操作し、MySQLに挿入するために動作しているので注意してください.

MySQLへの挿入とヌルの保存

今、私たちは、GoogleシートからPythonを使ってデータを返す関数を持っています.
その前に、Googleシートのすべての空の値を'' コントゥーNone .
これは主要な問題を行の下に引き起こす重要な区別です.我々は正確な詳細に行くつもりはないが、長い話は短いNull ○は1と等しくない'' .
この場合、関数preserveNullValues . これはネストされたループを使って全ての'' .
技術的には、次のようなものを使ってこのタイトな記述もできます.
[' None 'はDでVの他のVでない場合)
しかし、我々はしばしば混乱することができますので、我々は機能が何かの明確な理解があったことを確認したいと思います.
さらに、私たちは、MySQLに挿入する関数をWriteToMysqlTable .
まず、パスワード、ホスト、ユーザ名、およびデータベース名を含む別のファイルがあります.これを使用して、接続を作成します.
この接続は、insert 声明.
この場合、スクリプトはハードインサートをコード化します.しかし、おそらく設定を設定して、変数として渡したり、動的に作成したりする必要があります.
一旦我々が1度走ることに注意することも重要ですinsert 私たちは電話をかける必要がありますcursor.commit() そして、データが実際に挿入されることを確認します.
それで、我々はほとんど完了です.今、我々は一緒にすべてのこれらの機能を一緒にする必要があります.

# Replaces any empty cells with 'NULL'
def preserveNULLValues(listName):
   print('Preserving NULL values...')
   for x in range(len(listName)):
       for y in range(len(listName[x])):
           if listName[x][y] == '':
               listName[x][y] = None
   print('NULL values preserved.')

def WriteToMySQLTable(sql_data, tableName):
   try:
# Connection credentials for MySQL.
       connection = mysql.connector.connect(
       user = mc.user,
       password = mc.password,
       host = mc.host,
       database = mc.database
       )

       sql_insert_statement = """INSERT INTO {}(
           Username,
           Date_Taken,
           Time_Started,
           Time_Finished,
           Answer_Question_1,
           Answer_Question_2,
           Answer_Question_3,
           Answer_Question_4,
           Answer_Question_5,
           Answer_Question_6 )
           VALUES ( %s,%s,%s,%s,%s,%s,%s,%s,%s,%s )""".format(tableName)

       cursor = connection.cursor()
       for i in sql_data:
           print(i)
           #print(sql_insert_statement)
           cursor.execute(sql_insert_statement, i)
# Now we execute the commit statement, and print to the console
# that the table was updated successfully
       connection.commit()
       print("Table {} successfully updated.".format(tableName))
# Errors are handled in the except block, and we will get
# the information printed to the console if there is an error
   except mysql.connector.Error as error :
       print("Error: {}. Table {} not updated!".format(error, tableName))
       connection.rollback()
       print("Error: {}. Table {} not updated!".format(error, tableName))
# We need to close the cursor and the connection,
# and this needs to be done regardless of what happened above.

Pythonスクリプトの作成

さて、すべてのこれらの機能を持っているので、我々はすべてを一緒に置く必要があります.
下記のコードを見ると、GoogleシートのURLを提供するので、データを引き出すために、Googleのシートに対して動的にこれを使用することができます.

import gspread
import mysql.connector
from oauth2client.service_account import ServiceAccountCredentials

# This is the mysqlcredentials.py file containing your credentials.
import mysqlcredentials as mc

# The required variables for gspread:
scope = ['https://spreadsheets.google.com/feeds', \
       'https://www.googleapis.com/auth/drive']

# The credentials created for the service account in your Google project
# is stored in a .json file after you click 'Create Key'
# I renamed this file to sheetstodb.json.
creds = ServiceAccountCredentials.from_json_keyfile_name('creds.json', scope)
client = gspread.authorize(creds)

# Now that that's done, pull data from the Google sheet.
# 'sheetName' describes the Google sheet's name,
# 'worksheetIndex' describes the index of the worksheet at the bottom.
def GetSpreadsheetData(sheetName, worksheetIndex):
   sheet = client.open_by_url(sheetName).get_worksheet(worksheetIndex)
   return sheet.get_all_values()[1:]

# Finally, write this data to MySQL:
def WriteToMySQLTable(sql_data, tableName):
   try:
# Connection credentials for MySQL.
       connection = mysql.connector.connect(
       user = mc.user,
       password = mc.password,
       host = mc.host,
       database = mc.database
       )
       sql_drop = " DROP TABLE IF EXISTS {} ".format(tableName)
               sql_create_table = """CREATE TABLE {}(
           Username VARCHAR(255),
           Date_Taken VARCHAR(16),
           Time_Started VARCHAR(16),
           Time_Finished VARCHAR(16),
           Answer_Question_1 VARCHAR(100),
           Answer_Question_2 VARCHAR(100),
           Answer_Question_3 VARCHAR(100),
           Answer_Question_4 VARCHAR(100),
           Answer_Question_5 VARCHAR(100),
           Answer_Question_6 VARCHAR(10),
           PRIMARY KEY (Username)
           )""".format(tableName)

       sql_insert_statement = """INSERT INTO {}(
           Username,
           Date_Taken,
           Time_Started,
           Time_Finished,
           Answer_Question_1,
           Answer_Question_2,
           Answer_Question_3,
           Answer_Question_4,
           Answer_Question_5,
           Answer_Question_6 )
           VALUES ( %s,%s,%s,%s,%s,%s,%s,%s,%s,%s )""".format(tableName)
# Here we create a cursor, which we will use to execute
# the MySQL statements above. After each statement is executed,
# a message will be printed to the console if the execution was successful.
       cursor = connection.cursor()
       cursor.execute(sql_drop)
       print('Table {} has been dropped'.format(tableName))
       cursor.execute(sql_create_table)


           print('Table {} has been created'.format(tableName))
# We need to write each row of data to the table, so we use a for loop
# that will insert each row of data one at a time
       print(sql_data)
       for i in sql_data:
           print(i)
           #print(sql_insert_statement)
           cursor.execute(sql_insert_statement, i)
# Now we execute the commit statement, and print to the console
# that the table was updated successfully
       connection.commit()
       print("Table {} successfully updated.".format(tableName))
# Errors are handled in the except block, and we will get
# the information printed to the console if there is an error
   except mysql.connector.Error as error :
       print("Error: {}. Table {} not updated!".format(error, tableName))
       connection.rollback()
       print("Error: {}. Table {} not updated!".format(error, tableName))
# We need to close the cursor and the connection,
# and this needs to be done regardless of what happened above.


# Replaces any empty cells with 'NULL'
def preserveNULLValues(listName):
   print('Preserving NULL values...')
   for x in range(len(listName)):
       for y in range(len(listName[x])):
           if listName[x][y] == '':
               listName[x][y] = None
   print('NULL values preserved.')

# Uses Google Drive's API.
# If you get an error regarding this, go to the link and enable it.
data = GetSpreadsheetData(mc.url, 0)

# Write to the table in the database.
preserveNULLValues(data)
WriteToMySQLTable(data, 'MyData')

一旦データが引かれるならば、我々はpreserveNullValues - 1WriteToMysqlTable .
それだ!
実際には、ほとんどの作業はgスプレッドライブラリで行われます.一旦データが抽出されて、配列で、それはデータのまわりで動くのがかなり簡単です.
それはPythonを偉大にするものです.あなたがしなければならないことの多くはどこかのライブラリに存在します.

あなたのGoogleシート抽出を自動開始する時間

このスクリプトがあなたを助けることを願っていますautomate your Google Sheet extracts  Pythonと同様に自動化することができます他のタスクを探す.
上の例では、このスクリプトを手動でスケジューリングします.しかし、あなたが将来的にこれをより効果的に自動化したいならyou could use a framework like Airflow . スクリプトを手動で実行する代わりに、日常的にこのスクリプトを実行することもできます.
何人かの人が.個人的に、我々はこれをお勧めしません.あなたの自動化されたタスクを管理することができますたくさんのフレームワークがたくさんあります.システムを構築することに対するより多くの時間を費やすのは、しばしばよりよくあります.
しかし、自動化することを決定すると、我々は幸運と幸せ自動化を願って!
あなたがこの記事を楽しむならば、同様にこれらの記事のいくつかを読んで検討してください.
Advanced SQL For Data Analysts, Data Scientists And Engineers
Airbnb's Airflow Vs. Spotify's Luigi
How Algorithms Can Become Unethical and Biased
Top 10 Business Intelligence (BI) Implementation Tips​
5 Great Big Data Tools For The Future --- From Hadoop To Cassandra
Learning Data Science: Our Favorite Resources Free Or Not