Python + MySQL DB dynamic insert query based on number of columns to insert

Question

I'm pretty novice at programming (recently learned functions), and have found myself re-writing the same "insert into mysql table" function (below) from script to script... mainly to just modify these two section - (name,insert_ts) &&& VALUES (%s, %s)

Is there a good way to re-write the below to accept ANY number of values , based on length of a tuple that contains values as well as inserting the column headers based on 'labels' list? VALUES (%s, %s) and this part (name,insert_ts)

    list_of_tuples = [] #list of records to be inserted.

    #take a list of dictionaries - and create a list of tuples in proper format/order
    for dict1 in output:
            one_list = []
            one_list.extend((dict1['name'],dict1['insert_ts']))
            list_of_tuples.append(tuple(one_list))
    labels = ['name', 'insert_ts']


    #db_write accepts table name as str, labels as str, and output as list of tuples
    def db_write(table,labels,output):

        local_cursor.executemany(""" INSERT INTO my_table 
        (name,insert_ts)  #this is pulled from 'labels'
          VALUES (%s, %s)   #number of %s comes from len(labels)
        """
        ,  list_of_tuples)
        local_db.commit()
        local_db.close()
        #print 'done posting!'

Or, is there a better way to accomplish what I'm trying to do, using mysqldb?

Thank you all in advance!

Possible duplicate of [How to use python mysqldb to insert many rows at once](https://stackoverflow.com/questions/14011160/how-to-use-python-mysqldb-to-insert-many-rows-at-once) — Tim Biegeleisen, May 09 '19 at 16:48
its not :) I'm trying to make the query more dynamic. But I did already implement executemany from the question you mentioned. — FlyingZebra1, May 09 '19 at 16:54

score 0 · Accepted Answer · answered Aug 30 '19 at 22:44

After a bit of experience (3 months, heh), wanted to update everyone on the solution that seems to work pretty well!

Instead of using mysqldb, I spent some time learning how to use SQL Alchemy python package, and would recommend everyone do the same!

SQL Alchemy allows you to:

1) Define a table within python code (used Excel to come up with column names, etc).

2) Most important! You can pass on a dictionary to SQL Alchemy, and as long as dictionary's key names match the table's key names, everything will magically get posted to your SQL table. If you have 60 columns in your sql table, but your dict has only two keys - BAM, SQL Alchemy will take care of everything and post just the two values, and leave the other values in MySQL as blanks. MAGIC!

Python + MySQL DB dynamic insert query based on number of columns to insert

1 Answers1