python - 日期格式不正确。隐藏密码的方法？ Excel、Python、MySQL

Question

我正在编写一个脚本来将信息从 Excel 加载到 SQL。我不知道如何修复循环以删除 sql 代码末尾的额外逗号和额外空格。

这是Python代码，旨在读取Excel文件，并创建INSERT INTO查询...

这是一个Python脚本，旨在从Excel文件中提取数据

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

Excel文件的图像 https://imagizer.imageshack.com/v2/1024x768q90/924/EOTu41.png

我的代码运行后，我在末尾得到了一个额外的空格和逗号..不知道如何解决这个问题..

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, );

编辑经过一番尝试后，我仍然坚持使用日期格式。另外，关于如何隐藏密码有什么想法吗？它不应该是可见的..

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

由于某种原因，我的日期更改为 435000.0...未格式化...检查链接上的 StartDate 和 EndDate 奥 git _a在 james.smith 和 michael.smith 之后，密码显示出来。关于如何隐藏密码有什么想法吗？

我的输出

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/,);

Answer 1

您可以通过两种方式解决这个问题:
- 检查哪一行是循环中的最后一行，并且不向其中添加 ', '，
- 将 write(str(cell.value) + ', ') 更改为 write(', ' +str(cell.value)) 并检查第一行并跳过添加 ', ' 到其中。