SQLAlchemy insert from select a null value in a not null column

Question

This will seen like a challenge just to know if there is a way to "solve" this issue.

I am studying SQLAlchemy core and now facing insert from select I have this issue

I am selecting info from a table with previews inserted information (multiple info about several schools from chicago). This table has several columns but all I want is 3 of them.

Select_stmt = select(chicago_schools_manual.columns['Name_of_School'], chicago_schools_manual.columns['Safety_Score'], chicago_schools_manual.columns['Location'])

The table is mannually mapped as Table with SqlAlchemy as below :

('School_ID', Column('School ID', Integer(), table=<chicago_schools>, key='School_ID', primary_key=True, nullable=False))
('Name_of_School', Column('Name of School', String(), table=<chicago_schools>, key='Name_of_School'))
('Safety_Score', Column('Safety Score', Float(), table=<chicago_schools>, key='Safety_Score'))
('Location', Column('Location', String(), table=<chicago_schools>))

I am inserting this info into a new table. This is the new table map :

NiceSchools = Table(
   'NiceSchools', metadata, 
   Column('id', Integer, primary_key = True, autoincrement = True), 
   Column('name', String (255), index = True, nullable = False), 
   Column('Safety_Score', Float, nullable = False, default = 0),
   Column('Location', String(50), nullable = False), 
   Column('Start_date', DateTime, default = datetime.today())

and this is the insert statement :

Insert_stmt = insert(NiceSchools).from_select(['name', 'Safety_Score', 'Location'], Select_stmt)

I face 2 issues :

1. The field 'Safety Score" from the origin column, sometimes, is null (the origin table does not have such constraint as not null). So, when I try to insert in the new table, the null values cause an SQL error. I though that having a default value would solve it (trying to insert null would replace for default), but does not. I found some info about an postgree SQL clause "on conflict" that would solve, but I am using DB2. How can I "adjust" the code to avoid the inserting of null info into the new table?

2. The field Name of Schools is a CLOB on origin, and sometimes the names are bigger than the destiny field (current at 255 but where smaller before). So, how can I truncate the incoming string to fit into the destination?

Obs : I am trying to do all this in SqlAlchemy, so, any SQL native function I need to use it in SqlAlchemy.

Thanks in advance!

Answer 1

Barman posted about useful functions above. Thanks!

An example of such use. Specially because such functions are not much described in SQLA website.

Select_stmt = select(func.SUBSTR(chicago_schools_manual.columns['Name_of_School'],1,50), func.coalesce(chicago_schools_manual.columns['Safety_Score'], 999), chicago_schools_manual.columns['Location']).order_by(chicago_schools_manual.columns['Safety_Score'].desc())

and the insert execution (no special function)

Insert_stmt = insert(NiceSchools).from_select(['name', 'Safety_Score', 'Location'], Select_stmt)
with engine.begin() as conn:
    conn.execute(Insert_stmt)