title: Naming style for SQL columns in Python
body: <p>Primary keys in databases are commonly named "id", which is problematic when using tools like SQLAlchemy to compose your database layout in Python:</p>
  
  <pre><code>from sqlalchemy.ext.declarative import declarative_base
  from sqlalchemy import Column, Integer
  
  Base = declarative_base()
  
  class User(Base):
      id = Column(Integer, primary_key=True)
      name = Column(String)
  </code></pre>
  
  <p>Obviously, this overwrites the built-in function <a href="https://docs.sqlalchemy.org/en/14/orm/tutorial.html#declare-a-mapping" rel="nofollow noreferrer"><code>id</code></a>. Ok, it is only overwritten in that very small scope of attribute references, however, I might need <code>id</code> in there. The other problem is that syntax highlighting tools treat the name "id" as a function and highlight it accordingly, regardless of the context. This can prominently be seen in <a href="https://docs.sqlalchemy.org/en/14/orm/tutorial.html#declare-a-mapping" rel="nofollow noreferrer">SQL-Alchemy's tutorials</a>. The third one is, it simply feels dirty.</p>
  
  <p>An naive approach to the problem would be to make syntax highlighting aware of the special contex. However, this cannot be a solution since it only disguises the original problem and thereby may cause further problems.</p>
  
  <p>This leads me to the assumption that renaming may be a possible soution. If I renamed the primary key to something like <code>user_id</code>, there would certainly be situations in which I would have to type</p>
  
  <pre><code>current_user = User('Peter')
  print(current_user.user_id)
  </code></pre>
  
  <p>And <code>current_user.user_id</code> looks and feels very redundant and cumbersome to me. This is because I know from the context that <code>current_user</code> is actually an instance of User. Genrally, it does not matter which table is queried and how to variable is name to which the result is assigned to. Hence, prepending "xxx_" to the class variables of <code>Xxx</code> is very obviously a remedy for a symptom that was caused by a whole other problem and is therefore not a good solution.</p>
  
  <p>Other posibilities are pre- or appending an underscore to "id". However, prepending is also not an option since it would interfere with Python's convetion for <a href="https://docs.python.org/3/tutorial/classes.html#private-variables" rel="nofollow noreferrer">non-public API parts</a>. So, from my point of view the following is the best solution available:</p>
  
  <pre><code>class User(Base):
      id_ = Column(Integer, primary_key=True)
      name = Column(String)
  </code></pre>
  
  <p>However, I am new to databases and I don't know how such problems are solved in practice or whether this is considered a problem at all. So, my question is:</p>
  
  <p>How would one name the primary key of tables in SQL-like databases declared using SQLAlchemy and Python3, such that the build-in <code>id</code> is not overwritten and there is minimal redundancy and obfuscation in the resulting code.</p>