4
\$\begingroup\$

I've written my first Python program longer than 15 rows and, of course, it looks terrible.

I'd be very glad to receive comments and suggestion about how to implement a similar tool correctly.

#!/usr/bin/python
# -*- coding: utf-8 -*-

# This script imports the csv data into a mysql database

import csv
import MySQLdb
import os
import sys
import datetime
import subprocess
import time
import logging
import notification

SCRIPT_PATH = os.path.dirname(sys.argv[0])
DATE_FORMAT = '%Y-%m-%d'
TIME_FORMAT = '%H:%M:%S'
DB_HOST = 'myhost'
DB_USER = 'myuser'
DB_PASSWORD = 'mypassword'
DB_NAME = 'mydbname'

logging.basicConfig(filename='csv_import_log.log', level=logging.DEBUG)


def main():
    clear_screen()
    if len(sys.argv) == 2:
        period = int(sys.argv[1])

        delete_all_csv_in_script_path()

        if period <= 6:
            start_process(period)
        else:
            print 'Wrong option'
    else:
        print 'Add one of the following parameters to retrieve the last relevant ticket number:'
        print ' 1 <-- today'
        print ' 2 <-- yesterday'
        print ' 3 <-- first day of current week'
        print ' 4 <-- first day of current month'
        print ' 5 <-- first day of current year'
        print ' 6 <-- all the data in the db'


def start_process(option):
    '''
    Contains the sequence of the application processes
    '''

    print ' 1 <-- today'
    print ' 2 <-- yesterday'
    print ' 3 <-- first day of current week'
    print ' 4 <-- first day of current month'
    print ' 5 <-- first day of current year'
    print ' 6 <-- all the data in the db'
    if option == 6:
        print 'Importing complete DB'

        # Logging begin time

        begin_time = datetime.datetime.now()
        begin_time_string = 'Process started at ' + str(begin_time)
        logging.info(begin_time_string)

        # File import processes

        delete_all_data_from_db()
        reset_typedoc_ini_file(0)
        start_tool_and_generate_csv()
        kill_tool()
        csv_import_start()

        # Logging end time

        end_time = datetime.datetime.now()
        end_time_string = 'Process ended at ' + str(end_time)
        logging.info(end_time_string)
        total_time = end_time - begin_time
        total_time = 'Total import time: ' + str(total_time)
        logging.info(str(total_time))
    else:
        print 'Importing option: ' + str(option)

        # Loggin begin time

        begin_time = datetime.datetime.now()
        begin_time_string = 'Process started at ' + str(begin_time)
        logging.info(begin_time_string)

        # File import processes

        last_ticket_number_and_date = \
            get_last_ticket_number_and_date(option)
        try:
            last_ticket_number = last_ticket_number_and_date[0]
            last_ticket_date = last_ticket_number_and_date[1]
            reset_typedoc_ini_file(last_ticket_number)
        except IndexError:
            print 'No last ticket for chosen date'
            sys.exit()
        except TypeError:
            print 'No last ticket for chosen date'
            sys.exit()
        delete_data_from_db(last_ticket_number, last_ticket_date)
        start_tool_and_generate_csv()
        kill_tool()
        csv_import_start()

        # Logging end time

        end_time = datetime.datetime.now()
        end_time_string = 'Process ended at ' + str(end_time)
        logging.info(end_time_string)
        total_time = end_time - begin_time
        total_time = 'Total import time: ' + str(total_time)
        logging.info(str(total_time))


def clear_screen():
    '''
    Cross platform clear screen function
    '''

    os.system(('cls' if os.name == 'nt' else 'clear'))


def reset_typedoc_ini_file(ticket_number):
    '''
    Changes ticket number into importantfile.ini
    '''

    file = open('importantfile.ini', 'w')
    file.write('1,data,' + str(ticket_number) + ',1\n')
    file.write('2,otherdata,1,1')
    file.close()
    print 'Added in file importantfile.ini ticket number: ' \
        + str(ticket_number)


def delete_all_csv_in_script_path():
    '''
    Deletes all csv contained into the script folder
    '''

    for file in os.listdir(SCRIPT_PATH):
        if file.endswith('.csv'):
            os.remove(file)


def get_last_ticket_number_and_date(period):
    '''
    Returns last ticket number and date based on given period
    '''

    if period == 1:
        try:
            now = datetime.datetime.now()
            today = now.strftime(DATE_FORMAT)
            today = str(today)
            print 'Checking last ticket number inserted today: ' + today
            mydb = MySQLdb.connect(host=DB_HOST, user=DB_USER,
                                   passwd=DB_PASSWORD, db=DB_NAME)
            cursor = mydb.cursor()
            cursor.execute("select number, date from docs where date = '%s' order by number desc limit 1"
                            % today)
            last_ticket_number_and_date = cursor.fetchone()
            return last_ticket_number_and_date
        except:
            return '0'
    elif period == 2:
        try:
            yesterday = datetime.datetime.now() \
                - datetime.timedelta(days=1)
            yesterday = yesterday.strftime(DATE_FORMAT)
            yesterday = str(yesterday)
            print 'Checking last ticket number inserted yesterday: ' \
                + yesterday
            mydb = MySQLdb.connect(host=DB_HOST, user=DB_USER,
                                   passwd=DB_PASSWORD, db=DB_NAME)
            cursor = mydb.cursor()
            cursor.execute("select number, date from docs where date = '%s' order by number desc limit 1"
                            % yesterday)
            last_ticket_number_and_date = cursor.fetchone()
            return last_ticket_number_and_date
        except:
            return '0'
    elif period == 3:
        try:
            today = datetime.datetime.now()
            monday = today - datetime.timedelta(days=today.weekday())
            monday = monday.strftime(DATE_FORMAT)
            monday = str(monday)
            print 'Checking last ticket number inserted this monday: ' \
                + monday
            mydb = MySQLdb.connect(host=DB_HOST, user=DB_USER,
                                   passwd=DB_PASSWORD, db=DB_NAME)
            cursor = mydb.cursor()
            cursor.execute("select number, date from docs where date = '%s' order by number desc limit 1"
                            % monday)
            last_ticket_number_and_date = cursor.fetchone()
            return last_ticket_number_and_date
        except:
            return '0'
    elif period == 4:
        try:
            today = datetime.datetime.now()
            first_day_of_the_month = datetime.date(today.year,
                    today.month, 1)
            first_day_of_the_month = \
                first_day_of_the_month.strftime(DATE_FORMAT)
            first_day_of_the_month = str(first_day_of_the_month)
            print 'Checking last ticket number inserted the 1st of this month: ' \
                + first_day_of_the_month
            mydb = MySQLdb.connect(host=DB_HOST, user=DB_USER,
                                   passwd=DB_PASSWORD, db=DB_NAME)
            cursor = mydb.cursor()
            cursor.execute("select number, date from docs where date = '%s' order by number desc limit 1"
                            % first_day_of_the_month)
            last_ticket_number_and_date = cursor.fetchone()
            return last_ticket_number_and_date
        except:
            return '0'
    elif period == 5:
        try:
            first_day_of_the_year = \
                datetime.date(datetime.date.today().year, 1, 1)
            first_day_of_the_year = \
                first_day_of_the_year.strftime(DATE_FORMAT)
            first_day_of_the_year = str(first_day_of_the_year)
            print 'Checking last ticket number inserted the 1st of this year: ' \
                + first_day_of_the_year
            mydb = MySQLdb.connect(host=DB_HOST, user=DB_USER,
                                   passwd=DB_PASSWORD, db=DB_NAME)
            cursor = mydb.cursor()
            cursor.execute("select number,date from docs where date = '%s' order by number desc limit 1"
                            % first_of_the_month)
            last_ticket_number_and_date = cursor.fetchone()
            return last_ticket_number_and_date
        except:
            return '0'
    elif period == 6:
        return '0'


def start_tool_and_generate_csv():
    '''
    Opens tool and starts csv generation
    '''

    subprocess.Popen('c:\IT\tool.exe')
    export_finished = False
    previous_total = 0
    equal_checks = 0
    while not export_finished:
        if equal_checks == 4:
            export_finished = True
            print 'Csv generation complete'
        file_sizes = []
        time.sleep(5)
        for file in os.listdir(SCRIPT_PATH):
            if file.endswith('.csv'):
                file_sizes.append(os.path.getsize(file))
        new_total = 1
        for value in file_sizes:
            new_total *= value
        if previous_total == new_total:
            equal_checks += 1
        else:
            equal_checks = 0
        previous_total = new_total


def kill_tool():
    '''
    Kills tool
    '''

    try:
        os.system('taskkill /im tool.exe /f')
    except:
        print 'Process Tool not found '


def delete_data_from_db(last_ticket_number, last_ticket_date):
    mydb = MySQLdb.connect(host=DB_HOST, user=DB_USER,
                           passwd=DB_PASSWORD, db=DB_NAME)
    cursor = mydb.cursor()
    cursor.execute("DELETE FROM docs where number > '%s'"
                   % last_ticket_number)


def delete_all_data_from_db():
    mydb = MySQLdb.connect(host=DB_HOST, user=DB_USER,
                           passwd=DB_PASSWORD, db=DB_NAME)
    cursor = mydb.cursor()
    cursor.execute('truncate docs')


def csv_import_start():
    '''
    Search into the script folder for csv file and imports those files into Mysql
    '''

    for file in os.listdir(SCRIPT_PATH):
        if file.endswith('.csv'):
            if file.startswith('Docs'):
                print 'Importing ' + file
                import_docs(file)
            elif file.startswith('GetCash'):
                print 'Importing ' + file
                import_getcash(file)
            elif file.startswith('GetUti'):
                print 'Importing ' + file
                import_getuti(file)
            else:
                print 'Input error'


def import_docs(docs_csv_file):
    '''
  Imports Docs.csv into mysql
  '''

    mydb = MySQLdb.connect(host=DB_HOST, user=DB_USER,
                           passwd=DB_PASSWORD, db=DB_NAME)
    cursor = mydb.cursor()
    firstline = True
    progress = 0
    with open(os.path.join(SCRIPT_PATH, docs_csv_file), 'r') as csvfile:
        csv_reader = csv.reader(csvfile, delimiter=';', quotechar='"',
                                quoting=csv.QUOTE_ALL)
        for row in csv_reader:
            if firstline:
                firstline = False
                continue
            csv_field_n1 = row[0]
            csv_field_n2 = row[1]
            csv_field_n3 = row[2]
            csv_field_n4 = row[3]
            csv_field_n5 = row[4]
            csv_field_n6 = row[5]
            csv_field_n7 = row[6]
            csv_field_n8 = row[7]
            csv_field_n9 = row[8]
            csv_field_n10 = row[9]
            csv_field_n11 = row[10]
            csv_field_n12 = row[11]
            csv_field_n13 = row[12]
            csv_field_n14 = row[13]
            csv_field_n15 = row[14]
            csv_field_n16 = row[15]
            csv_field_n17 = row[16]
            row[17] = row[17].replace(',', '.')
            csv_field_n18 = row[17]
            row[18] = row[18].replace(',', '.')
            csv_field_n19 = row[18]
            row[19] = row[19].replace(',', '.')
            csv_field_n20 = row[19]
            row[20] = row[20].replace(',', '.')
            csv_field_n21 = row[20]
            row[21] = row[21].replace(',', '.')
            csv_field_n22 = row[21]
            row[22] = row[22].replace(',', '.')
            csv_field_n23 = row[22]
            row[23] = row[23].replace(',', '.')
            csv_field_n24 = row[23]
            row[24] = row[24].replace(',', '.')
            csv_field_n25 = row[24]
            row[25] = row[25].replace(',', '.')
            csv_field_n26 = row[25]
            row[26] = row[26].replace(',', '.')
            csv_field_n27 = row[26]
            row[27] = row[27].replace(',', '.')
            csv_field_n28 = row[27]
            row[28] = row[28].replace(',', '.')
            csv_field_n29 = row[28]
            csv_field_n30 = row[29]
            row[30] = row[30].replace(',', '.')
            csv_field_n31 = row[30]
            row[31] = row[31].replace(',', '.')
            csv_field_n32 = row[31]
            row[32] = row[32].replace(',', '.')
            csv_field_n33 = row[32]
            row[33] = row[33].replace(',', '.')
            csv_field_n34 = row[33]
            csv_field_n35 = row[34]
            csv_field_n36 = row[35]
            csv_field_n37 = row[36]
            csv_field_n38 = row[37]
            csv_field_n39 = row[38]
            csv_field_n40 = row[39]
            csv_field_n41 = row[40]
            csv_field_n42 = row[41]
            row[42] = row[42].replace(',', '.')
            csv_field_n43 = row[42]
            row[43] = row[43].replace(',', '.')
            csv_field_n44 = row[43]
            csv_field_n45 = row[44]
            csv_field_n46 = row[45]
            csv_field_n47 = row[46]
            csv_field_n48 = row[47]
            row[48] = row[48].replace(',', '.')
            csv_field_n49 = row[48]
            csv_field_n50 = row[49]
            row[50] = row[50].replace(',', '.')
            csv_field_n51 = row[50]
            row[51] = row[51].replace(',', '.')
            csv_field_n52 = row[51]
            progress += 1
            print 'Rows imported in docs: ' + str(progress)
            cursor.execute('INSERT INTO docs(field_1, field_2, field_3, field_4, field_5, field_6, field_7, field_8, field_9, field_10, field_11, field_12, field_13, field_14, field_15, field_16, field_17, field_18, field_19, field_20, field_21, field_22, field_23, field_24, field_25, field_26, field_27, field_28, field_29, field_30, field_31, field_32, field_33, field_34, field_35, field_36, field_37, field_38, field_39, field_40, field_41, field_42, field_43, field_44, field_45, field_46, field_47, field_48, field_49, field_50, field_51, field_52 ) 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)'
                           , (
                csv_field_n1,
                csv_field_n2,
                csv_field_n3,
                csv_field_n4,
                csv_field_n5,
                csv_field_n6,
                csv_field_n7,
                csv_field_n8,
                csv_field_n9,
                csv_field_n10,
                csv_field_n11,
                csv_field_n12,
                csv_field_n13,
                csv_field_n14,
                csv_field_n15,
                csv_field_n16,
                csv_field_n17,
                csv_field_n18,
                csv_field_n19,
                csv_field_n20,
                csv_field_n21,
                csv_field_n22,
                csv_field_n23,
                csv_field_n24,
                csv_field_n25,
                csv_field_n26,
                csv_field_n27,
                csv_field_n28,
                csv_field_n29,
                csv_field_n31,
                csv_field_n32,
                csv_field_n33,
                csv_field_n34,
                csv_field_n35,
                csv_field_n36,
                csv_field_n37,
                csv_field_n38,
                csv_field_n39,
                csv_field_n40,
                csv_field_n41,
                csv_field_n42,
                csv_field_n43,
                csv_field_n44,
                csv_field_n45,
                csv_field_n46,
                csv_field_n47,
                csv_field_n48,
                csv_field_n49,
                csv_field_n50,
                csv_field_n51,
                csv_field_n52,
                ))


def import_getcash(getcash_csv_file):
    '''
    Imports GetCash.csv into mysql
    '''

    mydb = MySQLdb.connect(host=DB_HOST, user=DB_USER,
                           passwd=DB_PASSWORD, db=DB_NAME)
    cursor = mydb.cursor()
    firstline = True
    progress = 0
    with open(os.path.join(SCRIPT_PATH, getcash_csv_file), 'r') as \
        csvfile:
        csv_reader = csv.reader(csvfile, delimiter=';', quotechar='"',
                                quoting=csv.QUOTE_ALL)
        for row in csv_reader:
            if firstline:
                firstline = False
                continue
            csv_field_n1 = row[0]
            csv_field_n2 = row[1]
            csv_field_n3 = row[2]
            csv_field_n4 = row[3]
            csv_field_n5 = row[4]
            csv_field_n6 = row[5]
            csv_field_n7 = row[6]
            csv_field_n8 = row[7]
            row[8] = row[8].replace(',', '.')
            csv_field_n9 = row[8]
            csv_field_n10 = row[9]
            csv_field_n11 = row[10]
            csv_field_n12 = row[11]
            csv_field_n13 = row[12]
            progress += 1
            print 'Rows imported in getcash: ' + str(progress)
            cursor.execute('INSERT INTO getcash (field_1, field_2, field_3, field_4, field_5, field_6, field_7, field_8, field_9, field_10, field_11, field_12, field_13) VALUES(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)'
                           , (
                csv_field_n1,
                csv_field_n2,
                csv_field_n3,
                csv_field_n4,
                csv_field_n5,
                csv_field_n6,
                csv_field_n7,
                csv_field_n8,
                csv_field_n9,
                csv_field_n10,
                csv_field_n11,
                csv_field_n12,
                csv_field_n13,
                ))


def import_getuti(getuti_csv_file):
    '''
    Imports GetUti.csv into mysql
    '''

    mydb = MySQLdb.connect(host=DB_HOST, user=DB_USER,
                           passwd=DB_PASSWORD, db=DB_NAME)
    cursor = mydb.cursor()
    firstline = True
    progress = 0
    with open(os.path.join(SCRIPT_PATH, getuti_csv_file), 'r') as \
        csvfile:
        csv_reader = csv.reader(csvfile, delimiter=';', quotechar='"',
                                quoting=csv.QUOTE_ALL)
        for row in csv_reader:
            if firstline:
                firstline = False
                continue
            progress += 1
            print 'Rows imported in getuti: ' + str(progress)
            csv_field_n1 = row[0]
            csv_field_n2 = row[1]
            csv_field_n3 = row[2]
            csv_field_n4 = row[3]
            csv_field_n5 = row[4]
            csv_field_n6 = row[5]
            csv_field_n7 = row[6]
            csv_field_n8 = row[7]
            csv_field_n9 = row[8]
            csv_field_n10 = row[9]
            csv_field_n11 = row[10]
            csv_field_n12 = row[11]
            csv_field_n13 = row[12]
            cursor.execute('INSERT INTO getuti (field_1, field_2, field_3, field_4, field_5, field_6, field_7, field_8, field_9, field_10, field_11, field_12, field_13) VALUES(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)'
                           , (
                csv_field_n1,
                csv_field_n2,
                csv_field_n3,
                csv_field_n4,
                csv_field_n5,
                csv_field_n6,
                csv_field_n7,
                csv_field_n8,
                csv_field_n9,
                csv_field_n10,
                csv_field_n11,
                csv_field_n12,
                csv_field_n13,
                ))


if __name__ == '__main__':
    main()
\$\endgroup\$
  • \$\begingroup\$ Is this code working? \$\endgroup\$ – Nizam Mohamed Apr 1 '15 at 17:02
  • \$\begingroup\$ Yessir. Working and tested. I'm looking for "dos" and "don'ts" of this code and ideas about how to improve it. \$\endgroup\$ – Pitto Apr 1 '15 at 23:19
2
\$\begingroup\$

In function import_getuti,

for row in csv_reader:
            if firstline:
                firstline = False
                continue  

The above code skips first line of the file, but repetitious. To skip a line from any interator, use next.

next(csvfile)  

cursor.execute can be done as follows,

row is a list. Just build a tuple out of it and pass.

cursor.execute('INSERT INTO getuti (field_1, field_2, field_3, field_4, field_5, field_6, field_7, field_8, field_9, field_10, field_11, field_12, field_13)
                VALUES(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)', tuple(row[:13]))  

The following two lines can be moved out of functions and cursor should be passed to functions that need database access.

mydb = MySQLdb.connect(host=DB_HOST, user=DB_USER,
                           passwd=DB_PASSWORD, db=DB_NAME)
cursor = mydb.cursor()  

Use str.format to format stings.

print 'Rows imported in getcash: ' + str(progress)
print 'Rows imported in getcash: {}'.format(progress)  

In function import_docs, ',' is replaced with '.' in series of strings that can be done as follows,

row[17:34] = [s.replace(',', '.') for s in row[17:34]]

If the strings to be processed are non-contiguous in the list and can't be calculated, the whole list can be processed because str.replace is safe, it doesn't throw an error if couldn't find the specified old string.

cursor.execute("DELETE FROM docs where number > '%s'" % last_ticket_number)  

String formatting shouldn't be used in SQL statements for parameter substitution. Pass a tuple.

cursor.execute("DELETE FROM docs where number > %s", (last_ticket_number,))  

strftime returns string. No need to call str again.

In the try block of function get_last_ticket_number_and_date,

cursor.execute("select number,date from docs where date = '%s' order by number desc limit 1" % first_of_the_month)           
last_ticket_number_and_date = cursor.fetchone()            
return last_ticket_number_and_date   

non-critical code shouldn't be in try bolck and use else clause to return values.

try:
    cursor.execute("select number,date from docs where date = '%s' order by number desc limit 1", (first_of_the_month,))            
    last_ticket_number_and_date = cursor.fetchone()
except Exception as e:
    print "An error occured: {}".format(e)
else:
    return last_ticket_number_and_date   

glob module can be used to get list of files matching a pattern.

for file in glob.glob(os.path.join(SCRIPT_PATH,'*.csv')):
    os.remove(file)

As no calculation are performed on the input sys.argv[1], it's needless to convert to int, just string comparison can be done as perion == '1'.

if period in '123456':
    start_process(period)
else:
    print 'Wrong option'

Rather than dumping all the functions in the main moudle, fuctions should be split by usage and put into separate modules.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.