SQLAlchemy alternative names for table columns

I know my question must be very simple but I couldn’t find any straight answer to it.

I am mapping a table with SQlAlchemy :

from sqlalchemy import Table, Column, Integer, String, MetaData, select
metadata = MetaData()
chicago_schools_manual = Table(
   'chicago_schools', metadata, 
   Column('School ID', Integer, primary_key = True), 
   Column('Name of School', String))

1. How to I set up an label for the existing columns above to avoid they current name with spaces?

2. Bonus question : What is the advantage of mapping as as class instead of mapping as table given that their content/efforts are similar (but a little bigger in class)?

Edit extra doubt:

Can you tell me what is wrong with this statement so I can´t use it for a simple select where? stmt = select(chicago_schools_manual).where(chicago_schools.columns['Name of School'] == 'Charles').limit(20)

Output exceeds the size limit. Open the full output data in a text editor
---------------------------------------------------------------------------
Exception                                 Traceback (most recent call last)
File c:UsersfabioAppDataLocalProgramsPythonPython310libsite-packagesibm_db_dbi.py:1287, in Cursor._set_cursor_helper(self)
   1286 try:
-> 1287     num_columns = ibm_db.num_fields(self.stmt_handler)
   1288 except Exception as inst:

Exception: SQLNumResultCols failed: [IBM][CLI Driver][DB2/LINUXX8664] SQL0203N  A reference to column "CHICAGO_SCHOOLS.Name of School" is ambiguous.  SQLSTATE=42702  SQLCODE=-203

During handling of the above exception, another exception occurred:

ProgrammingError                          Traceback (most recent call last)
File c:UsersfabioAppDataLocalProgramsPythonPython310libsite-packagessqlalchemyenginebase.py:1819, in Connection._execute_context(self, dialect, constructor, statement, parameters, execution_options, *args, **kw)
   1818     if not evt_handled:
-> 1819         self.dialect.do_execute(
...
[SQL: SELECT chicago_schools."School ID", chicago_schools."Name of School", chicago_schools."Safety Score", chicago_schools."Location" 
FROM chicago_schools, chicago_schools 
WHERE chicago_schools."Name of School" = ? FETCH FIRST 20 ROWS ONLY]
[parameters: ('Charles',)]

If i use it by the way suggested it works :

stmt = select(chicago_schools_manual.c.Name_of_School).where(chicago_schools_manual.c.Name_of_School == 'Charles').limit(20) 

Answer

You can use key="some_name" to refer to columns by a name that is different from the actual column name in the table. For example,

from sqlalchemy import create_engine, Table, Column, Integer, String, MetaData, select

engine = create_engine("sqlite://")

metadata = MetaData()
chicago_schools_manual = Table(
    "chicago_schools",
    metadata,
    Column("School ID", Integer, primary_key=True, key="school_id"),
    Column("Name of School", String, key="name_of_school"),
)

metadata.create_all(engine)

with engine.begin() as conn:
    conn.execute(
        chicago_schools_manual.insert(), dict(name_of_school="School #1")
    )
"""SQL emitted:
INSERT INTO chicago_schools ("Name of School") VALUES (?)
[generated in 0.00032s] ('School #1',)
"""

with engine.begin() as conn:
    results = conn.execute(
        select(
            chicago_schools_manual.c.school_id,
            chicago_schools_manual.c.name_of_school,
        )
    ).all()
    print(results)  # [(1, 'School #1')]

re: bonus question – See

What is the difference between SQLAlchemy Core and ORM?