Python Forum
A script I made to run with a cron
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
A script I made to run with a cron
#1
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
I welcome all feedback.
The only dumb question, is one that doesn't get asked.
My Github
How to post code using bbtags


Reply
#2
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()
I welcome all feedback.
The only dumb question, is one that doesn't get asked.
My Github
How to post code using bbtags


Reply
#3
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.
I welcome all feedback.
The only dumb question, is one that doesn't get asked.
My Github
How to post code using bbtags


Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  python-cron-migration | cronjobs managing itaybardugo 2 2,087 Jun-30-2020, 06:06 PM
Last Post: itaybardugo

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020