MySQL Database Utility Functions

A set of Python functions to interact with MySQL databases. These functions cover various database operations, including table manipulation, data insertion, selection, update, and deletion.

Function Definitions

get_database_url(host, port, database_name, username, password)

Returns a database_url which needs to pass for all the further functions

list_tables(database_url)

Returns a list of tables in the specified MySQL database.

create_table(database_url, table_name, columns)

Creates a new table in the database with the given name and columns.

insert(database_url, table_name, data)

Inserts a single row of data into the specified table.

insert_many(database_url, table_name, data_list)

Inserts multiple rows of data into the specified table.

select_all(database_url, table_name)

Selects all rows from the specified table.

select_with_pagination(database_url, table_name, from_index, to_index)

Selects a range of rows from the specified table with pagination.

select_by_column(database_url, table_name, column_name, column_value)

Selects rows from the specified table where a specific column matches a given value.

select(database_url, table_name, where_dict)

Selects rows from the specified table based on the provided conditions.

update(database_url, table_name, update_dict, where_dict)

Updates rows in the specified table based on the provided conditions with new values.

delete(database_url, table_name, where_dict)

Deletes rows from the specified table based on the provided conditions.

truncate(database_url, table_name)

Truncates (removes all rows from) the specified table.

delete_table(database_url, table_name)

Deletes the specified table from the database.

sql_query(database_url, raw_sql_query)

Executes a raw SQL query on the specified database and returns the result.

Example Usage

Below are examples illustrating the usage of some functions. Note that you need to replace the placeholder values with your actual database details.

# Example usage of get_database_url
database_url = get_database_url("localhost", "5432", "test", "root", "password")

# Example Usage of list_tables
list_of_tables = list_tables(database_url)
print("List of Tables:", list_of_tables)

# Example Usage of create_table
create_table(database_url, "new_table", ["column1 INT", "column2 VARCHAR(50)"])

# Example Usage of insert
insert(database_url, "existing_table", {"column1": 123, "column2": "example_data"})

# Example Usage of insert_many
data_to_insert_many = [
    {"column1": 456, "column2": "more_data"},
    {"column1": 789, "column2": "additional_data"}
]
insert_many(database_url, "existing_table", data_to_insert_many)

# Example Usage of select_all
all_rows = select_all(database_url, "existing_table")
print("All Rows:", all_rows)

# Example Usage of select_with_pagination
selected_rows = select_with_pagination(database_url, "existing_table", 1, 5)
print("Selected Rows:", selected_rows)

# Example Usage of select_by_column
selected_rows_by_column = select_by_column(database_url, "existing_table", "column1", 456)
print("Selected Rows by Column:", selected_rows_by_column)

# Example Usage of select
conditions = {"column1": 123}
selected_rows_with_conditions = select(database_url, "existing_table", conditions)
print("Selected Rows with Conditions:", selected_rows_with_conditions)

# Example Usage of update
update_conditions = {"column1": 123}
update_values = {"column2": "updated_data"}
update(database_url, "existing_table", update_values, update_conditions)

# Example Usage of delete
delete_conditions = {"column1": 123}
delete(database_url, "existing_table", delete_conditions)

# Example Usage of truncate
truncate(database_url, "existing_table")

# Example Usage of delete_table
delete_table(database_url, "existing_table")

# Example Usage of sql_query
raw_sql_query = "SELECT * FROM example_table WHERE age > 25"
result = sql_query(database_url, raw_sql_query)
print(result)