import pymysql.cursors
import os
from attrdict import AttrDict
import sys

# Test a one-row Insert, Select for Capacity DB
# ... using Py DB-API
#
# Install:
#   pip3 install PyMySQL attrdict
#
# Refs:
#   PyMySQL pure-Py driver for MySQL - Python DB-API
#
# Run:
# env DB_PASSWD=xyzzy1234 DB_HOST='10.10.100.101' DB=capacity DB_TABLE=capacity_openstack  python3 -m pdb capacity_openstack/csv_summary_tosql.py  # noqa

TEST_ENTRY = "compute,rackspace-dfw-dev,total_ram,380.0,500.0,0.76,120.0,2017-07-13 22:44:11.194554"  # noqa: E501


def main():

    vals = TEST_ENTRY.split(',')
    print(vals, file=sys.stderr)

    connobj = AttrDict(host=os.getenv('DB_HOST', 'localhost'),
                       user=os.getenv('DB_USER', 'capacity'),
                       password=os.getenv('DB_PASSWD', 'xyzzy'),
                       db=os.getenv('DB', 'mydbname'),
                       charset='utf8',
                       cursorclass=pymysql.cursors.DictCursor,)

    # connect to the Capacity database
    connobj.dbc = pymysql.connect(**connobj)
    connobj.db_table = os.getenv('DB_TABLE', 'my_db_table')

    try:
        with connobj.dbc.cursor() as cursor:
            # Create a new Capacity record
            sql = """INSERT INTO {db_table}(cap_type, cloud_region,
            measurement, in_use, cap_limit, percent, remaining, utc_datetime)
            VALUES (%s, %s, %s, %s, %s, %s, %s, %s)""".\
                format(db_table=connobj.db_table)

            cursor.execute(sql, vals)

        # connection is NOT "autocommit by default; commit to save changes!
        connobj.dbc.commit()

        with connobj.dbc.cursor() as cursor:
            # Read a single Capcity record
            sql = "SELECT * FROM {db_table}".format(db_table=connobj.db_table)
            cursor.execute(sql)
            result = cursor.fetchone()
            print(result, file=sys.stderr)

    finally:
        connobj.dbc.close()


if __name__ == '__main__':
    main()