Get metadata for existing tables using SQLAlchemy

Purpose

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

environment

Operating environment

Required library

The version used in the verification is in ().

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

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'}

reference

SQLAlchemy 1.3 Documentation

Recommended Posts

Get metadata for existing tables using SQLAlchemy
Get note information using Evernote SDK for Python 3
Generate SQLAlchemy table definition from existing MySQL server using sqlacodegen