表一:
表二:
需求:
从表一中取出CHEATED_USER,为十一位手机号码,取出前七位,到表二中查询归属地省和市,插入表一。
代码:
#coding=utf-8
import MySQLdb
conn = MySQLdb.connect(
host='localhost',
port=3306,
user='root',
passwd='root',
db='ChinaUnicom',
charset='utf8',
)
cur = conn.cursor()
sql = "select SUBSTRING(CHEATED_USER,1,7) from t_main_fraud"
cur.execute(sql)
cheated_user = cur.fetchall()
i = 0
for ch in cheated_user:
i = i + 1
sql2 = "select province,city from b_tel_ownership where tel = " + str(ch[0])
cur.execute(sql2)
province_city =cur.fetchall()
if province_city:
sql3 = "UPDATE t_main_fraud SET CHEATED_PROVINCE = '" + province_city[0][0].encode('utf-8') +"'," + "CHEATED_CITY ='" + province_city[0][1].encode('utf-8') + "' where ID =" + str(i)
print sql3
cur.execute(sql3)
cur.close()
conn.commit()
conn.close()
主要问题:
cur取出的数据是long型,并不是具体数据,所以需要对数据进行处理,通过IDE来一步一步断点调试。