from datetime import date
import mysql.connector as _mysql
from mysql.connector.errors import ProgrammingError

print("replicate using documentation at https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursor-executemany.html")

con = _mysql.connect(host=****, user=****, password=****, database=****)


dataorCurNotPrep = [
  ('JaneForCurNotPrep', date(2005, 2, 12)),
  ('JoeForCurNotPrep', date(2006, 5, 23)),
  ('JohnForCurNotPrep', date(2010, 10, 3)),
]

dataForCurPrep = [
  ('JaneForCurPrep', date(2005, 2, 12)),
  ('JoeForCurPrep', date(2006, 5, 23)),
  ('JohnForCurPrep', date(2010, 10, 3)),
]

#set up the table 
try:
    cur = con.cursor()
    stmt = "CREATE TABLE `employees` (`first_name` VARCHAR(255), `hire_date` DATE)"
    cur.execute(stmt)
    cur.commit()
except _mysql.errors.ProgrammingError as ex:
    print(ex)
    print("We already have the employees table")
    

# MySQLCursor.executemany()
curNotPrep = con.cursor(prepared=False)
print("try using MySQLCursor.executemany()")
stmt = "INSERT INTO employees (first_name, hire_date) VALUES (%s, %s)"
curNotPrep.executemany(stmt, dataorCurNotPrep)

#===============================================================================
# THIS METHOD RESULTS IN THE BELOW ENTRY TO GENERAL QUERY LOG
# THIS IS MULTI-ROW SYNTAX
# Query    INSERT INTO employees (first_name, hire_date) VALUES ('JaneForCurNotPrep', '2005-02-12'),('JoeForCurNotPrep', '2006-05-23'),('JohnForCurNotPrep', '2010-10-03')
#===============================================================================

#  MySQLCursorPrepared.executemany()
curPrep = con.cursor(prepared=True)
print("try using MySQLCursorPrepared.executemany()")
stmt = "INSERT INTO employees (first_name, hire_date) VALUES (%s, %s)"
curPrep.executemany(stmt, dataForCurPrep)
    

#===============================================================================
# THIS METHOD RESULTS IN THE BELOW ENTRY TO GENERAL QUERY LOG
# THIS IS NOT MULTI-ROW SYNTAX
# Prepare    INSERT INTO employees (first_name, hire_date) VALUES (?, ?)
# Reset stmt    
# Execute    INSERT INTO employees (first_name, hire_date) VALUES ('JaneForCurPrep', '2005-02-12')
# Reset stmt    
# Execute    INSERT INTO employees (first_name, hire_date) VALUES ('JoeForCurPrep', '2006-05-23')
# Reset stmt    
# Execute    INSERT INTO employees (first_name, hire_date) VALUES ('JohnForCurPrep', '2010-10-03')
#===============================================================================