[Python] pandas.DataFrame.to_sql skips rows with missing foreign keys

I am attempting to load a CSV file into a SQL table. The CSV file has the exact same format as the SQL table. All columns are matching names and all data types are validated.

df = pd.read_csv(path, sep=";")
df.to_sql(table_name, schema=schema, con=conn, if_exists="append", index=False)


Blank fields in the CSV are loaded as NULL into the table. However, if such field is a foreign key (even if it is defined as nullable) that row will be skipped throwing no exception.

This is how my target table (PostgreSQL) is defined.

CREATE TABLE
residence_permits (
residence_permits_id serial PRIMARY KEY,
province_id int REFERENCES geo.province (province_id),
country_id int REFERENCES geo.countries (country_id),
total_permitrs int NOT NULL CHECK (total_permits >= 0)
);


Is this the expected beheaviour? If so, how can I work around it?

Continue reading...