'''Consolidate several sql files generated by parse_sunrise.py
     and execute against a data base (astronomical data are available in
     one-year chunks from US Naval Observatory).'''

import mysql.connector
import sys

from database import login_info

FILES=['d:\\pecan\\austin_sunrise_2011.sql',
       'd:\\pecan\\austin_sunrise_2012.sql',
       'd:\\pecan\\austin_sunrise_2013.sql',
       'd:\\pecan\\austin_sunrise_2014.sql',
       'd:\\pecan\\austin_sunrise_2015.sql',
       ]

DB = 'pecan'
TBL = 'sun'
KILL_TBL=True
TBL_CREATE="""CREATE TABLE {}(time DATETIME, 
                            is_daylight TINYINT, 
                            rise DATETIME,
                            sset DATETIME,
                            seconds_daylite INT,
                            seconds_since_rise INT,
                            city varchar(30),
                            state varchar(20)
                            )
                            """

db=mysql.connector.Connect(**login_info)
curs = db.cursor()

if KILL_TBL:
        answer = input('gonna kill table ' + TBL + ', OK? (y/n) ')
        if answer == 'y':
                curs.execute('DROP TABLE IF EXISTS %s' %TBL)
                curs.execute(TBL_CREATE.format(TBL))
        else:
                print('Terminating')
                sys.exit()

for f in FILES:		
        with open(f, 'r') as f:	
                print('transfering data from {} to table {}.{}'.format(f, DB, TBL))
                count = 0   
                for line in f:
                        count += 1
                        curs.execute(line)
                db.commit()
print('Finished!')


