Adding overwrite or ignore behavior for row conflicts.
State: closed

@cvonsteg started the effort to implement this. Since they are unable to continue and this is a highly desired feature, I made an attempt to clean up the code and fix merge conflicts. I would like to share the credit for this PR (I do not know how this works).

A new parameter is added to `to_sql`: `on_row_conflct`

    on_row_conflict : {'fail', 'overwrite', 'append'},
        default: 'fail'
        Determine insertion behavior in case of a primary key clash.
        - 'fail': Do nothing to handle primary key clashes, will raise an Error.
        - 'overwrite': Update existing rows in database with primary key clashes,
          and append the remaining rows with non-conflicting primary keys
        - 'append': Ignore incoming rows with primary key clashes, and
          insert only the incoming rows with non-conflicting primary keys




- [x] closes #14553, continuation of #29636
- [x] [Tests added and passed](https://pandas.pydata.org/pandas-docs/dev/development/contributing_codebase.html#writing-tests) if fixing a bug or adding a new feature
- [x] All [code checks passed](https://pandas.pydata.org/pandas-docs/dev/development/contributing_codebase.html#pre-commit).
- [ ] Added an entry in the latest `doc/source/whatsnew/vX.X.X.rst` file if fixing a bug or adding a new feature.


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

Comments:
Sqlalchemy handles row conflicts differently depending on the database dialect. E.g: for postgress and sqlite the implementation is similar to what was originally proposed in the original PR: `on_conflict_do_nothing` or `on_conflict_do_update`, for mysql on the other hand this is handled with `on_duplicate_key_update`.
This pull request is stale because it has been open for thirty days with no activity. Please [update](https://pandas.pydata.org/pandas-docs/stable/development/contributing.html#updating-your-pull-request) and respond to this comment if you're still interested in working on this.
@LSturtew seems little is missing to finalize this. Thank you for your effort and keep up the great work, lots of people are - and will be - grateful for this =)

@LSturtew few months passed, do you still have the capacity to complete this PR?
can we run this again? the logs for the tests are gone... Or what seems to be missing? I guess it is only a problem with the Docstrings and types, right?
I have added a pr for some small docstring changes to the repo of @LSturtew, because if I create a new PR to the pandas main branch all the colab would be lost I guess?
Otherwise, if you (LSturtew) give editing rights to maintainers I can do it directly (https://docs.github.com/en/pull-requests/collaborating-with-pull-requests/working-with-forks/allowing-changes-to-a-pull-request-branch-created-from-a-fork)
> https://docs.github.com/en/pull-requests/collaborating-with-pull-requests/working-with-forks/allowing-changes-to-a-pull-request-branch-created-from-a-fork

The box `Allow edits and access to secrets by maintainers` is checked

okay, i can't get it to work... maybe I'm just stuck in my thoughts
```
ERROR: Permission to LSturtew/pandas.git denied to JustinGuese.
fatal: Could not read from remote repository.

Please make sure you have the correct access rights
and the repository exists.
```
Same when trying to do the same push to pandas-dev/pandas.git

I'm:
1. `gh pr checkout -R pandas-dev/pandas 46232`
2. adding my changes, git add & git commit
3. `git push git@github.com:LSturtew/pandas.git sql-upsert:sql-upsert`

what am I doing wrong? Do I need to create a separate PR? But I do not want the main work by everyone to be lost
Thanks for the pull request, but it appears to have gone stale. If interested in continuing, please merge in the main branch, address any review comments and/or failing tests, and we can reopen.