Fix #35484 - re-allow sqlalchemy declarative system to use `%` in read_sql.
State: closed

- [x] closes #35484
- [x] tests added / passed
- [x] passes `black pandas`
- [x] passes `git diff upstream/master -u -- "*.py" | flake8 --diff`
- [x] whatsnew entry

* Adds tests to cover sql cases combining parameters and `%`.
* Notably, these tests highlight two regressions in v1.1
  - `pd.read_sql("SELECT 1 %% 2", engine)` fails. See #35871.
  - `pd.read_sql(sqlalchemy.sql.text("SELECT 1 % 2"), engine)` fails. See #35484.
* Fixes second issue by modifying SqlDatabase to not use `no_parameter`, and wrapping string queries in `sql.text()` when no parameters are passed.

I was going nuts trying to keep track of all the cases, so tossed them into a table! I didn't have time to explicitly check v1.05, so would be helpful if someone could verify that column (or I may be able to loop back to it)!

| query |   v1.05 | v.1.1 | PR | issue |
| --- | --- | --- | --- | --- |
| no params, `%` | ❌ | ✅ | ✅ | #34211 (merged) |
| no params, `%%` | ✅ | ❌ | ❌ | #35871 |
| params, `%` | ❌ | ❌ | ❌ |  https://github.com/psycopg/psycopg2/issues/827 |
| params, `%%` | ✅ | ✅ | ✅ | | 
| sqla declarative, `%` | ✅ | ❌ | ✅ | #35484 |

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

Comments:
@TomAugspurger any idea who to ping about sqlalchemy review?
I'm not sure. Perhaps contributors on the issue that caused the regression.

On Wed, Sep 23, 2020 at 3:03 PM jbrockmendel <notifications@github.com>
wrote:

> @TomAugspurger <https://github.com/TomAugspurger> any idea who to ping
> about sqlalchemy review?
>
> —
> You are receiving this because you were mentioned.
> Reply to this email directly, view it on GitHub
> <https://github.com/pandas-dev/pandas/pull/36275#issuecomment-697942716>,
> or unsubscribe
> <https://github.com/notifications/unsubscribe-auth/AAKAOIV77NXROY6LJSU4PL3SHJICTANCNFSM4RF2DVNQ>
> .
>

This pull request is stale because it has been open for thirty days with no activity. Please update or respond to this comment if you're still interested in working on this.
@john-bodley can you take a look at this
I guess this is a known issue with SQLAlchemy (using 1.2.8 per the Pandas specs) using the default connection for certain dialects, i.e., 

```python
from sqlalchemy import create_engine

engine = create_engine('postgresql://postgres:@localhost:5433/postgres', echo=False) 
engine.execute("SELECT 1 % 2")  # TypeError: 'dict' object does not support indexing
```

versus the raw-connection:

```python
from sqlalchemy import create_engine

engine = create_engine('postgresql://postgres:@localhost:5433/postgres', echo=False) 
cursor = engine.raw_connection().cursor() 
cursor.execute("SELECT 1 % 2")
cursor.fetchone()  # (1,)
```

Googling the issue it seems taking the `sql.text(...)` approach as proposed by @machow seems to be a commonly recommended solution. 
can you also merge master
@john-bodley @jreback thanks for your feedback. I'll have some time after next week--will add a whatsnew entry and bring things up to date.
This pull request is stale because it has been open for thirty days with no activity. Please update or respond to this comment if you're still interested in working on this.
closing this as stale, though maybe fixed by https://github.com/pandas-dev/pandas/pull/37534 (in 1.2) in any event.

if this is not the case @machow you can create a new PR.