pythonであるサーバから自分のcentOSサーバのmysqlデータベースに自動で更新していくコードを作りたい。

Question

プログラミング初心者のため、あまり詳しくないので、易しく解説していただけると嬉しいです。研究室で、下図のような環境を構築したいと考えています。つまり、元サーバのそれぞれのtree構造の一番下にあるtxtデータのうち、取得日と同じ日付のファイルを取得し、研究室のサーバのmysqlのテーブルに入れる環境を作りたいです。そもそも、可能でしょうか。また、将来的に、統計的な解析やグラフの記述をしていくことも踏まえて、言語は、pythonで統一しようと思っています。参考となるコードを教えていただけると嬉しいです。ちなみに学内のパソコンから外部のサーバにアクセスする際は、proxy環境をくぐらないといけませんので、そこもどう対処すればいいか教えていただけると嬉しいです。
![![イメージ説明]WIDTH:600]WIDTH:600

Comments

[lib]

細かいですが、気になった点をいくつか
・学内と学外の区別がわかりません。
・作ったものを配置する先はどちらなのか。（元サーバーなのか、研究室サーバーなのか）
推測で後者だとは思いますが。
・元サーバーにwebサーバーの設置は可能なのか、もしくはアクセスができるようになっているのか。
・ネットの環境が図に現れていません。まずは以下のような形からはいってみてはいかがでしょうか。
http://hesonogoma.com/visio/networkdiagram.html
・そもそも研究室内の話なので、そこの中で解決できませんか？（理由は秘密保持なんちゃら）

[HidenoriYamano]

元サーバが学外、研究室サーバが学内です。・研究室サーバに作ったものを配置します。・元サーバにはwebサーバ設置可能です。・データは秘密事項なのですが、なにせ、自分達の研究チームに、こういったインフラに詳しい者がいないため、なにか、ヒントになることはと思い、質問させていただきました＞＜

Answer 1

お邪魔します。

ご質問の内容は噛み砕くと以下の4点であっていますか？
0. pythonを使ってファイル名に特定の文字列を含むテキストファイルを取得
0. ファイルの内容を読み込む
0. データベースにファイルの内容を書き出す
0. データベースはプロキシ経由で別サーバにあるため、これとのコネクションの確立
まず、１からですが、pythonのosモジュールにwalkというディレクトリの内容を再帰的に辿って調べてくれる関数があるので、これを使います。
また、環境がよくわからないのでこれは経験とか感の領域ですが、対象のファイルを一旦収集して手元にコピーすることにします。

import re
from tempfile import mkdtemp
from shutil import copyfile
from os import walk

FILENAME_PATTERN = '20150601'
ROOT_PATH = r'C:\\Documents and Settings\\'

r = re.compile("[a-zA-Z]+-(?P<date>\d{8})")

def collect(filename):
    tempdir = mkdtemp() 
    copyfile(filename, tempdir)
    return os.path.join(tempdir, os.path.basename(filename))

def gen_filelist(path):
    for root, dirs, files in os.walk(path):
        for file in files:
            m = r.match(file)
            if(m or FILENAME_PATTERN in m.groupdict().values()):
                # ファイルを何かが開いていたら厄介なので、一旦テンポラリにコピーする
                yield collect(os.path.join(root, file))

２．のファイルの内容を読み込む、ですが、open関数というのが標準ライブラリにあるのでこれでファイルを開いて内容を読み込みます。

def read_line(data):
    #データを読み出す。例えばcsvファイルをカンマで分けるとか。今回は何もせずそのまま返します。
    return data

def read(filename):
    with open(filename, "r") as f:
        lines = []
        for line in f.readlines():
            lines.append(read_line(line))
        return lines

３．のデータベースにファイルの内容を書き出す、ですが、単純にやるよりは将来のデータ構造の変化を考慮してORMを使うのが良いと思います。SQLAlchemyを使ってみます。（データの取得時にも、ここで定義したテーブルアクセサは使うことができますしね。）
ORMをセットアップしてテーブルにデータを登録するには以下のようにします。
テーブルの構造とか適当ですので読み替えてください。
（既存のテーブルにORMをアタッチさせる場合、他に少し工夫がいるかもしれません。）

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Table, Column, Integer, String, MetaData, ForeignKey, Sequence

Base = declarative_base()

class Data(Base):
    __tablename__ = 'tablename'
    id = Column('id', Integer, Sequence('id_seq'), primary_key=True)
    somedata = Column('somedata', String(512))

    def __init__(self, somedata):
        self.somedata = somedata
    def __repr__(self):
        return "<Data('%s',...)>" % (self.somedata)

def main():
    #ORMのセットアップ
    engine = create_engine('mysql://<ユーザ名>:<パスワード>@<ip>/<db名>', encoding='utf-8', echo=True)
    Session = sessionmaker(bind=engine)
    session = Session()
    data = Data("データの内容。。。。。")
    #DBに対して書き込みを行う。
    session.add(data)
    session.commit()

４．のプロキシ越えの件ですが、基本的にはDBのクライアントソフトウェアで接続できるなら問題ないはずです。問題がある場合は、クライアントソフトウェアでも接続できないはずです。
（セッションを維持できない可能性があります。そういった問題が発生するのであれば、プロキシの設定を適時変更するなどしてください。プロキシの設定を変更できない場合、ポートフォワーディングを行うとか、何らかのプロトコルによるトンネリングを試みたりする余地はあるとは思いますが、大変そうです。どちらにしてもDBサーバと双方に何か仕込む必要がありますね。）

まとめ

提示したソースをつながるように一部改変したものを載せておきます。
一切テストをしていないので、問題があったら教えてください。w
なお、提示したソースの動作にはSQLAlchemyとMySQLに接続するためのpython用ドライバが必要です。
[MySQL用のドライバのうち一般的なもの]
・MySQL-python
・PyMySQL
・mysql-connector-python
適当に入れてください。

# -*- coding: utf-8 -*-

import re
import os
from tempfile import mkdtemp
from shutil import copyfile
from os import walk

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Table, Column, Integer, String, MetaData, ForeignKey, Sequence


FILENAME_PATTERN = '20150601' #収集対象とするファイル名のパターン
ROOT_PATH = r'C:\\Documents and Settings\\' #収集するファイルが存在するトップディレクトリ
CONNECT_STRING = 'mysql://<ユーザ名>:<パスワード>@<ip>/<db名>' #DB接続文字列

r = re.compile("[a-zA-Z]+-(?P<date>\d{8})\.txt") #対象ファイル名を示す正規表現を定義。

Base = declarative_base()

class Data(Base):
    __tablename__ = 'tablename'
    id = Column('id', Integer, Sequence('id_seq'), primary_key=True)
    somedata = Column('somedata', String(512))

    def __init__(self, somedata):
        self.somedata = somedata
    def __repr__(self):
        return "<Data('%s',...)>" % (self.somedata)



def collect(filename):
    tempdir = mkdtemp()
    copyfile(filename, tempdir)
    return os.path.join(tempdir, os.path.basename(filename))

def gen_filelist(path):
    for root, dirs, files in os.walk(path):
        for file in files:
            m = r.match(file)
            if(m and FILENAME_PATTERN in m.groupdict().values()):
                # ファイルを何かが開いていたら厄介なので、一旦テンポラリにコピーする
                yield collect(os.path.join(root, file))

def read_line(data):
    #データを読み出す。例えばcsvファイルをカンマで分けるとか。
    #今回は、ORMのクラスにデータを格納してそのまま返します。
    return Data(data)

def read(filename):
    with open(filename, "r") as f:
        lines = []
        for line in f.readlines():
            lines.append(read_line(line))
        return lines


def main():
    #SQLAlchemyの準備
    engine = create_engine(CONNECT_STRING, encoding='utf-8', echo=True)
    Session = sessionmaker(bind=engine)
    session = Session()

    #ファイルの一覧を取得
    files = gen_filelist(ROOT_PATH)

    #ファイルの内容を取得
    for file in files:
        data = read(file)
        session.add(data)

    #データベースへ内容を登録    
    session.commit()
    
if __name__ == '__main__':
    main()

---

(2015/06/28追記)

ssh(sftp)経由でリモートのパスを舐め、ファイルを取得してくるように改変します。
その前に、サードパーティのモジュールをインストールします。

pip install paramiko

※paramikoのインストールには色々と厄介なことがあるようです。linuxでは起こりにくいとは思いますが、ハッシュ化や暗号化モジュールがうまく入らない場合、また情報をください。

sshでファイルを収集するプログラムは次のような感じでできます。
ssh経由でファイルを探す部分は手作りですね。walkを適当に拾ったのを参考に自作しました。（黒）
1件だけ懸念点があり、SHHで接続に失敗する場合、直接鍵ファイルを指定する必要があるかもしれません。

コメントにしている、以下のコードを有効にし、鍵ファイルへのパスを直接指定してください。
conn.load_host_keys(os.path.expanduser('~/.ssh/known_hosts'))

以下、対象ホストSSH接続し、ファイルを収集、内容をデータベースに書き出すサンプルです。
データベースのテーブル定義などは考慮していません。適時修正ください。

# -*- coding: utf-8 -*-

import re
import os
from tempfile import mkdtemp

import paramiko
from stat import S_ISDIR

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Table, Column, Integer, String, MetaData, ForeignKey, Sequence

HOST_ADDRESS = "XX.XX.XX.XX" #データ収集時の接続先ホストIPアドレス
SSH_PORT = 22 #SSH接続ポート
SSH_PASSWORD = "XXXXXXXX" #SSH接続パスワード
SSH_USERNAME = "wheeluser" #SHH接続ユーザ名

FILENAME_PATTERN = '20150601' #収集対象とするファイル名のパターン
ROOT_PATH = r'/home/username/' #収集するファイルが存在するトップディレクトリ
CONNECT_STRING = 'mysql://<ユーザ名>:<パスワード>@<ip>/<db名>' #DB接続文字列

# ローカルがwindows機である場合、os.path.joinのパスのデリミタで問題がでるのでその対処
RELATIONSHIP_TO_HOST = "posix_to_posix" # "posix_to_posix" / "nt_to_posix" / "posix_to_nt"

if RELATIONSHIP_TO_HOST == "posix_to_posix":
    from os import path as remote_path
elif RELATIONSHIP_TO_HOST == "nt_to_posix":
    import posixpath as remote_path
elif RELATIONSHIP_TO_HOST == "posix_to_nt":
    import ntpath as remote_path

import logging
from datetime import datetime
logging.basicConfig(filename=datetime.today().strftime(r"%Y%m%d_%H%M%S")+'.log', level=logging.DEBUG)

r = re.compile("[a-zA-Z]+-(?P<date>\d{8})\.txt") #対象ファイル名を示す正規表現を定義。

Base = declarative_base()

class Data(Base):
    __tablename__ = 'tablename'
    id = Column('id', Integer, Sequence('id_seq'), primary_key=True)
    somedata = Column('somedata', String(512))

    def __init__(self, somedata):
        self.somedata = somedata
    def __repr__(self):
        return "<Data('%s',...)>" % (self.somedata)


def walk(sftp, path):
    current = path
    files = []
    folders = []
    for item in sftp.listdir_attr(current):
        if S_ISDIR(item.st_mode):
            folders.append(item.filename)
        else:
            files.append(item.filename)
    yield current, folders, files
    for folder in folders:
        for x in walk(sftp, remote_path.join(path, folder)):
            yield x

def collect(sftp, filename):
    tempdir = mkdtemp()
    dist = os.path.join(tempdir, os.path.basename(filename))
    logging.info("copy file from " + filename + " to " + dist)
    sftp.get(filename, dist, lambda x, y: (logging.warning("File tranceport fail !!!") if x!=y else logging.info("File tranceport success.")))
    return dist

def gen_filelist(sftp, path):
    for root, dirs, files in walk(sftp, path):
        for file in files:
            m = r.match(file)
            if(m and FILENAME_PATTERN in m.groupdict().values()):
                # ファイルを何かが開いていたら厄介なので、一旦テンポラリにコピーする
                yield collect(sftp, remote_path.join(root, file))

def read_line(data):
    #データを読み出す。例えばcsvファイルをカンマで分けるとか。
    #今回は、ORMのクラスにデータを格納してそのまま返します。
    return Data(data)

def read(filename):
    with open(filename, "r") as f:
        lines = []
        for line in f.readlines():
            if(line.strip() != ""):
                lines.append(read_line(line))
        return lines


def main():
    #SQLAlchemyの準備
    engine = create_engine(CONNECT_STRING, encoding='utf-8', echo=True)
    Session = sessionmaker(bind=engine)
    session = Session()

    #SFTP接続の準備
    conn = paramiko.SSHClient()
    conn.set_missing_host_key_policy(paramiko.AutoAddPolicy()) 
    #conn.load_host_keys(os.path.expanduser('~/.ssh/known_hosts')) #鍵ファイルを直接指定する場合
    conn.connect(HOST_ADDRESS, port=SSH_PORT, password=SSH_PASSWORD, username=SSH_USERNAME)
    sftp = conn.open_sftp()

    #ファイルの一覧を取得
    files = gen_filelist(sftp, ROOT_PATH)

    #ファイルの内容を取得
    for file in files:
        data = read(file)
        session.add(data)

    #データベースへ内容を登録    
    session.commit()
    
if __name__ == '__main__':
    main()

Comments

[HidenoriYamano]

ご回答ありがとうございます。質問が良くなかったので訂正させてください。
>>4. データベースはプロキシ経由で別サーバにあるため、これとのコネクションの確立
データベースは、研究室内のローカルネットワークにあるため、プロキシは不要です。ですが、元データのサーバが、外部にあるため、そこに、接続してデータを取得するために、プロキシ設定が必要なのかと思います。

[HidenoriYamano]

やりたいこととしては、ご回答者様の考え方で、基本的にはあっていますが、一応、私の方で見解をまとめますと、
1. 外部サーバにssh接続
2. 指定の日付（取得日）のファイルを取得
3. ファイルを開き、内容を取得
4. データベースのテーブル内に、適切な箇所にデータを挿入

1〜4を毎日、自動で行ってくれる。
これが、理想型です。自動化するコードなどありましたらそちらも教えていただきたいです＞＜

[HidenoriYamano]

何度もすいません。上記の１. の際に、秘密鍵の認証というのが必要です。こちらに関しては、別に質問しているのですが、まだ解決していません。
https://teratail.com/questions/11630

[ShinpeiYamamoto]

遅くなって申し訳ないです。外部サーバ上のディレクトリからファイルを取得する必要があるのですね。外部サーバにssh接続するのであれば、paramikoを使うと良いと思います。
ちょっと動かしてから投稿したいので週末にでも回答のソースに追記しておきますが、それでは間に合わないかもしれないので、リンクを張っておきます。（基本的には、提示したソースのうちファイル探索とコピーをssh経由（sftp）で行えばよいだけですね。ほんの少し変更するだけで大丈夫だと思います。悩むとしたらディレクトリを舐める部分を自分で実装しないといけなさそうなところですが、str(SSHClient.lstat())がd始まりかどうかとかでディレクトリか否か判断する ー ls -lhdと一緒ですね ーとできそうかなと思います。）
paramiko http://paramiko-docs.readthedocs.org/en/latest/api/client.html
paramiko wrapper(sftp_walk) https://gist.github.com/johnfink8/2190472
SFTP で任意のディレクトリ以下のリモートファイルをコピーする http://atasatamatara.hatenablog.jp/entry/20120814/1344940445

[ShinpeiYamamoto]

お邪魔します。ひとつコメント欄の中で「毎日、自動で行ってくれる」というのを読み飛ばしていました。スクリプト中のFILENAME_PATTERNという定数に収集対象のファイルを指定するパターンを設定しています。
スクリプトを動作させるサーバの時計が合っているなら、

FILENAME_PATTERN=datetime.today().strftime(r"%Y%m%d")

に置き換えればよいですね。（loggingを初期化しているコードより後でやってください。）
あとはcronで毎日定期実行すれば勝手にやってくれるでしょう。
テーブルに登録するところだけ実際の環境に合わせて調整が必要でしょうが。

[HidenoriYamano]

ありがとうございます。自分の環境でうまくか今週試してみたいと思います。また、質問するかもしれませんがよろしくお願いします。

[HidenoriYamano]

作っていただいたスクリプトで、step1のみ実行してみたのですが
rbenv: no such command `init'
File "/Applications/Python 2.7/program/pythontest/test1.py", line 25
yleid collect(os.path.join(root, file))
^
SyntaxError: invalid syntax
とエラーが出ます。なぜでしょうか。

[ShinpeiYamamoto]

スペルミスです；ごめんなさい。追記していった後のほうでは直ってますね。yield collect(os.path.join(root, file))ですね。

[HidenoriYamano]

ありがとうございます。また、つまずいたので質問させてください。
def collect(filename):
tempdir = mkdtemp()
copyfile(filename, tempdir)
return os.path.join(tempdir, os.path.basename(filename))
この部分のcopyfileのところで以下のようにエラーが出ます。
Traceback (most recent call last):
File "/Applications/Python 2.7/program/pythontest/text_test.py", line 57, in <module>
print collect("/Users/Hidenori/Desktop/xlsx/rakuten1160-20150201-plan-number.csv")
File "/Applications/Python 2.7/program/pythontest/text_test.py", line 29, in collect
copyfile(filename, tempdir)
File "/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/shutil.py", line 83, in copyfile
with open(dst, 'wb') as fdst:
IOError: [Errno 21] Is a directory: '/var/folders/27/dvh8p3j909x93tgqmlvw2tvm0000gn/T/tmpJ1EUKg'
対処法を教えてください＞＜

[ShinpeiYamamoto]

申し訳ない。バグですね。。。ごめんなさい。
copyfile(filename, tempdir)を
copyfile(filename, os.path.join(tempdir,os.path.basename(filename)))
に差し替えてください。
コピー先はファイル名まで指定する必要がありました。やっぱりテストなしでは色々でますね。苦笑