# Mysql Partition Rotator
This module allows you to simply rotate your data avoiding slow deletes on big tables, this module is inspedired by **Rick James** [go check his page for more details ](https://mysql.rjweb.org/doc.php/partitionmaint)
## Features
- Rotates table in daily periods
- Rotates table in hourly periods
- Rotates table in monthly periods
## Requires
Python 3
pip
## Installation
```sh
pip install mysql_partition_rotator
```
## Usage
First create a table with enabled partitioning for example:
```mysql
CREATE TABLE `test_rotate_daily` (
`dt` datetime NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
PARTITION BY RANGE (TO_DAYS(dt))
(PARTITION `start` VALUES LESS THAN (0) ,
PARTITION from20201001 VALUES LESS THAN (TO_DAYS('2020-10-02')) ,
PARTITION from20201002 VALUES LESS THAN (TO_DAYS('2020-10-03')) ,
PARTITION from20201003 VALUES LESS THAN (TO_DAYS('2020-10-04')) ,
PARTITION from20201004 VALUES LESS THAN (TO_DAYS('2020-10-05')) ,
PARTITION from20201005 VALUES LESS THAN (TO_DAYS('2020-10-06')) ,
PARTITION from20201006 VALUES LESS THAN (TO_DAYS('2020-10-07')) ,
PARTITION future VALUES LESS THAN MAXVALUE )
```
Then create a partition rotator instance with all the necessary config in order to rotate the table
```python
import pymysql
from pymysql.cursors import DictCursor
import logging
import datetime
from mysql_partition_rotator import RotateDaily
from mysql_partition_rotator import PartitionRotator
connection = pymysql.connect(host='localhost', user='johndoe', password='mypass', database='test', cursorclass=DictCursor)
old_time = datetime.datetime(2020, 10, 3)
new_time = datetime.datetime(2020, 10, 7)
rotate_mode = RotateDaily()
logging.basicConfig(level=logging.INFO)
logger = logging.getLogger()
pr = PartitionRotator(database_instance=connection,
database_name="tests",
table_name="test_rotate_daily",
oldest_partition_time=old_time,
newest_partition_time=new_time,
rotate_mode=rotate_mode,
logger=logger)
pr.rotate()
```
Raw data
{
"_id": null,
"home_page": "https://github.com/jinraynor1/mysql_partition_rotator",
"name": "mysql-partition-rotator",
"maintainer": "",
"docs_url": null,
"requires_python": "",
"maintainer_email": "",
"keywords": "mysql,partition,rotate,python",
"author": "Jimmy Atauje",
"author_email": "jimmy.atauje@gmail.com",
"download_url": "https://files.pythonhosted.org/packages/a4/fe/9fb683600cfae5b0a31a1b37ca15be664844f7b23bb54968a2ddf985f6dd/mysql_partition_rotator-0.3.tar.gz",
"platform": null,
"description": "# Mysql Partition Rotator\r\n\r\n\r\nThis module allows you to simply rotate your data avoiding slow deletes on big tables, this module is inspedired by **Rick James** [go check his page for more details ](https://mysql.rjweb.org/doc.php/partitionmaint)\r\n\r\n## Features\r\n\r\n- Rotates table in daily periods\r\n- Rotates table in hourly periods\r\n- Rotates table in monthly periods\r\n\r\n## Requires\r\nPython 3\r\npip\r\n\r\n## Installation\r\n\r\n```sh\r\npip install mysql_partition_rotator\r\n```\r\n\r\n## Usage\r\nFirst create a table with enabled partitioning for example:\r\n```mysql\r\nCREATE TABLE `test_rotate_daily` (\r\n`dt` datetime NOT NULL\r\n) ENGINE=MyISAM DEFAULT CHARSET=latin1\r\nPARTITION BY RANGE (TO_DAYS(dt))\r\n(PARTITION `start` VALUES LESS THAN (0) ,\r\nPARTITION from20201001 VALUES LESS THAN (TO_DAYS('2020-10-02')) ,\r\nPARTITION from20201002 VALUES LESS THAN (TO_DAYS('2020-10-03')) ,\r\nPARTITION from20201003 VALUES LESS THAN (TO_DAYS('2020-10-04')) ,\r\nPARTITION from20201004 VALUES LESS THAN (TO_DAYS('2020-10-05')) ,\r\nPARTITION from20201005 VALUES LESS THAN (TO_DAYS('2020-10-06')) ,\r\nPARTITION from20201006 VALUES LESS THAN (TO_DAYS('2020-10-07')) ,\r\nPARTITION future VALUES LESS THAN MAXVALUE )\r\n```\r\n\r\nThen create a partition rotator instance with all the necessary config in order to rotate the table\r\n\r\n```python\r\nimport pymysql\r\nfrom pymysql.cursors import DictCursor\r\nimport logging\r\nimport datetime\r\nfrom mysql_partition_rotator import RotateDaily\r\nfrom mysql_partition_rotator import PartitionRotator\r\n\r\nconnection = pymysql.connect(host='localhost', user='johndoe', password='mypass', database='test', cursorclass=DictCursor)\r\nold_time = datetime.datetime(2020, 10, 3)\r\nnew_time = datetime.datetime(2020, 10, 7)\r\nrotate_mode = RotateDaily()\r\nlogging.basicConfig(level=logging.INFO)\r\nlogger = logging.getLogger()\r\n\r\npr = PartitionRotator(database_instance=connection,\r\n database_name=\"tests\",\r\n table_name=\"test_rotate_daily\", \r\n oldest_partition_time=old_time,\r\n newest_partition_time=new_time,\r\n rotate_mode=rotate_mode,\r\n logger=logger)\r\n\r\npr.rotate()\r\n```\r\n\r\n\r\n",
"bugtrack_url": null,
"license": "MIT",
"summary": "Rotates mysql tables by using partition method",
"version": "0.3",
"project_urls": {
"Download": "https://github.com/jinraynor1/mysql_partition_rotator/releases/tag/v0.3-alpha",
"Homepage": "https://github.com/jinraynor1/mysql_partition_rotator"
},
"split_keywords": [
"mysql",
"partition",
"rotate",
"python"
],
"urls": [
{
"comment_text": "",
"digests": {
"blake2b_256": "a4fe9fb683600cfae5b0a31a1b37ca15be664844f7b23bb54968a2ddf985f6dd",
"md5": "8982310965a11ff67ba3baf7f60c40b6",
"sha256": "03eaa5b53ec34dbc0e6e83e690435fcba02fd40d754b097fc4c0158fd417d9f0"
},
"downloads": -1,
"filename": "mysql_partition_rotator-0.3.tar.gz",
"has_sig": false,
"md5_digest": "8982310965a11ff67ba3baf7f60c40b6",
"packagetype": "sdist",
"python_version": "source",
"requires_python": null,
"size": 5077,
"upload_time": "2023-08-30T18:38:30",
"upload_time_iso_8601": "2023-08-30T18:38:30.208017Z",
"url": "https://files.pythonhosted.org/packages/a4/fe/9fb683600cfae5b0a31a1b37ca15be664844f7b23bb54968a2ddf985f6dd/mysql_partition_rotator-0.3.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2023-08-30 18:38:30",
"github": true,
"gitlab": false,
"bitbucket": false,
"codeberg": false,
"github_user": "jinraynor1",
"github_project": "mysql_partition_rotator",
"github_not_found": true,
"lcname": "mysql-partition-rotator"
}
JSON
