Python Forum
I need help with a python script
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
I need help with a python script
#1
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 :

[Image: 6c0e02ff206b0379315fb28c652188e3-full.png]


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

Can someone help me please?

Thank you!
Reply
#2
Please copy and paste the full text of the error in output tags.
Craig "Ichabod" O'Brien - xenomind.com
I wish you happiness.
Recommended Tutorials: BBCode, functions, classes, text adventures
Reply
#3
(Jul-27-2019, 11:16 AM)ichabod801 Wrote: 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.
Reply
#4
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).
Craig "Ichabod" O'Brien - xenomind.com
I wish you happiness.
Recommended Tutorials: BBCode, functions, classes, text adventures
Reply
#5
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.
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Is there a *.bat DOS batch script to *.py Python Script converter? pstein 3 3,000 Jun-29-2023, 11:57 AM
Last Post: gologica
  How to kill a bash script running as root from a python script? jc_lafleur 4 5,784 Jun-26-2020, 10:50 PM
Last Post: jc_lafleur
  crontab on RHEL7 not calling python script wrapped in shell script benthomson 1 2,247 May-28-2020, 05:27 PM
Last Post: micseydel
  Package python script which has different libraries as a single executable or script tej7gandhi 1 2,581 May-11-2019, 08:12 PM
Last Post: keames
  How to run python script which has dependent python script in another folder? PrateekG 1 3,105 May-23-2018, 04:50 PM
Last Post: snippsat
  How to call one python script and use its output in another python script lravikumarvsp 3 32,285 May-16-2018, 02:08 AM
Last Post: lravikumarvsp
  Check Python version from inside script? Run Pythons script in v2 compatibility mode? pstein 2 9,786 Jul-07-2017, 08:59 AM
Last Post: snippsat
  Cant pass corect variables to python script in bash script neradp 3 6,128 Nov-05-2016, 01:26 AM
Last Post: Larz60+

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020