python处理数据脚本

1、python连接mysql

 

 #encoding=gbk

 

 import sys

 

 import MySQLdb

 
 
 

 conn=MySQLdb.Connection('127.0.0.1','root','123456','job',charset='gbk')

 

 cur=conn.cursor()

 

 cur.execute("select * from demo_jobs_store")

 

 conn.commit()

 

 conn.close()

 
 

 cur.scroll(0)

 

 row1=cur.fetchone()

 

 print row1[3]

 

 
 

 2、python清洗数据

 

 #encoding=gbk

 

 import sys

 

 import MySQLdb

 

 import datetime

 
 

 #计算时间

 

 starttime = datetime.datetime.now()

 
 

 conn=MySQLdb.Connection('127.0.0.1','root','123456','job',charset='gbk')

 

 cur=conn.cursor()

 

 #去除空数据 & 面议数据 & 工作地点为空数据

 

 cur.execute("delete from demo_jobs_store where etjobsalary='0'\

 

             or etjobsalary='' or LENGTH(etjobsalary)>3 \

 

             or etjoblocation_str=''")

 

 conn.commit()

 

 #去除非安徽地区数据

 

 cur.execute("delete from demo_jobs_store where etjoblocation_str not like '%安徽%'")

 

 conn.commit()

 

 conn.close()

 
 

 #时间结束

 

 endtime = datetime.datetime.now()

 

 #输出时间

 

 print (endtime - starttime).seconds

 

 
 

 3、清洗数据完整版

 

 #encoding=gbk

 

 import sys

 

 import MySQLdb

 

 import datetime

 

 import time

 
 

 #计算时间

 

 starttime = datetime.datetime.now()

 
 

 conn=MySQLdb.Connection('127.0.0.1','root','123456','job',charset='gbk')

 

 cur=conn.cursor()

 

 #去除空数据 & 面议数据 & 工作地点为空数据

 

 cur.execute("delete from demo_jobs_store where etjobsalary='0'\

 

             or etjobsalary='' or LENGTH(etjobsalary)>3 \

 

             or etjoblocation_str=''")

 

 conn.commit()

 

 #去除非安徽地区数据

 

 cur.execute("delete from demo_jobs_store where etjoblocation_str not like '%安徽%'")

 

 conn.commit()

 
 

 #去除工作经验异常的数据

 

 cur.execute("delete from demo_jobs_store where etjobworkexperience>10")

 

 conn.commit()

 
 

 #过滤掉三个月之前的数据

 

 time=int(time.time())

 

 time-=7776000

 

 cur.execute("delete from demo_jobs_store where etupdatetime<%s",time)

 

 conn.commit()

 
 
 

 conn.close()

 
 

 #时间结束

 

 endtime = datetime.datetime.now()

 

 #输出时间

 

 print (endtime - starttime).seconds