[sqlalchemy] foreign key auto-typing broken in sqlite?

[Lance Edgar]

I've recently come across a small issue, maybe not even an important one but I wonder why it's happened. I typically pass None as my Column type whenever I use a ForeignKey, as per the docs at http://www.sqlalchemy.org/docs/reference/sqlalchemy/schema.html#sqlalchemy.schema.Column

"""
If this column also contains a ForeignKey, the type argument may be left as None in which case the type assigned will be that of the referenced column.
"""

Now suddenly this breaks some of my test code which creates tables individually. Consider this example:


from sqlalchemy import *

metadata = MetaData("sqlite:///")

table1 = Table(
'table1', metadata,
Column('id', Integer, primary_key=True),
Column('name', String(50)),
)

table2 = Table(
'table2', metadata,
Column('table1_id', None, ForeignKey('table1.id')),
Column('description', String(255)),
)

# This works fine...
metadata.create_all()

# If I do _not_ call create_all() but instead do this, then I get an error...
table1.create()
table2.create()


Traceback (most recent call last):
File "C:\Documents and Settings\lance\Source\rattail\test.py", line 23, in <module>
table2.create()
File "c:\python25\lib\site-packages\sqlalchemy-0.6.0-py2.5.egg\sqlalchemy\schema.py", line 413, in create
bind.create(self, checkfirst=checkfirst)
File "c:\python25\lib\site-packages\sqlalchemy-0.6.0-py2.5.egg\sqlalchemy\engine\base.py", line 1504, in create
self._run_visitor(ddl.SchemaGenerator, entity, connection=connection, **kwargs)
File "c:\python25\lib\site-packages\sqlalchemy-0.6.0-py2.5.egg\sqlalchemy\engine\base.py", line 1535, in _run_visitor
visitorcallable(self.dialect, conn, **kwargs).traverse_single(element)
File "c:\python25\lib\site-packages\sqlalchemy-0.6.0-py2.5.egg\sqlalchemy\sql\visitors.py", line 77, in traverse_single
return meth(obj, **kw)
File "C:\Python25\lib\site-packages\sqlalchemy-0.6.0-py2.5.egg\sqlalchemy\engine\ddl.py", line 58, in visit_table
self.connection.execute(schema.CreateTable(table))
File "c:\python25\lib\site-packages\sqlalchemy-0.6.0-py2.5.egg\sqlalchemy\engine\base.py", line 1109, in execute
return Connection.executors[c](self, object, multiparams, params)
File "c:\python25\lib\site-packages\sqlalchemy-0.6.0-py2.5.egg\sqlalchemy\engine\base.py", line 1156, in _execute_ddl
compiled_ddl=ddl.compile(dialect=self.dialect),
File "c:\python25\lib\site-packages\sqlalchemy-0.6.0-py2.5.egg\sqlalchemy\sql\expression.py", line 1257, in compile
compiler.compile()
File "c:\python25\lib\site-packages\sqlalchemy-0.6.0-py2.5.egg\sqlalchemy\engine\base.py", line 678, in compile
self.string = self.process(self.statement)
File "c:\python25\lib\site-packages\sqlalchemy-0.6.0-py2.5.egg\sqlalchemy\engine\base.py", line 691, in process
return obj._compiler_dispatch(self, **kwargs)
File "c:\python25\lib\site-packages\sqlalchemy-0.6.0-py2.5.egg\sqlalchemy\sql\visitors.py", line 48, in _compiler_dispatch
return getter(visitor)(self, **kw)
File "c:\python25\lib\site-packages\sqlalchemy-0.6.0-py2.5.egg\sqlalchemy\sql\compiler.py", line 1136, in visit_create_table
first_pk=column.primary_key and not first_pk
File "c:\python25\lib\site-packages\sqlalchemy-0.6.0-py2.5.egg\sqlalchemy\dialects\sqlite\base.py", line 234, in get_column_specification
colspec = self.preparer.format_column(column) + " " + self.dialect.type_compiler.process(column.type)
File "c:\python25\lib\site-packages\sqlalchemy-0.6.0-py2.5.egg\sqlalchemy\engine\base.py", line 734, in process
return type_._compiler_dispatch(self)
File "c:\python25\lib\site-packages\sqlalchemy-0.6.0-py2.5.egg\sqlalchemy\sql\visitors.py", line 48, in _compiler_dispatch
return getter(visitor)(self, **kw)
File "c:\python25\lib\site-packages\sqlalchemy-0.6.0-py2.5.egg\sqlalchemy\sql\compiler.py", line 1442, in visit_null
raise NotImplementedError("Can't generate DDL for the null type")
NotImplementedError: Can't generate DDL for the null type


Interestingly, there is no error as recently as SA 0.6beta3, but the error happens in 0.6.0. Any thoughts?

Lance

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To post to this group, send email to sqlal...@googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.

[Michael Bayer]

On May 15, 2010, at 2:53 PM, Lance Edgar wrote:

> I've recently come across a small issue, maybe not even an important one but I wonder why it's happened. I typically pass None as my Column type whenever I use a ForeignKey, as per the docs at http://www.sqlalchemy.org/docs/reference/sqlalchemy/schema.html#sqlalchemy.schema.Column
>
> """
> If this column also contains a ForeignKey, the type argument may be left as None in which case the type assigned will be that of the referenced column.
> """
>
> Now suddenly this breaks some of my test code which creates tables individually. Consider this example:

yes I will probably remove this from the documentation as I don't really have time to maintain this awkward feature. Please specify the type explicitly.