mysql - 拥有多个列表并将它们的值插入 MySql

Question

这是我从一些数据读取到 MySql 的代码，MySql 表包含 4 列，在 2 列中我想插入一个数组的项目，每个项目都插入一个新行。我能够将一个列表插入MySql，但只是想知道如果我有多个列表会发生什么。这是代码：

cursor = db.cursor()
r=["2","3"]
b=["3","4","5","2"]
for x in r:
    for a in b:
        insertsql=("insert ignore into new_table (name,last,arrayeha,arrayeha_se) values ('ni','sal','"+x+"','"+a+"')")

这是输出：

1   ni  sa  2   2
2   ni  sa  3   2

有人可以解释为什么会这样吗？为什么我没有看到第二个列表 (b) 的所有值？在这些情况下最好的方法是什么？

标签： mysqlpython-3.x

Answer 1

我不确定它是否能解决您的问题。但是，对我来说，这可能与您迭代的方式有关。例子：

r=["2","3"]
b=["3","4","5","2"]
for x in r:
    for a in b:
        pass
    print(("insert ignore into new_table (name,last,arrayeha,arrayeha_se) values ('ni','sal','"+x+"','"+a+"')"))

输出（您当前的输出）：

insert ignore into new_table (name,last,arrayeha,arrayeha_se) values ('ni','sal','2','2')
insert ignore into new_table (name,last,arrayeha,arrayeha_se) values ('ni','sal','3','2')

如果我们用它来改变它，它就可以工作

r=["2","3"]
b=["3","4","5","2"]
for x in r:
    for a in b:
        print(("insert ignore into new_table (name,last,arrayeha,arrayeha_se) values ('ni','sal','"+x+"','"+a+"')"))

输出（如预期）：

insert ignore into new_table (name,last,arrayeha,arrayeha_se) values ('ni','sal','2','3')
insert ignore into new_table (name,last,arrayeha,arrayeha_se) values ('ni','sal','2','4')
insert ignore into new_table (name,last,arrayeha,arrayeha_se) values ('ni','sal','2','5')
insert ignore into new_table (name,last,arrayeha,arrayeha_se) values ('ni','sal','2','2')
insert ignore into new_table (name,last,arrayeha,arrayeha_se) values ('ni','sal','3','3')
insert ignore into new_table (name,last,arrayeha,arrayeha_se) values ('ni','sal','3','4')
insert ignore into new_table (name,last,arrayeha,arrayeha_se) values ('ni','sal','3','5')
insert ignore into new_table (name,last,arrayeha,arrayeha_se) values ('ni','sal','3','2')

忽略 SQL 注入

r=["2","3"]
b=["3","4","5","2"]
for x in r:
    for a in b:
        print(("insert ignore into new_table (name,last,arrayeha,arrayeha_se) values (?,?,?,?)",("ni",'sal',x,a)))

输出：

('insert ignore into new_table (name,last,arrayeha,arrayeha_se) values (?,?,?,?)', ('ni', 'sal', '2', '3'))
('insert ignore into new_table (name,last,arrayeha,arrayeha_se) values (?,?,?,?)', ('ni', 'sal', '2', '4'))
('insert ignore into new_table (name,last,arrayeha,arrayeha_se) values (?,?,?,?)', ('ni', 'sal', '2', '5'))
('insert ignore into new_table (name,last,arrayeha,arrayeha_se) values (?,?,?,?)', ('ni', 'sal', '2', '2'))
('insert ignore into new_table (name,last,arrayeha,arrayeha_se) values (?,?,?,?)', ('ni', 'sal', '3', '3'))
('insert ignore into new_table (name,last,arrayeha,arrayeha_se) values (?,?,?,?)', ('ni', 'sal', '3', '4'))
('insert ignore into new_table (name,last,arrayeha,arrayeha_se) values (?,?,?,?)', ('ni', 'sal', '3', '5'))
('insert ignore into new_table (name,last,arrayeha,arrayeha_se) values (?,?,?,?)', ('ni', 'sal', '3', '2'))