A script I made to run with a cron - Printable Version +- Python Forum (https://python-forum.io) +-- Forum: General (https://python-forum.io/forum-1.html) +--- Forum: Code sharing (https://python-forum.io/forum-5.html) +--- Thread: A script I made to run with a cron (/thread-21884.html) |
A script I made to run with a cron - menator01 - Oct-19-2019 I play around with python every now and again. Any input on the script is welcome. #! /usr/bin/python3 import pymysql from collections import Counter user = 'xxxxx' password = 'xxxx' host = 'localhost' db = pymysql.connect(host,user,password) def GetData(query): cur = db.cursor() cur.execute(query) data = cur.fetchall() MineData = [] for i in data: if i[0] != 'information_schema' and i[0] != 'phpmyadmin': MineData.append(i[0]) return MineData def GetCount(counter): count = sum(Counter(counter).values()) return count databases = GetData(query='show databases') print('Searching for databases') print('Found '+str(GetCount(databases))+ ' databases') for database in databases: print(database) print() for database in databases: action = GetData(query='use '+database) tables = GetData(query='show tables') print('Found '+str(GetCount(GetData(query='show tables')))+' tables in database '+database) for table in tables: print(table) print() db.close() I have more code that is in the my first writing of this code(this is the 3rd version) but, looking over it I noticed something that I don't understan. In the code where I put action='use '+database (this is/was for another part of the code to use) it does not work if I remove it. I don't understan why. Thanks RE: A script I made to run with a cron - menator01 - Oct-19-2019 Here is the completed code. The next version will be to get this to work as a class. As always any input welcome. Thanks #! /usr/bin/python3 import pymysql from collections import Counter user = 'xxxx' password = 'xxxx' host = 'localhost' db = pymysql.connect(host,user,password) def GetData(query): cur = db.cursor() cur.execute(query) data = cur.fetchall() MineData = [] for i in data: if i[0] != 'information_schema' and i[0] != 'phpmyadmin': MineData.append(i[0]) return MineData def GetCount(counter): count = sum(Counter(counter).values()) return count databases = GetData(query='show databases') print('Searching for databases') print('Found '+str(GetCount(databases))+ ' databases') for database in databases: print(database.capitalize()) print() for database in databases: action = GetData(query='use '+database) tables = GetData(query='show tables') print('Found '+str(GetCount(GetData(query='show tables')))+' tables in database '+database.capitalize()) for table in tables: print(table.capitalize()) print() print('Preparing to optimize tables for databases') print() for database in databases: action = GetData(query='use '+database) tables = GetData(query='show tables') print('Optimizing tables for '+database.capitalize()) for table in tables: GetData(query='optimize table '+table) print('Optimizing table '+table.capitalize()+' done') print() db.close() RE: A script I made to run with a cron - menator01 - Oct-19-2019 Could be written better but, here is my attempt at putting it in a class #! /usr/bin/python3 import pymysql from collections import Counter class Optimize: ''' This class searches and extracts databases and their tables. The tables are then optimized. ''' # Define some variables user = 'xxxxxx' password = 'xxxxxx' host = 'localhost' # We need db to be a global variable global db # Our connection to MySQL db = pymysql.connect(host,user,password) '''This function retrieves all the data we need.''' def GetData(query): # Setup our query function to mine the data we need. # This will be stored in the MineData array/list. # Also setting the databases we don't need using the # comparison operators != cur = db.cursor() cur.execute(query) data = cur.fetchall() MineData = [] for i in data: if i[0] != 'information_schema' and i[0] != 'phpmyadmin': MineData.append(i[0]) return MineData '''This function is used to count the databases and tables.''' def GetCount(counter): count = sum(Counter(counter).values()) return count ''' The main function grabs/mines the data we need, displays it and optimizes our tables in the databases. ''' def main(): # Grab all the databases with the exception of the ones # we do not want in the GetData function databases = Optimize.GetData(query = 'show databases') message = 'Searching for databases' print(message) # Print a message showing the count of databases found message = 'Found ' + str(Optimize.GetCount(databases)) + ' databases' print(message) # Loop through our databases and display for database in databases: print(database.capitalize()) print() message = 'Searching databases for tables' print(message) # Run the query to grab our tables in the databases for database in databases: action = Optimize.GetData(query = 'use ' + database) tables = Optimize.GetData(query = 'show tables') # Get the count for tables and display message = 'Found ' + str(Optimize.GetCount(tables)) + ' tables in database ' + database.capitalize() print(message) # Display the tables found for table in tables: print(table.capitalize()) print() print('Preparing to optimize tables') print() # Optimize the tables we found in our databases for database in databases: action = Optimize.GetData(query = 'use ' + database) tables = Optimize.GetData(query = 'show tables') print('Optimizing tables on database ' + database.capitalize()) for table in tables: Optimize.GetData(query = 'optimize table ' + table) print('Optimizing table ' + table.capitalize() + ' done') print('Finished optimizing tables on database ' + database.capitalize()) print() if __name__ == '__main__': main()As alway any input welcome. Good or bad. |