使用 SQLAlchemy 连接到 Oracle 数据库 [英] Connecting to an Oracle database using SQLAlchemy

问题描述

我能够成功连接到 SQLite 数据库并使用以下命令集访问特定表:

I am able to successfully connect to a SQLite database and access a particular table using the set of commands below:

from sqlalchemy import create_engine, MetaData, Table, and_
from sqlalchemy.sql import select
from pandas import DataFrame 

db = create_engine('sqlite:///path\\database.db')
metadata = MetaData(db)
table = Table('table name', metadata, autoload=True)

我能够使用 cx_Oracle 从 Oracle 数据库中获取数据.

I am able to fetch data from an Oracle database using cx_Oracle.

但是，当我尝试在 SQLAlchemy 中连接到 Oracle 数据库时，出现以下错误:

However, when I try to connect to an Oracle database in SQLAlchemy, I am getting the following error:

NoSuchTableError: <表名>

NoSuchTableError: <table name>

我使用了以下命令:

db = create_engine('oracle://username:password@hostname:1521/instance name', echo='debug')
md = MetaData(bind=db)
t = Table('table name', md, autoload=True, schema='schema name')

当我使用以下命令时:

t= Table('table name', md, autoload=True, oracle_resolve_synonyms=True)

我收到以下错误:

AssertionError: 有多个表对架构可见，您必须指定所有者

AssertionError: There are multiple tables visible to the schema, you must specify owner

你能解释一下我到底哪里出错了吗?

Can you please explain where exactly I am going wrong?

推荐答案

您不再需要导入 cx_Oracle.较新版本的 sqlalchemy 模块调用函数 cx_Oracle.makedsn().看看:

You don't need to import cx_Oracle anymore. The newer version of the sqlalchemy module calls the function cx_Oracle.makedsn(). Have a look:

from sqlalchemy.engine import create_engine

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)


#test query
import pandas as pd
test_df = pd.read_sql_query('SELECT * FROM global_name', engine)

这篇关于使用 SQLAlchemy 连接到 Oracle 数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！