Hi;
I don't claim to be an expert in MySQL. The following code was largely
supplied to me by someone who was. I don't really understand foreign keys.
He wrote this off the top of his head, and it's throwing an error. Here's
the python code:
def catTree():
user, passwd, db, host = login()
database = MySQLdb.connect(host, user, passwd, db)
cursor = database.cursor()
cursor.execute('''create table if not exists categories
(ID int(3) unsigned primary key,
Category varchar(40),
Parent varchar(40))''')
cursor.execute('select Category, Parent from categories;')
data = cursor.fetchall()
cursor.execute('select Category from categories order by Parent, ID')
print data
Categories = [itm[0] for itm in cursor] #untuple single column
if len(Categories) > 0:
cursor.execute('select Parent from categories order by Parent, ID')
Parents = [itm[0] for itm in cursor]
MAXLEVEL = 15
cursor.execute('''create table if not exists categories
(ID integer auto_increment primary key,
Name varchar(40) not null,
unique (Name)
)''')
cursor.execute('''create table if not exists Relationship
(ID integer auto_increment primary key,
Parent integer not null foreign key references categories (ID),
Child integer not null foreign key references categories (ID),
check (Parent <> Child) );''')
# get top level
print 'ok'
cursor.execute('select Name from categories order by Name')
theTree = expand(cursor.fetchall())
getChildren(theTree)
connection.commit()
return printTree(theTree)
else:
return ['There are no categories yet.']
Here's the error:
[Thu Nov 19 10:59:24 2009] [error] [client 208.84.198.58] from catTree
import catTree
[Thu Nov 19 10:59:24 2009] [error] [client 208.84.198.58] File
"/var/www/html/angrynates.com/cart/catTree.py", line 74, in ?
[Thu Nov 19 10:59:24 2009] [error] [client 208.84.198.58] catTree()
[Thu Nov 19 10:59:24 2009] [error] [client 208.84.198.58] File
"/var/www/html/angrynates.com/cart/catTree.py", line 59, in catTree
[Thu Nov 19 10:59:24 2009] [error] [client 208.84.198.58]
cursor.execute('''create table if not exists Relationship
[Thu Nov 19 10:59:24 2009] [error] [client 208.84.198.58] File
"/usr/lib64/python2.4/site-packages/MySQLdb/cursors.py", line 163, in
execute
[Thu Nov 19 10:59:24 2009] [error] [client 208.84.198.58]
self.errorhandler(self, exc, value)
[Thu Nov 19 10:59:24 2009] [error] [client 208.84.198.58] File
"/usr/lib64/python2.4/site-packages/MySQLdb/connections.py", line 35, in
defaulterrorhandler
[Thu Nov 19 10:59:24 2009] [error] [client 208.84.198.58] raise
errorclass, errorvalue
[Thu Nov 19 10:59:24 2009] [error] [client 208.84.198.58]
_mysql_exceptions.ProgrammingError: (1064, "You have an error in your SQL
syntax; check the manual that corresponds to your MySQL server version for
the right syntax to use near 'foreign key references categories
(ID),\\n Child integer not null foreign key' at line 3")
Please advise.
TIA,
Victor
