Purpose

Get metadata such as column names and column types of existing tables via SQLAlchemy.

environment

Operating environment

Windows 10 Home
Python 3.8.5 (Anaconda)
Oracle Database 18c XE

Required library

The version used in the verification is in ().

sqlalchemy (1.3.18)
cx_oracle (7.2.3)

Table to use

create table demo_tbl (
    i_col integer,
    f_col float,
    c_col char(1),
    d_col date,
    PRIMARY KEY (i_col)
)

Implementation

Preparation

Engine instance creation

Create a sqlalchemy.engine.Engine instance to connect to the RDB. Replace host, port, pdb-name, ʻuser, and pass` with the connection information to be used.

import sqlalchemy
import cx_Oracle

dsnStr = cx_Oracle.makedsn('host','port','pdb-name')
connect_str = 'oracle://user:pass@' + dsnStr.replace('SID', 'SERVICE_NAME')
engine = sqlalchemy.create_engine(connect_str, max_identifier_length=128)

Check instance creation

Create a sqlalchemy.engine.reflection.Inspector instance to get the metadata.

inspector = sqlalchemy.inspect(engine)

You can access the metadata of the table using the check instance.

The column list and how to get the primary key are introduced below. See the API docs for other information (https://docs.sqlalchemy.org/en/13/core/reflection.html#sqlalchemy.engine.reflection.Inspector).

Column list

If you pass the table name to the get_columns method as shown below, a list of column information will be returned.

columns = inspector.get_columns("demo_tbl")

[{'name': 'i_col',
  'type': INTEGER(),
  'nullable': False,
  'default': None,
  'autoincrement': 'auto',
  'comment': None},
 {'name': 'f_col',
  'type': FLOAT(),
  'nullable': True,
  'default': None,
  'autoincrement': 'auto',
  'comment': None},
 {'name': 'c_col',
  'type': CHAR(length=1),
  'nullable': True,
  'default': None,
  'autoincrement': 'auto',
  'comment': None},
 {'name': 'd_col',
  'type': DATE(),
  'nullable': True,
  'default': None,
  'autoincrement': 'auto',
  'comment': None}]

The value stored in the type key is an instance of a general type defined in SQLAlchemy based on sqlalchemy.types.TypeEngine, or an instance of a subclass implemented individually for each DB. See the API docs for more details (https://docs.sqlalchemy.org/en/13/core/type_basics.html).

# i_col(integer)in the case of
type_of_col0 = columns[0]["type"]
type(type_of_col0) # -> <class 'sqlalchemy.sql.sqltypes.INTEGER'>
isinstance(type_of_col0, sqlalchemy.types.TypeEngine) # -> True
isinstance(type_of_col0, sqlalchemy.types.Integer) # -> True
isinstance(type_of_col0, sqlalchemy.types.Float) # -> False

# d_col(date)in the case of
type_of_col3 = columns[3]["type"]
type(type_of_col3) # -> <class 'sqlalchemy.dialects.oracle.base.DATE'>
isinstance(type_of_col3, sqlalchemy.types.TypeEngine) # -> True
isinstance(type_of_col3, sqlalchemy.types.DateTime) # -> True
isinstance(type_of_col3, sqlalchemy.types.Date) # -> False

Since the date type of Oracle can be stored up to the time, it is DateTime as a SQLAlchemy class.

Primary key

If you pass the table name to the get_pk_constraint method as shown below, the information related to the primary key will be returned.

{'constrained_columns': ['i_col'], 'name': 'sys_c007315'}

There is also a method called get_primary_keys, but this is deprecated as it will be deprecated.

reference

SQLAlchemy 1.3 Documentation

[PYTHON] Get metadata for existing tables using SQLAlchemy