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.
- 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)
- 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))
- 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