# import the mysql client for python

import pymysql

# Create a connection object

databaseServerIP = "localhost"  # IP address of the MySQL database server

databaseUserName = "root"  # User name of the database server

databaseUserPassword = ""  # Password for the database user

newDatabaseName = "testdatabase"  # Name of the database that is to be created

charSet = "utf8mb4"  # Character set

cusrorType = pymysql.cursors.DictCursor

connectionInstance = pymysql.connect(host=databaseServerIP, user=databaseUserName, password=databaseUserPassword,

                                     charset=charSet, cursorclass=cusrorType)

def CreateDatabase():
  try:
      # Create a cursor object
      cursorInstance = connectionInstance.cursor()

      # SQL Statement to create a database
      sqlStatement = "CREATE DATABASE " + newDatabaseName

      # Execute the create database SQL statment through the cursor instance
      cursorInstance.execute(sqlStatement)

      # SQL query string
      sqlQuery = "SHOW DATABASES"

      # Execute the sqlQuery
      cursorInstance.execute(sqlQuery)

      # Fetch all the rows
      databaseList = cursorInstance.fetchall()

      for datatbase in databaseList:
          print(datatbase)

  except Exception as e:

      print("Exeception occured:{}".format(e))

  finally:

      connectionInstance.close()


def FillTable(database, table, data, querysize):
  try:
      print("FillTable called")
      # Create a cursor object
      cursorInstance = connectionInstance.cursor()

      # SQL Statement to create a database
      #sqlStatement = "INSERT INTO weatherdata.testtable VALUES (1,2,3,4) "
      #sqlStatement ="INSERT INTO weather.Element VALUES(0,'PRCP'),(1,'SNOW'),(2,'SNWD'),(3,'TMAX')"

      # Execute the create database SQL statment through the cursor instance
      #cursorInstance.execute(sqlStatement)
      import math
      batches = int(math.ceil(len(data) / querysize))
      print(batches)
      for i in range(batches):
        thisStatement = ""
        for z in range(querysize):
          if len(data) > 0:
            thisStatement = thisStatement + "(" + str(data[0][0]) + ",'" + str(data[0][1]) + "'),"
            del data[0]

        if thisStatement[-1] == ",":
            thisStatement = thisStatement[:-1]

        sqlStatement = "INSERT INTO " + database + "." + table + " VALUES " + thisStatement
        #sqlStatement = str(sqlStatement)
        print(sqlStatement)
        cursorInstance.execute(sqlStatement)



  except Exception as e:

      print("Exeception occured:{}".format(e))

  finally:

      connectionInstance.close()


#CreateDatabase()
lotsofdata = []
q = 0
for i in range(1000000):
  thisdata = []
  thisdata.append(1)
  if q == 0:
    thisdata.append("PRCP")
    q += 1
  elif q == 1:
    thisdata.append("SNOW")
    q += 1
  elif q == 2:
    thisdata.append("SNWD")
    q += 1
  else:
    thisdata.append("TMAX")
    q = 0
  lotsofdata.append(thisdata)

print(len(lotsofdata))
FillTable("weather", "element", lotsofdata, 400000)