102 lines
3.8 KiB
Python
102 lines
3.8 KiB
Python
import pymysql
|
||
import requests
|
||
import json
|
||
|
||
# MySQL数据库配置
|
||
DB_CONFIG = {
|
||
'host': 'localhost',
|
||
'user': 'testDB',
|
||
'password': 'WxMKtsNG8BRmWxDX',
|
||
'database': 'testdb',
|
||
'charset': 'utf8mb4',
|
||
'cursorclass': pymysql.cursors.DictCursor
|
||
}
|
||
|
||
# 高德地图API配置
|
||
AMAP_KEY = "ed2d6b26be983e04db8248adfe01ad24"
|
||
|
||
jumpnum = 0
|
||
|
||
def get_location(address):
|
||
"""通过高德地图API获取地址的经纬度"""
|
||
try:
|
||
url = f"https://restapi.amap.com/v3/geocode/geo?address={address}&key={AMAP_KEY}&city=南宁&output=json"
|
||
response = requests.get(url)
|
||
result = response.json()
|
||
|
||
if result['status'] == '1' and result['count'] != '0':
|
||
location = result['geocodes'][0]['location']
|
||
lng, lat = location.split(',')
|
||
return float(lng), float(lat)
|
||
else:
|
||
print(f"地址解析失败: {address}, 错误信息: {result['info']}")
|
||
return None, None
|
||
except Exception as e:
|
||
print(f"获取经纬度时发生错误: {str(e)}")
|
||
return None, None
|
||
|
||
def update_coordinates():
|
||
try:
|
||
print("正在连接数据库...")
|
||
conn = pymysql.connect(**DB_CONFIG)
|
||
cursor = conn.cursor()
|
||
print("数据库连接成功")
|
||
|
||
# 首先获取表结构信息
|
||
cursor.execute("DESCRIBE tingchequ")
|
||
columns = cursor.fetchall()
|
||
print("表结构：", [col['Field'] for col in columns])
|
||
|
||
# 从tingchequ表获取所有需要更新经纬度的记录
|
||
# 修改查询语句，使用正确的字段名
|
||
query = "SELECT * FROM tingchequ WHERE 经度 IS NULL OR 纬度 IS NULL"
|
||
cursor.execute(query)
|
||
records = cursor.fetchall()
|
||
|
||
# 修改获取地址的代码
|
||
recordindex = 0
|
||
for record in records:
|
||
address = record.get('站点地址') # 将 '地址' 改为 '站点地址'
|
||
if not address:
|
||
print(f"记录中没有地址信息：{record}")
|
||
continue
|
||
|
||
if recordindex < jumpnum:
|
||
print(f"跳过地址: {address}")
|
||
recordindex += 1
|
||
continue
|
||
|
||
print(f"正在处理地址: {address}")
|
||
lng, lat = get_location(address)
|
||
|
||
if lng is not None and lat is not None:
|
||
# 修改更新语句，使用正确的字段名
|
||
update_query = "UPDATE tingchequ SET 经度=%s, 纬度=%s WHERE 站点地址=%s" # 将 '地址' 改为 '站点地址'
|
||
cursor.execute(update_query, (lng, lat, address))
|
||
conn.commit()
|
||
print(f"更新成功: 地址={address}, 经度={lng}, 纬度={lat}")
|
||
else:
|
||
print(f"无法获取地址的经纬度: {address}")
|
||
|
||
print("所有记录处理完成")
|
||
|
||
except pymysql.Error as e:
|
||
error_msg = f"数据库错误: {str(e)}"
|
||
if "Can't connect to MySQL server" in str(e):
|
||
error_msg = "\n错误：MySQL服务未运行\n解决方案：\n1. 以管理员身份打开命令提示符\n2. 输入命令 'net start MySQL80' 启动服务"
|
||
elif e.args[0] == 2003:
|
||
error_msg += "\n解决方案：\n1. 检查MySQL服务是否启动\n2. 确认主机名是否正确"
|
||
elif e.args[0] == 1045:
|
||
error_msg += "\n解决方案：\n1. 验证用户名和密码是否正确"
|
||
elif e.args[0] == 1049:
|
||
error_msg += "\n解决方案：\n1. 确认数据库名称是否正确"
|
||
print(error_msg)
|
||
finally:
|
||
if 'cursor' in locals():
|
||
cursor.close()
|
||
if 'conn' in locals():
|
||
conn.close()
|
||
print("数据库连接已关闭")
|
||
|
||
if __name__ == "__main__":
|
||
update_coordinates() |