PandasSqlHandler
The class has a few helper methods for setting up the database connection, assigning the database environment, reading tables, and executing queries.
Methods
__init__
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
def __init__(self, environment): """Class to run SQL queries against MSFT database Parameters ---------- None Methods ---------- run_query(user_query) -> dataframe returns the query results in a list of dictionaries """ self._environment:str = environment self._conn_str:str = self.__assign_conn_str() self._conn_url = sql.engine.URL.create("mssql+pyodbc", query={"odbc_connect": self._conn_str}) self.engine = sql.create_engine(self._conn_url) |
First assign the environment. Environment configurations are set up in the config/sql_config.yaml file.
Next based on the environment, return the SQL_Driver string from the config/sql_config.yaml file.
Next combine the connection elements into the URL formatted string
Lastly invoke the sql alchemy create engine method.
__assign_conn_str
|
1 2 3 4 5 6 7 8 |
def __assign_conn_str(self) -> str: match self._environment.lower(): case 'dev': return sql_config.sql.dev.SQL_DRIVER case 'prod': return sql_config.sql.prod.SQL_DRIVER case _ : return sql_config.sql.dev.SQL_DRIVER |
The method will read config/sql_config.yaml file, and return the applicable SQL_Driver string from the config/sql_config.yaml file.
read_table
|
1 2 3 4 5 6 7 8 9 |
def read_table(self, query, params=None) -> pd.DataFrame: """ Run the SQL query and return the results in a Pandas dataframe Returns ---------- Pandas Dataframe """ return pd.read_sql(sql=query, con=self.engine, params=params) |
Using Pandas package, call the read_sql method and return a Pandas Dataframe.
execute_query
|
1 2 3 4 5 6 7 |
def execute_query(self, user_query:str, params=None) -> None: """ Run and commit the sql query """ with self.engine.connect() as connection: connection.execute(sql.text(user_query), params or {}) connection.commit() |
With the sql database connection defined earlier, execute the sql query and commit the query.
On the next page we will discuss the configuration files.
Leave a Reply