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

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.

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"].