python脚本 mongodb到postgresql
安装 mongo模块
pip install pymongo
安装postgresql 驱动
pip install python-psycopg2
1 # -*- coding: utf-8 -*- 2
3 from pymongo import MongoClient 4 import datetime 5 import psycopg2 6 7 def get_db(): 8 print('connect mongo...') 9 client=MongoClient('localhost',27017) 10 db=client.webdb 11 db.authenticate('jackical','****') 12 return db 13 14 def get_collection(): 15 print('start read table..') 16 db=get_db() 17 coll=db['table'] 18 print(db.collection_names()) 19 return coll.find({'pid':12,'did':45}) 20 21 def get_pg(): 22 print('read pg db') 23 conn=psycopg2.connect(database='jackical',user='postgres',password='******',host='localhost',port='5432') 24 cur=conn.cursor() 25 colles=get_collection() 26 pernumb=0 27 for i in colles:
28 searchKey=i['searchKey'] if 'searchKey' in i else '' 29 websiteType=i['websiteType'] if 'websiteType' in i else '' 30 31 searchKey=searchKey.replace('\'','*_*') 32 websiteType=websiteType.replace('\'','*_*') 33 34 persql='insert into public.myapp_res2(searchkey,websitetype) values(\'%s\',\'%s\')' % (searchKey,websiteType) 35 print(persql) 36 cur.execute(persql) 37 38 pernumb=pernumb+1 39 40 if pernumb>1000: 41 conn.commit() 42 pernumb=0 43 44 if pernumb>0: 45 conn.commit() 46 cur.close() 47 conn.close() 48 print('insert end') 49 50 51 if __name__=='__main__': 52 get_pg() 53 54 55 56 57 58 59 60
代码中
i['searchKey'] if 'searchKey' in i else ''
为mongo中有没有 "searchKey" 这列,有的话就显示,没有的话就为 ''
为保证入库正常,所以直接将 ‘ 单引号替换成 *_* ,取数据时,再换回来。