前言：需求是将100MB的GeoJson数据导入到Mysql中，这块使用Python

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
import geopandas as gpd
import mysql.connector
from mysql.connector import Error
import json
import pandas as pd
from shapely.geometry import mapping
from tqdm import tqdm

# 读取GeoJSON文件
gdf = gpd.read_file('C:/Users/Lenovo/Desktop/demo/总览.geojson')

# 将NaN值替换为None
gdf = gdf.where(pd.notnull(gdf), None)

# 连接到MySQL数据库
try:
    conn = mysql.connector.connect(
        host='数据库IP',
        port=数据库端口,
        database='数据库名',
        user='数据库用户名',
        password='数据库密码'
    )
    if conn.is_connected():
        cursor = conn.cursor()

        # 创建MySQL表
        create_table_query = """
             CREATE TABLE IF NOT EXISTS your_map (
                 ID INT AUTO_INCREMENT PRIMARY KEY,
                 TOWNSHIP VARCHAR(255),
                 VILLAGE VARCHAR(255),
                 NAME VARCHAR(255),
                 PHONE VARCHAR(255),
                 ID_NUMBER VARCHAR(255),
                 TEA_VARIETIES VARCHAR(255),
                 AREA VARCHAR(255),
                 MU VARCHAR(255),
                 CENTRAL_ELEVATION VARCHAR(255),
                 LAYER VARCHAR(255),
                 COORDINATES TEXT,
                 REMARK VARCHAR(255)
             );
             """
        cursor.execute(create_table_query)

        # 准备批量插入数据的SQL语句
        insert_query = """
        INSERT INTO your_map (TOWNSHIP, VILLAGE, NAME, PHONE, ID_NUMBER, TEA_VARIETIES, AREA, MU, CENTRAL_ELEVATION,LAYER, COORDINATES ,REMARK )
        VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
        """

        # 批量插入数据
        insert_data = []
        total_rows = len(gdf)
        for index, row in tqdm(gdf.iterrows(), total=total_rows, desc="处理进度"):
            geometry_geojson = mapping(row['geometry'])
            coordinates = json.dumps(geometry_geojson['coordinates'])
            insert_data.append((
                row['乡镇'], row['村名'], row['姓名'], row['电话号码'], row['身份证号'],
                row['茶种'], row['面积'] if pd.notnull(row['面积']) else None, row['亩'],
                row['中心海拔'], row['layer'], coordinates, row['备注']
            ))

        cursor.executemany(insert_query, insert_data)
        conn.commit()
        print("数据插入成功")

except Error as e:
    print("连接到MySQL时出错", e)
finally:
    if conn.is_connected():
        cursor.close()
        conn.close()
        print("MySQL连接已关闭")