Adding (Insert or update if key exists) option to .to_sql #14553
State: closed

This PR adds SQL `upsert` functionality to the `to_sql` method. As outlined in the issue [discussion](https://github.com/pandas-dev/pandas/issues/14553#issuecomment-536743090), there will be 2 types of upsert:

1. `upsert_delete` - prioritizes new data from incoming dataframe.  Deletes clashing records in the database and then inserts new dataframe.
2. `upsert_ignore` - prioritizes what is already in the database, over what is coming in from the new dataframe.  Deletes records which already exist, from existing dataframe before inserting the rest into the db.

Both upserts currently only check for **primary key** duplicates.

Please note 1 important change over the design proposal made in #14553 - `upsert_delete` and `upsert_ignore` have now been made options for the `if_exists` arg, rather than the `method` argument.  This was revised, because on second consideration, `upsert` was deemed mutually exclusive to `append` and `replace`, but not to the `method` argument, so making it part of `if_exists` seemed the more sound design.  
 
- [ ] closes #14553 
- [ ] tests added / passed
- [ ] passes `black pandas`
- [ ] passes `git diff upstream/master -u -- "*.py" | flake8 --diff`
- [ ] whatsnew entry

Labels:
name: Enhancement
name: IO SQL
description: to_sql, read_sql, read_sql_query

Comments:
Hello @cvonsteg! Thanks for updating this PR. We checked the lines you've touched for [PEP 8](https://www.python.org/dev/peps/pep-0008) issues, and found:







There are currently no PEP 8 issues detected in this Pull Request. Cheers! :beers: 

##### Comment last updated at 2021-10-06 06:36:06 UTC
Hi - thanks for the PR - can you please run black on your changes?

```Checking black formatting
would reformat /home/vsts/work/1/s/pandas/io/sql.py
All done! 💥 💔 💥
1 file would be reformatted, 867 files would be left unchanged.
Checking black formatting DONE
```
If you merge master the travis failures should now be sorted
@alimcmaster1 - thanks for your help so far.  I just merged the pandas-dev master into my fork, (introduced about 570 commits), and upon re-running pytest on `test_sql.py` got several errors on code I haven't touched.  Whilst the errors are raised in different ways by various tests, it seems they all boil down to the same `TypeError` in  `pandas/core/internals/managers.py`:

![image](https://user-images.githubusercontent.com/28671095/69050073-2924b680-09f9-11ea-8630-6ba2044b73f9.png)

@cvonsteg did you rebuild the C extensions via `python setup.py build_ext --inplace`?
~~@WillAyd no I hadn't done that.  But after rebuilding the C extensions (I can see that it re-compiled several files) I'm getting the same errors.  Just to check - after rebuilding the extensions do I also need to re-run `python -m pip install -e .` ?~~
Scratch this - got it working today
@WillAyd @alimcmaster1 - I have merged master, recompiled binaries, and addressed reviewer feedback.  This seems to have fixed almost everything, however there is still a failed build in Travis, namely job `"3.6, locale" ENV_FILE="ci/deps/travis-36-locale.yaml" PATTERN="((not slow and not network) or (single and db))" LOCALE_OVERRIDE="zh_CN.UTF-8"`, which fails with the error message `No output has been received in the last 10m0s, this potentially indicates a stalled build or something wrong with the build itself.`

I can see that 1 test has failed, and I don't know if that's what is causing the stalled build.  
Running `ci/run_tests.sh` locally indicates that it is `TestReadHtml.test_thousands_macau_index_col[bs4]` which fails:

![image](https://user-images.githubusercontent.com/28671095/69645658-6d99fd00-105e-11ea-9e58-34dc49604dd9.png)

However, what actually stops the build from running, seems to be a database lock in the `sqlite3` tests:

![image](https://user-images.githubusercontent.com/28671095/69645786-a1752280-105e-11ea-9f50-bd4fbdc3d533.png)

I am not sure if it is the locking which is causing the Travis-CI timeouts.  Have you got any tips on what I can do to address this?
@cvonsteg it seems to me that the traceback indicates an issue with IPython and its (SQlite) database history. Can you try with ":memory:" instead of a path as indicated [here](https://ipython.readthedocs.io/en/latest/config/options/terminal.html#configtrait-HistoryAccessor.hist_file)? I'm suggesting this because I saw this [issue on someone using parallel kernels](https://github.com/ipython/ipython/issues/11460) where someone gave this solution to circumvent concurrent accesses (which does not work with an SQlite database file).

Hope this helps! I'm still a newbie with the source code so I can't do much more sorry.
The ‘test_thousands_macau_index_col’ failure is an allowed failure due to beautiful soup versions - there is an issue open for it - so you can ignore that. Would definitely recommend trying @ThibTrip helpful suggestion 
Thanks for the tip @ThibTrip,  however I'm struggling to make this work with pytest.  From what I can tell, setting the IPython history file to `:memory:` impacts how an ipython notebook runs.  I can't see any way to pass this into a `pytest` run.  I've tried launching an ipython session with the above params, and calling the tests from in there - but no luck.  

There are 2 things I find weird. The first is that only 1 of the CI builds is failing;  If the build time-out really is being caused by DB locking, I'd expect it to affect all builds, as the main difference in the builds seems to be the python version, not the DB version.  Secondly, when the DB locking causes the test-suite to stall locally, the whole run breaks, and doesn't finish, whereas in the CI test run, all tests finish, and only then do we get the no output message.    
This is incredible - I've been looking for this type of functionality for months. Seems like it is pretty close to completion. Fingers crossed and best of luck!
@cvonsteg can you merge master?
@WillAyd - I had been merging in `upstream/master` at every stage since you and @alimcmaster1 suggested it initially.  However, I've just done it again, but now when I try to rebuild the C extensions , I get 215 warnings and 17 errors, and a failed compilation - (I'm on macos catalina 10.15.2) do you have any suggestions?
What errors are you getting?

Sent from my iPhone

> On Dec 18, 2019, at 3:47 PM, tinovs <notifications@github.com> wrote:
> 
> ﻿
> @WillAyd - I had been merging in upstream/master at every stage since you and @alimcmaster1 suggested it initially. However, I've just done it again, but now when I try to rebuild the C extensions , I get 215 warnings and 17 errors, and a failed compilation - (I'm on macos catalina 10.15.2) do you have any suggestions?
> 
> —
> You are receiving this because you were mentioned.
> Reply to this email directly, view it on GitHub, or unsubscribe.

The errors seem to be related to the `cmath` header:

![image](https://user-images.githubusercontent.com/28671095/71133191-b96d3b80-21f1-11ea-85c4-9c0c62e568f6.png)

![image](https://user-images.githubusercontent.com/28671095/71133230-ca1db180-21f1-11ea-8873-31ef9524bacd.png)

![image](https://user-images.githubusercontent.com/28671095/71133253-dc97eb00-21f1-11ea-80bd-632a582476b6.png)

The warnings all look to be about type specifiers `_Nonnull, _Nullable or _Null_unspecified`
Does cleaning and rebuilding have any impact? If not did anything change on your computer recently? Screenshots would maybe suggest an issue with Xcode install 

Sent from my iPhone

> On Dec 18, 2019, at 3:56 PM, tinovs <notifications@github.com> wrote:
> 
> ﻿
> The errors seem to be related to the cmath header:
> 
> 
> 
> 
> 
> 
> 
> The warnings all look to be about type specifiers _Nonnull, _Nullable or _Null_unspecified
> 
> —
> You are receiving this because you were mentioned.
> Reply to this email directly, view it on GitHub, or unsubscribe.

Yeah I upgraded to Catalina recently.  From what I gather Apple have made some changes to CommandLineTools again.  I presume that will be the issue.  I haven't managed to fix it yet.  I'll keep trying, but in the mean time, if anyone has tips, they'd be greatly appreciated!
Maybe try a clean reinstall of Xcode?
> Maybe try a clean reinstall of Xcode?

I experienced some issues related to CommandLineTools after upgrading to Cataline. Running the below command to reinstall failed, but my issue was fixed. 😅 Maybe you'll have the same luck.
`xcode-select --install`

Looking forward to this being merged! Good job!

I found some [tips on Xcode+Catalina problems here](https://medium.com/faun/macos-catalina-xcode-homebrew-gems-developer-headaches-cf7b1edf10b7)
Hi @cvonsteg, I forked your pandas fork and played around with the sql-upsert branch. Could not get it to crash (except with datatype mismatches which is to be expected) :+1:.

I can't wait for this to be merged in master (wish I could help...) because I want to propose a PR for automatically adding new columns (I don't mind if it gets rejected I had fun digging around :smile: ). It should be relatively flavor agnostic (it uses ALTER TABLE). It is functional but still needs some work. See the example below.

EDIT: actually it does work with SQLite! I just used the same test with an SQLite database and it ran as expected. I just had to create my table with the primary key directly (this is one of the kind of alterations that is not allowed). 

EDIT2: I cannot fork pandas twice (one time from cvonsteg fork and one time from pandas directly) and want to solve a bug so I had to delete my fork from cvonsteg :disappointed: . The example below still stands and I'll put my branch back as soon as I am done or when SQL upsert is merged into master.

<details>
 <summary>Example sql upsert with columns creation</summary>

```python
import sqlalchemy
import pandas as pd
```

# Config


```python
schema = 'tests'
name = 'test_add_column'
engine = sqlalchemy.create_engine('<YOUR_CONNECTION_STRING>')
```

# Create test DataFrame


```python
df = pd.DataFrame({'test':['foo']})
df.index.name = 'profileid'
df
```




<div>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>test</th>
    </tr>
    <tr>
      <th>profileid</th>
      <th></th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>0</th>
      <td>foo</td>
    </tr>
  </tbody>
</table>
</div>



# Save test DataFrame


```python
df.to_sql(schema = schema, name = name, con = engine, if_exists = 'replace')
engine.execute(f"""ALTER TABLE {schema}.{name}
                   ADD PRIMARY KEY (profileid);
                """)
pd.read_sql(f'SELECT * FROM {schema}.{name}', con = engine, index_col = 'profileid')
```




<div>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>test</th>
    </tr>
    <tr>
      <th>profileid</th>
      <th></th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>0</th>
      <td>foo</td>
    </tr>
  </tbody>
</table>
</div>



# Create new data to save


```python
new_df = pd.DataFrame({'test':['bar'],
                       'test2':[123],
                       'test3':['bar'],
                       'test4':[1.2],
                       'test5':pd.to_datetime([pd.Timestamp('2019-01-01 10:00:00')], utc = True),
                       'test6':[pd.Timestamp('2019-01-01 10:00:00')],
                       'test7':[None],
                       'column with space':['foo']})
new_df.index.name = 'profileid'
new_df
```




<div>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>test</th>
      <th>test2</th>
      <th>test3</th>
      <th>test4</th>
      <th>test5</th>
      <th>test6</th>
      <th>test7</th>
      <th>column with space</th>
    </tr>
    <tr>
      <th>profileid</th>
      <th></th>
      <th></th>
      <th></th>
      <th></th>
      <th></th>
      <th></th>
      <th></th>
      <th></th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>0</th>
      <td>bar</td>
      <td>123</td>
      <td>bar</td>
      <td>1.2</td>
      <td>2019-01-01 10:00:00+00:00</td>
      <td>2019-01-01 10:00:00</td>
      <td>None</td>
      <td>foo</td>
    </tr>
  </tbody>
</table>
</div>



# Save new data


```python
new_df.to_sql(con = engine,
              name = name,
              schema = schema,
              # append also works\
              # (just change the profileid in new_df to something else \
              # than 0 to avoid UniqueViolation)
              if_exists = 'upsert_overwrite',
              add_new_columns = True)
```

    WARNING:root:Added column test2 BIGINT in tests."test_add_column"
    WARNING:root:Added column test3 TEXT in tests."test_add_column"
    WARNING:root:Added column test4 FLOAT(53) in tests."test_add_column"
    WARNING:root:Added column test5 TIMESTAMP WITH TIME ZONE in tests."test_add_column"
    WARNING:root:Added column test6 TIMESTAMP WITHOUT TIME ZONE in tests."test_add_column"
    WARNING:root:Added column test7 TEXT in tests."test_add_column"
    WARNING:root:Added column "column with space" TEXT in tests."test_add_column"



```python
pd.read_sql(f'SELECT * FROM {schema}.{name}', engine, index_col = 'profileid')
```




<div>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>test</th>
      <th>test2</th>
      <th>test3</th>
      <th>test4</th>
      <th>test5</th>
      <th>test6</th>
      <th>test7</th>
      <th>column with space</th>
    </tr>
    <tr>
      <th>profileid</th>
      <th></th>
      <th></th>
      <th></th>
      <th></th>
      <th></th>
      <th></th>
      <th></th>
      <th></th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>0</th>
      <td>bar</td>
      <td>123</td>
      <td>bar</td>
      <td>1.2</td>
      <td>2019-01-01 10:00:00+00:00</td>
      <td>2019-01-01 10:00:00</td>
      <td>None</td>
      <td>foo</td>
    </tr>
  </tbody>
</table>
</div>



</details>
@adriantre @WillAyd thanks for your recommendations, I have tried every combination I can think of, of XCode and xcode-select installations and combinations, and so far no luck.  Scouring google and stackoverflow, it looks like there are numerous people having some form of similar issue.  As @ThibTrip mentioned, he had no problems building it, so it really looks more like an issue with my machine.  For the time being I have merged master, and built the package in a linux docker image.  Tests ran fine there, so now hoping it passes the various CI checks!
Can you push updates? Will take a look again after that; CI is currently in a failed state
@WillAyd - Done.  I should've said before; After I built it in a linux docker image, everything worked fine locally, however, when I pushed the same build failed again.  
@cvonsteg restarted Travis failure but still red so I think related to this PR - can you take a look?
@WillAyd - that is frustrating, but thank you for having a look.  I've tried emulating the build with virtual environments in the past but with no useful results.  I'll try to re-create the exact travis build config to see if I can get some more insightful feedback
*IMPORTANT NOTE:*  I have pushed a version of the code with upsert tests commented out, to verify that the stalled build is definitely caused by the new tests, as I am currently unable to reproduce the stalled build locally.  
Removing both tests passed build.  Re-enabling `to_upsert_keep` test
build failed with only `to_upsert_keep`, trying with only `to_upsert_overwrite` test enabled
Still none-the-wiser around the underlying issue, but at least I now know that the build stall seems to be caused by the `to_upsert_keep` test.  This is surprising, as I thought the `to_upsert_overwrite` would be more likely to cause a db lock.  But I can now progress with investigating this issue more closely
> does sqlalchemy not provide this functionaility? this adding a lot of code to pandas.

@jreback - From what I understand, the sqlalchemy core api does provide `on_conflict` methods, but these are engine specific, and only for `postrgresql` and `mysql` engines.  The idea behind this PR was to make that functionality engine-agnostic, as per the discussion on [issue 14553](https://github.com/pandas-dev/pandas/issues/14553)