mssql-dataframe 1.4.0

Update, Upsert, and Merge from Python dataframes to SQL Server and Azure SQL database.

mssql_dataframe

A data engineering package for Python pandas dataframes and Microsoft Transact-SQL. It provides more advanced methods for writting dataframes including update, merge, upsert.

1. Update: updates records in SQL table
2. Upsert: insert or update records in SQL table
3. Merge: update, insert, or delete records in SQL table

These more advanced methods are designed to provide more funcationality than is offered by the pandas.DataFrame.to_sql method.

# pandas.DataFrame.to_sql method if the table already exists
pandas.DataFrame.to_sql(name, con, if_exists='fail')

# if_exists : {‘fail’, ‘replace’, ‘append’}, default ‘fail’
# 'fail': Raise a ValueError.
# 'replace': Drop the table before inserting new values.
# 'append': Insert new values to the existing table.

See QUICKSTART for a full overview of functionality.

Update

Records in an SQL table are updated by simply providing a dataframe. By default a match on the SQL table's primary key is required for a record to be updated.

mssql.write.update(table_name, dataframe)

Merge

Records can be inserted/updated/deleted by providing a dataframe to the merge method. Again the primary key in the SQL table is used by default.

1. dataframe column value doesn't match SQL column value -> insert record into SQL
2. dataframe column value matches SQL column value -> update record in SQL
3. SQL column value not in dataframe column -> delete record in SQL

sql.write.merge(table_name, dataframe)

Upsert

The merge method can be restricted to not delete records in SQL by specifying the upsert flag. Records in SQL are then only inserted or updated.

sql.write.merge(table_name, dataframe, upsert=True)

Installation

pip install mssql-dataframe

Dependancies

pandas: Python DataFrames.

pyodbc: ODBC driver used for executing Transact-SQL statements.

Contributing

See CONTRIBUTING.