A comprehensive library for managing database operations using SQLAlchemy, pandas, and Redis
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.
execon method for executing functions on specific pubsub messagesYou can install the latest version of dbsys using pip:
pip install --upgrade dbsys
For development purposes, you can install the package with extra dependencies:
pip install dbsys[dev]
This will install additional packages useful for development, such as pytest, flake8, and mypy.
Here's a comprehensive example of how to use dbsys with SQL databases:
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:
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()
The main class for interacting with the database. It supports both SQL databases and Redis.
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.
get_connection_string() method.table(table_name: str) -> DatabaseManagerread() -> pd.DataFramewrite(data: pd.DataFrame) -> boolcreate(data: pd.DataFrame) -> booldelete_table() -> booldelete_row(row_identifier: Dict[str, Any]) -> intsearch(conditions: Union[Dict[str, Any], str], limit: Optional[int] = None, case_sensitive: bool = False) -> pd.DataFramebackup(file_path: str, columns: Optional[List[str]] = None) -> boolrestore(file_path: str, mode: str = 'replace') -> boolexecute_query(query: str, params: Optional[Dict[str, Any]] = None) -> Union[pd.DataFrame, int]pub(message: str, channel: str) -> intsub(channel: str, handler: Optional[Callable[[str, str], None]] = None, exiton: str = "") -> DatabaseManagerpubsub(pub_message: str, pub_channel: str, sub_channel: str, handler: Optional[Callable[[str, str], None]] = None, exiton: str = "CLOSE", wait: Optional[int] = None) -> DatabaseManagerunsub(channel: Optional[str] = None) -> DatabaseManagerget_stored_messages(channel: str) -> List[str]clear_stored_messages(channel: Optional[str] = None) -> DatabaseManagerFor detailed usage of each method, including all parameters and return values, please refer to the docstrings in the source code.
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.Contributions are welcome! Please feel free to submit a Pull Request.
git checkout -b feature/AmazingFeature)git commit -m 'Add some AmazingFeature')git push origin feature/AmazingFeature)This project is licensed under the MIT License. See the LICENSE file for details.
If you encounter any problems or have any questions, please open an issue on our GitHub repository.
Lifsys, Inc is an AI company dedicated to developing innovative solutions for data management and analysis. For more information, visit www.lifsys.com.
execon method for executing functions on specific pubsub messagesexecon method for executing functions on specific pubsub messagesresults() method(... previous changelog entries ...)
FAQs
A comprehensive library for managing database operations using SQLAlchemy, pandas, and Redis
We found that dbsys demonstrated a healthy version release cadence and project activity because the last version was released less than a year ago. It has 1 open source maintainer collaborating on the project.
Did you know?
Socket for GitHub automatically highlights issues in each pull request and monitors the health of all your open source dependencies. Discover the contents of your packages and block harmful activity before you install or update your dependencies.
Research
Security News
Socket’s threat research team has detected six malicious npm packages typosquatting popular libraries to insert SSH backdoors.
Security News
MITRE's 2024 CWE Top 25 highlights critical software vulnerabilities like XSS, SQL Injection, and CSRF, reflecting shifts due to a refined ranking methodology.
Security News
In this segment of the Risky Business podcast, Feross Aboukhadijeh and Patrick Gray discuss the challenges of tracking malware discovered in open source softare.