MySQL语句包含嵌套查询
from pygeohash import encode, decode
import plotly
import plotly.plotly as pyf
import plotly.graph_objs as go
import numpy as np
import pandas as pd
import math
from matplotlib.path import Path
import numpy as np
import plotly.offline as of
import plotly.graph_objs as go
import plotly.plotly as py
import numpy as np
import pandas as pd
import folium
import webbrowser
from folium.plugins import HeatMap
import datetime
import time
import pymysql.cursors
import decimal
import geohash
def geo_demand(geo_top_number):
conn = pymysql.connect(
host='localhost',
port=3306,
user='root',
passwd='xu19931026',
db='cd_taxi',
charset='utf8'
)
if conn:
print("连接成功!")
cursor = conn.cursor()
sql = "SELECT Geohash,number,CAST(number/(SELECT count( 0 ) FROM Order_Data where FROM_UNIXTIME(BeginTime) <'2016-11-01 24:00:00') as CHAR(15))as demand FROM (select left(`order_data`.`Geo`,%s) AS `Geohash`,count(0) AS `number` from `order_data` where (from_unixtime(`order_data`.`BeginTime`)< '2016-11-01 24:00:00') group by `Geohash` order by `number` desc) AS T;"
cursor.execute(sql,geo_top_number)
conn.commit()
result = cursor.fetchall()
df = list(result)
conn.close()
print('数据库处理数据完毕')
geo_dict = {}
geo_list = []
order_number = []
demand = []
for point in df:
geo_list.append((point[0]))
order_number.append(point[1])
demand.append(float(point[2]))
num = len(geo_list)
geo_data = [[geo_list[i], demand[i]] for i in range(num)]
geo_nb = [[geo_list[i], order_number[i]] for i in range(num)]
return geo_data,geo_nb
if __name__ == '__main__':
print(" 请按照格式输入得到Geohash的前几位:例:6 ")
geo_top_suzi = input("请输入:")
geo_data, geo_nb=geo_demand(geo_top_suzi)