Having multiple lists and inserting their values into MySql

Question

Here is my code to read from some data into MySql, the MySql table contains 4 columns, that in 2 columns I want to insert items of an array, each into a new row. I was able to insert one list into MySql , but just was curious to know what happens if I have more than one list. Here is the code:

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

and here is output:

1   ni  sa  2   2
2   ni  sa  3   2

Can someone please explain why this is happening? Why I do not see all the values for the second list (b)? What is the best approach in these circumstances?

I think indentation is the problem. Although it is not visible it seems you are actually not iterating the inner list. — mad_, Aug 13 '18 at 14:27
Hint prevent SQL injection with prepared statements.. Python does support them. — Raymond Nijland, Aug 13 '18 at 14:30

mad_ · Accepted Answer · 2018-08-13T14:55:22.377

I am not sure whether it solves your problem. But, to me it might be related to the way you are iterating. Example:

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

Outputs(your current output):

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

If we change it with something this it works

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

Outputs(as expected):

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

To ignore SQL injection

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

Outputs:

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

so it was all because of the indentation? Thx – nina_dev Aug 13 '18 at 17:30 — nina_dev, Aug 13 '18 at 17:30

Having multiple lists and inserting their values into MySql

1 Answers1

Linked