Python CSV command line tool to import CSV into MySQL

Question

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

• Nizam Mohamed: Is this code working?
• Pitto: Yessir. Working and tested. I'm looking for "dos" and "don'ts" of this code and ideas about how to improve it.

Answer 1

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.