Sunday, 10 July 2022

SQLAlchemy Insert() from multiples sources

I have 2 tables that provide information about employees and departments.

I will query information from those tables and insert it to a new third table, that must contains other info than such avaible from the previews 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. new table as columns as location and start_date that I would like to provide manually when executing the insert code above. So, what should I aggregate such values to the insert().from_select() above?

  2. Looking at the select statement, I choose several columns from the same table (e.g. employees.columns['emp_id'], employees.columns['f_name']). Can´t I use something like "employees.columns['emp_id','f_name']" the make the statement smaller?

obs : I´ve set up an default value for the Location and StartDate tables just to avoid leaving them null



from SQLAlchemy Insert() from multiples sources

No comments:

Post a Comment