0

I am creating a script Python (using pymongo) to migrate my data from Mysql to MongoDB (5 million of rows), but I am having some problems. My idea is to retrieve the data from Mysql using SQL and create a JSON structure to insert in MongoDB, but the data types are giving a problem and I don't know how to solve it.

So, I created a simple example to show the problem.

  1. In Mysql, I created a table and insert one row. After that, I retrived this one using SQL into MySQL:
CREATE TABLE employees (
  id1 int(11) NOT NULL AUTO_INCREMENT,
  nome1 varchar(255) NOT NULL,
  nick1 varchar(50) DEFAULT NULL,
  age1 int(5) NOT NULL,
  date1 date DEFAULT NULL,
  time1 time DEFAULT NULL,
  datetime1 datetime DEFAULT NULL,
  PRIMARY KEY (id1)
) ENGINE=MyISAM CHARSET=latin1;
INSERT INTO employees (id1, nome1, nick1, age1, date1, time1, datetime1) 
VALUES (1, 'MARIA SILVA', null, 35, '2020-12-23', '12:30:22', '2020-07-02 03:17:40');
SELECT * FROM employees;
+-----+-------------+-------+------+------------+----------+---------------------+
| id1 | nome1       | nick1 | age1 | date1      | time1    | datetime1           |
+-----+-------------+-------+------+------------+----------+---------------------+
|   1 | MARIA SILVA | NULL  |   35 | 2020-12-23 | 12:30:22 | 2020-07-02 03:17:40 |
+-----+-------------+-------+------+------------+----------+---------------------+
1 row in set (0.000 sec)
  1. So far everything ok. Let's go to Python using pymongo:
### Python Script - Retrieving data from Mysql
from pymongo import MongoClient
from pprint import pprint
import mysql.connector
import datetime

mydb = mysql.connector.connect(
  host="localhost",
  user="user",
  password="password",
  database="test"
)
mycursor = mydb.cursor()
mycursor.execute("SELECT * FROM employees")
myresult = mycursor.fetchall()
for x in myresult:
    print(x)

The result is below. Completly diferent format if compared to Mysql return:

(1, 'MARIA SILVA', None, 35, datetime.date(2020, 12, 23), datetime.timedelta(seconds=45022), datetime.datetime(2020, 7, 2, 3, 17, 40))
  1. That is ok, but now I need to use this one to create a JSON item and insert into MongoDB
### Python Script - Retrieving data from Mysql and creating JSON item:
from pymongo import MongoClient
from pprint import pprint
import mysql.connector
import datetime

mydb = mysql.connector.connect(
   host="localhost",
   user="user",
   password="password",
   database="test"
)
mycursor = mydb.cursor()
mycursor.execute("SELECT * FROM test")
myresult = mycursor.fetchall()
for x in myresult:
   item = { 
      "_id" : int(x[0]), 
      "id1" : int(x[0]), 
      "name1" : x[1], 
      "nick1" : x[2], 
      "age1" : x[3], 
      "date1" : x[4], 
      "time1" : x[5], 
      "datetime1" : x[6] 
   }
pprint(item)

The result is JSON below:

{
   '_id': 1,
   'id1': 1, 
   'name1': 'MARIA SILVA',
   'nick1': None,
   'age1': 35,
   'date1': datetime.date(2020, 12, 23),
   'time1': datetime.timedelta(seconds=45022),
   'datetime1': datetime.datetime(2020, 7, 2, 3, 17, 40)
 }

That is the problem. When I try to insert this one into MongoDB, I get errors because MongoDB doesn't recognize these:

'nick1': None #(MongoDB uses null instead of None but I don't know how to fix it)
'date1': datetime.date(2020, 12, 23) #(I don't know how to do)
'time1': datetime.timedelta(seconds=45022), #(I don't know how to do)
'datetime1': datetime.datetime(2020, 7, 2, 3, 17, 40) #(I don't know how to do)

So, can someone help me in order to fix that bad JSON in a correct JSON to insert into MongoDB?

Finally, this is the code to insert the item into MongoDB:

### Python Script (continuing)- Inserting JSON item:
client = MongoClient('localhost', 27017)
db = client.test
col = db.employees
item_id = col.insert_one(item).inserted_id

1 Answer 1

1

Using Studio 3T tool for MongoDB (limited to 1000 lines, I got this acceptable JSON for MongoDB:

{ 
    "_id" : NumberInt(1), 
    "id1" : NumberInt(1), 
    "name1" : "MARIA SILVA", 
    "nick1" : null, 
    "age1" : NumberInt(35),
    "date1" : ISODate("2020-12-23T03:00:00.000+0000"), 
    "time1" : "12:30:22", 
    "datetime1" : ISODate("2020-07-02T03:17:40.000+0000")
}

But how can I fix that bad JSON to look like this one?

Sign up to request clarification or add additional context in comments.

1 Comment

hey @Adriano, glad to learn your python script for migration, can we connect i had queries if there is more than 3 tables, then how we can we migrate effeciently

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.