-1

You are using SqlAlchemy and oracledb to connect to oracle database optionally SqlModel. You may use code samples given in following answer and following snippet, merged below:

from sqlalchemy.engine import create_engine

import sys
import oracledb
oracledb.version = "8.3.0"
sys.modules["cx_Oracle"] = oracledb

DIALECT = 'oracle'
SQL_DRIVER = 'cx_oracle'
USERNAME = 'your_username' #enter your username
PASSWORD = 'your_password' #enter your password
HOST = 'subdomain.domain.tld' #enter the oracle db host url
PORT = 1521 # enter the oracle port number
SERVICE = 'your_oracle_service_name' # enter the oracle db service name
ENGINE_PATH_WIN_AUTH = DIALECT + '+' + SQL_DRIVER + '://' + USERNAME + ':' + PASSWORD +'@' + HOST + ':' + str(PORT) + '/?service_name=' + SERVICE

engine = create_engine(ENGINE_PATH_WIN_AUTH)

But for some reasons, you are getting an EXCEPTION: (oracledb.exceptions.DatabaseError) DPY-4027.

Atilla Ozgur
  • 14,339
  • 3
  • 49
  • 69

2 Answers2

0

The reason is special characters in the password such as @. You need to escape special characters in the password as given in the sqlalchemy documentation

from sqlalchemy.engine import create_engine
import urllib.parse
DIALECT = 'oracle'
SQL_DRIVER = 'cx_oracle'
USERNAME = 'your_username' #enter your username
PASSWORD = urllib.parse.quote_plus('your_password') #enter your password to be encoded
HOST = 'subdomain.domain.tld' #enter the oracle db host url
PORT = 1521 # enter the oracle port number
SERVICE = 'your_oracle_service_name' # enter the oracle db service name
ENGINE_PATH_WIN_AUTH = DIALECT + '+' + SQL_DRIVER + '://' + USERNAME + ':' + PASSWORD +'@' + HOST + ':' + str(PORT) + '/?service_name=' + SERVICE
engine = create_engine(ENGINE_PATH_WIN_AUTH)

If you have special characters such as @ in your password. Most important line is below:

PASSWORD = urllib.parse.quote_plus('your_password')

Above code snippet works without oracle instant client.

Atilla Ozgur
  • 14,339
  • 3
  • 49
  • 69
0

With SQLAlchemy 2 you could connect in various ways such as this way which is useful when there are special characters in the password:

engine = create_engine(
    f'oracle+oracledb://:@',
    connect_args={
        "user": username,
        "password": password,
        "dsn": cs
    })

You don't need the sys.modules["cx_Oracle"] = oracledb lines when using SQLAlchemy 2 with python-oracledb.

See Using SQLAlchemy 2.0 with python-oracledb for Oracle Database.

Christopher Jones
  • 9,449
  • 3
  • 24
  • 48
  • Current SqlModel (Version: 0.0.7) works with SQLAlchemy (1.4.4). Above dictionary usage works with SQLAlchemy 1.4.4 with sqlalchemy2-stubs but you need to encode passwords still. – Atilla Ozgur Mar 09 '23 at 05:04