refactor: deprecate usage of `cursor.execute` statements in favor of the in class implementation of `execute`.
- [ ] closes #xxxx (Replace xxxx with the GitHub issue number)
- [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).
- [x] Added [type annotations](https://pandas.pydata.org/pandas-docs/dev/development/contributing_codebase.html#type-hints) to new arguments/methods/functions.
- [x] Added an entry in the latest `doc/source/whatsnew/vX.X.X.rst` file if fixing a bug or adding a new feature.

Hello @WillAyd.
So this is the follow up of https://github.com/pandas-dev/pandas/pull/60376.
I updated the code base to use panda's `execute` implementation as much as possible. I couldn't replace all places and a simple `git grep '.exec' -- pandas/io/sql.py` will show you that. Anyways what in my opinion is worth mentioning and asking for a review is in the following:
1. [SQLTable._execute_insert](https://github.com/pandas-dev/pandas/blob/ae42f3e1c1b7af55059921b41fd61710fe2dd785/pandas/io/sql.py#L987)
2. [SQLTable._execute_insert_multi](https://github.com/pandas-dev/pandas/blob/ae42f3e1c1b7af55059921b41fd61710fe2dd785/pandas/io/sql.py#L1003)
3. [SQLiteTable._execute_insert](https://github.com/pandas-dev/pandas/blob/ae42f3e1c1b7af55059921b41fd61710fe2dd785/pandas/io/sql.py#L2522)
4. [SQLiteTable._execute_insert_multi](https://github.com/pandas-dev/pandas/blob/ae42f3e1c1b7af55059921b41fd61710fe2dd785/pandas/io/sql.py#L2527)

This should be no problem because we can always wrap that execution around a `try-except` block (as I did in SQLiteTable._execute_insert)

Would you mind taking a look and LMK what you think while in draft ? Hello @WillAyd ,

All tests passed [here](https://github.com/pandas-dev/pandas/pull/60748/commits/71e0d24548014cd638b435a9494629576ae1a074) and therefore I'm opening this PR to review. I have updated the documentation :). Looks like this PR needs a rebase with the main branch Thanks @gmcrocetti 