How can I loop through my arrays and build a list of SQL INSERT commands?

Question

I'm trying to build some SQL INSERT commands using data from arrays.

My problem is, I can't figure out how to iterate through all of them at the same time.

Here is my beginning code:

import os

raceValues = [
    "Human",
    "Elf",
    "Orc"
    ]
    
classValues = [
    "Fighter",
    "Mage",
    "Cleric"
    ]

alignmentValues = [
    "Good",
    "Neutral",
    "Evil"
    ]

for x in insertValues:
    
    
characterData = """INSERT INTO game.Characters(race, class, alignment) 
              VALUES '{raceValues}', '{classValues}', '{alignmentValues}' """


for command in characterData.splitlines():
    command = command.format(**data)  
    print(command)

So for the above, I'm trying to get 3 INSERT statements using the data from the 3 arrays I defined.

Is there a way to do this in Python 3?

Thanks!

Please fix the indentation. What is supposed to be inside the `for x in insertValues:` loop? — Barmar, Feb 07 '23 at 21:32
I think you want a loop like `for race, class, alignment in zip(raceValues, classValues, alignmentValues):` — Barmar, Feb 07 '23 at 21:34
Don't use string formatting to create SQL queries. See https://stackoverflow.com/questions/902408/how-to-use-variables-in-sql-statement-in-python — Barmar, Feb 07 '23 at 21:35
@Barmar This is good advice in general, but SQL injection is not a concern when you have complete control over the input strings. — 0x5453, Feb 07 '23 at 21:38
To clarify, how many rows do you expect to result from these inserts? 3? 27? Or some other number? — 0x5453, Feb 07 '23 at 21:39
@0x5453 It's not just SQL injection, it also protects against syntax errors in case the values contain special characters (so you don't need to escape the values). — Barmar, Feb 07 '23 at 21:42
Pls adapt answers to use parametrized sql. I.e. dont do query string substitutions. SQL injections is a recurring top 10 vulnerability and this code might cost you a job interview. . And… second Barmar: how do you format `None` using strings??? Dates? Doing it correctly, via binding, is actually easier. — JL Peyret, Feb 07 '23 at 21:58
@0x5453 for the above, I'd expect 3 INSERT statements to be generated. Thanks! — SkyeBoniwell, Feb 07 '23 at 22:49

pedwards · Accepted Answer · 2023-02-07T23:43:20.703

Trying to get you a quick answer, and I don't have time to test it, unfortunately. I'm assuming you want the values from your lists grouped in order. So, raceValues[0] goes with classValues[0], etc. If so, this would work:

raceValues = [
    "Human",
    "Elf",
    "Orc"
]

classValues = [
    "Fighter",
    "Mage",
    "Cleric"
]

alignmentValues = [
    "Good",
    "Neutral",
    "Evil"
]
for i in range(0,len(raceValues)):
    characterData = "INSERT INTO game.Characters(race, class, alignment)VALUES '%s', '%s', '%s'" \
                    % (raceValues[i], classValues[i], alignmentValues[i])
    print(characterData)

If that throws an error, here's the concept that I'm working with.

a = "something"
b = "what else"
c = "again"

output = "%s %s %s" % (a, b, c)

print(output)

I think that should get you headed in the right direction, assuming your """ format was correct. Sorry I couldn't test it... having a quick lunch at work.

Hi! Thanks for the code! It didn't throw an error, but it only printed out one INSERT statement when I expected 3 and I'm not sure why — SkyeBoniwell, Feb 07 '23 at 23:07
So I had a chance to edit the initial code and test it. It prints 3 INSERT statements for me. Perhaps your print(characterData) wasn't indented? Either way, give it a try now. — pedwards, Feb 07 '23 at 23:44

score 1 · Answer 2 · answered Feb 07 '23 at 22:01

1

If your desired output is this:

["INSERT INTO game.Characters(race, class, alignment) VALUES 'Human','Fighter''Good'", "INSERT INTO game.Characters(race, class, alignment) VALUES 'Elf','Mage''Neutral'", "INSERT INTO game.Characters(race, class, alignment) VALUES 'Orc','Cleric''Evil'"]

Below code should work for you

sql_cmd_list = []
for rv, cv, av in zip (raceValues, classValues, alignmentValues):
    command = f"""INSERT INTO game.Characters(race, class, alignment) VALUES '{rv}','{cv}''{av}'"""
    sql_cmd_list.append(command)
print(sql_cmd_list)

answered Feb 07 '23 at 22:01

Karan Raj

344
2
8

What does `zip` do? Thanks! – SkyeBoniwell Feb 07 '23 at 22:57
@SkyeBoniwell Python zip() function takes iterables (can be zero or more), aggregates them in a tuple, and returns it. – Karan Raj Feb 08 '23 at 20:47

How can I loop through my arrays and build a list of SQL INSERT commands?

2 Answers2