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[source]¶ 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[source]¶ 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", )