pd.DataFrameのto_sql()で失敗した行をスキップする方法について

# はじめに
Pandasを使っていて、pd.DataFrameをDBにINSERTする際にto_sql()メソッドでやりたいことがうまく出来なかったのが解決できたのでメモとして残す。
※この方法で数万レコードをINSERTしようとすると時間がかなりかかったので要注意
## やりたいこと
pd.DataFrameをDBにINSERTする際にNotNull制約違反や主キー制約違反が発生した場合にその行をスキップしてINSERTしたい。
具体的には、以下のようなデータフレームを主キー制約がcompanyにNotNull制約がestablished_inにかけられたテーブルにto_sql()メソッドでINSERTしようとするとエラーになり全くINSERTされないが、INSERTできるレコードはINSERTして失敗したレコードはINSERTしないようにしたい。


<div>
<style scoped>
    .dataframe tbody tr th:only-of-type {
        vertical-align: middle;
    }

    .dataframe tbody tr th {
        vertical-align: top;
    }

    .dataframe thead th {
        text-align: right;
    }
</style>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>company</th>
      <th>established_in</th>
      <th>founder</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>0</th>
      <td>A</td>
      <td>1900.0</td>
      <td>Alice</td>
    </tr>
    <tr>
      <th>1</th>
      <td>B</td>
      <td>1925.0</td>
      <td>Bob</td>
    </tr>
    <tr>
      <th>2</th>
      <td>C</td>
      <td>NaN</td>
      <td>Charlie</td>
    </tr>
    <tr>
      <th>3</th>
      <td>A</td>
      <td>1995.0</td>
      <td>Ahn</td>
    </tr>
    <tr>
      <th>4</th>
      <td>D</td>
      <td>2010.0</td>
      <td>David</td>
    </tr>
  </tbody>
</table>
</div>

ちなみに、普通に、

```Python
with engine.begin() as conn:
    df_fail.to_sql('company', if_exists='append', index=False, con=conn)
```

してあげると、

```
(psycopg2.errors.NotNullViolation) null value in column "established_in" violates not-null constraint
DETAIL:  Failing row contains (C, null, Charlie).

[SQL: INSERT INTO company (company, established_in, founder) VALUES (%(company)s, %(established_in)s, %(founder)s)]
[parameters: ({'company': 'A', 'established_in': 1900.0, 'founder': 'Alice'}, {'company': 'B', 'established_in': 1925.0, 'founder': 'Bob'}, {'company': 'C', 'established_in': None, 'founder': 'Charlie'}, {'company': 'A', 'established_in': 1995.0, 'founder': 'Ahn'}, {'company': 'D', 'established_in': 2010.0, 'founder': 'David'})]
(Background on this error at: http://sqlalche.me/e/14/gkpj)
```

となり、全くINSERTされない。

## ソースコード
JupyterLabで以下を実行した。なお、DB側でcompanyには主キー制約がestablished_inにはNotNull制約がかけられている。

```python
import numpy as np
import pandas as pd
import psycopg2
import sqlalchemy
from IPython.display import display
```


```python
engine = sqlalchemy.create_engine(url='postgresql://postgres:postgres@192.168.56.101/mydb1')
```


```python
df_fail = pd.DataFrame({'company': ['A', 'B', 'C', 'A', 'D'], 
                        'established_in': [1900, 1925, np.nan, 1995, 2010],
                        'founder': ['Alice', 'Bob', 'Charlie', 'Ahn', 'David' ]})
df_fail
# 主キー制約違反・NotNull制約違反
```




<div>
<style scoped>
    .dataframe tbody tr th:only-of-type {
        vertical-align: middle;
    }

    .dataframe tbody tr th {
        vertical-align: top;
    }

    .dataframe thead th {
        text-align: right;
    }
</style>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>company</th>
      <th>established_in</th>
      <th>founder</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>0</th>
      <td>A</td>
      <td>1900.0</td>
      <td>Alice</td>
    </tr>
    <tr>
      <th>1</th>
      <td>B</td>
      <td>1925.0</td>
      <td>Bob</td>
    </tr>
    <tr>
      <th>2</th>
      <td>C</td>
      <td>NaN</td>
      <td>Charlie</td>
    </tr>
    <tr>
      <th>3</th>
      <td>A</td>
      <td>1995.0</td>
      <td>Ahn</td>
    </tr>
    <tr>
      <th>4</th>
      <td>D</td>
      <td>2010.0</td>
      <td>David</td>
    </tr>
  </tbody>
</table>
</div>




```python
for row in range(len(df_fail)):
    record = df_fail.iloc[[row]]
    try:
        with engine.begin() as conn:
            record.to_sql('company', if_exists='append', index=False, con=conn)
            print('Pass')
    except sqlalchemy.exc.IntegrityError as e:
        print('IntegrityError')
        print(e)
    except Exception as e:
        print('Error')
        print(e)
    finally:
        display(record)
        print('\n\n')
```

    Pass
    


<div>
<style scoped>
    .dataframe tbody tr th:only-of-type {
        vertical-align: middle;
    }

    .dataframe tbody tr th {
        vertical-align: top;
    }

    .dataframe thead th {
        text-align: right;
    }
</style>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>company</th>
      <th>established_in</th>
      <th>founder</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>0</th>
      <td>A</td>
      <td>1900.0</td>
      <td>Alice</td>
    </tr>
  </tbody>
</table>
</div>


    
    
    
    Pass
    


<div>
<style scoped>
    .dataframe tbody tr th:only-of-type {
        vertical-align: middle;
    }

    .dataframe tbody tr th {
        vertical-align: top;
    }

    .dataframe thead th {
        text-align: right;
    }
</style>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>company</th>
      <th>established_in</th>
      <th>founder</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>1</th>
      <td>B</td>
      <td>1925.0</td>
      <td>Bob</td>
    </tr>
  </tbody>
</table>
</div>


    
    
    
    IntegrityError
    (psycopg2.errors.NotNullViolation) null value in column "established_in" violates not-null constraint
    DETAIL:  Failing row contains (C, null, Charlie).
    
    [SQL: INSERT INTO company (company, established_in, founder) VALUES (%(company)s, %(established_in)s, %(founder)s)]
    [parameters: {'company': 'C', 'established_in': None, 'founder': 'Charlie'}]
    (Background on this error at: http://sqlalche.me/e/14/gkpj)
    


<div>
<style scoped>
    .dataframe tbody tr th:only-of-type {
        vertical-align: middle;
    }

    .dataframe tbody tr th {
        vertical-align: top;
    }

    .dataframe thead th {
        text-align: right;
    }
</style>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>company</th>
      <th>established_in</th>
      <th>founder</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>2</th>
      <td>C</td>
      <td>NaN</td>
      <td>Charlie</td>
    </tr>
  </tbody>
</table>
</div>


    
    
    
    IntegrityError
    (psycopg2.errors.UniqueViolation) duplicate key value violates unique constraint "constraint_pkey"
    DETAIL:  Key (company)=(A) already exists.
    
    [SQL: INSERT INTO company (company, established_in, founder) VALUES (%(company)s, %(established_in)s, %(founder)s)]
    [parameters: {'company': 'A', 'established_in': 1995.0, 'founder': 'Ahn'}]
    (Background on this error at: http://sqlalche.me/e/14/gkpj)
    


<div>
<style scoped>
    .dataframe tbody tr th:only-of-type {
        vertical-align: middle;
    }

    .dataframe tbody tr th {
        vertical-align: top;
    }

    .dataframe thead th {
        text-align: right;
    }
</style>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>company</th>
      <th>established_in</th>
      <th>founder</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>3</th>
      <td>A</td>
      <td>1995.0</td>
      <td>Ahn</td>
    </tr>
  </tbody>
</table>
</div>


    
    
    
    Pass
    


<div>
<style scoped>
    .dataframe tbody tr th:only-of-type {
        vertical-align: middle;
    }

    .dataframe tbody tr th {
        vertical-align: top;
    }

    .dataframe thead th {
        text-align: right;
    }
</style>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>company</th>
      <th>established_in</th>
      <th>founder</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>4</th>
      <td>D</td>
      <td>2010.0</td>
      <td>David</td>
    </tr>
  </tbody>
</table>
</div>


    
    
    
    


```python
with engine.begin() as conn:
    data_on_db = pd.read_sql('SELECT * FROM company;', con=conn)
data_on_db
```




<div>
<style scoped>
    .dataframe tbody tr th:only-of-type {
        vertical-align: middle;
    }

    .dataframe tbody tr th {
        vertical-align: top;
    }

    .dataframe thead th {
        text-align: right;
    }
</style>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>company</th>
      <th>established_in</th>
      <th>founder</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>0</th>
      <td>A</td>
      <td>1900</td>
      <td>Alice</td>
    </tr>
    <tr>
      <th>1</th>
      <td>B</td>
      <td>1925</td>
      <td>Bob</td>
    </tr>
    <tr>
      <th>2</th>
      <td>D</td>
      <td>2010</td>
      <td>David</td>
    </tr>
  </tbody>
</table>
</div>



確かに例外処理を使ってINSERTできるものだけINSERTできた（ただし、主キー制約違反の場合上の行からINSERTされてしまう点に注意）。

# おわりに
失敗した行だけ集めたpd.DataFrameを作っておけば、どこがINSERTされなかったのかわかるので便利なはず。