Skip to main content
Python Software Foundation 20th Year Anniversary Fundraiser  Donate today!

Microsoft SQL Server bcp (Bulk Copy) wrapper

Project description

bcpy

Latest Release latest release
License license
Build Status (master) travis build status

What is it?

This package is a wrapper for Microsoft's SQL Server bcp utility. Current database drivers available in Python are not fast enough for transferring millions of records (yes, I have tried pyodbc fast_execute_many). Despite the IO hits, the fastest option by far is saving the data to a CSV file in file system (preferably /dev/shm tmpfs) and using the bcp utility to transfer the CSV file to SQL Server.

How Can I Install It?

  1. Make sure your computeer has the requirements.
  2. You can download and install this package from PyPI repository by running the command below.
pip install bcpy

Examples

Following examples show you how to load (1) flat files and (2) DataFrame objects to SQL Server using this package.

Flat File

Following example assumes that you have a comma separated file with no qualifier in path 'tests/data1.csv'. The code below sends the the file to SQL Server.

import bcpy


sql_config = {
    'server': 'sql_server_hostname',
    'database': 'database_name',
    'username': 'test_user',
    'password': 'test_user_password1234'
}
sql_table_name = 'test_data1'
csv_file_path = 'tests/data1.csv'
flat_file = bcpy.FlatFile(qualifier='', path=csv_file_path)
sql_table = bcpy.SqlTable(sql_config, table=sql_table_name)
flat_file.to_sql(sql_table)

DataFrame

The following example creates a DataFrame with 100 rows and 4 columns populated with random data and then it sends it to SQL Server.

import bcpy
import numpy as np
import pandas as pd


sql_config = {
    'server': 'sql_server_hostname',
    'database': 'database_name',
    'username': 'test_user',
    'password': 'test_user_password1234'
}
table_name = 'test_dataframe'
df = pd.DataFrame(np.random.randint(-100, 100, size=(100, 4)),
                  columns=list('ABCD'))
bdf = bcpy.DataFrame(df)
sql_table = bcpy.SqlTable(sql_config, table=table_name)
bdf.to_sql(sql_table)

Requirements

You need a working version of Microsoft bcp installed in your system. Your PATH environment variable should contain the directory of the bcp utility. Following are the installation tutorials for different operating systems.

Project details


Download files

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

Files for bcpy, version 0.1.8
Filename, size File type Python version Upload date Hashes
Filename, size bcpy-0.1.8.tar.gz (8.1 kB) File type Source Python version None Upload date Hashes View

Supported by

AWS AWS Cloud computing Datadog Datadog Monitoring DigiCert DigiCert EV certificate Facebook / Instagram Facebook / Instagram PSF Sponsor Fastly Fastly CDN Google Google Object Storage and Download Analytics Microsoft Microsoft PSF Sponsor Pingdom Pingdom Monitoring Salesforce Salesforce PSF Sponsor Sentry Sentry Error logging StatusPage StatusPage Status page