ENH: Add table prefixes to to_sql method

[Diadochokinetic]

• closes ENH: Add table prefixes to to_sql method #60422
• Tests added and passed if fixing a bug or adding a new feature
• All code checks passed.
• Added type annotations to new arguments/methods/functions.
• Added an entry in the latest doc/source/whatsnew/vX.X.X.rst file if fixing a bug or adding a new feature.

This PR adds a prefixes parameter to the to_sql method and passes it down to the underlying sqlalchemy.Table class. It takes into account that temporary tables are not stored in a database's meta data and adds temporary table specific methods for _exists_temporary and _drop_temporary_table.

Unfortunately, temporary tables don't work with null pooling, so I had to create additional sqlalchemy fixtures with the default pooling.

TODO:

• case sensitivity check always warns: SQLDatabase.check_case_sensitive checks the databases's meta data. It will never find a temporary table and always throw a Warning. - I disabled case sensitivity checks for temporary tables.

[WillAyd]

Thanks for the PR, but this seems to only work with SQLAlchemy, meaning we won't get the same behavior for our ADBC drivers. I don't think this is worth pursuing unless there is a way to avoid fragmenting features across those two different areas

[Diadochokinetic]

I took a look into the ADBC implementation. to_sql uses adbc_ingest, which supports an (experimental) parameter temporary. I'll give it a shot. Although, this would only support the use case of temporary tables and not table prefixes in general. A more general solution could be to insert the prefixes into table_name. I will report, when I did some experimenting with it.

[Diadochokinetic]

I did some testing and was able to implement the feature partially for adbc drivers. I used the temporary parameter of the adbc_ingest method. On the one hand, I don't like that it doesn't implement the full feature capacity of the paramater prefixes as its done in sqlalchemy, on the other hand it enables creating and appending temporary tables with the to_sql method for sqlachemy and adbc drivers, which is the main reason why I started working on this feature in the first place. Furthermore, I assume temporary tables will be like >99% of the use cases for prefixes anyway.

Now the question is:

• Should the paramater prefixes stay as proposed? Then the documentation needs to be expanded and explicitly state only ["TEMPORARY"] will have an effect for adbc drivers. Maybe even throw a NotImplementedError for other values.
• Or reduce the parameter to a boolean temporary so it works exactly the same for both drivers, but loses functionality for sqlalchemy.

I personally prefer the first option, because I connect to db2 databases via sqlalchemy and sometimes need to pass prefixes=["GLOBAL", "TEMPORARY"].

[WillAyd]

Can you check if there is an open issue for this upstream in the arrow-adbc repository? I think it may be of interest to them to handle more than just the temporary case.

ADBC releases are pretty quick, so if that is implemented upstream it benefits the entire ecosystem, and likely wouldn't be a long turnaround to get into pandas

[Diadochokinetic]

That is a very good point. It wold be very beneficial, if both sqlalchemy and adbc support prefixes. So far, there is no open issue for that, so I opened one: apache/arrow-adbc#2343. Let's see what the adbc team thinks about this.

[Diadochokinetic]

It looks like the adbc team is hesitant to implement an abstract parameter prefixes as it is available in sqlalchemy. So the option to wait for feature parity in both back ends seems to be unavailable. I prefer to implement prefixes in to_sql() to:

• have the full feature for sqlalchemy back-ends
• only use the TEMPORARY keyword for adbc back-ends and throw a NotImplentedError for other keywords

@WillAyd What are your thoughts on this?

[WillAyd]

I don't think its worth adding to pandas if the various backends cannot both fully support it; we may have to document instead how you could do it with more low-level SQLAlchemy operations, but to have the pandas API only offer this for SQLAlchemy but not ADBC makes the abstraction of the pandas API more complicated

[Diadochokinetic]

And what about a reduced parameter temporary, that is supported by both back ends? That wouldn't solve all my use cases, but at least ~ 90% of it.

[github-actions]

This pull request is stale because it has been open for thirty days with no activity. Please update and respond to this comment if you're still interested in working on this.

[Diadochokinetic]

I'm still interested in working on this.

[Diadochokinetic]

And what about a reduced parameter temporary, that is supported by both back ends? That wouldn't solve all my use cases, but at least ~ 90% of it.

@WillAyd, what's your thought on this? Having a parameter temporary, that's supported by both back ends, would be a solution that solves 90% of prefix usage and does satisfy feature parity.

[WillAyd]

I think that would be a reasonable solution across the implementations

[WillAyd]

Does sqlalchemy not provide a higher level abstraction than ProgrammingError or OperationalError? I'm not sure I understand the distinction between those, and I am not sure if they would catch all possible errors thrown by sqlalchemy either

[Diadochokinetic]

The is a higher abstraction possible with DatabaseError.

[WillAyd]

The DMBS-specific features are something we want to avoid in pandas, as maintaining compatability on those is not a core specialty of our team. Does sqlalchemy not handle this natively?

[WillAyd]

Similar comment as before - we really want to avoid putting DBMS-specific logic into our implementation

[mroeschke]

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.