def get_engine(
connection: str,
catalog_id: Optional[str] = None,
boto3_session: Optional[boto3.Session] = None,
**sqlalchemy_kwargs: Any,
) -> sqlalchemy.engine.Engine:
"""Return a SQLAlchemy Engine from a Glue Catalog Connection.
Only Redshift, PostgreSQL and MySQL are supported.
Parameters
----------
connection : str
Connection name.
catalog_id : str, optional
The ID of the Data Catalog from which to retrieve Databases.
If none is provided, the AWS account ID is used by default.
boto3_session : boto3.Session(), optional
Boto3 Session. The default boto3 session will be used if boto3_session receive None.
sqlalchemy_kwargs
keyword arguments forwarded to sqlalchemy.create_engine().
https://docs.sqlalchemy.org/en/13/core/engines.html
Returns
-------
sqlalchemy.engine.Engine
SQLAlchemy Engine.
Examples
--------
>>> import awswrangler as wr
>>> res = wr.catalog.get_engine(name='my_connection')
"""
details: Dict[str,
Any] = get_connection(
name=connection,
catalog_id=catalog_id,
boto3_session=boto3_session)["ConnectionProperties"]
db_type: str = details["JDBC_CONNECTION_URL"].split(":")[1].lower()
host: str = details["JDBC_CONNECTION_URL"].split(":")[2].replace("/", "")
port, database = details["JDBC_CONNECTION_URL"].split(":")[3].split("/")
user: str = _quote_plus(details["USERNAME"])
password: str = _quote_plus(details["PASSWORD"])
if db_type == "postgresql":
_utils.ensure_postgresql_casts()
if db_type in ("redshift", "postgresql"):
conn_str: str = f"{db_type}+psycopg2://{user}:{password}@{host}:{port}/{database}"
sqlalchemy_kwargs["executemany_mode"] = "values"
sqlalchemy_kwargs["executemany_values_page_size"] = 100_000
return sqlalchemy.create_engine(conn_str, **sqlalchemy_kwargs)
if db_type == "mysql":
conn_str = f"mysql+pymysql://{user}:{password}@{host}:{port}/{database}"
return sqlalchemy.create_engine(conn_str, **sqlalchemy_kwargs)
raise exceptions.InvalidDatabaseType(
f"{db_type} is not a valid Database type."
f" Only Redshift, PostgreSQL and MySQL are supported.")
def get_engine(db_type: str, host: str, port: int, database: str, user: str,
password: str,
**sqlalchemy_kwargs: Any) -> sqlalchemy.engine.Engine:
"""Return a SQLAlchemy Engine from the given arguments.
Only Redshift, PostgreSQL and MySQL are supported.
Parameters
----------
db_type : str
Database type: "redshift", "mysql" or "postgresql".
host : str
Host address.
port : str
Port number.
database : str
Database name.
user : str
Username.
password : str
Password.
sqlalchemy_kwargs
keyword arguments forwarded to sqlalchemy.create_engine().
https://docs.sqlalchemy.org/en/13/core/engines.html
Returns
-------
sqlalchemy.engine.Engine
SQLAlchemy Engine.
Examples
--------
>>> import awswrangler as wr
>>> engine = wr.db.get_engine(
... db_type="postgresql",
... host="...",
... port=1234,
... database="...",
... user="******",
... password="******"
... )
"""
if db_type == "postgresql":
_utils.ensure_postgresql_casts()
if db_type in ("redshift", "postgresql"):
conn_str: str = f"{db_type}+psycopg2://{user}:{password}@{host}:{port}/{database}"
sqlalchemy_kwargs["executemany_mode"] = "values"
sqlalchemy_kwargs["executemany_values_page_size"] = 100_000
return sqlalchemy.create_engine(conn_str, **sqlalchemy_kwargs)
if db_type == "mysql":
conn_str = f"mysql+pymysql://{user}:{password}@{host}:{port}/{database}"
return sqlalchemy.create_engine(conn_str, **sqlalchemy_kwargs)
raise exceptions.InvalidDatabaseType(
f"{db_type} is not a valid Database type."
f" Only Redshift, PostgreSQL and MySQL are supported.")
def get_engine(db_type: str, host: str, port: int, database: str, user: str,
password: str) -> sqlalchemy.engine.Engine:
"""Return a SQLAlchemy Engine from the given arguments.
Only Redshift, PostgreSQL and MySQL are supported.
Parameters
----------
db_type : str
Database type: "redshift", "mysql" or "postgresql".
host : str
Host address.
port : str
Port number.
database : str
Database name.
user : str
Username.
password : str
Password.
Returns
-------
sqlalchemy.engine.Engine
SQLAlchemy Engine.
Examples
--------
>>> import awswrangler as wr
>>> engine = wr.db.get_engine(
... db_type="postgresql",
... host="...",
... port=1234,
... database="...",
... user="******",
... password="******"
... )
"""
if db_type == "postgresql":
_utils.ensure_postgresql_casts()
if db_type in ("redshift", "postgresql"):
conn_str: str = f"{db_type}+psycopg2://{user}:{password}@{host}:{port}/{database}"
return sqlalchemy.create_engine(conn_str,
echo=False,
executemany_mode="values",
executemany_values_page_size=100_000,
connect_args={'sslmode': 'verify-ca'})
if db_type == "mysql":
conn_str = f"mysql+pymysql://{user}:{password}@{host}:{port}/{database}"
return sqlalchemy.create_engine(conn_str, echo=False)
raise exceptions.InvalidDatabaseType( # pragma: no cover
f"{db_type} is not a valid Database type."
f" Only Redshift, PostgreSQL and MySQL are supported.")