MySQL Forums
Forum List  »  Connector/Python

Python Connector Create Tables Error
Posted by: Gerard Casey
Date: January 26, 2015 03:54AM

Hi all,

I am a new user and in need of some help. I have posted the code in question below. I am receiving the following error code. I have checked the syntax and verified against the tutorial given in the documents. I am struggling to see where I am going wrong. Many thanks

Creating table leg_data: Cannot add foreign key constraint
Creating table master: OK


from __future__ import print_function
import mysql.connector
from mysql.connector import errorcode

cnx = mysql.connector.connect(user='', password='', host='')

cursor = cnx.cursor()

DB_NAME = 'TEST'

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={}
TABLES['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 ,"
" `summary` text ,"
" PRIMARY KEY (`request_no`)"
") ENGINE=InnoDB")

TABLES['leg_data'] = (
"CREATE TABLE `leg_data` ("
" `leg_no` int(11) NOT NULL AUTO_INCREMENT,"
" `request_no` int(11),"
" `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`),"
" CONSTRAINT `master_ibfk` FOREIGN KEY (`request_no`)"
" REFERENCES `master` (`request_no`)"
") ENGINE=InnoDB")

for name, ddl in TABLES.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
Python Connector Create Tables Error
January 26, 2015 03:54AM


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.