Add oracledb support in prefect-sqlalchemy

[Aflous]

Describe the current behavior

currently, only cx_oracle driver is supported in prefect-sqlalchemy

Describe the proposed behavior

oracle+oracledb should be add in prefect-sqlalchemy

Example Use

No response

Additional context

No response

[desertaxle]

Thanks for the enhancement request @Aflous! I think adding oracle+oracledb support should be as simple as adding an entry to this enum:

prefect/src/integrations/prefect-sqlalchemy/prefect_sqlalchemy/credentials.py

Line 37 in 3067aaf

class SyncDriver(Enum):

I'd happily review a PR for that change!

[OUBVITOL]

@desertaxle I'm afraid it's not that simple since oracledb supports both async and sync modes with the same driver name oracle+oracledb.
The current implementation:

try:
    AsyncDriver(drivername)
    self._driver_is_async = True
except ValueError:
    self._driver_is_async = False

will end up forcing us to always use the async mode for oracledb if opt for using the uri when adding the SQLAlchemy block, as it's checked first.
We'll need a more flexible approach to handle drivers like oracledb that support both modes with the same driver name.

[desertaxle]

Good catch @OUBVITOL! Looks like we may need special handling in that case. I think the best option is to have a special name for the async version (something like oracle+oracledbasync) so that a user can specify if they want the sync or async version. We'll need to make sure we strip any extra characters before creating an engine.

[westford14]

@desertaxle just took a look at this and it looks like sqlalchemy natively 2.0 supports python-oracledb (https://docs.sqlalchemy.org/en/20/dialects/oracle.html#module-sqlalchemy.dialects.oracle.oracledb) but sqlalchemy 1.4 you have to do some shenanigans with importations (https://python-oracledb.readthedocs.io/en/latest/user_guide/appendix_c.html#python-frameworks-sql-generators-and-orms)

i'm happy to take a pass at the suggested oracle+oracledbasync parsing but not sure if this warrants a deeper discussion

[OUBVITOL]

@westford14 SQLAlchemy 1..4 only supports cx_Oracle, so this case is off the table
https://docs.sqlalchemy.org/en/14/dialects/oracle.html

@desertaxle I've just figured out that oracledb also supports oracle+oracledb_async as a URI, so we might not need special handling after all

[OUBVITOL]

@desertaxle This can be closed now.

However, given that this was added to the prefect-sqlalchemy integration, what would be the missing part to make it available on a Docker-deployed server?

This is running using 3.0.3-python3.12 and it's still not showing:"

[desertaxle]

@OUBVITOL You can update the block metadata registered with your server by running prefect block register -m prefect_sqlalchemy. I'll close this issue now, but let me know if you run into any other issues!