日期格式不正确。。如何隐藏密码？？Excel、Python、Mysq

Question

我正在编写一个脚本，将信息从excel加载到SQL。我不知道如何修复循环以删除sql代码末尾的多余逗号和多余空间。。你知道吗

这是python代码，用于读取excel文件，并在querys中创建INSERT。。。你知道吗

这是一个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。。。未格式化。。检查链接上的开始日期和结束日期 https://imagizer.imageshack.com/v2/1024x768q90/921/uODMTH.png 之后詹姆斯史密斯以及迈克尔·史密斯密码正在显示。。你知道怎么隐藏密码吗？你知道吗

我的输出

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))，检查第一行并跳过向其中添加', '。你知道吗

Answer 2

获取单元格值列表，即：cellvalues = ['user2','user1','user3','user4','user5']

然后像这样使用join： result = cellvalues.join(',') 结果将是： "user2,user1,user3,user4,user5"

您可以进一步执行以下操作：

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

只需将其添加到查询的开头。你知道吗

只有一件事你不需要对这些值单引号吗？ 或者，她有另一个想法，拿着手机的价值观做：

values = str(tuple(cellvalues))

Answer 3

如果使用pandas读取excel文件作为数据框，您可以将其导出到mysql，这将非常容易表.使用默认mysql连接器。您可以使用to\u sql或executemany命令将数据写回表。通过使用环境变量，可以隐藏密码。例如，如果您在计算机中设置pass='xyz'，您可以通过passwd将该密码分配给变量=操作系统获取环境（'pass'）.Refhttps://dev.mysql.com/doc/connector-python/en/connector-python-example-connecting.html