Oracle via sqlalchemy¶
Description:
- Work with data in OracleDB
Resources:
- [GitHub] https://github.com/sqlalchemy/sqlalchemy
- [Examples] https://github.com/sqlalchemy/sqlalchemy/tree/main/examples
- [Docs] https://docs.sqlalchemy.org/en/20/
- [Docs - Connections] https://docs.sqlalchemy.org/en/20/core/engines.html#oracle
- [Docs - Oracle] https://docs.sqlalchemy.org/en/20/dialects/oracle.html#module-sqlalchemy.dialects.oracle.oracledb
- [Tutorial] https://docs.sqlalchemy.org/en/20/tutorial/index.html
Notes:
- These examples use
python-oracledbin Thin mode, which connects directly to Oracle Database without Oracle Client libraries. python-oracledbis the successor to thecx_Oracledriver.
Environment variables¶
This notebook reads its settings from environment variables. Set them before starting Jupyter:
ORACLE_HOST: hostname of your Oracle DatabaseORACLE_PORT: optional, defaults to1521ORACLE_USER: database user nameORACLE_PASSWORD: password for that userORACLE_SERVICE_NAME: service name of the database (for exampleFREEPDB1)
Example 1:¶
Connect to a remote oracle database using sqlalchemy, insert a table and data, and read data from the table
In [ ]:
Copied!
import os
import sqlalchemy
from sqlalchemy import create_engine, URL, event
from sqlalchemy import Column
from sqlalchemy import Index
from sqlalchemy import Integer
from sqlalchemy import MetaData
from sqlalchemy import Table
from sqlalchemy.dialects import oracle
from sqlalchemy.schema import CreateIndex
from sqlalchemy import text
sqlalchemy.__version__
m = MetaData(naming_convention={"ix": "ix_%(column_0N_name)s"})
t = Table(
"t",
m,
Column("some_column_name_1", Integer),
Column("some_column_name_2", Integer),
Column("some_column_name_3", Integer),
)
ix = Index(
None,
t.c.some_column_name_1,
t.c.some_column_name_2,
t.c.some_column_name_3,
)
oracle_dialect = oracle.dialect(max_identifier_length=30)
print(CreateIndex(ix).compile(dialect=oracle_dialect))
url_object = URL.create(
"oracle+oracledb",
username=os.environ["ORACLE_USER"],
password=os.environ["ORACLE_PASSWORD"],
host=os.environ["ORACLE_HOST"],
database=os.environ["ORACLE_SERVICE_NAME"],
port=int(os.environ.get("ORACLE_PORT", "1521")),
)
engine = create_engine(url_object, echo=True, echo_pool="debug")
with engine.connect() as conn:
print("Connected to db")
conn.execute(text("CREATE TABLE some_table (x int, y int)"))
conn.execute(
text("INSERT INTO some_table (x, y) VALUES (:x, :y)"),
[{"x": 1, "y": 1}, {"x": 2, "y": 4}],
)
conn.commit()
result = conn.execute(text("SELECT x, y FROM some_table"))
for row in result:
print(f"x: {row.x} y: {row.y}")
import os
import sqlalchemy
from sqlalchemy import create_engine, URL, event
from sqlalchemy import Column
from sqlalchemy import Index
from sqlalchemy import Integer
from sqlalchemy import MetaData
from sqlalchemy import Table
from sqlalchemy.dialects import oracle
from sqlalchemy.schema import CreateIndex
from sqlalchemy import text
sqlalchemy.__version__
m = MetaData(naming_convention={"ix": "ix_%(column_0N_name)s"})
t = Table(
"t",
m,
Column("some_column_name_1", Integer),
Column("some_column_name_2", Integer),
Column("some_column_name_3", Integer),
)
ix = Index(
None,
t.c.some_column_name_1,
t.c.some_column_name_2,
t.c.some_column_name_3,
)
oracle_dialect = oracle.dialect(max_identifier_length=30)
print(CreateIndex(ix).compile(dialect=oracle_dialect))
url_object = URL.create(
"oracle+oracledb",
username=os.environ["ORACLE_USER"],
password=os.environ["ORACLE_PASSWORD"],
host=os.environ["ORACLE_HOST"],
database=os.environ["ORACLE_SERVICE_NAME"],
port=int(os.environ.get("ORACLE_PORT", "1521")),
)
engine = create_engine(url_object, echo=True, echo_pool="debug")
with engine.connect() as conn:
print("Connected to db")
conn.execute(text("CREATE TABLE some_table (x int, y int)"))
conn.execute(
text("INSERT INTO some_table (x, y) VALUES (:x, :y)"),
[{"x": 1, "y": 1}, {"x": 2, "y": 4}],
)
conn.commit()
result = conn.execute(text("SELECT x, y FROM some_table"))
for row in result:
print(f"x: {row.x} y: {row.y}")