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
– Barmar, Commented Feb 7, 2023 at 21:32
I think you want a loop like for race, class, alignment in zip(raceValues, classValues, alignmentValues): — Barmar
– Barmar, Commented Feb 7, 2023 at 21:34
Don't use string formatting to create SQL queries. See stackoverflow.com/questions/902408/… — Barmar
– Barmar, Commented Feb 7, 2023 at 21:35
@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
– Barmar, Commented Feb 7, 2023 at 21:42

pedwards · Accepted Answer · 2023-02-07 23:43:20Z

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

Karan Raj · Accepted Answer · 2023-02-07 22:01:06Z

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 7, 2023 at 22:01

Karan Raj

4445 silver badges9 bronze badges

2 Comments

SkyeBoniwell Over a year ago

What does zip do? Thanks!

Karan Raj Over a year ago

@SkyeBoniwell Python zip() function takes iterables (can be zero or more), aggregates them in a tuple, and returns it.

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Linked

Related