MySQL Forums
Forum List  »  Connector/Python

Re: Python Connector Create Tables Error
Posted by: Gerard Casey
Date: January 27, 2015 02:28AM

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()

Options: ReplyQuote


Subject
Written By
Posted
Re: Python Connector Create Tables Error
January 27, 2015 02:28AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.