search

pudo / dataset

Easy-to-use data handling for SQL data stores with support for implicit table creation, bulk loading, and transactions.
https://dataset.readthedocs.org/
MIT License
4.78k stars 298 forks source link

uppercase column names #270

Closed codefatherru closed 4 years ago

codefatherru commented 5 years ago

I suppose this is a bug (I couldn't google it) if you have got database table with column 'Tags' (capitalize first letter) try

table.insert(dict(name='Jane Doe', age=37, country='France', gender='female', tags = 'tag'))
#  lowercase letters only

you will get

sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) duplicate column name: tags [SQL: 'ALTER TABLE csv ADD COLUMN tags TEXT'] (Background on this error at: http://sqlalche.me/e/e3q8)

it comes as if table.insert is caseSensitive when checks for columns, but caseINsensitive when tries to create them

P.S. I hope this issue will save several hours for someone

kwinata commented 4 years ago

Based on this discussion, SQLite seems to have case-insensitive column names. Shall we convert/enforce every column name to be lowercase? We can do that in here. However, this will introduce backward incompatibility. I think a better approach is just to raise a more descriptive error before it goes inside the SQL Alchemy.

pudo commented 4 years ago

This should now throw a hard error now, making this ambiguity easier to debug. We can't really gloss over it in DBMS that make a distinction.