Get metadata such as column names and column types of existing tables via SQLAlchemy.
The version used in the verification is in ()
.
create table demo_tbl (
i_col integer,
f_col float,
c_col char(1),
d_col date,
PRIMARY KEY (i_col)
)
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)
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).
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
date
type of Oracle can be stored up to the time, it is DateTime
as a SQLAlchemy class.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'}
get_primary_keys
, but this is deprecated as it will be deprecated.