{ "info": { "author": "Josh Dimarsky", "author_email": "", "bugtrack_url": null, "classifiers": [ "Development Status :: 3 - Alpha", "Intended Audience :: Developers", "Intended Audience :: Financial and Insurance Industry", "License :: OSI Approved :: MIT License", "Operating System :: OS Independent", "Programming Language :: Python :: 3.6", "Programming Language :: Python :: 3.7", "Programming Language :: Python :: 3.8", "Programming Language :: SQL", "Topic :: Database" ], "description": "# bcpandas\n\n[![Code style: black](https://img.shields.io/badge/code%20style-black-000000.svg)](https://github.com/psf/black)\n[![PyPI version](https://img.shields.io/pypi/v/bcpandas.svg)](https://pypi.org/project/bcpandas/)\n[![Conda-Forge version](https://img.shields.io/conda/vn/conda-forge/bcpandas.svg)](https://anaconda.org/conda-forge/bcpandas)\n[![GitHub license](https://img.shields.io/github/license/yehoshuadimarsky/bcpandas.svg)](https://github.com/yehoshuadimarsky/bcpandas/blob/master/LICENSE)\n[![PyPI pyversions](https://img.shields.io/pypi/pyversions/bcpandas.svg)](https://pypi.python.org/pypi/bcpandas/)\n[![PyPI status](https://img.shields.io/pypi/status/bcpandas.svg)](https://pypi.python.org/pypi/bcpandas/)\n[![Awesome Badges](https://img.shields.io/badge/badges-awesome-green.svg)](https://github.com/Naereen/badges)\n\n\nHigh-level wrapper around BCP for high performance data transfers between pandas and SQL Server. No knowledge of BCP required!!\n\n## Quickstart\n\n```python\nIn [1]: import pandas as pd\n ...: import numpy as np\n ...: \n ...: from bcpandas import SqlCreds, to_sql, read_sql\n\nIn [2]: creds = SqlCreds(\n ...: 'my_server',\n ...: 'my_db',\n ...: 'my_username',\n ...: 'my_password'\n ...: )\n\nIn [3]: df = pd.DataFrame(\n ...: data=np.ndarray(shape=(10, 6), dtype=int), \n ...: columns=[f\"col_{x}\" for x in range(6)]\n ...: )\n\nIn [4]: df\nOut[4]: \n col_0 col_1 col_2 col_3 col_4 col_5\n0 4128860 6029375 3801155 5570652 6619251 7536754\n1 4849756 7536751 4456552 7143529 7471201 7012467\n2 6029433 6881357 6881390 7274595 6553710 3342433\n3 6619228 7733358 6029427 6488162 6357104 6553710\n4 7536737 7077980 6422633 7536732 7602281 2949221\n5 6357104 7012451 6750305 7536741 7340124 7274610\n6 7340141 6226036 7274612 7077999 6881387 6029428\n7 6619243 6226041 6881378 6553710 7209065 6029415\n8 6881378 6553710 7209065 7536743 7274588 6619248\n9 6226030 7209065 6619231 6881380 7274612 3014770\n\nIn [5]: to_sql(df, 'my_test_table', creds, index=False, if_exists='replace')\n\nIn [6]: df2 = read_sql('my_test_table', creds)\n\nIn [7]: df2\nOut[7]: \n col_0 col_1 col_2 col_3 col_4 col_5\n0 4128860 6029375 3801155 5570652 6619251 7536754\n1 4849756 7536751 4456552 7143529 7471201 7012467\n2 6029433 6881357 6881390 7274595 6553710 3342433\n3 6619228 7733358 6029427 6488162 6357104 6553710\n4 7536737 7077980 6422633 7536732 7602281 2949221\n5 6357104 7012451 6750305 7536741 7340124 7274610\n6 7340141 6226036 7274612 7077999 6881387 6029428\n7 6619243 6226041 6881378 6553710 7209065 6029415\n8 6881378 6553710 7209065 7536743 7274588 6619248\n9 6226030 7209065 6619231 6881380 7274612 3014770\n```\n\n## Requirements\n- BCP Utility\n - [Windows](https://docs.microsoft.com/en-us/sql/tools/bcp-utility)\n- SqlCmd Utility\n - [Windows](https://docs.microsoft.com/en-us/sql/tools/sqlcmd-utility)\n- python >= 3.6\n- pandas\n\n## Benchmarks\n_# TODO_\n\n## Installation\nYou can download and install this package from PyPI\n\n```\npip install bcpandas\n```\n\nor from conda\n```\nconda install -c conda-forge bcpandas\n```\n\n## Caveats and Limitations\n\nHere are some caveats and limitations of bcpandas. Hopefully they will be addressed in future releases\n* In the `to_sql` function:\n * If `replace` is passed to the `if_exists` parameter, the new SQL table created will make the columns all of `NVARCHAR(MAX)` type.\n * If `append` is passed to the `if_exists` parameter, if the dataframe columns don't match the SQL table columns exactly by both name and order, it will fail.\n * If there is a NaN/Null in the last column of the dataframe it will throw an error. This is due to a BCP issue. See my issue with Microsoft about this [here](https://github.com/MicrosoftDocs/sql-docs/issues/2689) .\n * Bcpandas attempts to use a delimiter that is not present in the dataframe. This is because BCP does __not__ ignore delimiter characters when surrounded by quotes, unlike CSVs - see [here](https://docs.microsoft.com/en-us/sql/relational-databases/import-export/specify-field-and-row-terminators-sql-server#characters-supported-as-terminators) in the Microsoft docs. Therefore, if all possible delimiter characters are present in the dataframe and bcpandas cannot find a delimiter to use, it will throw an error.\n * Possible delimiters are specified in `constants.py` .\n* Currently the STDOUT stream from BCP and SqlCmd is not asynchronous.\n* Currently this is being built with Windows in mind. Linux support is definitely easily added, it's just not in the immediate scope of the project yet. PRs are welcome.\n\n## Motivations and Design\n### Overview\nReading and writing data from pandas DataFrames to/from a SQL database is very slow using the built-in `read_sql` and `to_sql` methods, even with the newly introduced `execute_many` option. For Microsoft SQL Server, a far far faster method is to use the BCP utility provided by Microsoft. This utility is a command line tool that transfers data to/from the database and flat text files.\n\nThis package is a wrapper for seamlessly using the bcp utility from Python using a pandas DataFrame. Despite the IO hits, the fastest option by far is saving the data to a CSV file in the file system and using the bcp utility to transfer the CSV file to SQL Server. **Best of all, you don't need to know anything about using BCP at all!**\n\n### Existing Solutions\n\n\n\n \n \n \n\n\n \n \n \n\n\n \n \n \n\n
NameGitHubPyPI
bcpyhttps://github.com/titan550/bcpyhttps://pypi.org/project/bcpy
magical-sqlserverhttps://github.com/brennoflavio/magical-sqlserverhttps://pypi.org/project/magical-sqlserver/
\n\n#### bcpy\n`bcpy` has several flaws:\n* No support for reading from SQL, only writing to SQL\n* A convoluted, overly class-based internal design\n* Scope a bit too broad - deals with pandas as well as flat files\n\nThis repository aims to fix and improve on `bcpy` and the above issues by making the design choices described below.\n\n> Much credit is due to `bcpy` for the original idea and for some of the code that was adopted and changed.\n\n#### magical-sqlserver\n`magical-sqlserver` is a library to make working with Python and SQL Server very easy. But it doesn't fit what I'm trying to do:\n* No built in support for pandas DataFrame\n* Larger codebase, I'm not fully comfortable with the dependency on the very heavy pymssql\n\n### Design and Scope\nThe _**only**_ scope of `bcpandas` is to read and write between a pandas DataFrame and a Microsoft SQL Server database. That's it. We do _**not**_ concern ourselves with reading existing flat files to/from SQL - that introduces _way_ to much complexity in trying to parse and decode the various parts of the file, like delimiters, quote characters, and line endings. Instead, to read/write an exiting flat file, just import it via pandas into a DataFrame, and then use `bcpandas`.\n\nThe big benefit of this is that we get to precicely control all the finicky parts of the text file when we write/read it to a local file and then in the BCP utility. This lets us set library-wide defaults (maybe configurable in the future) and work with those.\n\nFor now, we are using the non-XML BCP format file type. In the future, XML format files may be added.\n\nBoth on-prem and cloud (Azure, AWS, etc.) versions of SQL Server are supported.\n\n## Testing\nTesting uses `pytest`. A local SQL Server is spun up using Docker.\n\n## Contributing\nPlease, all contributions are very welcome! \n\nI will attempt to use the `pandas` docstring style as detailed [here](https://pandas.pydata.org/pandas-docs/stable/development/contributing_docstring.html).\n\n\n", "description_content_type": "text/markdown", "docs_url": null, "download_url": "", "downloads": { "last_day": -1, "last_month": -1, "last_week": -1 }, "home_page": "https://github.com/yehoshuadimarsky/bcpandas", "keywords": "bcp mssql pandas", "license": "", "maintainer": "", "maintainer_email": "", "name": "bcpandas", "package_url": "https://pypi.org/project/bcpandas/", "platform": "", "project_url": "https://pypi.org/project/bcpandas/", "project_urls": { "Homepage": "https://github.com/yehoshuadimarsky/bcpandas" }, "release_url": "https://pypi.org/project/bcpandas/0.1.8/", "requires_dist": [ "pandas (>=0.22)" ], "requires_python": ">=3.6", "summary": "High-level wrapper around BCP for high performance data transfers between pandas and SQL Server. No knowledge of BCP required!!", "version": "0.1.8" }, "last_serial": 5777275, "releases": { "0.1.0": [ { "comment_text": "", "digests": { "md5": "1540c4ceac549b72377359e585f9886c", "sha256": "b551d5e6117447f989c41abcab3c75f4940fc78a49332b2e8d9c6cf90dd0ed3d" }, "downloads": -1, "filename": "bcpandas-0.1.0-py3-none-any.whl", "has_sig": false, "md5_digest": "1540c4ceac549b72377359e585f9886c", "packagetype": "bdist_wheel", "python_version": "py3", "requires_python": ">=3.6", "size": 11442, "upload_time": "2019-08-06T01:47:04", "url": "https://files.pythonhosted.org/packages/f6/9a/ed425e445ea4f1b83884d71cecfc99e2408143b370675bf4617260b08d74/bcpandas-0.1.0-py3-none-any.whl" }, { "comment_text": "", "digests": { "md5": "d6654eb70d043e5c5596bd1af35bc485", "sha256": "fc720618c7dcc3e294f20fc247420afebe4f0de877631a310f836085b1954bd6" }, "downloads": -1, "filename": "bcpandas-0.1.0.tar.gz", "has_sig": false, "md5_digest": "d6654eb70d043e5c5596bd1af35bc485", "packagetype": "sdist", "python_version": "source", "requires_python": ">=3.6", "size": 9528, "upload_time": "2019-08-06T01:47:06", "url": "https://files.pythonhosted.org/packages/6d/d3/a3c028bfbcbbab7cc08462f7bcf3e72afdb50ff42b6cb78cbdbe4ab1e91c/bcpandas-0.1.0.tar.gz" } ], "0.1.1": [ { "comment_text": "", "digests": { "md5": "cccfbbb8afe6a5a0d3fb78d8f1896aa6", "sha256": "a6d484a61c8aac3fe6711cbff32d17f03cdc14a3cada561a1119d756e1bea248" }, "downloads": -1, "filename": "bcpandas-0.1.1-py3-none-any.whl", "has_sig": false, "md5_digest": "cccfbbb8afe6a5a0d3fb78d8f1896aa6", "packagetype": "bdist_wheel", "python_version": "py3", "requires_python": ">=3.6", "size": 11442, "upload_time": "2019-08-06T02:07:53", "url": "https://files.pythonhosted.org/packages/e3/2c/061ba4072f10f6b7ce0d72b1ead7322b5cd8dcbd451f078132e23e041a45/bcpandas-0.1.1-py3-none-any.whl" }, { "comment_text": "", "digests": { "md5": "cc482c04a104fd976da6a66e93e5a9ae", "sha256": "71c9e247e905eb13824671684f2f26fc58e994bee8d1e7e3c48e0b78239e00f3" }, "downloads": -1, "filename": "bcpandas-0.1.1.tar.gz", "has_sig": false, "md5_digest": "cc482c04a104fd976da6a66e93e5a9ae", "packagetype": "sdist", "python_version": "source", "requires_python": ">=3.6", "size": 9543, "upload_time": "2019-08-06T02:07:54", "url": "https://files.pythonhosted.org/packages/50/35/f4c299266167ef5290ba7ce031eae4a250f2c6e804229e39fca77205ce93/bcpandas-0.1.1.tar.gz" } ], "0.1.2": [ { "comment_text": "", "digests": { "md5": "04bbd6c959c6bc607381d2d4193bf734", "sha256": "b4fc99297fb698c47eb5d56bea55cd1a1c82810dd853d8f2527a065c9d71bc91" }, "downloads": -1, "filename": "bcpandas-0.1.2-py3-none-any.whl", "has_sig": false, "md5_digest": "04bbd6c959c6bc607381d2d4193bf734", "packagetype": "bdist_wheel", "python_version": "py3", "requires_python": ">=3.6", "size": 11436, "upload_time": "2019-08-06T02:36:34", "url": "https://files.pythonhosted.org/packages/c0/4f/3cad40f43a412ffc0c51859ad1d39a683445bffac6126757792c78f3aa4a/bcpandas-0.1.2-py3-none-any.whl" }, { "comment_text": "", "digests": { "md5": "697250f4cc0b086d94f89fb82baa4f89", "sha256": "f451bb7cc0b35f8266d3f09f73005271897313293b94132f003236e796d6ac9b" }, "downloads": -1, "filename": "bcpandas-0.1.2.tar.gz", "has_sig": false, "md5_digest": "697250f4cc0b086d94f89fb82baa4f89", "packagetype": "sdist", "python_version": "source", "requires_python": ">=3.6", "size": 9540, "upload_time": "2019-08-06T02:36:35", "url": "https://files.pythonhosted.org/packages/9d/1f/9fd60370fd4cba6e2c8d525ba97c4f24e816b8788b032eed2ac536a789fe/bcpandas-0.1.2.tar.gz" } ], "0.1.4": [ { "comment_text": "", "digests": { "md5": "dc29995545df0f34e822a46f13c1a7c4", "sha256": "72e704a0cdfd80654b91780c52d77cf519c5fb7bd4d300e9d961b12b02fa8fba" }, "downloads": -1, "filename": "bcpandas-0.1.4-py3-none-any.whl", "has_sig": false, "md5_digest": "dc29995545df0f34e822a46f13c1a7c4", "packagetype": "bdist_wheel", "python_version": "py3", "requires_python": null, "size": 12036, "upload_time": "2019-08-07T19:25:03", "url": "https://files.pythonhosted.org/packages/bc/ad/b47917caf2fe7d9bd03ed06453906202597c3cd7188c0c9aa398c9de70f8/bcpandas-0.1.4-py3-none-any.whl" }, { "comment_text": "", "digests": { "md5": "e7339a3bbdd371d84ee1ccfb55f6becc", "sha256": "5c7fd9487189869132c7eb50d92a857817570aca0a66b057435d477e98216123" }, "downloads": -1, "filename": "bcpandas-0.1.4.tar.gz", "has_sig": false, "md5_digest": "e7339a3bbdd371d84ee1ccfb55f6becc", "packagetype": "sdist", "python_version": "source", "requires_python": null, "size": 10885, "upload_time": "2019-08-07T19:25:05", "url": "https://files.pythonhosted.org/packages/33/5b/71a711efedd7e8dbc04d875d2ff9fa080bae9a20298fa30bc89658212688/bcpandas-0.1.4.tar.gz" } ], "0.1.5": [ { "comment_text": "", "digests": { "md5": "e3fdebb577eb7b51b83916e9084b8da1", "sha256": "388a23a61624bf11fbe721b2f4658a9bd61a9370f4d9326d4f0a878b3a4c6d01" }, "downloads": -1, "filename": "bcpandas-0.1.5-py3-none-any.whl", "has_sig": false, "md5_digest": "e3fdebb577eb7b51b83916e9084b8da1", "packagetype": "bdist_wheel", "python_version": "py3", "requires_python": null, "size": 12035, "upload_time": "2019-08-07T19:41:46", "url": "https://files.pythonhosted.org/packages/3a/40/0322d926cad49f289ec218e946ab00e4115436c1f1dde37131934f365ea0/bcpandas-0.1.5-py3-none-any.whl" }, { "comment_text": "", "digests": { "md5": "866fb5fe499379e79b218a1f2faac91c", "sha256": "e8641aca4e00f67b8db1a94bd5300a9bff95b5f51ad8cfdb1138485fec3745ee" }, "downloads": -1, "filename": "bcpandas-0.1.5.tar.gz", "has_sig": false, "md5_digest": "866fb5fe499379e79b218a1f2faac91c", "packagetype": "sdist", "python_version": "source", "requires_python": null, "size": 10888, "upload_time": "2019-08-07T19:41:47", "url": "https://files.pythonhosted.org/packages/e1/76/8a7450b0ed23eb6ba4fb0721127d8816fc494c255ad9fbe6125513cb01bc/bcpandas-0.1.5.tar.gz" } ], "0.1.6": [ { "comment_text": "", "digests": { "md5": "658d73811c5383cb5bf439e92cd9c2e9", "sha256": "8a29b0ab2578ee5403670628ecc6831bc6c820e5416b2ce9b9241fddef88b858" }, "downloads": -1, "filename": "bcpandas-0.1.6-py3-none-any.whl", "has_sig": false, "md5_digest": "658d73811c5383cb5bf439e92cd9c2e9", "packagetype": "bdist_wheel", "python_version": "py3", "requires_python": ">=3.6", "size": 12031, "upload_time": "2019-08-07T22:30:58", "url": "https://files.pythonhosted.org/packages/8d/1a/d46b55e661bd30e3dbb77a9a98af8a2c31a8979bc9c252b17e09c53786b2/bcpandas-0.1.6-py3-none-any.whl" }, { "comment_text": "", "digests": { "md5": "e1ee854a1d96e9f0396927e80ea61194", "sha256": "1d0b427a5eec0f4d028c5cb9dc25e074a97dfaf2e9de40e85bfc22ca898da717" }, "downloads": -1, "filename": "bcpandas-0.1.6.tar.gz", "has_sig": false, "md5_digest": "e1ee854a1d96e9f0396927e80ea61194", "packagetype": "sdist", "python_version": "source", "requires_python": ">=3.6", "size": 10913, "upload_time": "2019-08-07T22:31:00", "url": "https://files.pythonhosted.org/packages/81/6a/7a40bb62d8680981de40a337a064e2c0f544293738d2ed5bc678b9594b5e/bcpandas-0.1.6.tar.gz" } ], "0.1.7": [ { "comment_text": "", "digests": { "md5": "afa22c61e9c4db5073c3c4e5048ffcbb", "sha256": "ee4df21b768ee41831d452223a08193958ae25ffdf4af79e2ddfeefc045d308c" }, "downloads": -1, "filename": "bcpandas-0.1.7-py3-none-any.whl", "has_sig": false, "md5_digest": "afa22c61e9c4db5073c3c4e5048ffcbb", "packagetype": "bdist_wheel", "python_version": "py3", "requires_python": ">=3.6", "size": 13055, "upload_time": "2019-08-15T14:10:59", "url": "https://files.pythonhosted.org/packages/0e/64/7df8d17f1051bb346403b6830135d3a61bcaf060fb1e15c381d0b41064cf/bcpandas-0.1.7-py3-none-any.whl" }, { "comment_text": "", "digests": { "md5": "43922bba3f2914db7ca9d365a49fccba", "sha256": "fb266639cd6e03b7aab3b0012d4e157e59f22845a7818aad149be5759f1b3edc" }, "downloads": -1, "filename": "bcpandas-0.1.7.tar.gz", "has_sig": false, "md5_digest": "43922bba3f2914db7ca9d365a49fccba", "packagetype": "sdist", "python_version": "source", "requires_python": ">=3.6", "size": 13982, "upload_time": "2019-08-15T14:11:00", "url": "https://files.pythonhosted.org/packages/1a/5a/f7f6c8642832429333d83513fa8a5fcdecd66e20ba562d61d250cb23b135/bcpandas-0.1.7.tar.gz" } ], "0.1.8": [ { "comment_text": "", "digests": { "md5": "b77280972b60bc2f581ece551d446390", "sha256": "c98780e30282f1469b7a661361e2f0a7a3aad3b5f168358fd10a61cffb664363" }, "downloads": -1, "filename": "bcpandas-0.1.8-py3-none-any.whl", "has_sig": false, "md5_digest": "b77280972b60bc2f581ece551d446390", "packagetype": "bdist_wheel", "python_version": "py3", "requires_python": ">=3.6", "size": 13182, "upload_time": "2019-09-03T18:01:15", "url": "https://files.pythonhosted.org/packages/aa/af/61a26f24199993b03fb7e45f5af51280c607e1cc48b7016b34b573cd6f35/bcpandas-0.1.8-py3-none-any.whl" }, { "comment_text": "", "digests": { "md5": "59bc82b4eceb33162afe32c1c3c1119e", "sha256": "fb651804d98348c5541ea0e7a7acb7664f0f28dbc0ed5d34b7d43f0b21564776" }, "downloads": -1, "filename": "bcpandas-0.1.8.tar.gz", "has_sig": false, "md5_digest": "59bc82b4eceb33162afe32c1c3c1119e", "packagetype": "sdist", "python_version": "source", "requires_python": ">=3.6", "size": 14143, "upload_time": "2019-09-03T18:01:16", "url": "https://files.pythonhosted.org/packages/cc/4e/e63974ee9b6ad9fc131171ab0a7757cc46942cdf3bab5b4b84ce50a5ceef/bcpandas-0.1.8.tar.gz" } ] }, "urls": [ { "comment_text": "", "digests": { "md5": "b77280972b60bc2f581ece551d446390", "sha256": "c98780e30282f1469b7a661361e2f0a7a3aad3b5f168358fd10a61cffb664363" }, "downloads": -1, "filename": "bcpandas-0.1.8-py3-none-any.whl", "has_sig": false, "md5_digest": "b77280972b60bc2f581ece551d446390", "packagetype": "bdist_wheel", "python_version": "py3", "requires_python": ">=3.6", "size": 13182, "upload_time": "2019-09-03T18:01:15", "url": "https://files.pythonhosted.org/packages/aa/af/61a26f24199993b03fb7e45f5af51280c607e1cc48b7016b34b573cd6f35/bcpandas-0.1.8-py3-none-any.whl" }, { "comment_text": "", "digests": { "md5": "59bc82b4eceb33162afe32c1c3c1119e", "sha256": "fb651804d98348c5541ea0e7a7acb7664f0f28dbc0ed5d34b7d43f0b21564776" }, "downloads": -1, "filename": "bcpandas-0.1.8.tar.gz", "has_sig": false, "md5_digest": "59bc82b4eceb33162afe32c1c3c1119e", "packagetype": "sdist", "python_version": "source", "requires_python": ">=3.6", "size": 14143, "upload_time": "2019-09-03T18:01:16", "url": "https://files.pythonhosted.org/packages/cc/4e/e63974ee9b6ad9fc131171ab0a7757cc46942cdf3bab5b4b84ce50a5ceef/bcpandas-0.1.8.tar.gz" } ] }