insert values into mysql table in python

Question

To insert values into mysql table in python. Below is the code extracting mongodb collection data and inserting it into mysql table in python.

     def insert():   
                           
        client=MongoClient('mongodb://localhost:27017')  
                     
        db=client['mydb']                          #database                                               
        coll=db['data']                            #collection                                 
        mongo_docs = coll.find({},{'_id':0})       #mongo cursor removed '_id' in projection                            
        fieldnames = list(mongo_docs[0].keys())    #all the column names in the dataset
                     
        for record in mongo_docs:                                   
                values = list(record.values())     #all the values in the dataset                         
                #print(values) 
                                
        connection=mysql.connector.connect(host="localhost",user="root",database="mydb",password="passwd")
        cursor1=connection.cursor()                      
        connection.commit()                        #mysql connection
       
        count=0                         
        for i in fieldnames:                         
              count=count+1
                                
        qmark=[]                
        a=0                    
        while a<count:  
                qmark.append('%s')
                a=a+1
        
        q=','.join(tuple(qmark))
        
        query="INSERT INTO  ndata VALUES ('%s')"%(q)
        
        cursor1.executemany("INSERT INTO  ndata VALUES (%s)" %(q),(values))

This code throws an error:
ProgrammingError: Could not process parameters: int(82506), it must be of type list, tuple or dict

The values in the dataset are like this: [82506, '1945-12-31', 0, '', 29.44444444, 17.22222222, 23.33333333, 0, '', 45, 12, 31, 0, '', '', 85, 63, 74, 0, '', '', '', '', '', '', '', '', '', '', '', ''] which has empty strings inside it.

q in the code produces %s, generates %s which equal to number of columns in the dataset. Here 31 columns in the dataset so there are 31 of (%s,%s,%s.....) in q

The same code with when executed with

cursor.execute("INSERT INTO  ndata VALUES (%s)" %(q),(values))        

in place of cursor.executemany() runs without any errors but it does not insert any values into the table in mysql.

What changes should i make to insert multiple rows of values at once ?
or how could i insert it row by row?

Answer 1

I can't test it but I think you create values in wrong way.

If it works for execute() then values has only one row of data but executemany() expects list with many rows of data.

And this may suggest that you create values in wrong way.

You should create list values = [] before for-loop and you should use values.append(...) instead of values = ... to add new row to list (instead of keeping only one row in variable).

# --- before loop ---

values = []

# --- loop ---

for record in mongo_docs:                                   
    row = list(record.values())
    values.append(row)

# --- after loop ---

print(values)

---

BTW:

Shorter

count = len(fieldnames)

and

qmark = ['%s'] * count
q = ','.join(qmark)

---

def insert():   
                           
    client = MongoClient('mongodb://localhost:27017')  # PEP8: spaces around `=`
                 
    db = mongo_client['mydb']
    collection = db['data']
    mongo_docs = collection.find({},{'_id':0})
    fieldnames = list(mongo_docs[0].keys())
                 
    values = []
    
    for record in mongo_docs:
        row = list(record.values())
        values.append(row)
                            
    connection = mysql.connector.connect(host="localhost", user="root", database="mydb", password="passwd")  # PEP8: spaces after `,`
    cursor = connection.cursor()
   
    count = len(fieldnames)
                            
    qmark = ['%s'] * count
    q = ','.join(qmark)     # no need `tuple()`
    
    query = f"INSERT INTO ndata VALUES ({q})"   # modern `f-string` instead of very old `%`
    
    cursor.executemany(query, values)
        
    connection.commit()  # after `executemany('INSERT ...')`

---

PEP 8 -- Style Guide for Python Code