Skip to main content

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

Project description

mssql_dataframe

Test Status Coverage Status Flake8 Status PyPI Code style: black Build Status Open in Visual Studio Code

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.

Project details


Download files

Download the file for your platform. If you're not sure which to choose, learn more about installing packages.

Source Distribution

mssql_dataframe-1.4.0.tar.gz (7.0 kB view hashes)

Uploaded Source

Built Distribution

mssql_dataframe-1.4.0-py3-none-any.whl (7.5 kB view hashes)

Uploaded Python 3

Supported by

AWS AWS Cloud computing and Security Sponsor Datadog Datadog Monitoring Fastly Fastly CDN Google Google Download Analytics Microsoft Microsoft PSF Sponsor Pingdom Pingdom Monitoring Sentry Sentry Error logging StatusPage StatusPage Status page