MySQL dump to SFTP

mysql_sftp_dump.py

#!/usr/bin/python
"""
Connects to MySQL, dumps a list of 10 movies, writes them to a CSV file
and upload that file to a SFTP server.
Example:

> ./main.py --ftp_host=localhost --ftp_username=username \
            --mysql_host=localhost --mysql_username=delme \
            --mysql_db=delme --mysql_password=delme \
            --export_file=/tmp/export
"""


__author__ = 'cozzi.martin@gmail.com'


import argparse
import csv
import getpass
import ftplib
import logging
import os
import pymysql


def main():
    """Where magic happens."""

    configs = get_configs()

    # connects to mysql
    LOGGER.debug('Connecting to MySQL server...')
    conn = pymysql.connect(
            host=configs.mysql_host,
            port=configs.mysql_port,
            user=configs.mysql_username,
            passwd=configs.mysql_password,
            db=configs.mysql_db)
    LOGGER.debug('Connected to MySQL server...')
            
    query = 'SELECT * FROM film LIMIT 1, 10;'
    dump_mysql_to_file(conn, query, configs.export_file)


    LOGGER.debug('Connecting to SFTP server...')
    sftp = ftplib.FTP_TLS(configs.ftp_host)
    sftp.login(configs.ftp_username, configs.ftp_password)
    sftp.prot_p()  # enable secure connection
    LOGGER.debug('Connected to SFTP server...')
    
    # only write the filename to remote FTP
    ftp_command = 'STOR %s' % os.path.basename(configs.export_file)
    LOGGER.debug('Writing file to SFTP server : %s', ftp_command)
    sftp.storbinary(ftp_command, open(configs.export_file, 'rb'))
    LOGGER.debug('Done')

    # close connections
    LOGGER.debug('Disconnecting from SFTP server...')
    sftp.quit()
    LOGGER.debug('Disconnecting from MySQL server...')
    conn.close()
    LOGGER.debug('Connections closed.')
    return
    

def dump_mysql_to_file(conn, query, path_to_file):
    """Executes a mysql query and dumps the result to file."""
    cursor = conn.cursor()
    LOGGER.debug('Executing query: "%s"', query)
    cursor.execute(query)

    # Write to file
    LOGGER.debug('Writing MySQL data to CSV: "%s"', path_to_file)
    csv_writer = csv.writer(open(path_to_file, 'wt'))
    csv_writer.writerow([line[0] for line in cursor.description])  # headers
    csv_writer.writerows(cursor)
    del csv_writer  # close writer 

    cursor.close()


def get_configs():
    """Makes use of argparse"""
    parser = argparse.ArgumentParser(
        description='SFTP backup stuff.')
    parser.add_argument('--ftp_host', type=str,
                        help='server to connect to',
                        required=True)
    parser.add_argument('--ftp_username', type=str,
                        help='username to connect with',
                        required=True)
    parser.add_argument('--ftp_password', type=str,
                        help='password to connect with')

    parser.add_argument('--mysql_host', type=str,
                        help='server to connect to',
                        required=True)
    parser.add_argument('--mysql_username', type=str,
                        help='username to connect with',
                        required=True)
    parser.add_argument('--mysql_db', type=str,
                        help='db to connect with',
                        required=True)
    parser.add_argument('--mysql_port', type=int,
                        help='port to connect with', default=3306)
    parser.add_argument('--mysql_password', type=str,
                        help='password to connect with')

    parser.add_argument('--export_file', type=str,
                        help='Path to file to export to',
                        required=True)

    args = parser.parse_args()
    if args.mysql_password is None:
        args.mysql_password = getpass.getpass('MySQL Password: ') 
    if args.ftp_password is None:
        args.ftp_password = getpass.getpass('FTP Password: ') 

    return args


def get_logger():
    """Deals with logging to console."""
    logger = logging.getLogger('collector')
    logger.setLevel(logging.DEBUG)
    console = logging.StreamHandler()
    console.setLevel(logging.DEBUG)
    console.setFormatter(
        logging.Formatter('%(asctime)s [%(levelname)s]: %(message)s'))

    logger.addHandler(console)
    return logger


LOGGER = get_logger()


if __name__ == '__main__':
    try:
        main()
    except KeyboardInterrupt:
        LOGGER.info('Ctrl+C was caught. Program is exiting now.')