Welcome to the MySQL Administration Script! This Python-based tool allows you to manage your MySQL databases with ease. It provides a wide range of functionalities, from listing databases and tables to creating users and managing privileges. Below, you'll find a detailed guide on how to use this script, including code snippets, explanations, and examples.
- Introduction
- Installation
- Usage
- 1. List databases with sizes
- 2. Switch database
- 3. Show tables
- 4. List tables with row counts
- 5. Create a table
- 6. Delete a table
- 7. Describe a table
- 8. Insert data into a table
- 9. Display data from a table
- 10. Update data in a table
- 11. Delete data from a table
- 12. Search data in a table
- 13. Flexible search queries
- 14. Create MySQL user
- 15. Delete MySQL user
- 16. Grant privileges
- 17. Revoke privileges
- 18. Exit
- Contributing
- License
- Authors
This script is designed to simplify MySQL database management tasks. It connects to a MySQL server using credentials stored in config.py and provides a command-line interface (CLI) for performing various operations. The script is modular, with each function handling a specific task, making it easy to extend or modify.
-
Clone the Repository:
git clone https://github.com/ziyad-tarek1/MySQL_Administration_Script.git cd MySQL_Administration_Script -
Install Dependencies: Ensure you have Python 3.x installed. Then, install the required MySQL connector:
pip install mysql-connector-python
-
Configure
config.py: Edit theconfig.pyfile to include your MySQL server credentials:MYSQL_HOST = "localhost" MYSQL_USER = "root" MYSQL_PASSWORD = "your_password"
-
Run the Script: Execute the script using Python:
python mysql_admin.py
Function: list_databases_with_size()
Description: Lists all databases on the MySQL server along with their sizes in MB.
Code Snippet:
def list_databases_with_size():
try:
cursor.execute("SHOW DATABASES")
databases = cursor.fetchall()
print("\nDatabases and Sizes:")
for db in databases:
db_name = db[0]
cursor.execute(f"SELECT table_schema AS 'Database', SUM(data_length + index_length) / 1024 / 1024 AS 'Size (MB)' FROM information_schema.TABLES WHERE table_schema = '{db_name}' GROUP BY table_schema")
size = cursor.fetchone()
if size:
print(f"- {db_name}: {size[1]:.2f} MB")
else:
print(f"- {db_name}: 0 MB")
except mysql.connector.Error as err:
print(f"Error: {err}")Usage:
Enter your choice: 1Output:
Databases and Sizes:
- db1: 12.34 MB
- db2: 0 MB
Function: switch_database()
Description: Switches to a specified database. If the database does not exist, it prompts the user to create it.
Code Snippet:
def switch_database():
global conn, cursor, current_db
while True:
try:
new_db = input("Enter the database name to switch to: ")
cursor.execute("SHOW DATABASES")
databases = [db[0] for db in cursor.fetchall()]
if new_db not in databases:
create_db = input(f"Database '{new_db}' does not exist. Do you want to create it? (yes/no): ").strip().lower()
if create_db == 'yes':
cursor.execute(f"CREATE DATABASE {new_db}")
print(f"Database '{new_db}' created successfully!")
else:
print("Switching canceled.")
return
conn.database = new_db
current_db = new_db
print(f"Switched to database: {new_db}")
break
except mysql.connector.Error as err:
print(f"Error: {err}. Please try again.")Usage:
Enter your choice: 2
Enter the database name to switch to: my_databaseOutput:
Switched to database: my_database
Function: show_tables()
Description: Lists all tables in the currently selected database.
Code Snippet:
def show_tables():
if not check_database_selected():
return
try:
cursor.execute("SHOW TABLES")
tables = cursor.fetchall()
if tables:
print("\nTables:")
for table in tables:
print(f"- {table[0]}")
else:
print("No tables found.")
except mysql.connector.Error as err:
print(f"Error: {err}")Usage:
Enter your choice: 3Output:
Tables:
- table1
- table2
Function: list_tables_with_row_counts()
Description: Lists all tables in the current database along with the number of rows in each table.
Code Snippet:
def list_tables_with_row_counts():
if not check_database_selected():
return
try:
cursor.execute("SHOW TABLES")
tables = cursor.fetchall()
if tables:
print("\nTables and Row Counts:")
for table in tables:
table_name = table[0]
cursor.execute(f"SELECT COUNT(*) FROM {table_name}")
row_count = cursor.fetchone()[0]
print(f"- {table_name}: {row_count} rows")
else:
print("No tables found.")
except mysql.connector.Error as err:
print(f"Error: {err}")Usage:
Enter your choice: 4Output:
Tables and Row Counts:
- table1: 100 rows
- table2: 50 rows
Function: create_table()
Description: Creates a new table in the current database.
Code Snippet:
def create_table():
if not check_database_selected():
return
while True:
try:
print("Leave the Table name empty to return to the main menu")
table_name = input("Enter table name: ")
if not table_name:
print("return to the menu.")
return
columns = input("Enter columns (e.g., id INT PRIMARY KEY, name VARCHAR(255)): ")
query = f"CREATE TABLE {table_name} ({columns})"
cursor.execute(query)
conn.commit()
print(f"Table '{table_name}' created successfully!")
break
except mysql.connector.Error as err:
print(f"Error: {err}. Please check your inputs and try again.")
except Exception as e:
print(f"Unexpected error: {e}. Please try again.")Usage:
Enter your choice: 5
Enter table name: users
Enter columns: id INT PRIMARY KEY, name VARCHAR(255)Output:
Table 'users' created successfully!
Function: delete_table()
Description: Deletes a table from the current database.
Code Snippet:
def delete_table():
if not check_database_selected():
return
while True:
try:
print("Leave the Table name empty to return to the main menu")
table_name = input("Enter table name to delete: ")
if not table_name:
print("return to the menu.")
return
query = f"DROP TABLE {table_name}"
cursor.execute(query)
conn.commit()
print(f"Table '{table_name}' deleted successfully!")
break
except mysql.connector.Error as err:
print(f"Error: {err}. Please check your inputs and try again.")
except Exception as e:
print(f"Unexpected error: {e}. Please try again.")Usage:
Enter your choice: 6
Enter table name to delete: usersOutput:
Table 'users' deleted successfully!
Function: describe_table()
Description: Displays the structure of a table.
Code Snippet:
def describe_table():
if not check_database_selected():
return
while True:
try:
table_name = input("Enter table name to describe: ")
cursor.execute(f"DESCRIBE {table_name}")
columns = cursor.fetchall()
print("\nTable Structure:")
for column in columns:
print(column)
break
except mysql.connector.Error as err:
print(f"Error: {err}. Please check your inputs and try again.")
except Exception as e:
print(f"Unexpected error: {e}. Please try again.")Usage:
Enter your choice: 7
Enter table name to describe: usersOutput:
Table Structure:
('id', 'int(11)', 'NO', 'PRI', None, '')
('name', 'varchar(255)', 'YES', '', None, '')
Function: insert_data()
Description: Inserts a new row into a table.
Code Snippet:
def insert_data():
if not check_database_selected():
return
while True:
try:
print("Leave the Table name empty to return to the main menu")
table_name = input("Enter table name : ")
if not table_name:
print("return to the menu.")
return
cursor.execute(f"DESCRIBE {table_name}")
columns = [col[0] for col in cursor.fetchall()]
values = []
for col in columns:
value = input(f"Enter value for {col}: ")
values.append(value)
query = f"INSERT INTO {table_name} VALUES ({', '.join(['%s'] * len(values))})"
cursor.execute(query, values)
conn.commit()
print("Data inserted successfully!")
break
except mysql.connector.Error as err:
print(f"Error: {err}. Please check your inputs and try again.")
except Exception as e:
print(f"Unexpected error: {e}. Please try again.")Usage:
Enter your choice: 8
Enter table name: users
Enter value for id: 1
Enter value for name: JohnOutput:
Data inserted successfully!
Function: select_data()
Description: Retrieves and displays all rows from a table.
Code Snippet:
def select_data():
if not check_database_selected():
return
while True:
try:
print("Leave the Table name empty to return to the main menu")
table_name = input("Enter table name : ")
if not table_name:
print("return to the menu.")
return
query = f"SELECT * FROM {table_name}"
cursor.execute(query)
rows = cursor.fetchall()
for row in rows:
print(row)
break
except mysql.connector.Error as err:
print(f"Error: {err}. Please check your inputs and try again.")
except Exception as e:
print(f"Unexpected error: {e}. Please try again.")Usage:
Enter your choice: 9
Enter table name: usersOutput:
(1, 'John')
(2, 'Jane')
Function: update_data()
Description: Updates a row in a table based on a condition.
Code Snippet:
def update_data():
if not check_database_selected():
return
while True:
try:
print("Leave the Table name empty to return to the main menu")
table_name = input("Enter table name : ")
if not table_name:
print("return to the menu.")
return
column = input("Enter column to update: ")
new_value = input(f"Enter new value for {column}: ")
condition = input(f'Enter condition (e.g., {column}=1 , for string use {column}="name" ): ')
query = f"UPDATE {table_name} SET {column} = %s WHERE {condition}"
cursor.execute(query, (new_value,))
conn.commit()
print("Data updated successfully!")
break
except mysql.connector.Error as err:
print(f"Error: {err}. Please check your inputs and try again.")
except Exception as e:
print(f"Unexpected error: {e}. Please try again.")Usage:
Enter your choice: 10
Enter table name: users
Enter column to update: name
Enter new value for name: Alice
Enter condition: id=1Output:
Data updated successfully!
Function: delete_data()
Description: Deletes rows from a table based on a condition.
Code Snippet:
def delete_data():
if not check_database_selected():
return
while True:
try:
print("Leave the Table name empty to return to the main menu")
table_name = input("Enter table name : ")
if not table_name:
print("return to the menu.")
return
condition = input("Enter condition to delete (e.g., id=1): ")
query = f"DELETE FROM {table_name} WHERE {condition}"
cursor.execute(query)
conn.commit()
print("Data deleted successfully!")
break
except mysql.connector.Error as err:
print(f"Error: {err}. Please check your inputs and try again.")
except Exception as e:
print(f"Unexpected error: {e}. Please try again.")Usage:
Enter your choice: 11
Enter table name: users
Enter condition to delete: id=1Output:
Data deleted successfully!
Function: search_data()
Description: Searches for rows in a table based on a condition.
Code Snippet:
def search_data():
if not check_database_selected():
return
while True:
try:
print("Leave the Table name empty to return to the main menu")
table_name = input("Enter table name: ")
if not table_name:
print("return to the menu.")
return
condition = input("Enter search condition (e.g., name='John'): ")
query = f"SELECT * FROM {table_name} WHERE {condition}"
cursor.execute(query)
rows = cursor.fetchall()
for row in rows:
print(row)
break
except mysql.connector.Error as err:
print(f"Error: {err}. Please check your inputs and try again.")
except Exception as e:
print(f"Unexpected error: {e}. Please try again.")Usage:
Enter your choice: 12
Enter table name: users
Enter search condition: name='John'Output:
(1, 'John')
Function: flexible_search()
Description: Allows for more complex search queries using SQL-like conditions.
Code Snippet:
def flexible_search():
if not check_database_selected():
return
while True:
try:
print("Leave the Table name empty to return to the main menu")
table_name = input("Enter table name : : ")
if not table_name:
print("return to the menu.")
return
condition = input("Enter search condition (e.g., name LIKE '%John%'): ")
query = f"SELECT * FROM {table_name} WHERE {condition}"
cursor.execute(query)
rows = cursor.fetchall()
for row in rows:
print(row)
break
except mysql.connector.Error as err:
print(f"Error: {err}. Please check your inputs and try again.")
except Exception as e:
print(f"Unexpected error: {e}. Please try again.")Usage:
Enter your choice: 13
Enter table name: users
Enter search condition: name LIKE '%John%'Output:
(1, 'John')
(3, 'Johnny')
Function: create_user()
Description: Creates a new MySQL user.
Code Snippet:
def create_user():
while True:
response = input("Are you sure you want to create a new user? (yes/no): ").lower()
if response in {'yes', 'y'}:
try:
username = input("Enter new username: ")
password = input("Enter password: ")
host = input("Enter host (default: localhost): ") or "localhost"
cursor.execute(f"CREATE USER '{username}'@'{host}' IDENTIFIED BY '{password}'")
conn.commit()
print(f"User '{username}' created successfully!")
break
except mysql.connector.Error as err:
print(f"Error: {err}. Please check your inputs and try again.")
except Exception as e:
print(f"Unexpected error: {e}. Please try again.")
else:
print("Creation canceled.")
return Usage:
Enter your choice: 14
Enter new username: new_user
Enter password: password123
Enter host (default: localhost): localhostOutput:
User 'new_user' created successfully!
Function: delete_user()
Description: Deletes an existing MySQL user.
Code Snippet:
def delete_user():
while True:
response = input("Are you sure you want to delete a user? (yes/no): ").lower()
if response in {'yes', 'y'}:
try:
username = input("Enter username to delete: ")
host = input("Enter host (default: localhost): ") or "localhost"
cursor.execute(f"DROP USER '{username}'@'{host}'")
conn.commit()
print(f"User '{username}' deleted successfully!")
break
except mysql.connector.Error as err:
print(f"Error: {err}. Please check your inputs and try again.")
except Exception as e:
print(f"Unexpected error: {e}. Please try again.")
else:
print("Deletion has been canceled.")
return Usage:
Enter your choice: 15
Enter username to delete: new_user
Enter host (default: localhost): localhostOutput:
User 'new_user' deleted successfully!
Function: grant_privileges()
Description: Grants privileges to a MySQL user on a specific database.
Code Snippet:
def grant_privileges():
while True:
response = input("Are you sure you want to grant privileges to a user? (yes/no): ").lower()
if response not in {'yes', 'y'}:
print("Operation cancelled.")
return
try:
username = input("Enter username: ")
host = input("Enter host (default: localhost): ") or "localhost"
if not user_exists(username, host):
print(f"Error: User '{username}' does not exist on host '{host}'.")
continue
database = input("Enter database name: ")
if not database_exists(database):
print(f"Error: Database '{database}' does not exist.")
continue
privileges = input("Enter privileges (e.g., SELECT, INSERT, UPDATE): ").upper()
confirmation = input(f"Are you sure you want to grant privileges {privileges} to {username}? (yes/no): ").lower()
if confirmation in {'yes', 'y'}:
cursor.execute(f"GRANT {privileges} ON {database}.* TO '{username}'@'{host}'")
conn.commit()
print(f"Privileges granted to '{username}' successfully!")
break
else:
print("Operation cancelled.")
return
except mysql.connector.Error as err:
print(f"Database error: {err}. Please check your inputs and try again.")
except Exception as e:
print(f"Unexpected error: {e}. Please try again.")Usage:
Enter your choice: 16
Enter username: new_user
Enter host (default: localhost): localhost
Enter database name: my_database
Enter privileges: SELECT, INSERTOutput:
Privileges granted to 'new_user' successfully!
Function: revoke_privileges()
Description: Revokes privileges from a MySQL user on a specific database.
Code Snippet:
def revoke_privileges():
while True:
response = input("Are you sure you want to revoke privileges from a user? (yes/no): ").lower()
if response in {'yes', 'y'}:
try:
username = input("Enter username: ")
host = input("Enter host (default: localhost): ") or "localhost"
if not user_exists(username, host):
print(f"Error: User '{username}' does not exist on host '{host}'.")
continue
database = input("Enter database name: ")
if not database_exists(database):
print(f"Error: Database '{database}' does not exist.")
continue
privileges = input("Enter privileges to revoke (e.g., SELECT, INSERT, UPDATE): ").upper()
confirmation = input(f"Are you sure you want to revoke privileges {privileges} from {username}? (yes/no): ").lower()
if confirmation in {'yes', 'y'}:
cursor.execute(f"REVOKE {privileges} ON {database}.* FROM '{username}'@'{host}'")
conn.commit()
print(f"Privileges revoked from '{username}' successfully!")
break
else:
print("operation cancelled")
return
except mysql.connector.Error as err:
print(f"Error: {err}. Please check your inputs and try again.")
except Exception as e:
print(f"Unexpected error: {e}. Please try again.")
else:
print("operation cancelled")
returnUsage:
Enter your choice: 17
Enter username: new_user
Enter host (default: localhost): localhost
Enter database name: my_database
Enter privileges to revoke: SELECTOutput:
Privileges revoked from 'new_user' successfully!
Function: Exits the script.
Usage:
Enter your choice: 18Output:
Exiting the script...
Contributions are welcome! If you find any issues or want to add new features, feel free to open an issue or submit a pull request.
This project is licensed under the MIT License. See the LICENSE file for details.
-
Ziyad Tarek
- GitHub: Ziyad Tarek
- Email: ziyadtarek180@gmail.com
-
Mohamed Mahmoud
- GitHub: Mohamed Mahmoud
- Email: mohamedmahmoud6498@gmail.com
-
Muhammed Galal Zalat
- GitHub: Muhammed Galal Zalat
- Email: mohmed.galal.zalat.mz@gmail.com
Enjoy managing your MySQL databases with this script! 😊