Re: Python Connector Create Tables Error

[Gerard Casey]

And yet, when I slightly change the syntax, it will work.
However, when inserting data, the foreign key does not work.

def create_database(cursor):
try:
cursor.execute(
"CREATE DATABASE {} DEFAULT CHARACTER SET 'utf8'".format(DB_NAME))
except mysql.connector.Error as err:
print("Failed creating database: {}".format(err))
exit(1)

try:
cnx.database = DB_NAME
except mysql.connector.Error as err:
if err.errno == errorcode.ER_BAD_DB_ERROR:
create_database(cursor)
cnx.database = DB_NAME
else:
print(err)
exit(1)

TABLES_1={}
TABLES_1['master'] = (
"CREATE TABLE `master` ("
" `request_no` int(11) NOT NULL AUTO_INCREMENT,"
" `distance` int NOT NULL,"
" `duration` int NOT NULL,"
" `duration_in_traffic` int NOT NULL,"
" `Orig_lat` double NOT NULL,"
" `Orig_lng` double NOT NULL,"
" `Orig_address` longtext ,"
" `Dest_lat` double NOT NULL,"
" `Dest_lng` double NOT NULL,"
" `Dest_address` longtext ,"
" PRIMARY KEY (`request_no`)"
") ENGINE=InnoDB")

for name, ddl in TABLES_1.iteritems():
try:
print("Creating table {}: ".format(name), end='')
cursor.execute(ddl)
except mysql.connector.Error as err:
if err.errno == errorcode.ER_TABLE_EXISTS_ERROR:
print("already exists.")
else:
print(err.msg)
else:
print("OK")

TABLES_2={}
TABLES_2['leg_data'] = (
"CREATE TABLE `leg_data` ("
" `leg_no` int(11) NOT NULL AUTO_INCREMENT,"
" `request_no` int(11) NOT NULL,"
" `summary` text ,"
" `warnings` text ,"
" `leg_distance` int ,"
" `leg_duration` int ,"
" `leg_travel_mode` longtext ,"
" `leg_Orig_lat` double ,"
" `leg_Orig_lng` double ,"
" `leg_Dest_lat` double ,"
" `leg_Dest_lng` double ,"
" `leg_html_inst` longtext ,"
" `leg_polyline` longtext ,"
" PRIMARY KEY (`leg_no`),"
" FOREIGN KEY (`request_no`)"
" REFERENCES `master` (`request_no`)"
") ENGINE=InnoDB")

for name, ddl in TABLES_2.iteritems():
try:
print("Creating table {}: ".format(name), end='')
cursor.execute(ddl)
except mysql.connector.Error as err:
if err.errno == errorcode.ER_TABLE_EXISTS_ERROR:
print("already exists.")
else:
print(err.msg)
else:
print("OK")

cursor.close()
cnx.close()