non-dbo schema

[jwcook23]

Ability to insert/update/merge using a schema besides dbo.

from mssql_dataframe import SQLServer
import pyodbc
import pandas as pd

sql = SQLServer(server='localhost', database='test', trusted_connection='yes')

schema_name = "foo"
table_name = "bar"
combined_name = f"{schema_name}.{table_name}"
cursor = sql.connection.cursor()
try:
    cursor.execute(f"CREATE SCHEMA {schema_name};")
except pyodbc.ProgrammingError:
    pass
cursor.execute(f"DROP TABLE IF EXISTS {combined_name}")
cursor.commit()

dataframe = pd.DataFrame(
    {"ColumnA": [3, 4]}, index=pd.Series([0, 1], name="_index")
)
sql.create.table(
    combined_name,
    {"ColumnA": "TINYINT", "_index": "TINYINT"},
    primary_key_column="_index",
)
dataframe = sql.write.insert(combined_name, dataframe)

# delete
dataframe = dataframe[dataframe.index != 0].copy()
# update
dataframe.loc[dataframe.index == 1, "ColumnA"] = 5
# insert
dataframe = pd.concat(
    [
        dataframe,
        pd.DataFrame([6], columns=["ColumnA"], index=pd.Index([2], name="_index")),
    ]
)
dataframe = sql.write.merge(combined_name, dataframe)

# _index 0 has been deleted
# _index 1 has been updated with ColumnA = 5
# _index 2 has been inserted with ColumnA = 6
result = sql.read.table(combined_name)
print(result)

[jwcook23]

/AzurePipelines run continuous-integration

[azure-pipelines[bot]]

Azure Pipelines successfully started running 1 pipeline(s).