I need an option to stop the default "id" column being created on new tables

[zarino]

I'm dumping some data into a database. It's my first time using dataset. I try this…

import dataset

dt = dataset.connect('sqlite:///:memory:')
table = dt['test']
table.insert({"id": "bv56fzi", "name": "This will fail"})

And I get an exception…

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/Library/Python/2.7/site-packages/dataset/persistence/table.py", line 70, in insert
    res = self.database.executable.execute(self.table.insert(row))
  File "/Library/Python/2.7/site-packages/sqlalchemy/engine/base.py", line 1614, in execute
    return connection.execute(statement, *multiparams, **params)
  File "/Library/Python/2.7/site-packages/sqlalchemy/engine/base.py", line 662, in execute
    params)
  File "/Library/Python/2.7/site-packages/sqlalchemy/engine/base.py", line 761, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/Library/Python/2.7/site-packages/sqlalchemy/engine/base.py", line 874, in _execute_context
    context)
  File "/Library/Python/2.7/site-packages/sqlalchemy/engine/base.py", line 1024, in _handle_dbapi_exception
    exc_info
  File "/Library/Python/2.7/site-packages/sqlalchemy/util/compat.py", line 163, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb)
  File "/Library/Python/2.7/site-packages/sqlalchemy/engine/base.py", line 867, in _execute_context
    context)
  File "/Library/Python/2.7/site-packages/sqlalchemy/engine/default.py", line 324, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.IntegrityError: (IntegrityError) datatype mismatch u'INSERT INTO test (id, name) VALUES (?, ?)' ('bv56fzi', 'This will fail')

It took me a while to work out that my id column was conflicting with the id INTEGER PRIMARY KEY created by dataset when the table was made.

I'm not sure whether the id is being created out of necessity (because you can't create a table with no columns) or out of design (because most of your users want an auto-incrementing id column and the default rowid is too obscure?).

Either way, it's a shame you do, because it meant there was seemingly no way for me to use dataset in this particular project. I ended up using Dumptruck which I'd really rather not do.

Are there plans to allow creation of a table without the automatic id INTEGER PRIMARY KEY column?

[pudo]

The reason this is done is because we cannot create a table without columns. As an alternative, we could implement a lazy table creation scheme where the table is only created as "ensure_columns" is first called, not upon database.get_table. Still, would you agree that id should be opt-out rather than opt-in?

[zarino]

Opt-out is good with me. I can see why it would be useful for newbies to have an automatic "id" column (even if it is just a copy of the "rowid" column they'd get anyway).

[abelsonlive]

+1 on this issue. I just spent an hour slamming my head against the wall. what if you just named the default primary key _id so that people wouldn't run into this error when inserting their own id column ?

[dnatag]

I vote for the "opt-out" or designate the name of primary key column user desired

[pudo]

I like the opt-out approach best, too. Would you want to submit a pull request?

[dnatag]

OK. Let me read your codebase first.

On Tuesday, September 3, 2013 at 11:17 AM, Friedrich Lindenberg wrote:

I like the opt-out approach best, too. Would you want to submit a pull request?

—
Reply to this email directly or view it on GitHub (#19 (comment)).

[pudo]

Fixed in #27, thanks @dnatag!