pymysqlhelper 1.6.0

A simple MySQL database helper for easy interactions

Database Library

A Python-based database management library that supports both MySQL and SQLite. It provides an easy-to-use interface for defining tables, inserting data, searching records, updating, deleting, and more.

Features

• Supports MySQL (via PyMySQL) and SQLite.
• Table creation, modification, and deletion.
• Data insertion, updating, and querying.
• Bulk insert operations.
• Pagination support.
• Schema introspection.
• Data replication between SQLite and MySQL.

Installation

pip install sqlalchemy pymysql

Usage

Initialize Database

from database import Database, LocalDatabase

# MySQL Database Connection
mysql_db = Database(username='root', password='password', host='localhost', port=3306, database='testdb')

# SQLite Local Database
sqlite_db = LocalDatabase(db_path='local.db')

---

Define a Table

table = mysql_db.define_table("users", id=Integer, name=Text, age=Integer)

---

Insert Data

mysql_db.insert("users", id=1, name="Alice", age=25)

---

Bulk Insert

data = [
    {"id": 2, "name": "Bob", "age": 30},
    {"id": 3, "name": "Charlie", "age": 22}
]
mysql_db.bulk_insert("users", data)

---

Search Data

users = mysql_db.search("users")
print(users)  # List of all users

Search with filters:

alice = mysql_db.search("users", id=1)
print(alice)

---

Get a Single Record

user = mysql_db.get("users", id=1)
print(user)

---

Update Data

mysql_db.update("users", {"id": 1}, {"age": 26})

---

Delete Data

mysql_db.delete("users", id=3)

---

List All Tables

tables = mysql_db.list_tables()
print(tables)

---

Count Rows

user_count = mysql_db.count_rows("users")
print(user_count)

---

Get Distinct Column Values

distinct_ages = mysql_db.distinct_values("users", "age")
print(distinct_ages)

---

Search with Pagination

users_page1 = mysql_db.search_paginated("users", page=1, page_size=2)
print(users_page1)

---

Get Table Schema

schema = mysql_db.get_table_schema("users")
print(schema)

---

Rename Table

mysql_db.rename_table("users", "members")

---

Add a Column

mysql_db.add_column("members", "email", "TEXT")

---

Drop a Column

mysql_db.drop_column("members", "email")

---

Delete Table

mysql_db.delete_table("members")

---

Replicate SQLite to MySQL

mysql_db.replicate_local_to_online(sqlite_db)

Replicate MySQL to SQLite

sqlite_db.replicate_online_to_local(mysql_db)

Get column type

print(sqlite_db.get_column_type("users", "age"))
print(my_sql.get_column_type("users", "age"))

Edit column type

sqlite_db.edit_column_type("users", "age", "VARCHAR(10)")
mysql_db.edit_column_type("users", "age", "TEXT")

---

License

This project is licensed under the Apache License 2.0. See the LICENSE file for details.