# 链接到 mysql ,创建表 mobike
conn = pymysql.connect(host='localhost', user='root', passwd=' ', port=3306, db='test1',charset="utf8")
cur = conn.cursor() # 创建一个游标对象
cur.execute("DROP TABLE IF EXISTS mobike") # 如果表存在则删除
cur.execute('''CREATE TABLE mobike
(Time datetime NOT NULL DEFAULT NOW(),
bikeIds VARCHAR(100),
biketype VARCHAR(100),
distId VARCHAR(100),
distNum VARCHAR(100),
distance VARCHAR(500) ,
type VARCHAR(100),
distX VARCHAR(100),
distY VARCHAR(100))''')
#插入数据,不用再设置 Time项了,会自动更新
try:
print(response.text) # 输出
decoded = ujson.decode(response.text)['object']
self.done += 1
for x in decoded:
sql = "INSERT INTO mobike (bikeIds, biketype, distId, distNum, distance, type, distX, distY) VALUES (%s,%s,%s,%s,%s,%s,%s,%s)"
cur.execute(sql, (x['bikeIds'], x['biketype'], x['distId'],
x['distNum'], x['distance'], x['type'], x['distX'], x['distY']))
# latitude 纬度 横的 distY
except Exception as ex:
print(ex)
conn.commit()
cur.close()
conn.close() # 关闭数据
break
按上面这样写成功了,输出的形式是:2017-08-01 09:47:56
有人说用current_timestamp,NOW(),我没用成功,留在这里,以后用到了再补充。