Date format not correct.. Ways to hide the password?? Excel, Python, Mysql

Question

I am working on a script to load information from excel to SQL. I cannot figure out how to fix my loops to remove the extra comma and extra space I get at the end of my sql code..

This is python code meant to read an excel file, and create INSERT INTO querys...

This is a python script meant to pull data from an excel file

import xlrd
with open('Phase3initial.sql', 'w') as file:
    data = xlrd.open_workbook('Phase3 Initial Data.xlsx', on_demand = True)
    sheets = data.sheet_names()
    tables = ['User', 'UserEmail', 'Employee', 'Site', 'Event', 'Transit', 'Connect', 'TakeTransit', 'AssignTo', 'VisitEvent', 'VisitSite']
    for i in range(data.nsheets):
        sheets[i] = data.sheet_by_index(i)
        for j in range (1, sheets[i].nrows):
            file.write('INSERT INTO ' + str(tables[i]) + '(')
            row = sheets[i].row_values(j)
            for k in range(len(row)):
                cell = sheets[i].cell(j,k)
                if tables[i] == 'User':
                    if k ==1:
                        file.write(str(cell.value) +', ')
                        continue
                file.write(str(cell.value) + ', ')
            file.write(');\n')

image of excel file https://imagizer.imageshack.com/v2/1024x768q90/924/EOTu41.png

I get an extra space and comma at the end.. after my code runs..Not sure how to fix that..

INSERT INTO User(james.smith, jsmith123, Approved, James, Smith, Employee, );
INSERT INTO User(michael.smith, msmith456, Approved, Michael, Smith, Employee, Visitor, );
INSERT INTO UserEmail(james.smith, jsmith@gatech.edu, );
INSERT INTO UserEmail(james.smith, jsmith@outlook.com, );

Edit After some trial an error I am still stuck with the Date format.. Also, any ideas on how to hide the password? It's not supposed to be visible..

import xlrd

with open('Phase3initial.sql', 'w') as file:
    data = xlrd.open_workbook('Phase3 Initial Data.xlsx', on_demand = True)
    sheets = data.sheet_names()
    tables = ['User', 'UserEmail', 'Employee', 'Site', 'Event', 'Transit', 'Connect', 'TakeTransit', 'AssignTo', 'VisitEvent', 'VisitSite']
    for i in range(data.nsheets):
        sheets[i] = data.sheet_by_index(i)
        for j in range (1, sheets[i].nrows):
            file.write('INSERT INTO ' + str(tables[i]) + '(')
            row = sheets[i].row_values(j)

            # first len(row)-1 columns
            for k in range(len(row)-1):
                cell = sheets[i].cell(j,k)
                if tables[i] == 'User':
                    if k ==1:
                        file.write(str(cell.value) +',')
                        continue
                file.write(str(cell.value) + ',')

            # last column
            k = len(row) - 1
            cell = sheets[i].cell(j, k)
            if tables[i] == 'User':
                if k == 1:
                    file.write(str(cell.value))
                    continue
            file.write(str(cell.value))

            file.write(');\n')

For some reason my date is changed to 435000.0... is not formated.. check StartDate and EndDate on link https://imagizer.imageshack.com/v2/1024x768q90/921/uODMTH.png after james.smith and michael.smith the password are showing.. any ideas on how to hide the passwords?

My output

INSERT INTO User(james.smith,jsmith123,Approved,James,Smith,Employee);
INSERT INTO User(michael.smith,msmith456,Approved,Michael,Smith,Employee, Visitor);
INSERT INTO Event(Eastside Trail,43500.0,Piedmont Park,43501.0,0.0,99999.0,1.0,A combination of multi-use trail and linear greenspace, the Eastside Trail was the first finished section of the Atlanta BeltLine trail in the old rail corridor. The Eastside Trail, which was funded by a combination of public and private philanthropic sources, runs from the tip of Piedmont Park to Reynoldstown. More details at https://beltline.org/explore-atlanta-beltline-trails/eastside-trail/,);
INSERT INTO Event(Eastside Trail,43500.0,Inman Park,43501.0,0.0,99999.0,1.0,A combination of multi-use trail and linear greenspace, the Eastside Trail was the first finished section of the Atlanta BeltLine trail in the old rail corridor. The Eastside Trail, which was funded by a combination of public and private philanthropic sources, runs from the tip of Piedmont Park to Reynoldstown. More details at https://beltline.org/explore-atlanta-beltline-trails/eastside-trail/,);

You may want to change your logic and append your `', '` **before** the cell value. You will need to suppress it for the first write.This is easier since you can always determine the first write but not the last. — PM 77-1, Apr 12 '19 at 21:22
@PM77-1 That's a hack. The proper approach is to use the appropriate MySQL string escaping method. Any field with `'`' in it will break this horribly. — tadman, Apr 12 '19 at 21:40
You may find it's easier to write out a CSV file using a Python CSV library and then import that directly into MySQL using [`LOAD DATA INFILE`](https://stackoverflow.com/questions/3635166/how-to-import-csv-file-to-mysql-table). Writing raw SQL is not as easy since you must get the syntax and escaping correct. — tadman, Apr 12 '19 at 21:41

score 0 · Answer 1 · answered Apr 12 '19 at 21:31

0

You can solve this two ways:
- Check which line is last in loop, and not add a ', ' to it,
- Change write(str(cell.value) + ', ') to write(', ' +str(cell.value)) and check for first line and skip adding ', ' to it.

answered Apr 12 '19 at 21:31

icwebndev

413
3
10

This needs to call the Python MySQL string escaping function. – tadman Apr 12 '19 at 21:40

Milos Grujic · Answer 2 · 2019-04-12T22:06:59.233

Get a list of cell values i.e.: cellvalues = ['user2','user1','user3','user4','user5']

And then use join on them like so: result = cellvalues.join(',') The result will be: "user2,user1,user3,user4,user5"

You can further do:

values = f"({cellvalues.join(',')})"

And just add it to the beginning of the query.

Just one thing don't you need single quotes on those values? Or hey, her's another idea just take the cellvalues and do:

values = str(tuple(cellvalues))

score 0 · Answer 3 · answered Apr 13 '19 at 05:19

It will be easy if use pandas to read excel file as data frame and you can export it to mysql table.Use default mysql connector. You can write data back to table using to_sql or execute or executemany command. By using environment variable you can hide your password. For an example if u set pass='xyz' in your computer you can assign that password to variable by passwd=os.getenv('pass').Ref https://dev.mysql.com/doc/connector-python/en/connector-python-example-connecting.html

Date format not correct.. Ways to hide the password?? Excel, Python, Mysql

3 Answers3