Probleme bei Relationen mit SQLAlchemy und MySQL

Installation und Anwendung von Datenbankschnittstellen wie SQLite, PostgreSQL, MySQL, der DB-API 2.0 und sonstigen Datenbanksystemen.
Antworten
Benutzeravatar
Whitie
User
Beiträge: 111
Registriert: Sonntag 4. Juni 2006, 12:39
Wohnort: Schulzendorf

Donnerstag 15. März 2007, 20:56

Hi Leute,
ich habe beim Erstellen von Tabellen mit dem folgenden Code ein eigenartiges Problem.
Erstmal der (gekürzte) Code:

Code: Alles auswählen

#!/usr/bin/env python
# -*- coding: UTF-8 -*-

from sqlalchemy import *

from settings import config

meta = BoundMetaData(config['db'])

pre = config['prefix']

school_table = Table(pre + 'school', meta,
                     Column('id', Integer, primary_key = True,
                            autoincrement = True),
                     Column('name', Unicode(60)),
                     mysql_engine = 'innoDB')

job_table = Table(pre + 'job', meta,
                   Column('id', Integer, primary_key = True,
                          autoincrement = True),
                   Column('job', Unicode(40), nullable = False))

candidat_table = Table(pre + 'candidat', meta,
                       Column('id', Integer, primary_key = True,
                              autoincrement = True),
                       Column('name', Unicode(60), nullable = False),
                       Column('front_name', Unicode(60), nullable = False),
                       Column('street', Unicode(60), nullable = False),
                       Column('zipcode', Unicode(60), nullable = False),
                       Column('city', Unicode(60), nullable = False),
                       Column('phone', Unicode(25)),
                       Column('mobile', Unicode(25)),
                       Column('sex', Unicode(1), nullable = False),
                       Column('nationality', Unicode(60)),
                       Column('birthday', Integer),
                       Column('school_id', Integer,
                              ForeignKey(school_table.c.id)),
                       Column('company', Unicode(60)),
                       Column('email', Unicode(60), default = u''),
                       Column('date', Integer, default = 0),
                       Column('day', Unicode(12)),
                       mysql_engine = 'innoDB')

job_candidat_table = Table(pre + 'job_candidat', meta,
                           Column('candidat_id', Integer,
                                  ForeignKey(candidat_table.c.id)),
                           Column('job_id', Integer,
                                  ForeignKey(job_table.c.id)),
                           mysql_engine = 'innoDB')

category_table = Table(pre + 'category', meta,
                       Column('id', Integer, primary_key = True,
                              autoincrement = True),
                       Column('parent', Integer, nullable = False,
                              default = 0),
                       Column('name', Unicode(60), nullable = False),
                       Column('description', Unicode),
                       Column('active', Boolean, default = False),
                       Column('weight', Integer, nullable = False,
                              default = 100),
                       Column('time', Integer, nullable = False),
                       mysql_engine = 'innoDB')

item_table = Table(pre + 'item', meta,
                   Column('id', Integer, primary_key = True,
                          autoincrement = True),
                   Column('text', TEXT, nullable = False),
                   Column('pic', Unicode(60), default = ''),
                   Column('choice', Unicode(1), nullable = False),
                   Column('time', Integer),
                   Column('active', Boolean, default = False),
                   mysql_engine = 'innoDB')

item_category_table = Table(pre + 'item_category', meta,
                            Column('category_id', Integer,
                                   ForeignKey(category_table.c.id)),
                            Column('item_id', Integer,
                                   ForeignKey(item_table.c.id)),
                            mysql_engine = 'innoDB')

if __name__ == '__main__':
    meta.engine.echo = True
    meta.create_all()
Diese Code verursacht den folgenden Traceback:

Code: Alles auswählen

Traceback (most recent call last):
  File "setup_db.py", line 139, in <module>
    meta.create_all()
  File "/usr/lib/python2.5/site-packages/sqlalchemy/schema.py", line 888, in create_all
    connectable.create(self, checkfirst=checkfirst, tables=tables)
  File "/usr/lib/python2.5/site-packages/sqlalchemy/engine/base.py", line 416, in create
    self._run_visitor(self.dialect.schemagenerator, entity, connection=connection, **kwargs)
  File "/usr/lib/python2.5/site-packages/sqlalchemy/engine/base.py", line 440, in _run_visitor
    element.accept_schema_visitor(visitorcallable(self, conn.proxy, connection=conn, **kwargs), traverse=False)
  File "/usr/lib/python2.5/site-packages/sqlalchemy/schema.py", line 908, in accept_schema_visitor
    visitor.visit_metadata(self)
  File "/usr/lib/python2.5/site-packages/sqlalchemy/ansisql.py", line 682, in visit_metadata
    table.accept_schema_visitor(self, traverse=False)
  File "/usr/lib/python2.5/site-packages/sqlalchemy/schema.py", line 269, in accept_schema_visitor
    return visitor.visit_table(self)
  File "/usr/lib/python2.5/site-packages/sqlalchemy/ansisql.py", line 717, in visit_table
    self.execute()
  File "/usr/lib/python2.5/site-packages/sqlalchemy/engine/base.py", line 791, in execute
    return self.proxy(self.buffer.getvalue(), None)
  File "/usr/lib/python2.5/site-packages/sqlalchemy/engine/base.py", line 368, in proxy
    return self._execute_raw(statement, parameters)
  File "/usr/lib/python2.5/site-packages/sqlalchemy/engine/base.py", line 332, in _execute_raw
    self._execute(cursor, statement, parameters, context=context)
  File "/usr/lib/python2.5/site-packages/sqlalchemy/engine/base.py", line 351, in _execute
    raise exceptions.SQLError(statement, parameters, e)
sqlalchemy.exceptions.SQLError: (OperationalError) (1005, "Can't create
 table './assessment/ac_job_candidat.frm' (errno: 150)") '\nCREATE TABLE
 ac_job_candidat (\n\tcandidat_id INTEGER, \n\tjob_id INTEGER, \n\t FOREIGN
 KEY(candidat_id) REFERENCES ac_candidat (id), \n\t FOREIGN KEY(job_id)
 REFERENCES ac_job (id)\n) ENGINE=innoDB\n\n' ()
Was mich daran jetzt fasziniert (vielleicht seh ich auch den Wald vor lauter Bäumen nicht) ist, dass die Many-To-Many Relation von item und category korrekt gebildet wird und die selbe Beziehung zwischen candidat und job den Traceback verursacht.
Ich sitze jetzt seit 3 Tagen nur daran und komme nicht weiter.
Vielleicht kann mir jemand auf die Sprünge helfen?

Danke, Whitie

Edit: Jetzt wo ich den Code ins Forum gepostet habe fällt es mir sofort ins Auge, die job Tabelle ist nicht auf innoDB gesetzt! Problem erledigt. :oops:
Antworten