python-从文件读取json数据写到数据库

最新推荐文章于 2024-08-24 17:00:29 发布

siwluxuefeng

最新推荐文章于 2024-08-24 17:00:29 发布

阅读量3.7k

点赞数

分类专栏： python

本文链接：https://blog.csdn.net/Linzhongyilisha/article/details/72834764

版权

python 专栏收录该内容

9 篇文章 0 订阅

订阅专栏



#!/bin/env python
#coding=utf-8

import sys
import urllib2
import json
import datetime
import pymysql as MySQLdb
import time
from itertools import islice 

reload(sys)
sys.setdefaultencoding('utf-8')

mysql_conn = MySQLdb.connect(host="127.0.0.1",user="admin",passwd="123456",db="test",port=3306,charset="UTF8")
mysql_cursor = mysql_conn.cursor()

if __name__=='__main__':
	today = datetime.date.today().strftime('%Y%m%d') if len(sys.argv)<2 else sys.argv[1]
	yesterday = (datetime.datetime.strptime(today,'%Y%m%d')-datetime.timedelta(days=1)).strftime('%Y%m%d')
	#print 'processing date %s' % (yesterday)
	logFilePath = '/data1/trace_data/daystat.'+yesterday+'.dat'
	#print logFilePath
	logFile = open(logFilePath)
	i = 0
	print 'delete old data...'
	#mysql_cursor.execute("delete from test_xx where log_date=%(log_date)s",{'log_date':yesterday})
	print "finish deleting"
		
	#跳过前两行   
	for line in islice(logFile,2,None): 
		#读取数据,获取字段信息
		lineData = json.loads(line)
		#log_date = lineData.get('day')
		log_date = time.strftime("%Y%m%d", time.localtime(lineData.get('day')))
		#if log_date != yesterday:
		#continue
		#print lineData
		channel = lineData.get('channel')
		Id = lineData.get('Id')
		total_time = lineData.get('total_time')
		xx_time = lineData.get('xx_time')
		oo_time = lineData.get('oo_time')
		distance = lineData.get('distance')
		xx_distance = lineData.get('xx_distance')
		oo_distance = lineData.get('oo_distance')
		id = lineData.get('_id')
		sid_num =  lineData.get('sid_num')	
		for address in lineData.get('city','none'):
           # print  lindeData.get('city','none')
			if type(address) != str:
				province = address.get('province','null')
				city = address.get('city','null')
			else:
				province = "null"
				city = "null"
			print city

			mysql_cursor.execute("insert into test_xx(log_date,channel,city,Id,total_time,xx_time,oo_time,distance,xx_distance,oo_distance,id,sid_num,province) values(%(log_date)s,%(channel)s, %(city)s,%(Id)s,%(total_time)s,%(xx_time)s,%(oo_time)s,%(distance)s, %(xx_distance)s,%(oo_distance)s,%(id)s,%(sid_num)s,%(province)s);",{'log_date':log_date,'channel':channel,'city':city,'Id':Id,'total_time':total_time,'xx_time':xx_time,'oo_time':oo_time,'distance':distance,'xx_distance':xx_distance,'oo_distance':oo_distance,'id':id,'sid_num':sid_num,'province':province})
			print log_date,channel,city,Id,total_time,xx_time,oo_time,distance,xx_distance,oo_distance,id,sid_num,province
			i += 1
	print i
	mysql_conn.commit()
	mysql_conn.close()