import pymysql
import pandas as pd


def get_city():
    sql = "select c.name,d.name as near_city_name from (select a.city_id,a.nearby_city_id,b.name " \
          "from api_transaction_nearby_city_to_es a left join api_city b on a.city_id = b.id) c " \
          "left join api_city d on c.nearby_city_id = d.id"

    db = pymysql.connect(host='172.16.30.141', port=3306, user='work', passwd='BJQaT9VzDcuPBqkd', db='zhengxing')
    cursor = db.cursor()
    cursor.execute(sql)
    result = cursor.fetchall()
    df = pd.DataFrame(list(result))
    name = ["name","near_city_name"]

    df = df.rename(columns=dict(zip(list(range(len(name))), name)))
    print(df.head(6))
    print(df.shape)

    data.to_csv('/tmp/city.csv',index=False)


if __name__ == "__main__":
    get_city()