SQL db插入到表中可用于1项，但不能用于多项

Question

我有一个sql数据库，我也试图通过python脚本来编写。python脚本解析DC开关，然后将其输出到结果列表中。然后我过滤下面两个项目的描述和界面

该应用程序的前端是一个烧瓶页我有按钮分配，将查询x柜和做所有上述。我想把下面所有的数据发送到一个sql数据库。我还想防止重复/更新表，如果项目已更改

现在，当我运行查询时，如果工作参数被传递到sql并且flask脚本的其余部分被处理。当我传递带有多个项的params时，我得到以下错误

OperationalError: (1241, 'Operand should contain 1 column(s)')

工作参数：

params = ([('TECH2_5750',)], [('Gi1/0/7',)], [(u'10.210.44.5',)])

不工作参数：

params = ([('TECH2_5252',), ('TECH2_5261',), ('TECH2_5857',), ('TECH2_5278',), ('TECH2_5855',), ('TECH2_5207',), ('TECH2_6539',), ('TECH2_6363',), ('TECH2_5253',), ('TECH2_5030',), ('TECH2_6575',), ('TECH2_5839',), ('TECH2_6953',), ('TECH2_6133',), ('TECH2_5046',), ('TECH2_6152',), ('TECH2_5267',), ('TECH2_5847',), ('TECH2_5748',), ('TECH2_5104',), ('TECH2_5649',), ('TECH2_5103',)], [('Gi2/0/11',), ('Gi1/0/11',), ('Gi2/0/7',), ('Gi3/0/27',), ('Gi1/0/7',), ('Gi2/0/9',), ('Gi3/0/7',), ('Gi2/0/27',), ('Gi1/0/29',), ('Gi3/0/25',), ('Gi2/0/25',), ('Gi2/0/29',), ('Gi2/0/31',), ('Gi1/0/31',), ('Gi2/0/5',), ('Gi1/0/25',), ('Gi1/0/5',), ('Gi3/0/5',), ('Gi1/0/9',), ('Gi1/0/35',), ('Gi1/0/27',), ('Gi1/0/33',)], [(u'10.210.45.5',)])

代码：

local1 = []
device1 = []
items = len(filtered_interface)
for item in range(0, items):
    t = filtered_tech[item].split(' ')
    hostname = t[1]
    device1.append((hostname,))
    t = filtered_interface[item].split(' ')
    interfacelocal = t[1]
    local1.append((interfacelocal,))
cab1 = []
cab1.append((cab,))
params = (device1,  local1, cab1,)
connection1 = MySQLdb.connect(user="user", passwd="password", db="db",host="127.0.0.1")
mycursor = connection1.cursor()
sql = """
        INSERT INTO devices (hostname, localint, cab) VALUES (%s, %s, %s)"""
mycursor.execute(sql, params)
        connection1.commit()

Answer 1

mycursur.execute()要求params具有与查询中占位符相同数量的元素。因为有3个占位符，params必须有3个元素

如果要插入多行，则需要使用mycursor.executemany。这将对参数序列的每个元素重复执行查询params应该是一个二维序列，顶级序列的每个元素都是一个由3个参数组成的序列，用于填充占位符

params = [('TECH2_5252', 'Gi1/0/7', u'10.210.44.5'),
          ('TECH2_5261', 'Gi1/0/11', u'10.210.45.5'),
          ('TECH2_5857', 'Gi2/0/7', u'10.210.45.5'),
          ...]
sql = """
        INSERT INTO devices (hostname, localint, cab) VALUES (%s, %s, %s)"""
mycursor.executemany(sql, params)