<jupyter_start><jupyter_text>Write to MySQL Database From Google Sheets Using Python Required Library to be install:1. pip install gspread    2. pip install oauth2client    3. pip install mysql-connectorNow Follow these steps to access Google Sheet via python :    1. Go to the Google APIs Console.    2. Create a new Project.    3. Click Enable API. Search for and enable Google Drive API.    4. Create Credentials and Select WebServer and Application Data. In Project Role Select Editor.5. Download JSON file save to your project folder and rename it as your required.    Open the JSON file and copy the cilent_email address and Share your Google Sheet with that Email ID with Write Permisssion. Otherwise you will get error SheetsNotFound    Note: When you run the script first time it gives error as to enable Google Sheet just click the link and its done.<jupyter_code># import libraries
import gspread
import mysql_credential as key  # Your MySQl Credential python file here: mysql_credential.py
import mysql.connector
from oauth2client.service_account import ServiceAccountCredentials

# initialize variables for gspread
scope = ['https://spreadsheets.google.com/feeds','https://www.googleapis.com/auth/drive']
creds = ServiceAccountCredentials.from_json_keyfile_name('secret.json', scope)
client = gspread.authorize(creds)
# define method to pull data from spreadsheet
def GetSpreadsheetData(sheetName, worksheetIndex):    
    sheet = client.open(sheetName).get_worksheet(worksheetIndex)    
    return sheet.get_all_values()[1:]
# Client Email inside secret.json file must have edit permission on the Google Sheet you are using
data_product = GetSpreadsheetData('ProductsTest', 0)
# View Data From Google Sheets 
print(data_product[0])
print(len(data_product))
# Client Email inside secret.json file must have edit permission on the Google Sheet you are using
data_group = GetSpreadsheetData('ProductsTest', 1)
# View Data From Google Sheets
data_group
print(data_group[0])
print(len(data_group))
# method to write this data to MySQL

def ToMySQLTable(sql_data):
    try:
        connection = mysql.connector.connect(
            user = key.user,     
            password = <PASSWORD>,        
            host = key.host,        
            database = key.database        
        )
        # Drop Group Table if it exists
        sql_drop = "DROP TABLE IF EXISTS {} ".format('GroupList')
        #create Group Table
        sql_create_table = """CREATE TABLE {}( 
            GroupId int NOT NULL AUTO_INCREMENT,
            GroupName VARCHAR(30) NOT NULL,
            GroupDescription VARCHAR(30),
            isActive VARCHAR(3) NOT NULL,
            PRIMARY KEY (GroupId)
            )""".format('GroupList')
         
        # Insert into GroupList Table From Google Sheet
        sql_insert_statement = """INSERT INTO {}( 
            GroupName,
            GroupDescription,
            isActive)
            VALUES ( %s,%s,%s )""".format('GroupList')
        
        cursor = connection.cursor()        
        cursor.execute(sql_drop)
        print('Table {} has been dropped'.format('GroupList'))        
        cursor.execute(sql_create_table)        
        print('Table {} has been created'.format('GroupList'))
        
        
        
        for i in sql_data:            
            cursor.execute(sql_insert_statement, i)
            
            connection.commit()        
           # print("Table {} successfully updated.".format('GroupList'))
            
        
        
            
    except mysql.connector.Error as error :        
        connection.rollback()        
        print("Error: {}. Table {} not updated!".format(error, 'GroupList'))
        
                
    finally:        
        cursor.execute('SELECT COUNT(*) FROM {}'.format('GroupList'))        
        rowCount = cursor.fetchone()[0]        
        print('GroupList', 'row count:', rowCount) 
        
        
        
        if connection.is_connected():            
            cursor.close()            
            connection.close()            
            print("MySQL connection is closed.")
                        
# method to write this data to MySQL

def ToMySQLTable1(sql_data):
    try:
        connection = mysql.connector.connect(
            user = key.user,     
            password = <PASSWORD>,        
            host = key.host,        
            database = key.database        
        )
        # Drop Product Table if it exists
        sql_drop = "DROP TABLE IF EXISTS {} ".format('ProductList')
        # Create Product Table
        sql_create_table = """CREATE TABLE {}( 
            ProductId int NOT NULL AUTO_INCREMENT,
            ProductName varchar(255) NOT NULL,
            ProductModel varchar(255),
            ProductSerial varchar(255),
            GroupAssociated varchar(255) NOT NULL REFERENCES GroupList(GroupId),
            ProductMRP numeric(9,2),
            PRIMARY KEY (ProductId)
            )""".format('ProductList')
         
        # Insert into ProductList Table From Google Sheet
        sql_insert_statement = """INSERT INTO {}( 
            ProductName,
            ProductModel,
            ProductSerial,
            GroupAssociated,
            ProductMRP)
            VALUES ( %s,%s,%s,%s,%s )""".format('ProductList')
        cursor = connection.cursor()        
        cursor.execute(sql_drop)
        print('Table {} has been dropped'.format('ProductList'))        
        cursor.execute(sql_create_table)        
        print('Table {} has been created'.format('ProductList'))
        
        
        for i in sql_data:            
            cursor.execute(sql_insert_statement, i)
            
            connection.commit()        
            #print("Table {} successfully updated.".format('ProductList'))
        
        
            
    except mysql.connector.Error as error :        
        connection.rollback()        
        print("Error: {}. Table {} not updated!".format(error, 'ProductList'))
                
    finally:        
        
        cursor.execute('SELECT COUNT(*) FROM {}'.format('ProductList'))        
        rowCount = cursor.fetchone()[0]        
        print('ProductList', 'row count:', rowCount) 
        
        
        if connection.is_connected():            
            cursor.close()            
            connection.close()            
            print("MySQL connection is closed.")
                        
ToMySQLTable(data_group)
ToMySQLTable1(data_product)<jupyter_output>Table ProductList has been dropped
Table ProductList has been created
ProductList row count: 110
MySQL connection is closed.
{'fraction_non_alphanumeric': 0.08432108027006752, 'fraction_numerical': 0.006151537884471118, 'mean_word_length': 2.010840108401084, 'pattern_counts': {'":': 0, '<': 6, '<?xml version=': 0, '>': 6, 'https://': 2, 'lorem ipsum': 0, 'www.': 1, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 1, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '1', 'dir': 'jupyter-structured-clean-dedup', 'id': '152154', 'license': 'MIT', 'n_tokens_mistral': 1707, 'n_tokens_neox': 1632, 'n_words': 509, 'path': 'Data-to-MySQL-From-GoogleSheets.ipynb', 'repo_name': 'byionline/medium-code', 'size': '6669'}