mydatapreprocessing.database package

Process database. Read or write.

It is working only for mssql server so far.

mydatapreprocessing.database.database_load(query: str, server: str, database: str, port: str | int | None = None, driver: str = '{SQL Server}', username: str | None = None, password: str | None = None, trusted_connection: bool = False) → pd.DataFrame

Load database into DataFrame.

Parameters:

• query (str) – Used query.
• server (str) – Name of server.
• database (str) – Name of database.
• port (str | int | None, optional) – Used port. May work with None. Defaults to None.
• driver (str, optional) – Used driver. One can be downloaded on https://docs.microsoft.com/en-us/sql/connect/odbc/download-odbc-driver-for-sql-server?view=sql-server-ver15 Defaults to ‘{SQL Server}’.
• username (str | None, optional) – Username. ‘sa’ for root on mssql.
• password (str | None, optional) – Password.
• trusted_connection (bool, optional) – If using windows authentication.

Returns:

DataFrame with data from database based on input SQL query.

Return type:

pd.DataFrame

Example:

data = mdp.database.database_load(
    server=".",
    database="DemoData",
    query='''
        SELECT TOP (1000) [ID] ,[ProductName]
        FROM [DemoData].[dbo].[Products]
    ''',
    username="sa",
    password="HelloPassword123",
)

mydatapreprocessing.database.database_write(df: pd.DataFrame, server: str, database: str, table: str, index: bool = False, port: str | int | None = None, driver: str = '{SQL Server}', username: str | None = None, password: str | None = None, trusted_connection: bool = False, schema: None | str = None, if_exists: Literal[('fail', 'replace', 'append')] = 'append') → None

Deploy DataFrame to SQL server.

Parameters:

• df (pd.DataFrame) – DataFrame passed to database.
• server (str) – Name of server.
• database (str) – Name of database.
• table (str) – Used table.
• index (bool, optional) – Whether use index as a column Defaults to False.
• port (str | int | None, optional) – Used port. May work with None. Defaults to None.
• driver (str, optional) – Used driver. One can be downloaded on https://docs.microsoft.com/en-us/sql/connect/odbc/download-odbc-driver-for-sql-server?view=sql-server-ver15 Defaults to ‘{SQL Server}’.
• username (str | None, optional) – Username. ‘sa’ for root on mssql. Defaults to None.
• password (str | None, optional) – Password. Defaults to None.
• trusted_connection (bool, optional) – If using windows authentication. You don’t need username and password then. Defaults to False.
• schema (None | str, optional) – Used schema. Defaults to None.
• if_exists (Literal['fail', 'replace', 'append'], optional) – Define whether append new data on the end, remove and replace or fail if table exists. Defaults to ‘append’.

Example:

import pandas as pd

df = pd.DataFrame([[1, 2, 3], [4, 5, 6]])

mdp.database.database_write(
    df,
    server=".",
    database="DemoData",
    table="Products",
    username="sa",
    password="HelloPassword123",
    if_exists="replace",
)