因为业务需要,要讲数据从mysqldb迁移到mongodb中去。结合peewee和pymongo写了一个小脚本,使用的也都是sql的原生语句读取数据。 碰到几个小坑,但是还比较容易解决。如果碰上数据量非常大,可以考虑使用peewee中的paginate方法,具体脚本记录如下:
1 from peewee import *
2
from datetime
import datetime
3 from pymongo import *
4 from decimal import Decimal
5
6
7 db = MySQLDatabase( ' test ',
8 host = ' 0.0.0.0 ',
9 user = ' root ',
10 passwd = ' itcast ',
11 charset = ' utf8 '
12 )
13
14 db.connect()
15 cur = db.execute_sql( ' show tables ')
16 tables = map( lambda x: x[0], cur.fetchall())
17
18
19 mdb = MongoClient( ' localhost ', 27017)[ 'test ']
20 count = 0
21 for table in tables:
22 print ' * '*30
23 print table
24 cur = db.execute_sql( ' desc '+ table)
25 fields = map( lambda x: x[0], cur.fetchall())
26 cur = db.execute_sql( ' select * from '+table)
27 values = cur.fetchall()
28 data = []
29
30 for value in values:
31 # mongo不支持Decimal格式,先转为float
32 value = map( lambda x: float(x) if isinstance(x, Decimal) else x, value)
33 data.append(dict(zip(fields, value)))
34
35 # 空数据插入mongo会报错,所以先做个判断
36 if len(data)>0:
37 mtb = mdb[table]
38 mtb.insert_many(data)
39
40 count += 1
41 print ' * '*30
42 print ' totally %d tables transferred ' % count
3 from pymongo import *
4 from decimal import Decimal
5
6
7 db = MySQLDatabase( ' test ',
8 host = ' 0.0.0.0 ',
9 user = ' root ',
10 passwd = ' itcast ',
11 charset = ' utf8 '
12 )
13
14 db.connect()
15 cur = db.execute_sql( ' show tables ')
16 tables = map( lambda x: x[0], cur.fetchall())
17
18
19 mdb = MongoClient( ' localhost ', 27017)[ 'test ']
20 count = 0
21 for table in tables:
22 print ' * '*30
23 print table
24 cur = db.execute_sql( ' desc '+ table)
25 fields = map( lambda x: x[0], cur.fetchall())
26 cur = db.execute_sql( ' select * from '+table)
27 values = cur.fetchall()
28 data = []
29
30 for value in values:
31 # mongo不支持Decimal格式,先转为float
32 value = map( lambda x: float(x) if isinstance(x, Decimal) else x, value)
33 data.append(dict(zip(fields, value)))
34
35 # 空数据插入mongo会报错,所以先做个判断
36 if len(data)>0:
37 mtb = mdb[table]
38 mtb.insert_many(data)
39
40 count += 1
41 print ' * '*30
42 print ' totally %d tables transferred ' % count