import pymysql.cursors

wasBornIn = {}
with open("wasBornIn.txt") as f:
for line in f:
   (key, val) = line.split(':')
   wasBornIn[key] = val

diedIn = {}
with open("diedIn.txt") as f:
for line in f:
   (key, val) = line.split(':')
   diedIn[key] = val

 isLocatedIn = {}
 with open("isLocatedIn.txt") as f:
for line in f:
   (key, val) = line.split(':')
   isLocatedIn[key] = val

connection = pymysql.connect(host='********', user='******', password='******', db='*******',
                         charset='utf8mb4', cursorclass=pymysql.cursors.DictCursor)
try:
with connection.cursor() as cursor:
# Create a new record
    sql = "DROP TABLE DiedIn"
    cursor.execute(sql)

with connection.cursor() as cursor:
# Create a new record
    sql = "DROP TABLE isLocatedIn"
    cursor.execute(sql)

with connection.cursor() as cursor:
# Create a new record
    sql = "DROP TABLE BornIn"
    cursor.execute(sql)

with connection.cursor() as cursor:
    sql = "CREATE TABLE `DiedIn`(`name` varchar(100) COLLATE utf8_bin NOT NULL, `city` varchar(50) COLLATE utf8_bin NOT NULL, "
         "PRIMARY KEY(`name`)) ENGINE = InnoDB DEFAULT CHARSET = utf8"
          " COLLATE = utf8_bin;"
    cursor.execute(sql)

with connection.cursor() as cursor:
    sql = "CREATE TABLE `isLocatedIn`(`name` varchar(150) COLLATE utf8_bin NOT NULL, `location` varchar(50) COLLATE utf8_bin NOT NULL, "
         "PRIMARY KEY(`name`)) ENGINE = InnoDB DEFAULT CHARSET = utf8"
          " COLLATE = utf8_bin;"
    cursor.execute(sql)

with connection.cursor() as cursor:
    sql = "CREATE TABLE `BornIn`(`name` varchar(100) COLLATE utf8_bin NOT NULL, `city` varchar(50) COLLATE utf8_bin NOT NULL, "
         "PRIMARY KEY(`name`)) ENGINE = InnoDB DEFAULT CHARSET = utf8"
          " COLLATE = utf8_bin;"
    cursor.execute(sql)

with connection.cursor() as cursor:
    for key, value in diedIn.iteritems():
        strKey = repr(key)
        strValue = repr(value)
        sql = "INSERT INTO 'DiedIn' ('name', 'city') VALUES (%s, %s);"
        cursor.execute(sql, (strKey, strValue))
# connection is not autocommit by default. So you must commit to save
# your changes.
connection.commit()
finally:
connection.close()