#import mysql
import math

#from mysql.connector import Error
from runsql import runsql
import pandas as pd
from sqlalchemy import create_engine
import pymysql

# Credentials to database connection
hostname="localhost"
dbname="computeddata_testDB"
uname="root"
pwd="Hallo112!"
max_row_size_to_write = 200
# Create SQLAlchemy engine to connect to MySQL Database
engine = create_engine("mysql+pymysql://{user}:{pw}@{host}/{db}"
				.format(host=hostname, db=dbname, user=uname, pw=pwd))

setup_ids = runsql('select distinct setup_id from computeddata order by setup_id asc')
setup_ids_list = list(setup_ids)

for ids in range(len(setup_ids_list)):
    SETUP_ID = int(setup_ids_list[ids]['setup_id'])
    if SETUP_ID == 868:
        reader = runsql('select * from computeddata where setup_id = {} order by distance asc'.format(SETUP_ID))
        # reader_data = list(reader)
        df = pd.DataFrame.from_dict(reader)
        rows = df.shape[0]
        print('write setup_id = %d'%(SETUP_ID))
        if rows > max_row_size_to_write:
            iterations = math.ceil(rows / max_row_size_to_write)
            for r in range(iterations):
                start = r * max_row_size_to_write
                end = (r+1) * max_row_size_to_write
                if end > rows:
                    end = rows
                print('-- table rows from %d to %d' % (start, end))
                df.iloc[start:end].to_sql(dbname, engine, index=False)
        else:
            df.to_sql(dbname, engine, index=False)

        # Convert dataframe to sql table
        df.to_sql('users', engine, index=False)

###############################################################################
SETUP_ID = 868
reader = runsql('select * from computeddata where setup_id = {} order by distance asc'.format(SETUP_ID))
#reader_data = list(reader)
df = pd.DataFrame.from_dict(reader)


empdata = df
'''
try:
    conn = mysql.connector.connect(host='localhost', database='mysql', user='root', password='Hallo112!')
    if conn.is_connected():
        cursor = conn.cursor()
        cursor.execute("select database();")
        record = cursor.fetchone()
        print("You're connected to database: ", record)
        cursor.execute('DROP TABLE IF EXISTS employee_data;')
        print('Creating table....')
# in the below line please pass the create table statement which you want #to create
        cursor.execute("CREATE TABLE employee_data(first_name varchar(255),last_name varchar(255),company_name varchar(255),address varchar(255),city varchar(255),county varchar(255),state varchar(255),zip int,phone1 varchar(255),phone2 varchar(255),email varchar(255),web varchar(255))")
        print("Table is created....")
        #loop through the data frame
        for i,row in empdata.iterrows():
            #here %S means string values
            sql = "INSERT INTO employee.employee_data VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
            cursor.execute(sql, tuple(row))
            print("Record inserted")
            # the connection is not auto committed by default, so we must commit to save our changes
            conn.commit()
except Error as e:
    print("Error while connecting to MySQL", e)
    
'''