# dbsys
dbsys is a comprehensive Python library for managing database operations using SQLAlchemy, pandas, and Redis. It provides a high-level interface for common database operations, including reading, writing, creating tables, deleting tables, columns, and rows, as well as advanced features like searching, backup, restore functionality, and Redis pub/sub support.
[](https://badge.fury.io/py/dbsys)
[](https://pypi.org/project/dbsys/)
[](https://opensource.org/licenses/MIT)
## What's New in Version 0.7.9
- Fixed version mismatch issue for PyPI upload
- All features from version 0.7.8 are included:
- Updated dependency requirements for broader compatibility
- Enhanced Redis functionality with new methods for streams, JSON, lists, and strings
- Added `execon` method for executing functions on specific pubsub messages
- Improved error handling and retry logic in Redis operations
- Updated documentation with examples for new Redis features
## Features
- Easy-to-use interface for database operations
- Support for multiple database types through SQLAlchemy
- Redis support for pub/sub operations and key-value storage
- Integration with pandas for efficient data handling
- Comprehensive error handling and custom exceptions
- Backup and restore functionality
- Advanced search capabilities with case-sensitive option
- Deduplication of data
- Custom SQL query execution
- Support for Locksys objects as connection strings
- Improved Redis pub/sub functionality
## Installation
You can install the latest version of dbsys using pip:
```bash
pip install --upgrade dbsys
```
For development purposes, you can install the package with extra dependencies:
```bash
pip install dbsys[dev]
```
This will install additional packages useful for development, such as pytest, flake8, and mypy.
## Quick Start
Here's a comprehensive example of how to use dbsys with SQL databases:
```python
from dbsys import DatabaseManager
import pandas as pd
# Initialize the DatabaseManager
db = DatabaseManager("sqlite:///example.db")
# If you're using Locksys:
# from your_locksys_module import Locksys
# lock = Locksys("Promptsys")
# db = DatabaseManager(lock)
# Create a sample DataFrame
data = pd.DataFrame({
'name': ['Alice', 'Bob', 'Charlie', 'David'],
'age': [30, 25, 35, 28],
'city': ['New York', 'San Francisco', 'London', 'Berlin']
})
# Create a new table and write data
success = db.table("users").create(data)
print(f"Table created successfully: {success}")
# Read the table
result = db.table("users").read()
print("Original data:")
print(result)
# Search for users from a specific city
result = db.table("users").search({"city": "London"})
print("\nUsers from London:")
print(result)
# Update data in the table
new_data = pd.DataFrame({
'name': ['Eve', 'Frank'],
'age': [22, 40],
'city': ['Paris', 'Tokyo']
})
success = db.table("users").write(new_data)
print(f"\nData updated successfully: {success}")
# Read the updated table
result = db.table("users").read()
print("\nUpdated data:")
print(result)
# Delete a specific row
rows_deleted = db.table("users").delete_row({"name": "Frank"})
print(f"\nRows deleted: {rows_deleted}")
# Execute a custom SQL query
result = db.execute_query("SELECT AVG(age) as avg_age FROM users")
print("\nAverage age:")
print(result)
# Backup the table
success = db.table("users").backup("users_backup.json")
print(f"\nBackup created successfully: {success}")
# Delete the table
success = db.table("users").delete_table()
print(f"\nTable deleted successfully: {success}")
# Restore the table from backup
success = db.table("users").restore("users_backup.json")
print(f"\nTable restored successfully: {success}")
# Verify restored data
result = db.table("users").read()
print("\nRestored data:")
print(result)
```
Example of using dbsys with Redis:
```python
from dbsys import DatabaseManager
import time
# Initialize the DatabaseManager with Redis
db = DatabaseManager("redis://localhost:6379/0")
# Publish a message
subscribers = db.pub("Hello, Redis!", "test_channel")
print(f"Message published to {subscribers} subscribers")
# Subscribe to a channel and process messages
def message_handler(channel, message):
print(f"Received on {channel}: {message}")
db.sub("test_channel", handler=message_handler)
# Publish and subscribe in one operation
db.pubsub("Test message", "pub_channel", "sub_channel", handler=message_handler, wait=5)
# Get stored messages
messages = db.get_stored_messages("sub_channel")
print("Stored messages:", messages)
# Clear stored messages
db.clear_stored_messages()
```
## API Reference
### DatabaseManager
The main class for interacting with the database. It supports both SQL databases and Redis.
#### Constructor
```python
DatabaseManager(connection_string: Union[str, 'Locksys'])
```
Initialize the DatabaseManager with a database URL or a Locksys object.
- `connection_string`: The connection string for the database or a Locksys object.
- For SQL databases, use SQLAlchemy connection strings.
- For Redis, use the format: "redis://[[username]:[password]]@localhost:6379/0"
- For Locksys objects, ensure they have a `get_connection_string()` method.
#### Methods
- `table(table_name: str) -> DatabaseManager`
- `read() -> pd.DataFrame`
- `write(data: pd.DataFrame) -> bool`
- `create(data: pd.DataFrame) -> bool`
- `delete_table() -> bool`
- `delete_row(row_identifier: Dict[str, Any]) -> int`
- `search(conditions: Union[Dict[str, Any], str], limit: Optional[int] = None, case_sensitive: bool = False) -> pd.DataFrame`
- `backup(file_path: str, columns: Optional[List[str]] = None) -> bool`
- `restore(file_path: str, mode: str = 'replace') -> bool`
- `execute_query(query: str, params: Optional[Dict[str, Any]] = None) -> Union[pd.DataFrame, int]`
- `pub(message: str, channel: str) -> int`
- `sub(channel: str, handler: Optional[Callable[[str, str], None]] = None, exiton: str = "") -> DatabaseManager`
- `pubsub(pub_message: str, pub_channel: str, sub_channel: str, handler: Optional[Callable[[str, str], None]] = None, exiton: str = "CLOSE", wait: Optional[int] = None) -> DatabaseManager`
- `unsub(channel: Optional[str] = None) -> DatabaseManager`
- `get_stored_messages(channel: str) -> List[str]`
- `clear_stored_messages(channel: Optional[str] = None) -> DatabaseManager`
For detailed usage of each method, including all parameters and return values, please refer to the docstrings in the source code.
## Error Handling
dbsys provides custom exceptions for better error handling:
- `DatabaseError`: Base exception for database operations.
- `TableNotFoundError`: Raised when a specified table is not found in the database.
- `ColumnNotFoundError`: Raised when a specified column is not found in the table.
- `InvalidOperationError`: Raised when an invalid operation is attempted.
## Contributing
Contributions are welcome! Please feel free to submit a Pull Request.
1. Fork the repository
2. Create your feature branch (`git checkout -b feature/AmazingFeature`)
3. Commit your changes (`git commit -m 'Add some AmazingFeature'`)
4. Push to the branch (`git push origin feature/AmazingFeature`)
5. Open a Pull Request
## License
This project is licensed under the MIT License. See the [LICENSE](LICENSE) file for details.
## Support
If you encounter any problems or have any questions, please [open an issue](https://github.com/lifsys/dbsys/issues) on our GitHub repository.
## About Lifsys, Inc
Lifsys, Inc is an AI company dedicated to developing innovative solutions for data management and analysis. For more information, visit [www.lifsys.com](https://www.lifsys.com).
## Changelog
### 0.7.9
- Fixed version mismatch issue for PyPI upload
### 0.7.8
- Updated dependency requirements for broader compatibility
- Enhanced Redis functionality with new methods for streams, JSON, lists, and strings
- Added `execon` method for executing functions on specific pubsub messages
- Improved error handling and retry logic in Redis operations
- Updated documentation with examples for new Redis features
### 0.7.7
- Updated package metadata for improved PyPI representation
- Enhanced documentation with more detailed examples
- Minor bug fixes and performance optimizations
- Improved error handling in database operations
### 0.7.6
- Added new Redis-specific methods for working with streams, JSON, lists, and strings
- Introduced the `execon` method for executing functions on specific pubsub messages
- Enhanced error handling and retry logic in Redis operations
- Updated documentation with examples for new Redis features
- Minor bug fixes and performance improvements
### 0.7.5
- Added type hints and py.typed file for better IDE support
- Improved error handling in Redis operations
- Enhanced documentation with more detailed examples
- Minor bug fixes and performance optimizations
- Updated package metadata for better PyPI representation
### 0.6.0
- Added support for Locksys objects as connection strings
- Updated API to remove the need for `results()` method
- Enhanced error handling for connection string parsing
- Updated documentation and README
### 0.5.0
- Major refactoring of the codebase
- Updated API for better consistency and ease of use
- Enhanced Redis support with pubsub operations
- Improved error handling and logging
- Added case-sensitive option for search operations
- Updated documentation and README
(... previous changelog entries ...)
Raw data
{
"_id": null,
"home_page": "https://github.com/lifsys/dbsys",
"name": "dbsys",
"maintainer": null,
"docs_url": null,
"requires_python": ">=3.8",
"maintainer_email": null,
"keywords": "database sqlalchemy pandas redis orm data management",
"author": "Mark Powers",
"author_email": "mpoweru@lifsys.com",
"download_url": "https://files.pythonhosted.org/packages/c0/47/0802d034836c66c886d29c356beeb51a82f18a118453a3fe346868fe9d43/dbsys-0.7.9.tar.gz",
"platform": null,
"description": "# dbsys\n\ndbsys is a comprehensive Python library for managing database operations using SQLAlchemy, pandas, and Redis. It provides a high-level interface for common database operations, including reading, writing, creating tables, deleting tables, columns, and rows, as well as advanced features like searching, backup, restore functionality, and Redis pub/sub support.\n\n[](https://badge.fury.io/py/dbsys)\n[](https://pypi.org/project/dbsys/)\n[](https://opensource.org/licenses/MIT)\n\n## What's New in Version 0.7.9\n\n- Fixed version mismatch issue for PyPI upload\n- All features from version 0.7.8 are included:\n - Updated dependency requirements for broader compatibility\n - Enhanced Redis functionality with new methods for streams, JSON, lists, and strings\n - Added `execon` method for executing functions on specific pubsub messages\n - Improved error handling and retry logic in Redis operations\n - Updated documentation with examples for new Redis features\n\n## Features\n\n- Easy-to-use interface for database operations\n- Support for multiple database types through SQLAlchemy\n- Redis support for pub/sub operations and key-value storage\n- Integration with pandas for efficient data handling\n- Comprehensive error handling and custom exceptions\n- Backup and restore functionality\n- Advanced search capabilities with case-sensitive option\n- Deduplication of data\n- Custom SQL query execution\n- Support for Locksys objects as connection strings\n- Improved Redis pub/sub functionality\n\n## Installation\n\nYou can install the latest version of dbsys using pip:\n\n```bash\npip install --upgrade dbsys\n```\n\nFor development purposes, you can install the package with extra dependencies:\n\n```bash\npip install dbsys[dev]\n```\n\nThis will install additional packages useful for development, such as pytest, flake8, and mypy.\n\n## Quick Start\n\nHere's a comprehensive example of how to use dbsys with SQL databases:\n\n```python\nfrom dbsys import DatabaseManager\nimport pandas as pd\n\n# Initialize the DatabaseManager\ndb = DatabaseManager(\"sqlite:///example.db\")\n\n# If you're using Locksys:\n# from your_locksys_module import Locksys\n# lock = Locksys(\"Promptsys\")\n# db = DatabaseManager(lock)\n\n# Create a sample DataFrame\ndata = pd.DataFrame({\n 'name': ['Alice', 'Bob', 'Charlie', 'David'],\n 'age': [30, 25, 35, 28],\n 'city': ['New York', 'San Francisco', 'London', 'Berlin']\n})\n\n# Create a new table and write data\nsuccess = db.table(\"users\").create(data)\nprint(f\"Table created successfully: {success}\")\n\n# Read the table\nresult = db.table(\"users\").read()\nprint(\"Original data:\")\nprint(result)\n\n# Search for users from a specific city\nresult = db.table(\"users\").search({\"city\": \"London\"})\nprint(\"\\nUsers from London:\")\nprint(result)\n\n# Update data in the table\nnew_data = pd.DataFrame({\n 'name': ['Eve', 'Frank'],\n 'age': [22, 40],\n 'city': ['Paris', 'Tokyo']\n})\nsuccess = db.table(\"users\").write(new_data)\nprint(f\"\\nData updated successfully: {success}\")\n\n# Read the updated table\nresult = db.table(\"users\").read()\nprint(\"\\nUpdated data:\")\nprint(result)\n\n# Delete a specific row\nrows_deleted = db.table(\"users\").delete_row({\"name\": \"Frank\"})\nprint(f\"\\nRows deleted: {rows_deleted}\")\n\n# Execute a custom SQL query\nresult = db.execute_query(\"SELECT AVG(age) as avg_age FROM users\")\nprint(\"\\nAverage age:\")\nprint(result)\n\n# Backup the table\nsuccess = db.table(\"users\").backup(\"users_backup.json\")\nprint(f\"\\nBackup created successfully: {success}\")\n\n# Delete the table\nsuccess = db.table(\"users\").delete_table()\nprint(f\"\\nTable deleted successfully: {success}\")\n\n# Restore the table from backup\nsuccess = db.table(\"users\").restore(\"users_backup.json\")\nprint(f\"\\nTable restored successfully: {success}\")\n\n# Verify restored data\nresult = db.table(\"users\").read()\nprint(\"\\nRestored data:\")\nprint(result)\n```\n\nExample of using dbsys with Redis:\n\n```python\nfrom dbsys import DatabaseManager\nimport time\n\n# Initialize the DatabaseManager with Redis\ndb = DatabaseManager(\"redis://localhost:6379/0\")\n\n# Publish a message\nsubscribers = db.pub(\"Hello, Redis!\", \"test_channel\")\nprint(f\"Message published to {subscribers} subscribers\")\n\n# Subscribe to a channel and process messages\ndef message_handler(channel, message):\n print(f\"Received on {channel}: {message}\")\n\ndb.sub(\"test_channel\", handler=message_handler)\n\n# Publish and subscribe in one operation\ndb.pubsub(\"Test message\", \"pub_channel\", \"sub_channel\", handler=message_handler, wait=5)\n\n# Get stored messages\nmessages = db.get_stored_messages(\"sub_channel\")\nprint(\"Stored messages:\", messages)\n\n# Clear stored messages\ndb.clear_stored_messages()\n```\n\n## API Reference\n\n### DatabaseManager\n\nThe main class for interacting with the database. It supports both SQL databases and Redis.\n\n#### Constructor\n\n```python\nDatabaseManager(connection_string: Union[str, 'Locksys'])\n```\n\nInitialize the DatabaseManager with a database URL or a Locksys object.\n\n- `connection_string`: The connection string for the database or a Locksys object.\n - For SQL databases, use SQLAlchemy connection strings.\n - For Redis, use the format: \"redis://[[username]:[password]]@localhost:6379/0\"\n - For Locksys objects, ensure they have a `get_connection_string()` method.\n\n#### Methods\n\n- `table(table_name: str) -> DatabaseManager`\n- `read() -> pd.DataFrame`\n- `write(data: pd.DataFrame) -> bool`\n- `create(data: pd.DataFrame) -> bool`\n- `delete_table() -> bool`\n- `delete_row(row_identifier: Dict[str, Any]) -> int`\n- `search(conditions: Union[Dict[str, Any], str], limit: Optional[int] = None, case_sensitive: bool = False) -> pd.DataFrame`\n- `backup(file_path: str, columns: Optional[List[str]] = None) -> bool`\n- `restore(file_path: str, mode: str = 'replace') -> bool`\n- `execute_query(query: str, params: Optional[Dict[str, Any]] = None) -> Union[pd.DataFrame, int]`\n- `pub(message: str, channel: str) -> int`\n- `sub(channel: str, handler: Optional[Callable[[str, str], None]] = None, exiton: str = \"\") -> DatabaseManager`\n- `pubsub(pub_message: str, pub_channel: str, sub_channel: str, handler: Optional[Callable[[str, str], None]] = None, exiton: str = \"CLOSE\", wait: Optional[int] = None) -> DatabaseManager`\n- `unsub(channel: Optional[str] = None) -> DatabaseManager`\n- `get_stored_messages(channel: str) -> List[str]`\n- `clear_stored_messages(channel: Optional[str] = None) -> DatabaseManager`\n\nFor detailed usage of each method, including all parameters and return values, please refer to the docstrings in the source code.\n\n## Error Handling\n\ndbsys provides custom exceptions for better error handling:\n\n- `DatabaseError`: Base exception for database operations.\n- `TableNotFoundError`: Raised when a specified table is not found in the database.\n- `ColumnNotFoundError`: Raised when a specified column is not found in the table.\n- `InvalidOperationError`: Raised when an invalid operation is attempted.\n\n## Contributing\n\nContributions are welcome! Please feel free to submit a Pull Request.\n\n1. Fork the repository\n2. Create your feature branch (`git checkout -b feature/AmazingFeature`)\n3. Commit your changes (`git commit -m 'Add some AmazingFeature'`)\n4. Push to the branch (`git push origin feature/AmazingFeature`)\n5. Open a Pull Request\n\n## License\n\nThis project is licensed under the MIT License. See the [LICENSE](LICENSE) file for details.\n\n## Support\n\nIf you encounter any problems or have any questions, please [open an issue](https://github.com/lifsys/dbsys/issues) on our GitHub repository.\n\n## About Lifsys, Inc\n\nLifsys, Inc is an AI company dedicated to developing innovative solutions for data management and analysis. For more information, visit [www.lifsys.com](https://www.lifsys.com).\n\n## Changelog\n\n### 0.7.9\n- Fixed version mismatch issue for PyPI upload\n\n### 0.7.8\n- Updated dependency requirements for broader compatibility\n- Enhanced Redis functionality with new methods for streams, JSON, lists, and strings\n- Added `execon` method for executing functions on specific pubsub messages\n- Improved error handling and retry logic in Redis operations\n- Updated documentation with examples for new Redis features\n\n### 0.7.7\n- Updated package metadata for improved PyPI representation\n- Enhanced documentation with more detailed examples\n- Minor bug fixes and performance optimizations\n- Improved error handling in database operations\n\n### 0.7.6\n- Added new Redis-specific methods for working with streams, JSON, lists, and strings\n- Introduced the `execon` method for executing functions on specific pubsub messages\n- Enhanced error handling and retry logic in Redis operations\n- Updated documentation with examples for new Redis features\n- Minor bug fixes and performance improvements\n\n### 0.7.5\n- Added type hints and py.typed file for better IDE support\n- Improved error handling in Redis operations\n- Enhanced documentation with more detailed examples\n- Minor bug fixes and performance optimizations\n- Updated package metadata for better PyPI representation\n\n### 0.6.0\n- Added support for Locksys objects as connection strings\n- Updated API to remove the need for `results()` method\n- Enhanced error handling for connection string parsing\n- Updated documentation and README\n\n### 0.5.0\n- Major refactoring of the codebase\n- Updated API for better consistency and ease of use\n- Enhanced Redis support with pubsub operations\n- Improved error handling and logging\n- Added case-sensitive option for search operations\n- Updated documentation and README\n\n(... previous changelog entries ...)\n",
"bugtrack_url": null,
"license": null,
"summary": "A comprehensive library for managing database operations using SQLAlchemy, pandas, and Redis",
"version": "0.7.9",
"project_urls": {
"Bug Tracker": "https://github.com/lifsys/dbsys/issues",
"Documentation": "https://github.com/lifsys/dbsys/blob/main/README.md",
"Homepage": "https://github.com/lifsys/dbsys",
"Source Code": "https://github.com/lifsys/dbsys"
},
"split_keywords": [
"database",
"sqlalchemy",
"pandas",
"redis",
"orm",
"data",
"management"
],
"urls": [
{
"comment_text": "",
"digests": {
"blake2b_256": "2645db0db3e786957d8e50b7fb4048129b06180a4208d1c34151bf0b73ee2c12",
"md5": "c0f4510e8f9022c7076c4e11411b41f5",
"sha256": "dea5d2bec11c08c0ecc1e834e2278c53667e6d370d25523088a34cb1371dc006"
},
"downloads": -1,
"filename": "dbsys-0.7.9-py3-none-any.whl",
"has_sig": false,
"md5_digest": "c0f4510e8f9022c7076c4e11411b41f5",
"packagetype": "bdist_wheel",
"python_version": "py3",
"requires_python": ">=3.8",
"size": 12522,
"upload_time": "2024-08-26T02:31:42",
"upload_time_iso_8601": "2024-08-26T02:31:42.079229Z",
"url": "https://files.pythonhosted.org/packages/26/45/db0db3e786957d8e50b7fb4048129b06180a4208d1c34151bf0b73ee2c12/dbsys-0.7.9-py3-none-any.whl",
"yanked": false,
"yanked_reason": null
},
{
"comment_text": "",
"digests": {
"blake2b_256": "c0470802d034836c66c886d29c356beeb51a82f18a118453a3fe346868fe9d43",
"md5": "90de0e0264ef683bda23370dfb037f94",
"sha256": "f273aa423ca83a4b83e5db1c3cb2d7dcd17722bc0253b1bf70dea4c40ab92fff"
},
"downloads": -1,
"filename": "dbsys-0.7.9.tar.gz",
"has_sig": false,
"md5_digest": "90de0e0264ef683bda23370dfb037f94",
"packagetype": "sdist",
"python_version": "source",
"requires_python": ">=3.8",
"size": 15955,
"upload_time": "2024-08-26T02:31:43",
"upload_time_iso_8601": "2024-08-26T02:31:43.264455Z",
"url": "https://files.pythonhosted.org/packages/c0/47/0802d034836c66c886d29c356beeb51a82f18a118453a3fe346868fe9d43/dbsys-0.7.9.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2024-08-26 02:31:43",
"github": true,
"gitlab": false,
"bitbucket": false,
"codeberg": false,
"github_user": "lifsys",
"github_project": "dbsys",
"travis_ci": false,
"coveralls": false,
"github_actions": false,
"requirements": [
{
"name": "pandas",
"specs": [
[
">=",
"1.0.0"
]
]
},
{
"name": "sqlalchemy",
"specs": [
[
">=",
"1.4.0"
]
]
},
{
"name": "redis",
"specs": [
[
">=",
"3.5.0"
]
]
}
],
"lcname": "dbsys"
}
JSON
