# L-37 MCS 275 Wed 12 Apr 2017 : tempdbtupins.py
"""
Given data stored as dictionaries in a file "temperatures.txt",
this scripts shows the data as tuples as they would be used for
insertion in the MySQL table inputdata of the temperatures database.

The database is created with mysqladmin at the command prompt as
mysqladmin create temperatures.
To make the table, we use the mysql monitor as follows:
mysql> use temperatures
Database changed
mysql> create table inputdata
    -> (k INT, p CHAR(20), d DATE, t TIME, temp REAL);
Query OK, 0 rows affected (0.01 sec)

mysql> explain inputdata;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| k     | int(11)  | YES  |     | NULL    |       |
| p     | char(20) | YES  |     | NULL    |       |
| d     | date     | YES  |     | NULL    |       |
| t     | time     | YES  |     | NULL    |       |
| temp  | double   | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+

If the temperatures.txt file contains the line
{'place': 'Chicago', 'date': '2017-04-12', 'time': '17:08', 'temp': 61.3}
then the script prints
[(0, 'Chicago', '2017-04-12', '17:08', 61.3)]
['insert into inputdata values ("0","Chicago","2017-04-12","17:08","61.3");']
and then at the mysql monitor we can see the added record as
mysql> select * from inputdata;
+------+---------+------------+----------+------+
| k    | p       | d          | t        | temp |
+------+---------+------------+----------+------+
|    0 | Chicago | 2017-04-12 | 17:08:00 | 61.3 |
+------+---------+------------+----------+------+
"""
# import MySQLdb
import pymysql

def DataTuples():
    """
    Returns a list of tuples for the
    data in the file temperatures.txt.
    """
    file = open('temperatures.txt', 'r')
    T = []
    key = 0
    while True:
        L = file.readline()
        if L == '':
            break
        d = eval(L)
        tp = (key,d['place'],d['date'],d['time'],d['temp'])
        T.append(tp)
        key = key + 1
    file.close()
    return T

def InsertCommands(T):
    """
    Given the list T of data tuples,
    returns the list of MySQL commands
    to insert the data in the table inputdata.
    """
    L = []
    for t in T:
        s = 'insert into inputdata values (' \
          + '\"' + str(t[0]) + '\",' \
          + '\"' + t[1] + '\",' \
          + '\"' + t[2] + '\",' \
          + '\"' + t[3] + '\",' \
          + '\"' + str(t[4]) + '\");'
        L.append(s)
    return L

def InsertData(L):
    """
    Executes the MySQL commands in L.
    """
    # db = MySQLdb.connect(db="temperatures")
    db = pymysql.connect(db="temperatures")
    cr = db.cursor()
    for c in L: 
        cr.execute(c)
    db.commit()

def main():
    """
    Reads the data from temperatures.txt
    and inserts into the inputdata table
    of the database temperatures.
    """
    T = DataTuples()
    print(T)
    L = InsertCommands(T)
    print(L)
    InsertData(L)

if __name__ == "__main__":
    main()