Python MySQL inserts only 10 records out of 23,751 records

Question

I am trying to insert submissions from ODK central API into MySQL table. Only 10 records out of 23,751 are inserted. I've also tried inserting from a JSON file I created with the downloaded data. See the code below:

import json
import mysql.connector
from datetime import datetime

db = mysql.connector.connect(
  host="localhost",
  user="root",
  password="",
  database="mapped_customers"
)

f = open("submissions.json", "r")

data=f.read()

data= json.loads(data)
count=0
try:
   for customer in data['value']:

      count+=1
      print(count)

      # Input timestamp in ISO 8601 format
      timestamp = customer['__system']['submissionDate']

      # Convert to datetime object
      dt_object = datetime.strptime(timestamp, '%Y-%m-%dT%H:%M:%S.%fZ')

      # Format datetime object as MySQL datetime string
      mysql_datetime = dt_object.strftime("%Y-%m-%d %H:%M:%S")

      cust_name=customer['shop_name']
      cust_contact=customer['daily_contact_number']
      contact_person=customer['shop_owner']
      cust_category=customer['customer_category']
      latitude=customer['store_gps']['coordinates'][1]
      longitude=customer['store_gps']['coordinates'][0]
      cust_img=customer['photo']
      location=customer['zone_name']
      landmark=customer['landmark']
      mapper=customer['mapper']
      submission_date=mysql_datetime
      importance=customer['importance']
      sales=customer['sales']
      purchases=customer['purchases']
      alt_contact=customer['alternative_phone_number']

      cursor = db.cursor()
      sql="INSERT INTO gsmrt_cstmr_vrfctn(CSTMR_NME,CST_CNTCT,CNTCT_PRSN,CSTMR_CLSS,CSTMR_LTTD,CSTMR_LNGTD,CSTMR_IMG,CSTMR_LCTN,CSTMR_LND_MRK,MAPPER,SUBMSN_DTE,IMPORTNCE,SLS,PURCHSES,ALT_CNTCT) VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
      val=(cust_name,cust_contact,contact_person,cust_category,latitude,longitude,cust_img,location,landmark,mapper,submission_date,importance,sales,purchases,alt_contact)
      cursor.execute(sql,val)
      db.commit()
   db.close()
except (mysql.connector.Error, mysql.connector.Warning) as e:
      print(e)
     
print("Done!")

What could I be doing wrong?

NB: The loop is able to successfully traverse through all the records and no timeout error is thrown.

As a side note : You might use `executemany` instead as being more performant. — Barbaros Özhan, Jan 27 '23 at 08:02
did you try to run script without try / except block? [JFYI](https://stackoverflow.com/questions/16476413/how-to-insert-pandas-dataframe-via-mysqldb-into-database): you can do it easily using `pandas`/ — Danila Ganchar, Jan 27 '23 at 08:05
@DanilaGanchar Yes. I only added error handling after several attempts to check whether MySQL could be throwing timeout errors. — Kennedy Mwenda, Jan 27 '23 at 08:18
@BarbarosÖzhan That will require creating tuples for each of the record. — Kennedy Mwenda, Jan 27 '23 at 08:26
@DanilaGanchar I tried it but without creating tuples for each record. Maybe I try to create tuples and check whether it'll work. — Kennedy Mwenda, Jan 27 '23 at 08:31
@DanilaGanchar Another challenge is how to create over 23k tuples and append them to cursor. — Kennedy Mwenda, Jan 27 '23 at 08:38
@BarbarosÖzhan I've modified the code like this to use ```executemany```. ```customers=(cust_name,cust_contact,contact_person,cust_category,latitude,longitude,cust_img,location,landmark,mapper,submission_date,importance,sales,purchases,alt_contact) val.append(customers)``` but now mysql is losing connection trying to insert all those tuples. If I break at 50 for example only 10 rows are inserted still. — Kennedy Mwenda, Jan 27 '23 at 08:50
@KennedyMwenda could you add example how to reproduce behavior? where is `mysql`? inside `docker` container? — Danila Ganchar, Jan 27 '23 at 09:05
@DanilaGanchar MySQL is just installed on my Windows PC. Nothing is containerized. Just executing the script on cmd. — Kennedy Mwenda, Jan 27 '23 at 09:11

score 0 · Answer 1 · answered Jan 28 '23 at 11:25

0

Actually the code is working perfectly. It was WorkBench's SQL execution Limit Row Count that was set at 10.

answered Jan 28 '23 at 11:25

Kennedy Mwenda

23
1
6

could you explain details please? I don't understand. As I know `Workbench` is a visual tool. – Danila Ganchar Jan 29 '23 at 00:03
@DanilaGanchar On ***Preferences*** menu under ***Edit** menu there are settings to configure how data is displayed on various windows. By default SQL execution is set to return 10 rows. Changing to 0 will return all the rows in a table. I think the settings are Workbench specific. Viewing records with PHPMyadmin will return all records with pagination. – Kennedy Mwenda Jan 29 '23 at 12:52
It never occurred to me to check on PHPMyadmin since I thought the code was failing at some point. I noted when I wanted to create dummy data to reproduce the problem. Setting phone number columns to dummy with UPDATE I noticed 23,751 rows affected.That's when I noticed there was something wrong with retrieved data on Workbench. FWIW I even ported the code to PHP thinking it had something to with Python MySQL connector. The results were the same as described on the question. – Kennedy Mwenda Jan 29 '23 at 12:53

Python MySQL inserts only 10 records out of 23,751 records

1 Answers1