Sqlalchemy

See tables in a schema

import cx_Oracle

from sqlalchemy import create_engine, inspect

import sys

import os

sys.path.append("C:\\oracle\\instantclient_19_5")

def get_con_str(db, u, p):

    if db == "DATABASE":

        dnsstring = cx_Oracle.makedsn(

            "SERVER", "PORT_NUM", service_name="SERVICE_NAME"

        )

    return 'oracle://' + u + ':' + p + '@' + dnsstring

uri = get_con_str("DATABASE", os.getenv('ORC_USN'), os.getenv('ORC_PSW'))

engine = create_engine(uri)

inspector = inspect(engine)

inspector.get_schema_names()

inspector.get_table_names(schema=schema)

inspector.get_view_names(schema=schema)

inspector.get_columns('table', schema=schema) # ... to get columns from the dictionary

Write pandas.DataFrame to table

from sqlalchemy import create_engine, types

engine = create_engine(uri)

d = {'DATE': pd.to_datetime('01-01-2020'), 'VAL': 4.5, 'CAT': 'a'}

df = pd.DataFrame(data=d, index=[0])

dtypes = {'DATE': types.DATE, 'VAL': types.FLOAT, 'CAT': types.VARCHAR(1)}

df.to_sql('table', con=engine, schema='SCHEMA', if_exists='fail', index=False, dtype=dtypes)

Create an empty record

engine = sqlalchemy.create_engine("sqlite://")