create_table_from_csv fails with MySql

[troelskn]

I tried to modify the hello_world example to use MySql, but get the following error, suggesting that create_table_from_csv doesn't use valid ddl for MySql?

$ python prepare_data.py 
loading data...
Traceback (most recent call last):
  File "prepare_data.py", line 31, in <module>
    create_id=True    
  File "/Library/Python/2.7/site-packages/cubes-0.8.0-py2.7.egg/cubes/tutorial/sql.py", line 37, in create_table_from_csv
    table.create()
  File "/Library/Python/2.7/site-packages/sqlalchemy/schema.py", line 583, in create
    checkfirst=checkfirst)
  File "/Library/Python/2.7/site-packages/sqlalchemy/engine/base.py", line 2234, in _run_visitor
    conn._run_visitor(visitorcallable, element, **kwargs)
  File "/Library/Python/2.7/site-packages/sqlalchemy/engine/base.py", line 1904, in _run_visitor
    **kwargs).traverse_single(element)
  File "/Library/Python/2.7/site-packages/sqlalchemy/sql/visitors.py", line 86, in traverse_single
    return meth(obj, **kw)
  File "/Library/Python/2.7/site-packages/sqlalchemy/engine/ddl.py", line 86, in visit_table
    self.connection.execute(schema.CreateTable(table))
  File "/Library/Python/2.7/site-packages/sqlalchemy/engine/base.py", line 1405, in execute
    params)
  File "/Library/Python/2.7/site-packages/sqlalchemy/engine/base.py", line 1490, in _execute_ddl
    compiled = ddl.compile(dialect=dialect)
  File "/Library/Python/2.7/site-packages/sqlalchemy/sql/expression.py", line 1724, in compile
    return self._compiler(dialect, bind=bind, **kw)
  File "/Library/Python/2.7/site-packages/sqlalchemy/schema.py", line 2872, in _compiler
    return dialect.ddl_compiler(dialect, self, **kw)
  File "/Library/Python/2.7/site-packages/sqlalchemy/engine/base.py", line 699, in __init__
    self.string = self.process(self.statement)
  File "/Library/Python/2.7/site-packages/sqlalchemy/engine/base.py", line 718, in process
    return obj._compiler_dispatch(self, **kwargs)
  File "/Library/Python/2.7/site-packages/sqlalchemy/sql/visitors.py", line 59, in _compiler_dispatch
    return getter(visitor)(self, **kw)
  File "/Library/Python/2.7/site-packages/sqlalchemy/sql/compiler.py", line 1389, in visit_create_table
    not first_pk
  File "/Library/Python/2.7/site-packages/sqlalchemy/dialects/mysql/base.py", line 1369, in get_column_specification
    self.dialect.type_compiler.process(column.type)
  File "/Library/Python/2.7/site-packages/sqlalchemy/engine/base.py", line 764, in process
    return type_._compiler_dispatch(self)
  File "/Library/Python/2.7/site-packages/sqlalchemy/sql/visitors.py", line 59, in _compiler_dispatch
    return getter(visitor)(self, **kw)
  File "/Library/Python/2.7/site-packages/sqlalchemy/sql/compiler.py", line 1727, in visit_string
    return self.visit_VARCHAR(type_)
  File "/Library/Python/2.7/site-packages/sqlalchemy/dialects/mysql/base.py", line 1656, in visit_VARCHAR
    self.dialect.name)
sqlalchemy.exc.CompileError: (in table 'ft_irbd_balance', column 'category'): VARCHAR requires a length on dialect mysql

[Stiivi]

Thank you for reporting. The method for loading data to CSV is just for tutorial purposes and is just "quick hack". I do not think that full-featured CSV loading method should be part of Cubes, as the ETL is out-of-scope of this framework.

I was thinking to remove the tutorial helpers completely and add tutorial dependency on Brewery which has better functions to handle data piping between different storages.

For the time being, try to load data this way:

http://dev.mysql.com/doc/refman/5.0/en/mysqlimport.html

Meanwhile I will think of a reasonable fix without adding too much complexity. Perhaps defaulting for strings to be 200-500 characters long for tutorials would be enough... What do you think?

[Stiivi]

Fixed with default str length.

[troelskn]

I can confirm it works on my machine, with this change.

The default is probably fine (Although perhaps 255 is a better choice than 256). With many columns, the table could get rather large in storage, but if that's an issue, I guess one should just load the table manually instead. For defaults it seems reasonable.