# mysql_wrap
A wrapper for Python Mysqldb with pandas functionality.
- Emiliano Lupo @HENN, June 2024
- Documentation https://pypi.org/project/mysqlwrap/
- License: GPL v2
Built on top of the SimpleMysql package available at https://github.com/knadh/simplemysql
# Installation
with pip:
pip install mysql_wrap
from source:
python -m pip install .
# Usage
## For normal connection
```python
from mysql_wrap import MysqlWrap, ConnectionOptions
options = ConnectionOptions(
host="127.0.0.1",
db="mydatabase",
user="username",
passwd="password",
keep_alive=True # try and reconnect timedout mysql connections?
)
db = MysqlWrap(**options)
```
## For SSL Connection
```python
from mysql_wrap import MysqlWrap
db = MysqlWrap(
host="127.0.0.1",
db="mydatabase",
user="username",
passwd="password",
ssl = {'cert': 'client-cert.pem', 'key': 'client-key.pem'},
keep_alive=True # try and reconnect timedout mysql connections?
)
```
```python
# insert a record to the <em>books</em> table
db.insert("books", {"type": "paperback", "name": "Time Machine", "price": 5.55, year: "1997"})
book = db.getOne("books", ["name"], ["year = 1997"])
print "The book's name is " + book.name
```
# Utility methods
getDataTypefromDType(), setMySqlFieldName()
# Pandas methods
getTable(), createTable(), SyncColumns(), insertFromDataFrame(), InsertOrUpdateFromDataFrame(), CreateInsertTable(), CreateUpdateTable()
# regular Query methods
insert(), update(), insertOrUpdate(), describe(), delete(), getOne(), getAll(), lastId(), query(), tableExist()
## insert(table, record{})
Inserts a single record into a table.
```python
db.insert("food", {"type": "fruit", "name": "Apple", "color": "red"})
db.insert("books", {"type": "paperback", "name": "Time Machine", "price": 5.55})
```
## update(table, row{}, condition[])
Update one more or rows based on a condition (or no condition).
```python
# update all rows
db.update("books", {"discount": 0})
# update rows based on a simple hardcoded condition
db.update("books",
{"discount": 10},
["id=1"]
)
# update rows based on a parametrized condition
db.update("books",
{"discount": 10},
("id=%s AND year=%s", [id, year])
)
```
## insertBatch(table, rows{})
Insert Multiple values into table.
```python
# insert multiple values in table
db.insertBatch("books", [{"discount": 0},{"discount":1},{"discount":3}])
```
## insertOrUpdate(table, row{}, key)
Insert a new row, or update if there is a primary key conflict.
```python
# insert a book with id 123. if it already exists, update values
db.insertOrUpdate("books",
{"id": 123, type": "paperback", "name": "Time Machine", "price": 5.55},
"id"
)
```
## getOne(table, fields[], where[], order[], limit[])
## getAll(table, fields[], where[], order[], limit[])
Get a single record or multiple records from a table given a condition (or no condition). The resultant rows are returned as namedtuples. getOne() returns a single namedtuple, and getAll() returns a list of namedtuples.
```python
book = db.getOne("books", ["id", "name"])
```
```python
# get a row based on a simple hardcoded condition
book = db.getOne("books", ["name", "year"], ("id=1"))
```
```python
# get multiple rows based on a parametrized condition
books = db.getAll("books",
["id", "name"],
("year > %s and price < %s", [year, 12.99])
)
```
```python
# get multiple rows based on a parametrized condition with an order and limit specified
books = db.getAll("books",
["id", "name", "year"],
("year > %s and price < %s", [year, 12.99]),
["year", "DESC"], # ORDER BY year DESC
[0, 10] # LIMIT 0, 10
)
```
## lastId()
Get the last insert id
```python
# get the last insert ID
db.lastId()
```
## lastQuery()
Get the last query executed
```python
# get the SQL of the last executed query
db.lastQuery()
```
## delete(table, fields[], condition[], order[], limit[])
Delete one or more records based on a condition (or no condition)
```python
# delete all rows
db.delete("books")
# delete rows based on a condition
db.delete("books", ("price > %s AND year < %s", [25, 1999]))
```
## query(table)
Run a raw SQL query. The MySQLdb cursor is returned.
```python
# run a raw SQL query
db.query("DELETE FROM books WHERE year > 2005")
```
## commit()
Insert, update, and delete operations on transactional databases such as innoDB need to be committed
```python
# Commit all pending transaction queries
db.commit()
```
To run tests:
- add your test file to the tests/ folder
- import the modules you want to test using src.folder.module path
- run tests from terminal from the project root folder:
python3 -m unittest tests.{test file}
Raw data
{
"_id": null,
"home_page": null,
"name": "mysql-wrap",
"maintainer": null,
"docs_url": null,
"requires_python": ">=3.7",
"maintainer_email": null,
"keywords": "mysql, pandas",
"author": null,
"author_email": "emiliano lupo <emiliano.lupo@henn.com>",
"download_url": "https://files.pythonhosted.org/packages/1e/a1/15863053190833b2ae4835a8f91d6bff1893666fb5c999f7245965bb6a92/mysql_wrap-1.1.3.tar.gz",
"platform": null,
"description": "# mysql_wrap\nA wrapper for Python Mysqldb with pandas functionality.\n\n- Emiliano Lupo @HENN, June 2024\n- Documentation https://pypi.org/project/mysqlwrap/\n- License: GPL v2\n\nBuilt on top of the SimpleMysql package available at https://github.com/knadh/simplemysql\n\n# Installation\nwith pip:\n\npip install mysql_wrap\n\nfrom source:\n\n python -m pip install .\n\n# Usage\n## For normal connection\n```python\nfrom mysql_wrap import MysqlWrap, ConnectionOptions\n\noptions = ConnectionOptions(\n\thost=\"127.0.0.1\",\n\tdb=\"mydatabase\",\n\tuser=\"username\",\n\tpasswd=\"password\",\n\tkeep_alive=True # try and reconnect timedout mysql connections?\n)\n\ndb = MysqlWrap(**options)\n```\n\n## For SSL Connection\n```python\nfrom mysql_wrap import MysqlWrap\n\ndb = MysqlWrap(\n host=\"127.0.0.1\",\n db=\"mydatabase\",\n user=\"username\",\n passwd=\"password\",\n ssl = {'cert': 'client-cert.pem', 'key': 'client-key.pem'},\n keep_alive=True # try and reconnect timedout mysql connections?\n)\n\n```\n\n\n```python\n# insert a record to the <em>books</em> table\ndb.insert(\"books\", {\"type\": \"paperback\", \"name\": \"Time Machine\", \"price\": 5.55, year: \"1997\"})\n\nbook = db.getOne(\"books\", [\"name\"], [\"year = 1997\"])\n\nprint \"The book's name is \" + book.name\n```\n\n# Utility methods\ngetDataTypefromDType(), setMySqlFieldName()\n\n# Pandas methods\ngetTable(), createTable(), SyncColumns(), insertFromDataFrame(), InsertOrUpdateFromDataFrame(), CreateInsertTable(), CreateUpdateTable()\n\n# regular Query methods\ninsert(), update(), insertOrUpdate(), describe(), delete(), getOne(), getAll(), lastId(), query(), tableExist()\n\n## insert(table, record{})\nInserts a single record into a table.\n\n```python\ndb.insert(\"food\", {\"type\": \"fruit\", \"name\": \"Apple\", \"color\": \"red\"})\ndb.insert(\"books\", {\"type\": \"paperback\", \"name\": \"Time Machine\", \"price\": 5.55})\n```\n\n## update(table, row{}, condition[])\nUpdate one more or rows based on a condition (or no condition).\n\n```python\n# update all rows\ndb.update(\"books\", {\"discount\": 0})\n\n# update rows based on a simple hardcoded condition\ndb.update(\"books\",\n\t{\"discount\": 10},\n\t[\"id=1\"]\n)\n\n# update rows based on a parametrized condition\ndb.update(\"books\",\n\t{\"discount\": 10},\n\t(\"id=%s AND year=%s\", [id, year])\n)\n```\n## insertBatch(table, rows{})\nInsert Multiple values into table.\n\n```python\n# insert multiple values in table\ndb.insertBatch(\"books\", [{\"discount\": 0},{\"discount\":1},{\"discount\":3}])\n```\n\n## insertOrUpdate(table, row{}, key)\nInsert a new row, or update if there is a primary key conflict.\n\n```python\n# insert a book with id 123. if it already exists, update values\ndb.insertOrUpdate(\"books\",\n\t\t{\"id\": 123, type\": \"paperback\", \"name\": \"Time Machine\", \"price\": 5.55},\n\t\t\"id\"\n)\n```\n\n## getOne(table, fields[], where[], order[], limit[])\n## getAll(table, fields[], where[], order[], limit[])\nGet a single record or multiple records from a table given a condition (or no condition). The resultant rows are returned as namedtuples. getOne() returns a single namedtuple, and getAll() returns a list of namedtuples.\n\n```python\nbook = db.getOne(\"books\", [\"id\", \"name\"])\n```\n\n```python\n# get a row based on a simple hardcoded condition\nbook = db.getOne(\"books\", [\"name\", \"year\"], (\"id=1\"))\n```\n\n```python\n# get multiple rows based on a parametrized condition\nbooks = db.getAll(\"books\",\n\t[\"id\", \"name\"],\n\t(\"year > %s and price < %s\", [year, 12.99])\n)\n```\n\n```python\n# get multiple rows based on a parametrized condition with an order and limit specified\nbooks = db.getAll(\"books\",\n\t[\"id\", \"name\", \"year\"],\n\t(\"year > %s and price < %s\", [year, 12.99]),\n\t[\"year\", \"DESC\"],\t# ORDER BY year DESC\n\t[0, 10]\t\t\t# LIMIT 0, 10\n)\n```\n## lastId()\nGet the last insert id\n```python\n# get the last insert ID\ndb.lastId()\n```\n\n## lastQuery()\nGet the last query executed\n```python\n# get the SQL of the last executed query\ndb.lastQuery()\n```\n\n## delete(table, fields[], condition[], order[], limit[])\nDelete one or more records based on a condition (or no condition)\n\n```python\n# delete all rows\ndb.delete(\"books\")\n\n# delete rows based on a condition\ndb.delete(\"books\", (\"price > %s AND year < %s\", [25, 1999]))\n```\n\n## query(table)\nRun a raw SQL query. The MySQLdb cursor is returned.\n\n```python\n# run a raw SQL query\ndb.query(\"DELETE FROM books WHERE year > 2005\")\n```\n\n## commit()\nInsert, update, and delete operations on transactional databases such as innoDB need to be committed\n\n```python\n# Commit all pending transaction queries\ndb.commit()\n```\n\nTo run tests: \n\n- add your test file to the tests/ folder\n\n- import the modules you want to test using src.folder.module path\n\n- run tests from terminal from the project root folder:\n python3 -m unittest tests.{test file} \n",
"bugtrack_url": null,
"license": null,
"summary": "Simple wrapper for common mysql queries, including utilities for pandas DataFrames",
"version": "1.1.3",
"project_urls": {
"Bug Tracker": "https://github.com/henn-dt/MySQL-wrap/issues",
"Homepage": "https://github.com/henn-dt/MySQL-wrap"
},
"split_keywords": [
"mysql",
" pandas"
],
"urls": [
{
"comment_text": "",
"digests": {
"blake2b_256": "caeab1a69ac50332b2f8fab495704aad3a263bd9449710c7d26871f8bb4653a2",
"md5": "54f73bf64df3235aa6de18551ae4d9dd",
"sha256": "a15aae229209ff314725e8587cc3fb15e00a42f9dc223b09291f4f6fee77dd93"
},
"downloads": -1,
"filename": "mysql_wrap-1.1.3-py3-none-any.whl",
"has_sig": false,
"md5_digest": "54f73bf64df3235aa6de18551ae4d9dd",
"packagetype": "bdist_wheel",
"python_version": "py3",
"requires_python": ">=3.7",
"size": 9589,
"upload_time": "2024-06-28T13:07:15",
"upload_time_iso_8601": "2024-06-28T13:07:15.825440Z",
"url": "https://files.pythonhosted.org/packages/ca/ea/b1a69ac50332b2f8fab495704aad3a263bd9449710c7d26871f8bb4653a2/mysql_wrap-1.1.3-py3-none-any.whl",
"yanked": false,
"yanked_reason": null
},
{
"comment_text": "",
"digests": {
"blake2b_256": "1ea115863053190833b2ae4835a8f91d6bff1893666fb5c999f7245965bb6a92",
"md5": "9fcf7ee25a39bc7ec00ceac8747ac035",
"sha256": "c5aa7482fab635eafa4ee043f226213d964b1e80156fe3e1bf37397fb786501f"
},
"downloads": -1,
"filename": "mysql_wrap-1.1.3.tar.gz",
"has_sig": false,
"md5_digest": "9fcf7ee25a39bc7ec00ceac8747ac035",
"packagetype": "sdist",
"python_version": "source",
"requires_python": ">=3.7",
"size": 11516,
"upload_time": "2024-06-28T13:07:18",
"upload_time_iso_8601": "2024-06-28T13:07:18.140079Z",
"url": "https://files.pythonhosted.org/packages/1e/a1/15863053190833b2ae4835a8f91d6bff1893666fb5c999f7245965bb6a92/mysql_wrap-1.1.3.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2024-06-28 13:07:18",
"github": true,
"gitlab": false,
"bitbucket": false,
"codeberg": false,
"github_user": "henn-dt",
"github_project": "MySQL-wrap",
"travis_ci": false,
"coveralls": false,
"github_actions": false,
"requirements": [
{
"name": "mysql-connector-python",
"specs": []
},
{
"name": "pandas",
"specs": []
},
{
"name": "python-dotenv",
"specs": []
},
{
"name": "twine",
"specs": []
}
],
"lcname": "mysql-wrap"
}
JSON
