I need help with a python script

[nicopython]

Hello,

A colleague coded a script for me.

this script is supposed to remove every entry from an access file where the content of the field specified in a csv file contains the characters string also specified in the csv file. (NOT only the exact match)

So we specify several characters strings (patterns) in the csv file, in order to be able to remove several types of entries when running the script.

in this csv file, for each characters string, the name of the field is also specified

For example, if I want to remove all lines containing "ABC" in the ID field and "XYZ" in "Val" field, I would list 2 lines in the csv file : "ID, ABC" and "Val, XYZ", and the script would remove the 2 types of entries in 1 same run.

In this example, ABC and XYZ are patterns, not exact matches.



Here is the python script :

import os, glob, shutil
import pyodbc

# Specify field(F) and pattern(S) file
patternFileName = 'samplepattern.csv'

rootFoler  ='G:\\user\\Desktop\\'
inputFolder=os.path.join(rootFoler,'Input')
outputFolder=os.path.join(rootFoler,'Output')
compactFolder=os.path.join(rootFoler,'CompactOutput')

def LOG(msg):
	print (msg)

def getFileList(inFolder):
	LOG("Creating file list...")
	if not os.path.exists(inFolder):
		LOG("Error: Input folder does not exists")
		return list()
	return list(os.path.basename(file) for file in glob.glob(os.path.join(inFolder,'*.mdb')))

def copyFileToOut(inFileList):
	# Create output directory if does not exists
	if not os.path.exists(outputFolder):
		LOG("Creating output directory")
		os.mkdir(outputFolder)

	for file in inFileList:
		shutil.copyfile(os.path.join(inputFolder, file), os.path.join(outputFolder, file))

def doRemoveEntriesFromFile(filePath, patternList):
	print (filePath)
	MDB = filePath
	DRV = '{Microsoft Access Driver (*.mdb, *.accdb)}'
	PWD = ''
	con = pyodbc.connect('DRIVER={0};DBQ={1};PWD={2}'.format(DRV,MDB,PWD))
	cur = con.cursor()
	tables = [table[2] for table in list(cur.tables()) if table[3] == 'TABLE']
	LOG('Found {} table[s]'.format(len(tables)))

	for table in tables:
		LOG('\tProcessing table {}'.format(table))
		SQL = "DELETE FROM {}".format(table)
		conditions = list()
		for pattern, field  in patternList:
			condition = "{} LIKE '%{}%'".format(field, pattern)
			conditions.append(condition)

		if len(conditions) != 0:
			conditionsStr = ' WHERE ' + ' OR '.join(conditions)
			SQL = SQL + conditionsStr + ';'
			#LOG(SQL)
			cur.execute(SQL)
			cur.commit()
			LOG('\tRows removed = {}'.format(cur.rowcount))
		else:
			print ("No pattern specified")


def removeEntries(outputFolder, fileList, patternList):
	for fileName in fileList:
		LOG('Processing file {}'.format(fileName))
		doRemoveEntriesFromFile(os.path.join(outputFolder, fileName), patternList)

def compactFiles(outputFolder, fileList):
	if not os.path.exists(compactFolder):
		LOG("Creating compactFolder directory")
		os.mkdir(compactFolder)
	import win32com.client
	oApp = win32com.client.Dispatch("Access.Application")
	for file in fileList:
		srcDB = os.path.join(outputFolder, file)
		destDB= os.path.join(compactFolder, file)
		oApp.compactRepair(srcDB, destDB)
	oApp = None 

def readPatternList():
	LOG("Reading pattern list..")
	f_in = open(os.path.join(rootFoler, patternFileName))
	p_list = list()
	for line in f_in.readlines():
		line = line.strip()
		line = line.strip('"')
		if line != "":
			p_list.append(line.split(','))
	return p_list

def main():
	inFileList = getFileList(inputFolder)
	copyFileToOut(inFileList)

	patternList = readPatternList()
	#print inFileList
	removeEntries(outputFolder, inFileList, patternList)
	LOG("Compact and Repair...")
	compactFiles(outputFolder, inFileList)
	LOG("Done")

if __name__ == '__main__':
	main()

I get an error message when I run this script :




I don't know how to code and I don't know Python.

Can someone help me please?

Thank you!

[ichabod801]

Please copy and paste the full text of the error in output tags.

[nicopython]

Please copy and paste the full text of the error in output tags.

Hello,

I don't know how to copy and paste from CMD.
If you click on the screenshot, you'll be able to read the full error message. There's nothing else but what you can see in the screenshot.

[ichabod801]

That's not a Python error, it's an SQL error. It's a bunch of conditions like Kd Tc 4c LIKE '%id%'. But that's three variable names (although 4c isn't a valid variable name) in a row. My only guess is that line 46 has field and pattern backwards, and that should be id LIKE '%Kd Tc 4c%'. Although that's looking suspiciously like a bunch of cards (King of Diamond, Ten of Diamonds, and Four of Clubs).

[jonnin]

on windows, in a console, you can right click, edit, mark and select the text you want to copy, press enter, and you can paste it into a text editor or this site etc. On dos/unix/etc command line you can also say python code.py > output.txt and open the text file, copy it, and past it here. If the program prompts and waits for input, the prompt won't show anymore, its in the text file, so you need to know what to type ahead of time before doing a capture.