I am currently engaged in evaluating SQLAlchemy for a new project. When trying to reflect the tables of an existing database (postgres, oracle), I am encountering some issues.
In order to connect to an Oracle database the following code is being used:
from sqlalchemy import create_engine, MetaData, Table, inspect, select
import pandas as pd
import keyring
dbtype = 'Oracle'
dbenv = 'LOCAL'
dbname = 'MYDB'
dbsys = '%s%s' % (dbtype, dbenv)
dbusr = 'myusr'
dbpwd = keyring.get_password(dbsys, dbusr)
dbhost = 'mydbhost'
dbport = 1521
dbconstr = 'oracle+cx_oracle://%s:%s@%s:%s/%s' % (dbusr, dbpwd, dbhost, dbport, dbname)
engine = create_engine(dbconstr)
After some try-and-error regarding how to assemble the connection string (there is no tnsnames.ora present on my system), I managed to successfully establish a connection to the remote Oracle database.
When inspecting the database and trying to retrieve the names of the tables available, I received an empty list.
inspector = inspect(engine)
instbls = inspector.get_table_names()
print('instbls: %s' % instbls)
# instbls: []
What I actually was expecting is that the system tables or the public tables would have been listed.
Next I tried reflecting the database tables of the SYS schema.
md01 = MetaData(bind=engine, schema=['SYS', 'SYSTEM'], reflect=True)
# Warning: sqlalchemy_oracle.py:37: SADeprecationWarning: The MetaData.reflect flag is deprecated and will be removed in a future release. Please use the MetaData.reflect() method.
print('type(md01): %s' % type(md01))
# type(md01): <class 'sqlalchemy.sql.schema.MetaData'>
md01tbls = md01.tables
print('md01tbls: %s' % md01tbls)
# md01tbls: immutabledict({})
So I modified the code accordingly in order to get rid of the warning
md02 = MetaData(bind=engine, schema=['SYS', 'SYSTEM']).reflect()
print('type(md02): %s' % type(md02))
# type(md02): <class 'NoneType'>
md02tbls = md02.tables
# Exception: Traceback (most recent call last):
# ...
# File "sqlalchemy_oracle.py", line 44, in <module>
# AttributeError: 'NoneType' object has no attribute 'tables'
print('md02tbls: %s' % md02tbls)
No warning, but an exception instead. :(
After some researching The Net it appeared as if some further code modification is required.
md03 = MetaData().reflect(bind=engine, schema=['SYS', 'SYSTEM'])
# Exception: Traceback (most recent call last):
# ...
# File "/site-packages/sqlalchemy/engine/default.py", line 637, in denormalize_name
# name_lower = name.lower()
# AttributeError: 'list' object has no attribute 'lower'
print('type(md03): %s' % type(md03))
md03tbls = md03.tables
print('md03tbls: %s' % md03tbls)
Even less successful than the previous attempt. Converting the schema names listed to lower case letters lead to the identical result.
So maybe reflect can only handle strings and nolists?
md05a = MetaData().reflect(bind=engine, schema='sys')
print('type(md05a): %s' % type(md05a))
# type(md05a): <class 'NoneType'>
md05b = MetaData().reflect(bind=engine, schema='system')
print('type(md05b): %s' % type(md05b))
# type(md05b): <class 'NoneType'>
md05atbls = md05a.tables
# Exception: Traceback (most recent call last):
# ...
# File "sqlalchemy_oracle.py", line 44, in <module>
# AttributeError: 'NoneType' object has no attribute 'tables'
print('md05atbls: %s' % md05atbls)
md05btbls = md05b.tables
print('md05btbls: %s' % md05btbls)
No luck there either!
Maybe it is not possible to reflect the system tables? So lets try user-specific objects.
md06 = MetaData().reflect(bind=engine, schema='MYUSR')
print('type(md06): %s' % type(md06))
# type(md06): <class 'NoneType'>
md06tbls = md06.tables
# Exception: Traceback (most recent call last):
# ...
# File "sqlalchemy_oracle.py", line 44, in <module>
# AttributeError: 'NoneType' object has no attribute 'tables'
print('md06tbls: %s' % md06tbls)
Again the already know exception.
Moving back to the deprecated call version.
md07 = MetaData(bind=engine, schema=['MYUSR', 'MYUSR2'], reflect=True)
print('type(md07): %s' % type(md07))
# type(md07): <class 'sqlalchemy.sql.schema.MetaData'>
md07tbls = md07.tables
print('md07tbls: %s' % md07tbls)
# md07tbls: immutabledict({})
No exception, but no tables either, despite that there are several to be found in the schemas MYUSR and MYUSR2.
Splitting the list into strings when specifying the schema.
md08a = MetaData(bind=engine, schema='MYUSR', reflect=True)
# Warning: sqlalchemy_oracle.py:37: SADeprecationWarning: The MetaData.reflect flag is deprecated and will be removed in a future release. Please use the MetaData.reflect() method.
print('type(md08a): %s' % type(md08a))
# ype(md08a): <class 'sqlalchemy.sql.schema.MetaData'>
md08b = MetaData(bind=engine, schema='MYUSR2', reflect=True)
# Warning: sqlalchemy_oracle.py:40: SADeprecationWarning: The MetaData.reflect flag is deprecated and will be removed in a future release. Please use the MetaData.reflect() method.
print('type(md08b): %s' % type(md08b))
# type(md08b): <class 'sqlalchemy.sql.schema.MetaData'>
md08atbls = md08a.tables
print('md08atbls: %s' % md08atbls)
# md08atbls: immutabledict({'MYUSR.table01': Table(...)})
md08btbls = md08b.tables
print('md08btbls: %s' % md08btbls)
# md08btbls: immutabledict({'MYUSR2.table01': Table(...)})
So this attempt finally produced the expected result. :)
From all this it appears as if
- system/public tables (database objects) cannot be reflected;
- only user-specific tables can be reflected;
- only the deprecated version of reflection is working;
- this behaviour can be observed both with Oracle as well as PostgreSQL databases.
Is there something I overlooked? Something I have mis-perceived? Done wrong, differently from how it actually is intended?
My environment comprises of
- Ubuntu linux 16.04LTS;
- Python 3.8;
- SQLAlchemy 1.3.16;
- cx_Oracle 7.3.0;
- psycopg2 2.8.5;
- local Oracle 18c Instant client;
- remote Oracle 19c database;
- local PostgreSQL 9.5 database.
