sql.py

import glob
import gzip
import os
import pymysql

# path to gz directory
GZ_DIR = '/Users/mani/Desktop/log file/csv/'
# Database Infomation
DB_HOST='localhost'
DB_USER='diaguser'
DB_PASS='Password'
DB_NAME='PADIAGDB'

def csv_reader(file, header=False):
    import csv
    with open(file, "r") as f:
        reader = csv.reader(f)

        if header:
            next(reader)

        for row in reader:
            yield row

# function to connect sql
def import_sql(filename):
    #Prepare SQL query to INSERT a record into the database.

    # Open database connection
    db = pymysql.connect(host=DB_HOST,
                         user=DB_USER,
                         password=DB_PASS,
                         db=DB_NAME,
                         charset='utf8')

    for row in csv_reader(filename, False):
        # prepare a cursor object using cursor() method
        with db.cursor() as cursor:
            if row[3] == "THREAT":
                sql = ( """INSERT INTO threat(
                        Domain,Receive_Time,Serial,Type,Threat_Content_Type,
                        Config_Version,Generate_Time,Source_address,Destination_address,
                        NAT_Source_IP,NAT_Destination_IP,Rule,Source_User,
                        Destination_User,Application,Virtual_System,Source_Zone,
                        Destination_Zone,Inbound_Interface,Outbound_Interface,Log_Action,
                        Time_Logged,Session_ID,Repeat_Count,Source_Port,Destination_Port,
                        NAT_Source_Port,NAT_Destination_Port,Flags,IP_Protocol,Action,
                        URL_Filename,Threat_Content_Name,Category,Severity,Direction,
                        Sequence_Number,Action_Flags,Source_Country,Destination_Country,
                        cpadding,contenttype,pcap_id,filedigest,cloud,url_idx,user_agent,
                        filetype,xff,referer,sender,subject,recipient,reportid,
                        dg_hier_level_1,dg_hier_level_2,dg_hier_level_3,dg_hier_level_4,
                        Virtual_System_Name,Device_Name,file_url )

                        VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,
                        %s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,
                        %s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s
                        );""" )

                try:
                    cursor.execute('SET foreign_key_checks = 0')
                    # Execute the SQL command
                    cursor.execute(sql, row)

                    #Commit your changes in the database
                    cursor.execute('SET foreign_key_checks = 1')
                    db.commit()

                except Exception as e:
                    print(e)
                    # Rollback in case there is any error
                    db.rollback()

    # disconnect from server
    db.close()

#####################################################################
# Main
#####################################################################

# List of all gz files
gz_files = (gz for gz in glob.glob(os.path.join(GZ_DIR, '*.gz')))

# Loop through all gz files
for gz_file in gz_files:
    # Step 1
    with gzip.open(gz_file, 'rb') as in_file:
        s = in_file.read()

    # store the uncompressed data
    sql_file = gz_file[:-3]

    #Convert to csv
    sql_file= sql_file[:-4] + '.csv'
    with open(sql_file, 'wb') as out_file:
        out_file.write(s)

    # Step 2: import to sql
    import_sql(sql_file)

    # Step 3: remove uncompresed file
    os.remove(sql_file)

    # Step 4: in loop, back to step 1 automatically for another gz files