sqlalchemy insert（）来自倍数源

Question

我有2个表提供有关员工和部门的信息。

我将从这些表中查询信息，然后将其插入一个新的第三个表，该表必须包含除预览2。

stmt = select (
    employees.columns['emp_id'],
    employees.columns['f_name'],
    departments.columns['dept_id_dep'],
    departments.columns['dep_name']
)\
.select_from(
    employees.join(
        departments,
        employees.columns['dep_id'] == departments.columns['dept_id_dep'],
        isouter=True
        )
    )

EandP = Table('EmployeesPlusDepart', metadata,
       Column('Emp_id', String(50), primary_key = True, autoincrement = False), 
       Column('Name', String (50), index = False, nullable = False), 
       Column('Dept_id', String (50), nullable = False),
       Column('Dept_Name', String (50), nullable = False),
       Column('Location', String(50), default = 'CasaDuCarai', nullable = False), 
       Column('Start_date', Date,
           default = date.today() - timedelta(days=5), onupdate = date.today()),
       extend_existing=True, #força a redefinição no metadata
    )

Insert_stmt = insert(EandP).from_select(
    ['Emp_id', 'Name', 'Dept_id', 'Dept_Name'],
    stmt
)

1. 新表作为位置和start_date的其他信息以外的其他信息，我想在执行时要手动提供该信息。上面插入代码。那么，我应该将这些值汇总到上面的insert（）。from_select（）？

2. 查看选择语句，我从同一表（ eg employs.columns ['emp_id']，员工。我可以使用诸如“ 雇员。

OBS：我已经为位置设置了一个默认值，并为了避免将它们留为空

Answer 1

您可以通过创建一个具有自定义编译器行为的自定义表达式。就您而言，我假设您想手动确定特定列的值 - 您可以在选择语句中执行此操作：

from sqlalchemy.sql.expression import Executable, ClauseElement, literal


# Modify your select query to match the destination table
select_statement = select (
    employees.columns['emp_id'].label('Emp_id'),
    employees.columns['f_name'].label('Name'),
    departments.columns['dept_id_dep'].label('Dept_id'),
    departments.columns['dep_name'].label('Dept_id'),
    # Note: I am not 100% sure about the expression below ("literal")
    literal("Some_Location").label("Location")  # Manually set a location
)\
.select_from(
    employees.join(
        departments,
        employees.columns['dep_id'] == departments.columns['dept_id_dep'],
        isouter=True
        )
    )


class InsertFromSelect(Executable, ClauseElement):
    """
    Copied from SQLAlchemy Documentation:
    https://docs.sqlalchemy.org/en/14/core/compiler.html#compiling-sub-elements-of-a-custom-expression-construct
    """
    inherit_cache = False

    def __init__(self, table, select):
        self.table = table
        self.select = select

@compiles(InsertFromSelect)
def _insert_from_select(element, compiler, **kwargs):
    table_formatted = compiler.process(element.table, asfrom=True, **kwargs)
    select_formatted = compiler.process(element.select, asfrom=True, **kwargs)
    return f"INSERT INTO {table_formatted} ({select_formatted})"

# Create your insert statement
insert_statement = InsertFromSelect(
    table=EandP,
    select=select_statement
)

# Execute
session.execute(insert_statement)