fivestack/bcp

This is a python utility that allows users to import/export data to/from a database.

BCP

< badges will go here >

This is a python utility that allows users to import/export data to/from a database.

---

Overview

This library began as a wrapper around SQL Server's BCP utility. It makes some assumptions about parameters to simplify the interface and allow the user to work natively in python. Though it currently supports MSSQL, there are plans to extend support to other database dialects.

Requirements

• Python 3.6+

This library purposely requires no python packages outside of the standard library, beyond testing and documentation needs. The intention is to maintain this status. However, you will need to have the appropriate command line utilities installed for the specific database dialects with which you'll interact. For example, if your database is a MS SQL SERVER instance, you'll need BCP installed. Consult the table below for further documentation, including download files and instructions.

RDBMS	Utility	Documentation / Installation
MS SQL Server	BCP	https://docs.microsoft.com/en-us/sql/tools/bcp-utility

Installation

This library is still in development. So you'll have to build it from source in the meantime. I'll soon get around to publishing it on pypi, in which case you'll be able to install it using pip

pip install bcp

Examples

Import data:

import bcp

conn = bcp.Connection(host='HOST', driver='mssql', username='USER', password='PASSWORD')
my_bcp = bcp.BCP(conn)
file = bcp.DataFile(file_path='path/to/file.csv', delimiter=',')
my_bcp.load(input_file=file, table='table_name')

Export data:

import bcp

conn = bcp.Connection(host='HOST', driver='mssql', username='USER', password='PASSWORD')
my_bcp = bcp.BCP(conn)
file = bcp.DataFile(file_path='path/to/file.csv', delimiter=',')
my_bcp.dump(query='select * from sys.tables', output_file=file)

Full Documentation

For the full documentation, please visit: https://bcp.readthedocs.io/en/latest/

bcp's Issues

Setup CI/CD

Add configuration for the selected tool (probably TravisCI)
Add test/coverage/build badges to the README

Missing `-o` option on Mac

I don't have the -o option, so this throws an error.

| $ bcp -h
usage: bcp {dbtable | query} {in | out | queryout | format} datafile
  [-m maxerrors]            [-f formatfile]          [-e errfile]
  [-F firstrow]             [-L lastrow]             [-b batchsize]
  [-n native type]          [-c character type]      [-w wide character type]
  [-N keep non-text native] [-q quoted identifier]
  [-t field terminator]     [-r row terminator]
  [-a packetsize]           [-K application intent]
  [-S server name or DSN if -D provided]             [-D treat -S as DSN]
  [-U username]             [-P password]
  [-T trusted connection]   [-v version]             [-R regional enable]
  [-k keep null values]     [-E keep identity values]
  [-h "load hints"]         [-d database name]
| $ bcp -v
BCP - Bulk Copy Program for Microsoft SQL Server.
Copyright (C) Microsoft Corporation. All Rights Reserved.
Version: 17.5.0002.1

subprocess.popen throws "File name too long" on Mac

If I pass a longer query through, I get an OSError: [Errno 63] File name too long: <redacted bcp command>. Adding a shell=True to subprocess.run fixes things, but I don't recall all the details of subprocess to remember if this is a general solution.

Change license to Apache 2.0

How to set batch_size,port,character paramter?

Thank you very much for your code.

In the python3 environment:

How to set like a command line?

-b batch_size
-e errorfile
-c Causes the bcp utility to use character data.*
Sql port (such as 14331)

thanks

Return values for dump() and load() are unnecessary, and could be misleading

Add encryption for writing/reading the data file

See if the bcp utility can encrypt the data prior to writing it to a file so that data is not stored in plain text. If that's not an option, then look into encrypting the file once it is created to minimize the amount of time data is exposed in plain text.

Add database and schema as optional parameters for the Connection class

This could simplify the interface for loading data into mssql databases. But schema wouldn't be used by the BCP command line utility, so it could be misleading. Setting at the connection level may lead a user to think that the provided query does not need a two/three part name when they are not querying the default schema.

Add "skip headers" argument (-F in BCP CLI)

The (MSSQL) BCP CLI has an option to skip header rows: "-F". This option is needed specifically for reading files in. There are two interpretations of the number that would be passed in. It is either the number of rows to skip, which would default to 0. Or it is the line on which to start reading, which would default to 1. The latter is the way BCP's -F flag works, but may not be as intuitive in the python mindset. I'm open to arguments, and I'm currently thinking of how the skiprows option works in pandas.read_csv(). I think I want to be consistent with python more than MSSQL BCP.

Running error in python3, ubuntu18.04 environment，Where is the problem?

ubuntu 18.04

1. Use bcp in/out, both normal

/opt/mssql-tools/bin/bcp dbo.ip2 out ult2.txt -S 192.168.1.3 -U tmsdb -P 123 -d tmsdb -t',' -c

Starting copy...
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC Driver 17 for SQL Server]Warning: BCP import with a format file will convert empty strings in delimited columns to NULL.
1000 rows successfully bulk-copied to host-file. Total received: 1000

2.run sql.py. error

``sql1.py source code `
from bcp import BCP, Connection, DataFile
conn = Connection(host='192.168.1.3', driver='mssql', username='tmsdb', password='123')
bcp1 = BCP(conn)
dbfile = DataFile(file_path='111.txt', delimiter=',')
bcp1.load(dbfile, 'dbo.ip2')

3.run  python3 sql1.py  error message

> (base) aaa@aaa1:~/edit1/dk$ python3 sql1.py 
> Traceback (most recent call last):
>   File "sql1.py", line 17, in <module>
>     bcp1.load(dbfile, 'dbo.ip2')
>   File "/usr/local/lib/python3.6/dist-packages/bcp/core.py", line 61, in load
>     return load.execute()
>   File "/usr/local/lib/python3.6/dist-packages/bcp/dialects/mssql.py", line 81, in execute
>     subprocess.run(f'bcp {self.command}', check=True)
>   File "/usr/local/lib/python3.6/dist-packages/bcp/dialects/mssql.py", line 92, in command
>     return f'{self.table} in "{self.file.path}" {self.connection} {self.config} {self.logging} {self.error}'
>   File "/usr/local/lib/python3.6/dist-packages/bcp/files.py", line 56, in path
>     return self.file.absolute()
**> AttributeError: 'str' object has no attribute 'absolute'**

4. Where is the general problem?

Is it possible to correct the readme file.
Add examples of parameters such as database name, char, and batch.
bcp dbo.ip2 in ult2.txt -S 192.168.1.3 -U tmsdb -P 123 -d tmsdb -t',' -c -b 3

Looking forward to your reply, thank you!