Pandas を使って、Google スプレッドシートのデータをsqlite3 にimportする

python3 -m pip install df2gspread --user

from oauth2client.service_account import ServiceAccountCredentials
def download_as_df(sheet_id, sheet_name):
    from df2gspread import gspread2df as g2d
    # key_file 以下の指定方法だと、notebook と同じディレクトリにあるキーファイルを取得しています。
    key_file = "spreadsheet_api_key.json"
    scope = ['https://spreadsheets.google.com/feeds']
    credentials = ServiceAccountCredentials.from_json_keyfile_name(key_file, scope)    
    # 1brCpWvk2uofc3MEt-ASb2cuZ-u8Zmx-ICxSTltlbVBQ はスプレッドシートのID なのでそれぞれ取得対象のスプレッドシートで変わります。
    df = g2d.download(sheet_id, wks_name=sheet_name, col_names=True, row_names=False, credentials=credentials, start_cell = 'A15')
    df = df.sort_values(by='ga:date') 
    return df

import pandas as pd
df = download_as_df("1brCpWvk2uofc3MEt-ASb2cuZ-u8Zmx-ICxSTltlbVBQ", "ユーザーの行動レポート 201710")

# データフレームの内容を確認する
df

import sqlite3
#db作成
dbname = "sheet.db"
#dbコネクト
conn = sqlite3.connect(dbname)
# tableを作成する 
df.to_sql("userreports_201710", conn, if_exists="replace")
# コネクションを閉じる
conn.close()

#db作成
dbname = "sheet.db"
#dbコネクト
conn = sqlite3.connect(dbname)
c = conn.cursor()
sql = 'select * from userreports_201710'
for row in c.execute(sql):
    print(row)
conn.close()

(154, '106339101.2', '/blog/cent-os-69-に-memcached-をインストールログの設定まで実施する/', '2017-10-01', '1')
(3316, '257535979.2', '/blog/python-requests-post-リクエスト送信時にheader-を設定する/', '2017-10-01', '1')
(875, '132184887.2', '/blog/google-search-console-の-キーワードを-python-sklearn-randomforestclassifier-でクラス分類してカテゴリ分けする/', '2017-10-01', '2')
(1727, '1633723053', '/blog/google-search-console-の-キーワードを-python-sklearn-の-ナイーブベイズ分類-で-カテゴリ分けする/', '2017-10-01', '1')
(2066, '175791501.2', '/blog/pep8wraning-do-not-assign-a-lambda-expression-use-a-def/', '2017-10-01', '1')
(2233, '1818150150', '/blog/python-requests-post-リクエスト送信時にheader-を設定する/', '2017-10-01', '2')
(4348, '639013335.2', '/blog/youtube-data-api-v3-java-paging-search/', '2017-10-01', '1')
(4767, '785076192.2', '/blog/dropwizard-adminとapplicationのcontextrootを切り替える/', '2017-10-01', '1')
(1078, '139048351.2', '/blog/google-apps-script-でスプレッドシートの列の値を取得する/', '2017-10-01', '1')
(5266, '993520188.2', '/blog/migrations-to-mezzanine-4.2.3-from-4.2.2/', '2017-10-01', '1')
(4550, '703959728.2', '/', '2017-10-01', '1')

import sqlite3
#db作成
dbname = "sheet.db"
#dbコネクト
conn = sqlite3.connect(dbname)
# tableを作成する
df.to_sql("userreports_201710",  conn, index=False, if_exists="replace")
# コネクションを閉じる
conn.close()

#db作成
dbname = "sheet.db"
#dbコネクト
conn = sqlite3.connect(dbname)
c = conn.cursor()
sql = 'PRAGMA TABLE_INFO(userreports_201710);'
for row in conn.execute(sql).fetchall():
    print(row)
conn.close()

(0, 'ga:dimension8', 'TEXT', 0, None, 0)
(1, 'ga:pagePath', 'TEXT', 0, None, 0)
(2, 'ga:date', 'TEXT', 0, None, 0)
(3, 'ga:pageviews', 'TEXT', 0, None, 0)

# column に含まれる: を _ に置換する
df.columns = [ column.replace(":","_") for column in df.columns]
import sqlite3
#db作成
dbname = "sheet.db"
#dbコネクト
conn = sqlite3.connect(dbname)
# tableを作成時に index=False をパラメータで渡す
df.to_sql("userreports_201710",  conn, index=False, if_exists="replace")
# コネクションを閉じる
conn.close()

#db作成
dbname = "sheet.db"
#dbコネクト
conn = sqlite3.connect(dbname)
c = conn.cursor()
sql = 'PRAGMA TABLE_INFO(userreports_201710);'
for row in conn.execute(sql).fetchall():
    print(row)
conn.close()

(0, 'ga_dimension8', 'TEXT', 0, None, 0)
(1, 'ga_pagePath', 'TEXT', 0, None, 0)
(2, 'ga_date', 'TEXT', 0, None, 0)
(3, 'ga_pageviews', 'TEXT', 0, None, 0)