rom time import mktime
import mysql.connector
import datetime

from dateutil.relativedelta import relativedelta
db = mysql.connector.connect(user='user_dataengineering', password='dataengineering',
                              host='ruudlinssen.com',
                              database='dataengineering')

cursor = db.cursor()
nodes = []
edges = []
amount = []
purdata = []
hhdata = []
years = ["1974_final", "1975_final", "1976_final", "1977_final", "1978_final", "1979_final", "1980_final", "1981_final", "1982_final",
         "1983_final", "1984_final", "1985_final", "1986_final", "1987_final", "1988_final", "1989_final",
         "1990_final", "1991_final", "1992_final", "1993_final", "1994_final", "1995_final", "1996_final",
         "1997_final", "1998_final", "1999_final", "2000_final"]
i = 0
for table in years:
    #print("""SELECT hhno, gor, stmth, styr FROM %s WHERE TRUE ORDER BY styr, stmth, gor, hhno""" % (table))
    cursor.execute("""SELECT hhno, gor, stmth, styr FROM %s WHERE 1 ORDER BY styr""" % (table))
    hhdata.extend(cursor.fetchall())
    for row in hhdata:
        if not any(p['hhno'] == row[0] for p in nodes):
            nodes.append({
               'id': len(nodes),
               'node label': "Household",
               'hhno': row[0],
               'region': row[1],
               })
    print(datetime.datetime.now())
# Output to files
f = open('household_nodes.csv', 'w')

for node in nodes:
    to_write = [
        node['id'],
        'Node Label',
        node['Node Label']
    ]

    for key, value in node.iteritems():
        if key not in ['id', 'Node Label']:
            to_write.append(key)
            to_write.append(value)

    f.write(', '.join([str(i) for i in to_write]))
    f.write('\n')

f.close()