MSSQL: Error when creating a new table without primary_keys

[douglasfabretti]

Hi there folks.

I'm new using dataset, I'm evaluating it to a new project that I'm working on.

I just tried to create a new table with no primary key (primary_id=False):

db = dataset.connect('mssql+pymssql://User:Password@Server:1433/database?charset=utf8')
table = db.create_table('test', primary_id=False)
table

And faced the following the exception, I'm not sure if I missed anything or if this is a bug:

Traceback (most recent call last):
  File "src\pymssql.pyx", line 448, in pymssql.Cursor.execute
  File "src\_mssql.pyx", line 1064, in _mssql.MSSQLConnection.execute_query
  File "src\_mssql.pyx", line 1095, in _mssql.MSSQLConnection.execute_query
  File "src\_mssql.pyx", line 1228, in _mssql.MSSQLConnection.format_and_run_query
  File "src\_mssql.pyx", line 1639, in _mssql.check_cancel_and_raise
  File "src\_mssql.pyx", line 1683, in _mssql.maybe_raise_MSSQLDatabaseException
_mssql.MSSQLDatabaseException: (102, b"Incorrect syntax near ')'.DB-Lib error message 20018, severity 15:\nGeneral SQL Server error: Check messages from the SQL Server\n")

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "C:\Users\user\AppData\Local\Programs\Python\Python36-32\lib\site-packages\sqlalchemy\engine\base.py", line 1193, in _execute_context
    context)
  File "C:\Users\user\AppData\Local\Programs\Python\Python36-32\lib\site-packages\sqlalchemy\engine\default.py", line 509, in do_execute
    cursor.execute(statement, parameters)
  File "src\pymssql.pyx", line 465, in pymssql.Cursor.execute
pymssql.ProgrammingError: (102, b"Incorrect syntax near ')'.DB-Lib error message 20018, severity 15:\nGeneral SQL Server error: Check messages from the SQL Server\n")

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "C:\Users\user\AppData\Local\Programs\Python\Python36-32\lib\site-packages\dataset\table.py", line 586, in __repr__
    return '<Table(%s)>' % self.table.name
  File "C:\Users\user\AppData\Local\Programs\Python\Python36-32\lib\site-packages\dataset\table.py", line 47, in table
    self._sync_table(())
  File "C:\Users\user\AppData\Local\Programs\Python\Python36-32\lib\site-packages\dataset\table.py", line 248, in _sync_table
    self._table.create(self.db.executable, checkfirst=True)
  File "C:\Users\user\AppData\Local\Programs\Python\Python36-32\lib\site-packages\sqlalchemy\sql\schema.py", line 779, in create
    checkfirst=checkfirst)
  File "C:\Users\user\AppData\Local\Programs\Python\Python36-32\lib\site-packages\sqlalchemy\engine\base.py", line 1549, in _run_visitor
    **kwargs).traverse_single(element)
  File "C:\Users\user\AppData\Local\Programs\Python\Python36-32\lib\site-packages\sqlalchemy\sql\visitors.py", line 121, in traverse_single
    return meth(obj, **kw)
  File "C:\Users\user\AppData\Local\Programs\Python\Python36-32\lib\site-packages\sqlalchemy\sql\ddl.py", line 791, in visit_table
    include_foreign_key_constraints=include_foreign_key_constraints
  File "C:\Users\user\AppData\Local\Programs\Python\Python36-32\lib\site-packages\sqlalchemy\engine\base.py", line 948, in execute
    return meth(self, multiparams, params)
  File "C:\Users\user\AppData\Local\Programs\Python\Python36-32\lib\site-packages\sqlalchemy\sql\ddl.py", line 68, in _execute_on_connection
    return connection._execute_ddl(self, multiparams, params)
  File "C:\Users\user\AppData\Local\Programs\Python\Python36-32\lib\site-packages\sqlalchemy\engine\base.py", line 1009, in _execute_ddl
    compiled
  File "C:\Users\user\AppData\Local\Programs\Python\Python36-32\lib\site-packages\sqlalchemy\engine\base.py", line 1200, in _execute_context
    context)
  File "C:\Users\user\AppData\Local\Programs\Python\Python36-32\lib\site-packages\sqlalchemy\engine\base.py", line 1413, in _handle_dbapi_exception
    exc_info
  File "C:\Users\user\AppData\Local\Programs\Python\Python36-32\lib\site-packages\sqlalchemy\util\compat.py", line 265, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "C:\Users\user\AppData\Local\Programs\Python\Python36-32\lib\site-packages\sqlalchemy\util\compat.py", line 248, in reraise
    raise value.with_traceback(tb)
  File "C:\Users\user\AppData\Local\Programs\Python\Python36-32\lib\site-packages\sqlalchemy\engine\base.py", line 1193, in _execute_context
    context)
  File "C:\Users\user\AppData\Local\Programs\Python\Python36-32\lib\site-packages\sqlalchemy\engine\default.py", line 509, in do_execute
    cursor.execute(statement, parameters)
  File "src\pymssql.pyx", line 465, in pymssql.Cursor.execute
sqlalchemy.exc.ProgrammingError: (pymssql.ProgrammingError) (102, b"Incorrect syntax near ')'.DB-Lib error message 20018, severity 15:\nGeneral SQL Server error: Check messages from the SQL Server\n") [SQL: '\nCREATE TABLE test (\n)\n\n'] (Background on this error at: http://sqlalche.me/e/f405)

[pudo]

You're attempting to create a table without columns, and I guess it's fair for MSSQL not to be on board with this :)

What happens is that merely running repr(table) will attempt to create the table, so running this instead should work:

db = dataset.connect('mssql+pymssql://User:Password@Server:1433/database?charset=utf8')
table = db.create_table('test', primary_id=False)
table.create_column('some_column')
table