Connection

Connection

Connection class for interfacing with cloud assets.

__init__()

Primary class and interface for connecting, reading, writing to cloud systems in our ecosystem.

This class expects credentials to be stored as environment variables.

create(name, server, database, port, dialect, driver=None, auth=None, msi=False)

Method for creating a connection engine to an asset.

This initializes a sqlalchemy engine from which the user can execute queries. Args: server (str): The server. database (str): The database to connect to. port (int): The port to connect to on the server. dialect (str): The database dialect, either 'mssql' or 'postgresql'. driver (str): Optional argument for specifying the driver, e.g. ODBC. auth (str): Optional argument for specifying the auth method, e.g. 'azureAD'. msi (bool): Optional argument for specifying if connection is using Managed System Identity to access server instead of usr/pwd.

create_default_engine(name, database=None, msi=False)

Method for creating a default engines listed in connection_map.

Parameters:

Name	Type	Description	Default
name	str	The name of the default system to connect to.	required
database	str	The name of the database to connect to. Needed in order to connect to soigms.	None
msi	bool	Set to True if using Managed Server Instance to connect to server. False if using credentials.	False

ensure_az_login()

This will run the command 'az login' in the terminal if not previously run. This handles the MFA requirement for macs. It will open a browser that you need to click to sign in to Microsoft.

execute_query(query)

Helper method for executing a query from a pandas df.

execute_query_from_str(qry)

Helper method for execute a string as a query.

Parameters:

Name	Type	Description	Default
`qry`	str	The query to execute.	required

execute_query_from_str_stored_procedure(qry)

Helper method for execute a string as a query. This is for creating stored procedures where it has a failure to commit issue.

First option:

#with self.engine.begin() as conn:
#    conn.execute(qry)

The Second option is what we are doing, if we want to add it below

create_engine then we would do the following commented out line
#self.engine = self.engine.execution_options(isolation_level="AUTOCOMMIT")

Parameters:

Name	Type	Description	Default
`qry`	str	The query to execute.	required

write_to_pandas(df, table, schema, index=False, if_exists='append', chunk=True, chunksize=5000, time_delay=0)

Helper method for writing data from a pandas df.

Parameters:

Name	Type	Description	Default
`df`	DataFrame	The dataframe to write from.	required
`table`	str	The name of the table to write to.	required
`schema`	str	The schema containing the target table.	required
`index`	bool	Optional if writing the dataframe index, usually False.	required
`if_exists`	str	Whether to append or replace the target table.	required