{ "info": { "author": "Gabriel Bordeaux", "author_email": "pypi@gab.lc", "bugtrack_url": null, "classifiers": [ "Development Status :: 5 - Production/Stable", "License :: OSI Approved :: MIT License", "Natural Language :: English", "Operating System :: MacOS", "Operating System :: POSIX :: Linux", "Programming Language :: Python", "Programming Language :: Python :: 2.7", "Programming Language :: Python :: 3", "Topic :: Database", "Topic :: Database :: Database Engines/Servers", "Topic :: Software Development" ], "description": "# mysql-batch\n\n[![Build Status](https://travis-ci.org/gabfl/mysql-batch.svg?branch=master)](https://travis-ci.org/gabfl/mysql-batch)\n\nUpdating or deleting a large amount of rows in MySQL will create locks that will paralyze other queries running in parallel.\n\nThis tool will run UPDATE and DELETE queries in small batches to prevent table-level and row-level locking (with InnoDB). If a large number of rows has to be updated or deleted, it is also possible to limit the number of rows selected at once.\n\n## Installation\n\n```\npip3 install mysql_batch\n```\n\n## UPDATE example\n\nYou can run this example with the schema available in [sample_table/schema.sql](sample_table/schema.sql)\n\nThe following example will be identical to the following update:\n\n```sql\nUPDATE batch_test SET date = NOW() WHERE number > 0.2 AND date is NULL;\n```\n\nThis is the equivalent to process this update with batches of 20 rows:\n\n```bash\nmysql_batch --host localhost \\\n --user root \\\n --password secret_password \\\n --database \"test\" \\\n --table \"batch_test\" \\\n --write_batch_size 20 \\\n --where \"number > 0.2 AND date IS NULL\" \\\n --set \"date = NOW()\"\n```\n\nOutput sample:\n\n```bash\n* Selecting data...\n query: SELECT id as id FROM batch_test WHERE number > 0.2 AND date IS NULL AND id > 0 ORDER BY id LIMIT 1000\n* Preparing to modify 83 rows...\n* Updating 20 rows...\n query: UPDATE batch_test SET date = NOW() WHERE id IN (1, 2, 3, 4, 5, 6, 8, 9, 10, 11, 12, 14, 15, 16, 17, 18, 19, 20, 21, 22)\n* Start updating? [Y/n]\n* Updating 20 rows...\n query: UPDATE batch_test SET date = NOW() WHERE id IN (23, 25, 26, 28, 29, 30, 31, 33, 35, 36, 37, 38, 39, 40, 42, 43, 44, 45, 46, 47)\n* Updating 20 rows...\n query: UPDATE batch_test SET date = NOW() WHERE id IN (48, 49, 50, 51, 52, 53, 54, 55, 56, 58, 59, 60, 61, 63, 64, 65, 68, 69, 70, 71)\n* Updating 20 rows...\n query: UPDATE batch_test SET date = NOW() WHERE id IN (72, 74, 75, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 88, 89, 90, 91, 92, 94, 95)\n* Updating 3 rows...\n query: UPDATE batch_test SET date = NOW() WHERE id IN (97, 98, 100)\n* Selecting data...\n query: SELECT id as id FROM batch_test WHERE number > 0.2 AND date IS NULL AND id > 100 ORDER BY id LIMIT 1000\n* No more rows to modify!\n* Program exited\n```\n\n## DELETE example\n\nThe following example will be identical to the following delete:\n\n```sql\nDELETE FROM batch_test WHERE number > 0.2 AND date is NULL;\n```\n\nThis is the equivalent to process this delete with batches of 20 rows:\n\n```bash\nmysql_batch --host localhost \\\n --user root \\\n --password secret_password \\\n --database \"test\" \\\n --table \"batch_test\" \\\n --write_batch_size 20 \\\n --where \"number > 0.2 AND date IS NULL\" \\\n --action \"delete\"\n```\n\nOutput sample:\n\n```bash\n* Selecting data...\n query: SELECT id as id FROM batch_test WHERE number > 0.2 AND date IS NULL AND id > 0 ORDER BY id LIMIT 1000\n* Preparing to modify 79 rows...\n* Deleting 20 rows...\n query: DELETE FROM batch_test WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 14, 15, 17, 19, 20, 21, 22, 23)\n* Start deleting? [Y/n]\n* Deleting 20 rows...\n query: DELETE FROM batch_test WHERE id IN (24, 25, 26, 28, 34, 35, 36, 37, 38, 39, 40, 41, 44, 45, 47, 48, 50, 51, 52, 53)\n* Deleting 20 rows...\n query: DELETE FROM batch_test WHERE id IN (54, 56, 57, 58, 60, 61, 62, 63, 64, 65, 66, 67, 68, 70, 71, 72, 73, 74, 75, 76)\n* Deleting 19 rows...\n query: DELETE FROM batch_test WHERE id IN (77, 78, 79, 80, 82, 83, 86, 87, 88, 89, 90, 91, 93, 94, 95, 96, 98, 99, 100)\n* Selecting data...\n query: SELECT id as id FROM batch_test WHERE number > 0.2 AND date IS NULL AND id > 100 ORDER BY id LIMIT 1000\n* No more rows to modify!\n* Program exited\n```\n\n## Usage\n\n```bash\nusage: mysql_batch [-h] [-H HOST] [-P PORT] -U USER [-p PASSWORD] -d DATABASE\n -t TABLE [-id PRIMARY_KEY] -w WHERE [-s SET]\n [-rbz READ_BATCH_SIZE] [-wbz WRITE_BATCH_SIZE] [-S SLEEP]\n [-a {update,delete}] [-n]\n\noptional arguments:\n -h, --help show this help message and exit\n -H HOST, --host HOST MySQL server host\n -P PORT, --port PORT MySQL server port\n -U USER, --user USER MySQL user\n -p PASSWORD, --password PASSWORD\n MySQL password\n -d DATABASE, --database DATABASE\n MySQL database name\n -t TABLE, --table TABLE\n MySQL table\n -id PRIMARY_KEY, --primary_key PRIMARY_KEY\n Name of the primary key column\n -w WHERE, --where WHERE\n Select WHERE clause\n -s SET, --set SET Update SET clause\n -rbz READ_BATCH_SIZE, --read_batch_size READ_BATCH_SIZE\n Select batch size\n -wbz WRITE_BATCH_SIZE, --write_batch_size WRITE_BATCH_SIZE\n Update/delete batch size\n -S SLEEP, --sleep SLEEP\n Sleep after each batch\n -a {update,delete}, --action {update,delete}\n Action ('update' or 'delete')\n -n, --no_confirm Don't ask for confirmation before to run the write\n queries\n```\n\n## License\n\nThis program is under MIT license ([view license](LICENSE)).\n\n\n", "description_content_type": "", "docs_url": null, "download_url": "", "downloads": { "last_day": -1, "last_month": -1, "last_week": -1 }, "home_page": "https://github.com/gabfl/mysql-batch", "keywords": "", "license": "MIT", "maintainer": "", "maintainer_email": "", "name": "mysql-batch", "package_url": "https://pypi.org/project/mysql-batch/", "platform": "", "project_url": "https://pypi.org/project/mysql-batch/", "project_urls": { "Homepage": "https://github.com/gabfl/mysql-batch" }, "release_url": "https://pypi.org/project/mysql-batch/1.2/", "requires_dist": [ "pymysql", "argparse" ], "requires_python": "", "summary": "Run large MySQL UPDATE and DELETE queries with small batches to prevent table/row-level locks", "version": "1.2" }, "last_serial": 3893339, "releases": { "1.0.3": [ { "comment_text": "", "digests": { "md5": "4c75f98e1967412ee3f91b0bd816a916", "sha256": "4a61a1489503f589819b93ab23ce8bf7e1705b0920b87d50dcf6b17577ffcb3d" }, "downloads": -1, "filename": "mysql_batch-1.0.3-py2.py3-none-any.whl", "has_sig": false, "md5_digest": "4c75f98e1967412ee3f91b0bd816a916", "packagetype": "bdist_wheel", "python_version": "py2.py3", "requires_python": null, "size": 8278, "upload_time": "2017-07-06T13:44:44", "url": "https://files.pythonhosted.org/packages/97/12/e3577db09d0e5276dbe1e0e2a509d4665229ee05eb2dca07e80b93ce5ff3/mysql_batch-1.0.3-py2.py3-none-any.whl" }, { "comment_text": "", "digests": { "md5": "7eaf5ec837dc3fc80638ade087760232", "sha256": "90c30fd426c10c84a033382a5ea13cb4c00b97217c9abf815dfd885eb8570be9" }, "downloads": -1, "filename": "mysql_batch-1.0.3.tar.gz", "has_sig": false, "md5_digest": "7eaf5ec837dc3fc80638ade087760232", "packagetype": "sdist", "python_version": "source", "requires_python": null, "size": 5000, "upload_time": "2017-07-06T13:44:46", "url": "https://files.pythonhosted.org/packages/fe/71/341554af784ba270776af22d2293e57edf787a077a3777119b0b2b85f5f0/mysql_batch-1.0.3.tar.gz" } ], "1.0.4": [ { "comment_text": "", "digests": { "md5": "bd02ff6e2636986508a6b1191d02a90d", "sha256": "2e36b13485edf1371ecd756837b30263c3a384312540a8eec547bc58d64d1924" }, "downloads": -1, "filename": "mysql_batch-1.0.4-py2.py3-none-any.whl", "has_sig": false, "md5_digest": "bd02ff6e2636986508a6b1191d02a90d", "packagetype": "bdist_wheel", "python_version": "py2.py3", "requires_python": null, "size": 8267, "upload_time": "2017-07-10T01:37:17", "url": "https://files.pythonhosted.org/packages/e7/6c/93c3d43f1f6194940035405f491d752a6fd268c971f8ff80cca2e4198d30/mysql_batch-1.0.4-py2.py3-none-any.whl" }, { "comment_text": "", "digests": { "md5": "eb1d247cc0b03964f509bfeec1324f77", "sha256": "681a8013d28e722df5d7d0f30861298bb3408efa3562a77c5a99d7d403b04514" }, "downloads": -1, "filename": "mysql_batch-1.0.4.tar.gz", "has_sig": false, "md5_digest": "eb1d247cc0b03964f509bfeec1324f77", "packagetype": "sdist", "python_version": "source", "requires_python": null, "size": 5017, "upload_time": "2017-07-10T01:37:19", "url": "https://files.pythonhosted.org/packages/ed/47/41cab317088fdbd4186f744ef7957985b1e0ff36c296b26c99c0cc361053/mysql_batch-1.0.4.tar.gz" } ], "1.0.5": [ { "comment_text": "", "digests": { "md5": "e08caaa09d2083b4c5db4a3b6fbaeb98", "sha256": "7449227f00374c649e3a5180206aabb4b78bfc284c4b3ef6b33ecdd5fa206979" }, "downloads": -1, "filename": "mysql_batch-1.0.5-py2.py3-none-any.whl", "has_sig": false, "md5_digest": "e08caaa09d2083b4c5db4a3b6fbaeb98", "packagetype": "bdist_wheel", "python_version": "py2.py3", "requires_python": null, "size": 8262, "upload_time": "2017-07-12T15:50:34", "url": "https://files.pythonhosted.org/packages/34/6b/a271a8514143f65ae4fcafc065035a4551c23f7e742e612df74c0c6019a5/mysql_batch-1.0.5-py2.py3-none-any.whl" }, { "comment_text": "", "digests": { "md5": "3ca1a604bbe73a6867efd8fe959d033e", "sha256": "3e44dc6e5cd53e20bd0a41020f2e375f34fbeac0ddbdd04d1481fb7a6bcc90a2" }, "downloads": -1, "filename": "mysql_batch-1.0.5.tar.gz", "has_sig": false, "md5_digest": "3ca1a604bbe73a6867efd8fe959d033e", "packagetype": "sdist", "python_version": "source", "requires_python": null, "size": 6136, "upload_time": "2017-07-12T15:50:36", "url": "https://files.pythonhosted.org/packages/34/bf/fd6faed6f1edd0b01992cdb9a2e768ac88844ef80dc8e91e7aaee0719c66/mysql_batch-1.0.5.tar.gz" } ], "1.0.6": [ { "comment_text": "", "digests": { "md5": "05e23a04c0bcd4b9cb137d0387d68e61", "sha256": "f9666df03a0734b6692652c7ad25113edd9287f33408f2ccb34e35d4fd89696f" }, "downloads": -1, "filename": "mysql_batch-1.0.6-py2.py3-none-any.whl", "has_sig": false, "md5_digest": "05e23a04c0bcd4b9cb137d0387d68e61", "packagetype": "bdist_wheel", "python_version": "py2.py3", "requires_python": null, "size": 8599, "upload_time": "2017-07-14T12:59:35", "url": "https://files.pythonhosted.org/packages/e0/c3/0e016e8b73c47bce7ab34f1e9e4366ec0a094c07b4cedbd6ecc2fccaaf3b/mysql_batch-1.0.6-py2.py3-none-any.whl" }, { "comment_text": "", "digests": { "md5": "743c8f20c28401b5c14b858794172583", "sha256": "a4a4bd77aa49e893560db9035deee8beaa4e2ee241071725492518b33349367c" }, "downloads": -1, "filename": "mysql_batch-1.0.6.tar.gz", "has_sig": false, "md5_digest": "743c8f20c28401b5c14b858794172583", "packagetype": "sdist", "python_version": "source", "requires_python": null, "size": 6406, "upload_time": "2017-07-14T12:59:36", "url": "https://files.pythonhosted.org/packages/e7/87/3d7e66b3efccb7946f21679d776e1bcb1947b0ca37737c67e07f618fb08a/mysql_batch-1.0.6.tar.gz" } ], "1.1": [ { "comment_text": "", "digests": { "md5": "8c8e5da694515e125d7ca24e8da73925", "sha256": "1362437cc50fe3f5eac3dc967283664abcb29dd544b930554a917fe6a72f6907" }, "downloads": -1, "filename": "mysql_batch-1.1-py2.py3-none-any.whl", "has_sig": false, "md5_digest": "8c8e5da694515e125d7ca24e8da73925", "packagetype": "bdist_wheel", "python_version": "py2.py3", "requires_python": null, "size": 9088, "upload_time": "2018-05-24T01:01:31", "url": "https://files.pythonhosted.org/packages/35/ab/81a05a453311eb9785b15fc23a3bf5a131d9d8a57c9b5c417449f46a5f1a/mysql_batch-1.1-py2.py3-none-any.whl" }, { "comment_text": "", "digests": { "md5": "15c9d7dc95b03fce819b02370339bcb9", "sha256": "7e8d00d0facab9a3fba26853102eea22efe112615bcf10cb044a810e93c30e10" }, "downloads": -1, "filename": "mysql_batch-1.1.tar.gz", "has_sig": false, "md5_digest": "15c9d7dc95b03fce819b02370339bcb9", "packagetype": "sdist", "python_version": "source", "requires_python": null, "size": 6643, "upload_time": "2018-05-24T01:01:32", "url": "https://files.pythonhosted.org/packages/0c/cd/049ae303c594e4622518d8789280081e21380409efe66b44dea9f53b92f8/mysql_batch-1.1.tar.gz" } ], "1.2": [ { "comment_text": "", "digests": { "md5": "76258cf6ae8995f6380c3e8f6fa86382", "sha256": "60ddbcbd21d4466f15b5ccabca0c9365ffba6b7f6d23e2a85c6ba64e34a506f9" }, "downloads": -1, "filename": "mysql_batch-1.2-py2.py3-none-any.whl", "has_sig": false, "md5_digest": "76258cf6ae8995f6380c3e8f6fa86382", "packagetype": "bdist_wheel", "python_version": "py2.py3", "requires_python": null, "size": 9119, "upload_time": "2018-05-24T01:19:01", "url": "https://files.pythonhosted.org/packages/6f/c5/e814a6ca64f7e0818045198d2bef12161abe8927d22dc79665db8e370ed4/mysql_batch-1.2-py2.py3-none-any.whl" }, { "comment_text": "", "digests": { "md5": "b88cc0399e3191e8e76a0557f03c41b5", "sha256": "58ade693599f35b7fa8b86f51a65d735d6ec30307929b4f6f6e3f6175477909e" }, "downloads": -1, "filename": "mysql_batch-1.2.tar.gz", "has_sig": false, "md5_digest": "b88cc0399e3191e8e76a0557f03c41b5", "packagetype": "sdist", "python_version": "source", "requires_python": null, "size": 6680, "upload_time": "2018-05-24T01:19:02", "url": "https://files.pythonhosted.org/packages/8b/3f/952a02dda444e56209e6709aabc04b9d50250949acb955a8231a4193faaf/mysql_batch-1.2.tar.gz" } ] }, "urls": [ { "comment_text": "", "digests": { "md5": "76258cf6ae8995f6380c3e8f6fa86382", "sha256": "60ddbcbd21d4466f15b5ccabca0c9365ffba6b7f6d23e2a85c6ba64e34a506f9" }, "downloads": -1, "filename": "mysql_batch-1.2-py2.py3-none-any.whl", "has_sig": false, "md5_digest": "76258cf6ae8995f6380c3e8f6fa86382", "packagetype": "bdist_wheel", "python_version": "py2.py3", "requires_python": null, "size": 9119, "upload_time": "2018-05-24T01:19:01", "url": "https://files.pythonhosted.org/packages/6f/c5/e814a6ca64f7e0818045198d2bef12161abe8927d22dc79665db8e370ed4/mysql_batch-1.2-py2.py3-none-any.whl" }, { "comment_text": "", "digests": { "md5": "b88cc0399e3191e8e76a0557f03c41b5", "sha256": "58ade693599f35b7fa8b86f51a65d735d6ec30307929b4f6f6e3f6175477909e" }, "downloads": -1, "filename": "mysql_batch-1.2.tar.gz", "has_sig": false, "md5_digest": "b88cc0399e3191e8e76a0557f03c41b5", "packagetype": "sdist", "python_version": "source", "requires_python": null, "size": 6680, "upload_time": "2018-05-24T01:19:02", "url": "https://files.pythonhosted.org/packages/8b/3f/952a02dda444e56209e6709aabc04b9d50250949acb955a8231a4193faaf/mysql_batch-1.2.tar.gz" } ] }