表列的SQLalchemy替代名称

Question

我知道我的问题一定很简单，但我找不到任何直接答案。

我正在用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. 如何为上述现有列设置标签，以避免使用空格的当前名称？

2. 奖励问题：映射为类的优点是什么优点，而不是映射为表，因为它们的内容/工作相似（但在课堂上有点更大）？

编辑额外的疑问：

您能告诉我这个声明有什么问题，以便我可以将其用于简单的选择？ stmt = select（chicago_schools_manual）.Where（chicago_schools.columns ['School of School'] =='Charles'）。限制（20）

Output exceeds the size limit. Open the full output data in a text editor
---------------------------------------------------------------------------
Exception                                 Traceback (most recent call last)
File c:\Users\fabio\AppData\Local\Programs\Python\Python310\lib\site-packages\ibm_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:\Users\fabio\AppData\Local\Programs\Python\Python310\lib\site-packages\sqlalchemy\engine\base.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',)]

如果我逐步使用它建议它有效：

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

Answer 1

您可以使用key =“ some_name”来引用与表中实际列名不同的名称的列。例如，

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')]

回复：奖励问题 - 请参见

有什么区别sqlalchemy core和orm？