Issue: Preserving column names without needing Pythonic attribute names convention
Author: ketozhang
State: closed
Created: 2018-04-25 18:25:21

I understand that peewee requires storing every column as a the attribute of the Model instance.  @coleifer once told me that `column_name` only preserves SQL column_name however it is impossible to access the column in the Model instance:

> In this way, the actual underlying column-name is still preserved and used, it's just that the accessor for it conforms to a Pythonic naming convention.

I would like to suggest Pandas' convention which is to use `__getitem__` which solves the issue of naming convention such as a variable name starting with a number

```python
>>> df = pd.DataFrame([[1,2,3],['a','b','c']], columns=['1a', '1b', '1c'])

>>> df
  1a 1b 1c
0  1  2  3
1  a  b  c

>>> df.1a
  File "<stdin>", line 1
    df.1a
       ^
SyntaxError: invalid syntax

>>> df['1a']
0    1
1    a
Name: 1a, dtype: object

>>>df.__dict__
{'_data': BlockManager
 Items: Index(['1a', '1b', '1c'], dtype='object')
 Axis 1: RangeIndex(start=0, stop=2, step=1)
 ObjectBlock: slice(0, 3, 1), 3 x 2, dtype: object,
 '_iloc': <pandas.core.indexing._iLocIndexer at 0x1e9928e15c0>,
 '_item_cache': {},
 'is_copy': None}
```

There are many cases where my column name would conflict with Python naming convention. Take for instance a [catalog I am current working with](http://aquarius.elte.hu/glade/Download.html) has the column named `2MASS` and another named `SDSS-DR12` (both fail Python name convention). The task is to import the 'space' delimited text file to SQL via:

```python
class Catalog(BaseModel):
    two_MASS = TextField(column_name='2MASS')
    SDSS_DR12 = TextField(column_name='SDSS-DR12')
    # ... some more fields

df = pd.read_csv('catalog.txt')

# ... do some data cleaning

data = df.dicts('records')
Catalog.insert_many(data).execute()
```

The last line would fail unless in `df`, I were to change the names via `df.rename({'2MASS': 'two_MASS', 'SDSS-DR12':'SDSS_DR12'})`.  I would love to not do this because I expect all column names to be consistent. It is much more expandable to access the column by `Model.__getitem__('column name') ` rather than `getattr(Model, 'column_name')`. For instance:

```python
Catalog.select(Catalog['2MASS']) # equivalent to Catalog.select(Catalog.twoMASS)
```

 @coleifer please let me know if peewee is able to support this in another way.



Columns are exposed as attributes on model classes. As such, they must be valid Python identifiers. Peewee makes allowances for the weird types of column-names you might see by allowing you to specify the `column_name` parameter when defining your field, as you have done in your example model definition.

As long as you're working with the data in Python *as Python objects* it makes sense to me that you'd need to use Pythonic identifiers. As you said, you're importing this from a CSV file ... it's unclear to me why, if you're defining the schema, you wouldn't take that opportunity to make it conventional?

Take a look here for a thorough explanation and rationale: https://launchbylunch.com/posts/2014/Feb/16/sql-naming-conventions/

Peewee supports the above naming conventions, which are considered to be the "80%" use-case, while making it possible to still work with unconventional schemas ("the 20% case"). I think that's how it shall remain.

As an additional note, using the getitem APIs is particularly user-hostile because:

* No tab-completion when debugging
* No validation when code is being interpreted (hence, no AttributeError)
* Additional typing (4 characters for brackets+quotes vs 1 for dot).
* Non-obvious -- fields are model-class attributes, you should access them as you would any class attribute.

> As long as you're working with the data in Python as Python objects it makes sense to me that you'd need to use Pythonic identifiers. As you said, you're importing this from a CSV file ... it's unclear to me why, if you're defining the schema, you wouldn't take that opportunity to make it conventional?

Unfortunately the naming is not up to me. How would you propose naming a column like `2MASS` (Two Micron All-Sky Survey) to a Python identifier that preserves its historical name possibly `TMASS` but by the link you provided it follow pythonic variable names very closely so maybe `tmass`.

You propose for every schema I wish to create via importing some csv, I change the column names on the csv (given I'm importing via list of dicts) so it matches python convention. Then, I have to create a Model with this new columns names while also specifying the old "unconventional" columns names to the Field argument `column_name`. The 2nd step is reasonable but the 1st step shouldn't be needed as my Model knows if I refer to "2MASS" it should recognize it's the same w/e I named the Python conventional attribute by looking at the Field attribute `column_name`. 

Sorry if it's a bit confusing it was difficult to word this out.

Theoretically once you've got your data into your db you don't need to think about what it was originally named in the csv...so we're talking about a one-time data-loading operation...

I'd suggest making a translation table... untested code but should convey the idea hopefully:

```python
class MyModel(Model):
    tmass = FloatField(column_name='2mass')
    # etc...

ttable = {f.column_name: f.name for f in MyModel._meta.sorted_fields}
def translate(in_data):
    return {ttable[k]: v for k, v in in_data.items()}

for row_dict in csv_file:
    MyModel.insert(translate(row)).execute()
```