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?

Please format the code at the bottom for better understanding — Miguel Conde
– Miguel Conde, Commented Aug 13, 2022 at 1:00
Maybe first use print() (and print(type(...)), print(len(...)), etc.) to see which part of code is executed and what you really have in variables. It is called "print debuging" and it helps to see what code is really doing. — furas
– furas, Commented Aug 16, 2022 at 3:25
if it works for execute() then you have wrong data in values. It seems you have only one row of data (and it works with execute() which expect only one row of data) but executemany() expect list with many results. As for me you create values in wrong way. You should create values = [] before for-loop and you should use values.append(...) instead of values = ... — furas
– furas, Commented Aug 16, 2022 at 3:27

furas · Accepted Answer · 2022-08-16 03:55:05Z

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

Collectives™ on Stack Overflow

insert values into mysql table in python

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related