コード一覧

【Python】データベースからデータを引っ張ってきてスプレッドシートに入れる方法

  /

データベース（mysql)からデータを引っ張ってきて、スプレッドシートに入れます。

・動機としては、知りたい数字があるけど、社内で使っているCRMは改修にお金がかかるから易々と変更できない。
・ベンチマークとかを使って、クエリで呼び出したデータをコピペしてスプレッドシートに貼り付けていた。でも、貼り付ける作業も毎日なので、しんどくなってきて、自動でスプレッドシートに更新されればいいのに。と思った。

完成イメージとしては、バッチファイルを作って、毎日パソコンを起動したときに、自動で数字を更新してくれたらいいな。という感覚。

今回やったステップを順番にまとめると以下。

１．貼り付け先のスプレッドシートを用意する。
２．貼り付け先は、A:AとかB:Bとか、縦一列のイメージ。
３．Pythonを使ってスプレッドシートにアクセスできるようにする。
４．Pythonを使って、データベースにアクセスできるようにする。
５．セルを一つづつ更新していたら、APIのエラーが出て更新出来ない。
６．APIエラーが出ないようにクエリの結果をA:Aに貼り付けたい。

こんな感じ。

とりあえずやりたいのが、データベースからデータを引っ張ってきてスプレッドシートに入力するということ。

まずは、pythonを使ってスプレッドシートにアクセルする方法。
これは、調べても山のように出てくるから、「python スプレッドシート　連携」とかで調べたらすぐにわかると思う。

次に、データベースの件。「import mysql.connector」で調べたら出てくる。

次に、セルを一つづつ更新したら、エラーが出る件。
100秒以内に100回だったかな？APIを呼び出すとエラーが出る。
エラーは以下のようなエラー内容

raise APIError(response)
gspread.exceptions.APIError: {
  "error": {
    "code": 429,
    "message": "Quota exceeded for quota group 'WriteGroup' and limit 'USER-100s' of service 'sheets.googleapis.com' for consumer 'project_number:〇〇〇〇〇〇〇'.",
    "status": "RESOURCE_EXHAUSTED",
    "details": [
      {
        "@type": "type.googleapis.com/google.rpc.Help",
        "links": [
          {
            "description": "Google developer console API key",
            "url": "https://console.developers.google.com/project/〇〇〇〇〇/apiui/credential"
          }
        ]
      }
    ]
  }
}
1000行以上あるから、worksheet.update_acellを使っていたら、エラーが出る。
worksheet.update_acellは、一つづつ値を更新していくので、それだけAPIを使わなければいけないので、すぐにエラーになる。一気に値を追加しようと思ったら、worksheet.update_cellsを使う。
worksheet.update_cellsを使うとAPIをたたく回数が少なくて済む。
一気に取得して、一気に入力！という感じ。実際にどんな風に書いているのかコードを張っておく。


# データベースから引っ張ってきた情報（出力カラムは１つ）をスプレッドシートへ入力する。
# スプレッドシートの2行目から貼り付けられるようにする。

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

scope = ['https://spreadsheets.google.com/feeds']

#ダウンロードしたjsonファイル名を記入
json_file = '〇〇〇〇.json'
#スプレッドシートIDを記入
sheet_id = 'スプレッドシートのID'
#スプレッドシートのシート名を記入
sheet_name = 'sheet'

# jsonを読みに行き、スプレッドシートと連携
credentials = ServiceAccountCredentials.from_json_keyfile_name(json_file, scope)
gc = gspread.authorize(credentials)
sp = gc.open_by_key(sheet_id)
worksheet = sp.worksheet(sheet_name)

# データベースへ接続
# コネクションの作成

def sql_input(sql,col_select):
    conn = mysql.connector.connect(
        host='localhost',
        port='〇〇〇',
        user='〇〇〇',
        password='〇〇〇',
        database='〇〇〇'
    )
    cur = conn.cursor()
    cur.execute(sql)
    rows = cur.fetchall()

    # keyword_listへsqlの結果を格納
    keyword_list=[]
    for row in rows:
        row = row[0]
        keyword_list.append(row)
    
    #　キーワードの個数を確認してA1:A〇を作る。keyword_listに+１している理由はA2行目から始めるから。
    range_count = len(keyword_list)+1
    range_count = str(range_count)
    range_counts = col_select+range_count

    # rangeに入るA1:A●をworksheet.rangeへ入れる
    #cell_list = worksheet.range(A1:A7)
    cell_list = worksheet.range(range_counts)
    
    # cell_valuesは入力したい値。今回の場合は、keyword_listの内容が入力したい値になる。
    # cell_valuesをkeyword_listへ変更する。
    #　cell_values = [1,2,3,4,5,6,7]

    for i, val in enumerate(keyword_list):  
        cell_list[i].value = val    

    worksheet.update_cells(cell_list)

    #　データベースを閉じる
    cur.close()
    conn.close()
# sqlの文章というところへ、select カラム名 from デーブル名・・・を入れる
#　”A2:A"のところへ、貼り付けたい列を入れる。（単一列のみ）A1:B10とはエラー出る。
sql_input("sqlの文章","A2:A")
これで、エラーで出ずに、一撃でスプレッドシートに追加することが出来た。
   
   
関連記事