创建SQL插入脚本，返回：TypeError:不支持的+操作数类型：'NoneType'和'str'

Question

我有下面的数据框

df = pd.DataFrame({'vin': ['test'], 'modelId':['5641'], 'year': ['2021'] })

我正在尝试使用下面的函数从中创建SQL插入脚本

import pandas as pd
    def createSqlInsertStatement():
        global sql_texts,row
        sql_texts = []
        for index, row in df.iterrows():
            sql_texts.append('INSERT INTO '+ ' registry'+' (source, create_time, geo_group_id, status, make, vin, modelId, year)' 
            + ' VALUES "MANUAL_LOAD", sysdate, 42, 0, "MITSUBISHI", '+ ',' + str(tuple(row.values))) +';'
            return sql_texts

但我一直得到：

TypeError                                 Traceback (most recent call last)
c:\Users\Desktop\testsql.py in <module>
----> 1 createSqlInsertStatement()

c:\Users\Desktop\testsql.py in createSqlInsertStatement()
      9     sql_texts = []
     10     for index, row in df.iterrows():
---> 11         sql_texts.append('INSERT INTO '+ ' registry'+' (source, create_time, geo_group_id, status, make, vin, modelId, year)' 
     12         + ' VALUES "MANUAL_LOAD", sysdate, 42, 0, "MITSUBISHI", '+ ',' + str(tuple(row.values))) +';'
     13         return sql_texts

TypeError: unsupported operand type(s) for +: 'NoneType' and 'str'

我需要

INSERT INTO registry (source, create_time, geo_group_id, status, make, vin, modelId, year) VALUES ('MANUAL_LOAD', sysdate, 42, 0, 'MITSUBISHI', '5641', '2021');

我快要发疯了，正在寻找一种我找不到的“无”类型，还是我看错了

Answer 1

append语句末尾有一个错误： +';'应该在“append”的括号内。现在，您正在尝试将“append”的结果与字符串（字符）';'连接起来

更正为：

sql_texts.append('INSERT INTO '+ ' registry'+' (source, create_time, geo_group_id, status, make, vin, modelId, year)' 
            + ' VALUES "MANUAL_LOAD", sysdate, 42, 0, "MITSUBISHI", '+ ',' + str(tuple(row.values)) +';')

编辑：除了纠正上面的语法错误外，我相信您的代码不会提供您想要的输出，因为您不想将元组添加到字符串中。此语句可能更干净（using ^{} function而不是+）

sql_texts.append("INSERT INTO registry (source, create_time, geo_group_id, status, make, vin, modelId, year) VALUES (\"MANUAL_LOAD\", sysdate, 42, 0, \"MITSUBISHI\", \"{0}\", \"{1}\");".format(row.values[1], row.values[2]))

这将输出为：

['INSERT INTO registry (source, create_time, geo_group_id, status, make, vin, modelId, year) VALUES ("MANUAL_LOAD", sysdate, 42, 0, "MITSUBISHI", "5641", "2021");']

希望这有帮助

Answer 2

您错过了sql_text.append的右括号，并在str中添加了一个额外的括号

尝试此操作，它将返回带有查询的数组

import pandas as pd
    def createSqlInsertStatement():
        global sql_texts,row
        sql_texts = []
        for index, row in df.iterrows():
            sql_texts.append('INSERT INTO '+ ' registry'+' (source, create_time, geo_group_id, status, make, vin, modelId, year)' 
            + ' VALUES "MANUAL_LOAD", sysdate, 42, 0, "MITSUBISHI", '+ ',' + str(tuple(row.values)) +';')
            return sql_texts