【Python】pyodbc経由でSQL Serverにデータを追加する。

環境　Windows Python3.7

#これからやること
Pythonからpyodbc経由でSQL Serverに接続、SQLでデータを追加する。
前回( https://qiita.com/gaborotta/items/328d01355cd3e12bd070 )取得したデータをSQL Serverに入れておきます。

###追加するデータ
テーブルはSQL Serverの方で作成しておく。Pythonからはデータの追加のみ。

• 動画情報テーブル（動画IDを基にした重複なしテーブルとする）
  • 動画ID
  • 投稿日時
  • 再生時間
  • 過去最高ランク
• 投稿者情報テーブル（投稿者IDを基にした重複なしテーブルとする）
  • 投稿者ID
  • ユーザー登録時のニコニコ動画のバージョン
  • フォロワー数
  • 投稿動画数
• 動画-投稿者関連テーブル（動画IDを基にした重複なしテーブルとする）
  • 動画ID
  • 投稿者ID
• 動画タグテーブル（動画ID and タグ名を基にした重複なしテーブルとする）
  • 動画ID
  • タグ名

#pyodbcについて

pythonからODBCを通してデータベースを操作するライブラリ。
SQL構文を介してデータベース側とやりとりが出来る。
詳しくはこちら。
https://docs.microsoft.com/ja-jp/sql/connect/python/pyodbc/python-sql-driver-pyodbc?view=sql-server-2017
https://mkleehammer.github.io/pyodbc/

###Windows認証でSQL Serverへ接続
ユーザーIDとパスワードを使って接続もできるけど、こちらの方が楽な気がするので。

    driver='{SQL Server}'
    server = 'localhost\SQLEXPRESS' 
    database = 'データベース名' 
    trusted_connection='yes'

    cnxn = pyodbc.connect('DRIVER='+driver+';SERVER='+server+';DATABASE='+database+';Trusted_Connection='+trusted_connection+';')
    cursor = cnxn.cursor()

###接続の切り方
忘れずにcloseしましょう。

    cursor.close()
    cnxn.close()

###重複チェックをしてデータの追加
SQLを書いてexecuteで投げる。
commitを忘れずに行わないとデータベースに追加されない。

    sql = "INSERT INTO [dbo].[Hoge]\
        ([ID],[DATE],[DATA])\
        SELECT 1,'20190202','hogehoge'\
        WHERE NOT EXISTS(SELECT [ID] FROM [dbo].[Hoge] WHERE [ID] = '{id}' )"
    cursor.execute(sql)
    cnxn.commit()

#実際にやってみた
というわけで、前回取得したデータを全てSQLに突っ込んでみた。

#%%
import pyodbc
import datetime
import json
import sys

###SQL Serverへの接続
def connectSQL():
    driver='{SQL Server}'
    server = 'localhost\SQLEXPRESS' 
    database = 'NicoNico' 
    trusted_connection='yes'

    cnxn = pyodbc.connect('DRIVER='+driver+';SERVER='+server+';DATABASE='+database+';Trusted_Connection='+trusted_connection+';')
    cursor = cnxn.cursor()
    print("ok")
    return cnxn,cursor

def closeSQL(_cursor,_cnxn):
    _cursor.close()
    _cnxn.close()
    print("close")
    return

###Videoデータの追加
def insetVideo(_cn,_cur,_dateStr):
    ##Jsonファイル読み込み
    fr = open("VideoData/"+ _dateStr +".json", 'r',encoding='utf-8')
    videoData = json.load(fr)
    fr.close()

    ##SQLへの書き込み
    #クエリ構文の作成
    SQL_TEMPLATE = "INSERT INTO [dbo].[Video]\
        ([ID],[DATE],[LENGTH],[MAX_RANK],[URL])\
        SELECT '{id}','{date}','{length}','{maxRank}','{videoUrl}'\
        WHERE NOT EXISTS(SELECT [ID] FROM [dbo].[Video] WHERE [ID] = '{id}' )"

    #SQLへの書き込みクエリの実行
    for videoId in videoData:
        editSql = SQL_TEMPLATE                          
        editSql = editSql.replace('{id}', videoId)
        video=videoData[videoId]
        tags=video.pop('tags',None)
        if video["date"] :
            video["date"]=video["date"].replace('年','-').replace('月','-').replace('日','T')
        if video["maxRank"]==sys.maxsize:
            video["maxRank"]=None
        for key,data in video.items():
            editSql = editSql.replace('{' + key + '}', str(data))
        editSql=editSql.replace("None","")
        #print(editSql)
        _cur.execute(editSql)
    #コミット
    _cn.commit()
    return

###Usersデータの追加
def insetUsers(_cn,_cur,_dateStr):
    ##Jsonファイル読み込み
    fr = open("UserData/"+ _dateStr +".json", 'r',encoding='utf-8')
    userData = json.load(fr)
    fr.close()

    ##SQLへの書き込み
    #クエリ構文の作成
    
    SQL_TEMPLATE = "INSERT INTO [dbo].[Users]\
        ([ID],[NAME],[FOLLOWER_NUM],[VIDEO_NUM],[NICO_VER],[URL])\
        SELECT '{id}','{userName}','{followerNum}','{videoNum}','{nicoVer}','{userUrl}'\
        WHERE NOT EXISTS(SELECT [ID] FROM [dbo].[Users] WHERE [ID] = '{id}' )"

    #SQLへの書き込みクエリの実行
    for userId in userData:
        editSql = SQL_TEMPLATE                          
        editSql = editSql.replace('{id}', userId)
        user=userData[userId]
        for key,data in user.items():
            editSql = editSql.replace('{' + key + '}', str(data))
        editSql=editSql.replace("None","")
        #print(editSql)
        _cur.execute(editSql)
    #コミット
    _cn.commit()
    return

###UsersVideoデータの追加
def insetUsers_Video(_cn,_cur,_dateStr):
    ##Jsonファイル読み込み
    fr = open("User_VideoData/"+ _dateStr +".json", 'r',encoding='utf-8')
    user_videoData = json.load(fr)
    fr.close()

    ##SQLへの書き込み
    #クエリ構文の作成    
    SQL_TEMPLATE = "INSERT INTO [dbo].[UsersVideo]\
        ([VIDEO_ID],[USER_ID])\
        SELECT '{id}','{userId}'\
        WHERE NOT EXISTS(SELECT [VIDEO_ID] FROM [dbo].[UsersVideo] WHERE [VIDEO_ID] = '{id}' )"

    #SQLへの書き込みクエリの実行
    for videoId in user_videoData:
        editSql = SQL_TEMPLATE                          
        editSql = editSql.replace('{id}', videoId)
        editSql = editSql.replace('{userId}', user_videoData[videoId]["userId"])
        editSql=editSql.replace("None","")
        #print(editSql)
        _cur.execute(editSql)
    #コミット
    _cn.commit()
    return

###動画タグデータの追加
def insetVideoTags(_cn,_cur,_dateStr):
    ##Jsonファイル読み込み
    fr = open("VideoData/"+ _dateStr +".json", 'r',encoding='utf-8')
    videoData = json.load(fr)
    fr.close()

    ##SQLへの書き込み
    #クエリ構文の作成
    SQL_TEMPLATE = "INSERT INTO [dbo].[VideoTag]\
        ([VIDEO_ID],[TAG_NAME])\
        SELECT '{id}','{tagName}'\
        WHERE NOT EXISTS(SELECT [VIDEO_ID] FROM [dbo].[VideoTag] WHERE ([VIDEO_ID] = '{id}') AND ([TAG_NAME] LIKE '{tagName}') )"

    #SQLへの書き込みクエリの実行
    for videoId in videoData:
        video=videoData[videoId] 
        if not video["tags"]:   
            continue   
        for tag in video["tags"]:
            name=str(tag).replace("'","''")
            editSql = SQL_TEMPLATE                          
            editSql = editSql.replace('{id}', videoId)
            #print(name)
            editSql = editSql.replace('{tagName}', str(name))
            #print(editSql)
            _cur.execute(editSql)
                
    #コミット
    _cn.commit()
    return

print("ok")

#%%
##SQL接続
cn,cur=connectSQL()

###データを挿入
date = datetime.date(2019,2,2)
count=365

for num in range(count):
    date -=datetime.timedelta(days=1)
    dateStr=date.strftime("%Y%m%d")
    print(dateStr)
    insetVideo(cn,cur,dateStr)
    insetUsers(cn,cur,dateStr)
    insetUsers_Video(cn,cur,dateStr)
    insetVideoTags(cn,cur,dateStr)

#%%
closeSQL(cn,cur)

#結果
前回収集したJsonファイルからデータをSQL Serverに追加できた。
次回はこのデータをRから取り出して統計処理。