How can I create SQL table keys with items from looping through a list in Python

user_12815294 0
Viewed 442

I have a list called headers of about 40-50 items, and I'm trying to make them all keys in a table. I'm trying to avoid typing them all out, here's what I have so far:

def createtable_db():
    cnx = mysql.connector.connect(user='x', password='x', host='x', database='data')

    mycursor = cnx.cursor()

    create_table = "CREATE TABLE IF NOT EXISTS players (id INT AUTO_INCREMENT PRIMARY KEY)"

    for h in headers:
        pop_table = "ALTER TABLE players ADD COLUMN "+ h +" VARCHAR(255)"

    mycursor.execute(create_table)
    mycursor.execute(pop_table)

    cnx.close()

createtable_db()

This only adds the very last key, I tried adding a + to make this line:

 pop_table += "ALTER TABLE players ADD COLUMN "+ h +" VARCHAR(255)"

but that's also not working: it gives me a syntax error.

alter list sql python loops
3 Answers

The following demonstrative code is for generating a pop_table string as a sequence of ALTER TABLE SQL commands, one for each column to be added. You can

  1. create a list for the single columns commands, each of them in the form ADD columnName column format
  2. populate it with a column data section for each element of headers
  3. join them to build the complete sequence of queries. ADD sections have to be separated by commas ,
  4. Append the joined list to initial ALTER TABLE. The query is terminated by a semicolon ;
headers = [ "Stack22", "overflow22", "rules22"]

header_columns_commands =[]

for h in headers:
    header_columns_commands .append("ADD "+ h +" VARCHAR(255)")

pop_table = "ALTER TABLE players " + ",".join(header_columns_commands ) + ";"

print(pop_table)

Output:

ALTER TABLE players
    ADD Stack VARCHAR(255),
    ADD overflow VARCHAR(255),
    ADD rules VARCHAR(255);

Tested on MySQL.

user_113367620

Try initializing pop_table to an empty string and then concatenating to it, inside of the for loop.

pop_table = ''

for h in headers:
    pop_table += "ALTER TABLE players ADD COLUMN "+ h +" VARCHAR(255)"

You could also initialize it to an empty list and append the generated strings

pop_table = []

for h in headers:
    pop_table.append("ALTER TABLE players ADD COLUMN "+ h +" VARCHAR(255)")

# join them with you choice of delimiter
joined_up = ','.join(pop_table)
user_31253120 
pop_table = "ALTER TABLE players "
for h in headers:
    pop_table += "ADD COLUMN "+ h + " VARCHAR(255), "

#Replace the last , with a ;
pop_table.replace(pop_table[len(pop_table)-1], ';')
mycursor.execute(create_table)
mycursor.execute(pop_table, multi=True)

Maybe if you did it like this? Sorry its been a while since I used mysql.connector. If I remember correctly, multi must be set to True if you intend to execute more than one command.

user_135052550
Related