Python Forum

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

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()

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.

Is this one optimizing tables?

I tried similar one but the changes are not reflecting in DB.