ThoseKind ThoseKind - 2 months ago 5x
MySQL Question

(Long) Removing Single Quotes From Strings in a List

This is all a bit vague because the program is rather in-depth, but stick with me as I will try to explain it as best I can. I wrote a program that takes a

file and turns it into
statements for a MySQL database. For example:

ID Number Letter Decimal Random
0 1 a 1.8 A9B34
1 4 b 2.4 C8J91
2 7 c 3.7 L9O77

would result in an insert statement like:

INSERT INTO table_name ('ID' int, 'Number' int, 'Letter' varchar(). 'Decimal', float(), 'Random' varchar()) VALUES ('0', '1', 'a', '1.8', 'A9B34');

However not all of the
files have the same column headers yet they need to be inserted into the same table. For files that do not have certain column headers I would like to insert a
value to show this. For example:

Lets say the first
file, A, has the information:

ID Number Decimal Random
0 1 1.8 A9B34
1 4 2.4 C8J91

The second
file, B, has different column headers:

ID Number Letter Decimal
0 3 x 5.6
1 8 y 4.8

After being converted to an
statement and put in the database it would ideally look like this:

ID TableID Number Decimal Letter Random
0 A 1 1.8 NULL A9B34
1 A 4 2.4 NULL C8J91
2 B 3 5.6 x NULL
3 B 8 4.8 y NULL

Now this is where I will probably start to lose you.

In order to accomplish what I needed I first take each file and create a master list of the all the column headers that the

def createMaster(path):
global master
master = []
for file in os.listdir(path):
if file.endswith('.csv'):
with open(path + file) as inFile:
csvFile = csv.reader(inFile)
col = next(csvFile) # gets the first line of the file, aka the column headers
master.extend(col) # adds the column headers from each file to the master list
masterTemp = OrderedDict.fromkeys(master) # gets rid of duplicates while maintaining order
masterFinal = list(masterTemp.keys()) # turns from OrderedDict to list
return masterFinal

Which would take all the column headers from multiple
files and assemble them into a master list in order without duplicates:

['ID', 'Number', 'Decimal', 'Letter', 'Random']

This provides me with the first part of the
statement. Now I need to add the
part to the statement, so I take and make a list of all the values in each row of each
file one at a time. For each row a temporary list is created, and then the list of column headers for that file is compared to the master list of column headers for all files. It then goes through each thing in the master list and tries to get the index of that same item in the column list. If it finds the item in the column list it inserts the item from the row list at that same index into the temporary list. If it can't find the item it inserts
into the temporary list instead. Once it has finished the temporary list it then turns the list into a string in the proper MySQL syntax and appends it to a
file for insertion. Here is the same idea in code:

def createInsert(inPath, outPath):
for file in os.listdir(inpath):
if file.endswith('.csv'):
with open(inPath + file) as inFile:
with open(outPath + 'table_name' + '.sql', 'a') as outFile:
csvFile = csv.reader(inFile)
col = next(csvFile) # gets the first row of column headers
for row in csvFile:
tempMaster = [] # creates a tempMaster list
insert = 'INSERT INTO ' + 'table_name' + ' (' + ','.join(master)+ ') VALUES ' # SQL syntax crap
for x in master:
i = col.index(x) # looks for the value in the column list
r = row[i] # gets the row value at the same index as the found column
tempMaster.append(r) # appends the row value to a temporary list
except ValueError:
tempMaster.append('NULL') # if the value is not found in the column list it just appends the string to the row master list
values = map((lambda x: "'" + x.strip() + "'"), tempMaster) # converts tempMaster from a list to a string
printOut = insert + ' (' + ','.join(values) + '):')
outFile.write(printOut + '\n') # writes the insert statement to the file

Finally now time for the question.

The problem with this program is that
takes all the row values from the tempMaster list and joins them with
marks via the line:

values = map((lambda x: "'" + x.strip() + "'"), tempMaster)

This is all fine and dandy except that MySQL wants
values to be inserted and just
instead of

How can I take the assembled row list and search for
strings and change them into just

I have two different ideas:

I could do something along these lines pull the
string from the
marks and replace it in the list.

def findBetween(s, first, last):
start = s.index(first) + len(first)
end = s.index(last, start)
return s[start:end]
except ValueError:
print('ERROR: findBetween function failure.')

def removeNull(aList):
tempList = []
for x in aList:
if x == 'NULL':
norm = findBetween(x, "'", "'")
return tempList

Or I could maybe add the
values into the list without
to begin with. This is within the

for x in tempMaster:
if x == 'NULL':
value = x
value = "'" + x + "'"
values = map((lambda x: x.strip()), tempMaster)
printOut = insert + ' (' + ','.join(values) + ');')
outFile.write(printOut + '\n')

However I think neither of these are viable because they would slow the program down significantly (with the larger files these raise a
). Therefore I am asking your opinion. I apologize if this was confusing or hard to follow. Please let me know what I could fix to make it easier to understand if this is the case and congratulations for making it to the end!


instead of

values = map((lambda x: "'" + x.strip() + "'"), tempMaster)

put this

 values = map((lambda x: "'" + x.strip() + "'" if x!='NULL' else x), tempMaster)