def unique( column: str, table: str, database: str = "DefaultDB", server: str = "DefaultServer", username: Optional[str] = None, password: Optional[str] = None, dsn: str = "MYMSSQL", ) -> pd.DataFrame: r""" Return unique values of ``column`` from ``table``. Parameters ---------- column : str Name of column for which to return the unique values of. table : str Name of the table for which ``column`` belongs to. database : str, optional The database to connect to. By default is "DefaultDB". server : str, optional The server to connect to. By default is "DefaultServer". username : str in the form of "FRD\\pcosta", optional SQL database username. By default None, uses Kerberos authentication if on Windows or environmental variable ``SQLUSERNAME`` if on Linux or macOS. password : str, optional SQL database password. By default None, uses Kerberos authentication if on Windows or environmental variable ``SQLPASSWORD`` if on Linux or macOS. dsn : str, optional Server connection object for macOS if using unixODBC. By default set to "MYMSSQL". Returns ------- unique_values: pd.DataFrame The unique values of the column specified in ``column``. """ query = f""" --sql SELECT DISTINCT({column}) FROM {table}; """ return executers.run_query( query, database=database, server=server, username=username, password=password, dsn=dsn, )
def head( table_name: str, n: int = 5, database: str = "DefaultDB", server: str = "DefaultServer", username: Optional[str] = None, password: Optional[str] = None, dsn: str = "MYMSSQL", ) -> pd.DataFrame: r""" Return the first ``n`` rows from a table. Parameters ---------- table_name : str Name of the table from which the top ``n`` rows will be returned from. n : int, optional The number of rows to return. By default is 5. database : str, optional The database to connect to. By default is "DefaultDB". server : str, optional The server to connect to. By default is "DefaultServer". username : str in the form of "FRD\\pcosta", optional SQL database username. By default None, uses Kerberos authentication if on Windows or environmental variable ``SQLUSERNAME`` if on Linux or macOS. password : str, optional SQL database password. By default None, uses Kerberos authentication if on Windows or environmental variable ``SQLPASSWORD`` if on Linux or macOS. dsn : str, optional Server connection object for macOS if using unixODBC. By default set to "MYMSSQL". Returns ------- table_head : pd.DataFrame The top ``n`` rows of the selected table. """ query = f""" --sql SELECT TOP {n} * FROM {table_name}; """ return executers.run_query( query, database=database, server=server, username=username, password=password, dsn=dsn, )
def get_cols( table_name: str, database: str = "DefaultDB", server: str = "DefaultServer", username: Optional[str] = None, password: Optional[str] = None, dsn: str = "MYMSSQL", ) -> List[str]: r""" Return all of a table's column names. Parameters ---------- table_name : str Name of the table whose columns will be returned. database : str, optional The database to connect to. By default is "DefaultDB". server : str, optional The server to connect to. By default is "DefaultServer". username : str in the form of "FRD\\pcosta", optional SQL database username. By default None, uses Kerberos authentication if on Windows or environmental variable ``SQLUSERNAME`` if on Linux or macOS. password : str, optional SQL database password. By default None, uses Kerberos authentication if on Windows or environmental variable ``SQLPASSWORD`` if on Linux or macOS. dsn : str, optional Server connection object for macOS if using unixODBC. By default set to "MYMSSQL". Returns ------- column_names : List[str] The column names of the inputted table. """ query = f""" --sql SELECT TOP 0 * FROM {table_name}; """ return executers.run_query( query, database=database, server=server, username=username, password=password, dsn=dsn, ).columns.tolist()
def show_temp( database: str = "DefaultDB", server: str = "DefaultServer", username: Optional[str] = None, password: Optional[str] = None, dsn: str = "MYMSSQL", ) -> pd.DataFrame: r""" Show all temporary tables in the database. Parameters ---------- database : str, optional The database to connect to. By default is "DefaultDB". server : str, optional The server to connect to. By default is "DefaultServer". username : str in the form of "FRD\\pcosta", optional SQL database username. By default None, uses Kerberos authentication if on Windows or environmental variable ``SQLUSERNAME`` if on Linux or macOS. password : str, optional SQL database password. By default None, uses Kerberos authentication if on Windows or environmental variable ``SQLPASSWORD`` if on Linux or macOS. dsn : str, optional Server connection object for macOS if using unixODBC. By default set to "MYMSSQL". Returns ------- temp_table_names : pd.DataFrame Query results are returned as a Pandas DataFrame. """ query = """ --sql SELECT name FROM tempdb.sys.objects WHERE name LIKE '##%'; """ return executers.run_query( query, database=database, server=server, username=username, password=password, dsn=dsn, )
def change_schema( schema: str, database: str = "DefaultDB", server: str = "DefaultServer", username: Optional[str] = None, password: Optional[str] = None, dsn: str = "MYMSSQL", ) -> None: r""" Change the default schema of the user. Parameters ---------- schema : str Schema name to be set to new default. database : str, optional The database to connect to. By default is "DefaultDB". server : str, optional The server to connect to. By default is "DefaultServer". username : str in the form of "FRD\\pcosta", optional SQL database username. By default None, uses Kerberos authentication if on Windows or environmental variable ``SQLUSERNAME`` if on Linux or macOS. password : str, optional SQL database password. By default None, uses Kerberos authentication if on Windows or environmental variable ``SQLPASSWORD`` if on Linux or macOS. dsn : str, optional Server connection object for macOS if using unixODBC. By default set to "MYMSSQL". """ username = executers.run_query( "SELECT CURRENT_USER;", database=database, server=server, username=username, password=password, dsn=dsn, ).iloc[0][0] executers.run_command( f"ALTER USER [{username}] WITH DEFAULT_SCHEMA = {schema};", database=database, server=server, username=username, password=password, dsn=dsn, )
def test_temp_table() -> None: """Test that ``TempTable`` can create a table that can be queried.""" expected_data = pd.DataFrame({"test": [1]}) tt = """ --sql SELECT 1 test INTO ##one; """ one = helpers.TempTable(tt) query = """ --sql SELECT * FROM ##one; """ actual_data = executers.run_query(query) one.close() pd.testing.assert_frame_equal(expected_data, actual_data)
def find_tables( search_terms: Union[str, List[str], Tuple[str, ...]], database: str = "DefaultDB", server: str = "DefaultServer", username: Optional[str] = None, password: Optional[str] = None, dsn: str = "MYMSSQL", ) -> pd.DataFrame: r""" Return the tables whose names contain the inputted search terms. Parameters ---------- search_terms : Union[str, List[str], Tuple[str, ...]] Terms of list of terms that will be searched for. If multiple terms are inputted, returned table names must match each term. database : str, optional The database to connect to. By default is "DefaultDB". server : str, optional The server to connect to. By default is "DefaultServer". username : str in the form of "FRD\\pcosta", optional SQL database username. By default None, uses Kerberos authentication if on Windows or environmental variable ``SQLUSERNAME`` if on Linux or macOS. password : str, optional SQL database password. By default None, uses Kerberos authentication if on Windows or environmental variable ``SQLPASSWORD`` if on Linux or macOS. dsn : str, optional Server connection object for macOS if using unixODBC. By default set to "MYMSSQL". Returns ------- tables : pd.DataFrame DataFrame containing all matching tables. Column name is "table_name". """ if isinstance(search_terms, str): search_terms = [search_terms] query_template = """ --sql SELECT name table_name FROM sys.tables WHERE {%- for term in search_terms -%} {%- if loop.first %}\n\tname LIKE '%{{ term }}%' {%- else %}\n\tAND name LIKE '%{{ term }}%' {%- endif %} {%- endfor %} UNION ALL SELECT name table_name FROM sys.views WHERE {%- for term in search_terms -%} {%- if loop.first %}\n\tname LIKE '%{{ term }}%' {%- else %}\n\tAND name LIKE '%{{ term }}%' {%- endif %} {%- endfor %}; """ query = jinja2.Template(query_template).render(search_terms=search_terms) return executers.run_query( query, database=database, server=server, username=username, password=password, dsn=dsn, )