Import file which do: - SSH Download of a .sql.gz.gpg file - Decrypting it using GPG - Decompress line by line using gzip - Create requests (separated by ";" and run it on mysql) It also has a retry and try_once function to handle errors and automatically re-run a determined amount of time if needed

import.py

import sys
from datetime import datetime
import paramiko
import gzip
import gnupg 
import MySQLdb

# apt-get install:
# python-paramiko
# python-mysqldb
# python-dev

# download gnupg: https://code.google.com/p/python-gnupg/downloads/detail?name=python-gnupg-0.3.3.tar.gz
# install it: python setup.py install

log_path = "/opt/jedox/ps/log/import-sql.log"
# log_path = "import-sql.log"

def log(logType, msg=""):
  with open(log_path, 'a') as f:
    f.write("{0} {1} {2}\n".format(logType, datetime.now(), msg))
  print("{0} {1} {2}".format(logType, datetime.now(), msg))


def abort():
  log("abort", "Script execution aborted")
  print "Script execution aborted"
  sys.exit()


def try_once(func, exception, name="", exceptionHandler=None, logStep=1, currentStep=0):
  try:
    if(currentStep % logStep == 0):
      log("ok", name)
    res = func()
    return res
  except exception as e:
    if exceptionHandler != None:
      exceptionHandler()
    if name != "":
      log("error", "{0} - {1}".format(name, str(e)))
    else:
      log("error", str(e))
    abort()


def retry(func, exception, nb, name=""):
  i = 1
  while i <= nb:
    try:
      res = func()
      log("ok", name)
      return res
    except exception as e:
      err_type = "error" if i == nb else "retry"
      if name != "":
        log(err_type, "{0} (try {2} of {3}) - {1}".format(name, str(e), i, nb))
      else:
        log(err_type, str(e))
      i+=1
  abort()


def gpg_decrypt(filename, passphrase, output):
  ####
  # TODO: check if the archive is dated less than 12 hours ago
  ####

  gpg = gnupg.GPG()
  with open(filename, 'rb') as f:
    status = try_once(lambda: gpg.decrypt_file(f, passphrase=passphrase, output=output),
                      Exception, "GPG decrypting file...")
    if status.ok == False:
      log("error", "{0} - {1}".format("GPG decrypt error", status.status))


def gzip_decompress_into_mysql(filename, host, user, password, dbname):
  conn = retry(lambda: MySQLdb.connect(host, user, password, dbname),
               Exception, 3, "MySQL connection") 
  cursor = conn.cursor()

  with gzip.open(filename) as content:
    nb = 0
    req = ""
    for line in content:
      if line and line != "" and line[0] != "#" and line[0] != "-" and line[0] != "\n" and line[0] != "\r":
        trim_line = line.rstrip('\r\n')
        if(trim_line[-1:] == ";"):
          req += trim_line
          if(req):
            nb += 1
            try_once(lambda: cursor.execute(req), Exception, "Importing data (request nb " + str(nb) + ")...", lambda: conn.rollback(), 100, nb)
            req = ""
        else:
          req += trim_line

    try_once(lambda: conn.commit(), Exception, "Commiting " + str(nb) + " lines...", lambda: conn.rollback())
   
  conn.close()


def ssh_download(filename, destination, username, connection_tuple, private_key_path): 
  pkey = try_once(lambda: paramiko.DSSKey.from_private_key_file(private_key_path),
                  Exception, "Load of private key")
  #pkey = try_once(lambda: paramiko.RSAKey.from_private_key_file(private_key_path),
  #                Exception, "Load of private key")

  # Connection to distant server
  connect_result = retry(lambda: paramiko.Transport(connection_tuple), 
                         paramiko.SSHException, 2, "SSH connection")
 
  transport = connect_result
  try_once(lambda: transport.connect(username = username, pkey = pkey),
           paramiko.AuthenticationException, "Authentication on server")

  # Create an SFTP (SSH) connection
  sftp = paramiko.SFTPClient.from_transport(transport)
  try_once(lambda: sftp.get(filename, destination),
           Exception, "Downloading file {0}...".format(filename))

  # Close everything
  sftp.close()
  transport.close()


# ====== Main =======
if __name__ == "__main__":

  log("info", "starting download...")

  ssh_download("/home/koalauser/backup/koala.sql.gz.gpg", "/tmp/koala.sql.gz.gpg", "root", ('172.17.220.212', 22), 'id_dsa')
  gpg_decrypt("/tmp/koala.sql.gz.gpg", "iloveyou", "/tmp/koala.sql.gz")
  gzip_decompress_into_mysql("/tmp/koala.sql.gz", "localhost", "root", "1q2w3e", "import")

  log("info", "Data imported successfully!")