Resolve inconsistencies of connection uri / objects in read_database, read_database_uri and write_database

[mesner]

Description

Accepting a db connection object in #10649 was a great enhancement, but it fragmented the db apis such that write_database and read_database_uri accept a connection string, whereas read_database accepts a connection object, and there is no way to write to a db table with an existing connection object.

A common use case for sharing a connection object between reads and writes is to stage data into a temp table and then query from it, joining to other tables later. A part of this request would be able to pass a connection object to write database. Pandas has this functionality. The other part is to have consistency in the api.

I'd probably vote for separate connection and connection_uri parameters on single methods, but there are a number of reasonable solutions.

In my opinion, this overlaps but is not a duplicate of #7852

Thanks for a great library.

[alexander-beedie]

Thanks for a great library.

Thanks ;)

In my opinion, this overlaps but is not a duplicate of #7852

My strong suspicion is that, unless connectorx starts showing signs of life again in the near future, we are going to end up eventually deprecating and retiring read_database_uri, as adbc can equally-well provide connection objects; therefore, without connectorx there is no need for a URI variant of that function. (This is probably a long way off though).

I have long-standing (and long-delayed 😅) plans to address the various issues with write-database, and it will indeed take connection objects, as read_database does.

While I agree this is not exactly the same issue, it's close enough that I'd rather close this in favour of the existing one. A solution there will essentially address the issues here. Feel free to add a comment to that issue though, to make sure 👍