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,
)