{"id":763,"date":"2025-11-20T22:06:01","date_gmt":"2025-11-20T14:06:01","guid":{"rendered":"https:\/\/smartaiunion.com\/?p=763"},"modified":"2025-11-20T22:06:01","modified_gmt":"2025-11-20T14:06:01","slug":"%e9%9d%a2%e5%90%91%e5%bf%99%e7%a2%8c%e6%95%b0%e6%8d%ae%e5%b7%a5%e7%a8%8b%e5%b8%88%e7%9a%84-5-%e4%b8%aa%e5%ae%9e%e7%94%a8-python-%e8%84%9a%e6%9c%ac","status":"publish","type":"post","link":"https:\/\/smartaiunion.com\/index.php\/2025\/11\/20\/763\/","title":{"rendered":"\u9762\u5411\u5fd9\u788c\u6570\u636e\u5de5\u7a0b\u5e08\u7684 5 \u4e2a\u5b9e\u7528 Python \u811a\u672c"},"content":{"rendered":"\n

\u6570\u636e\u5de5\u7a0b\u5e08\u5e38\u9700\u5904\u7406\u91cd\u590d\u7684\u6570\u636e\u7ba1\u9053\u642d\u5efa\u3001\u683c\u5f0f\u8f6c\u6362\u7b49\u5de5\u4f5c\uff0c\u4ee5\u4e0b 5 \u4e2a Python \u811a\u672c\u53ef\u76f4\u63a5\u590d\u7528\uff0c\u5e2e\u4f60\u8282\u7701\u5927\u91cf\u65f6\u95f4\u3002<\/p>\n\n\n\n

1. \u6570\u636e\u683c\u5f0f\u6279\u91cf\u8f6c\u6362\u811a\u672c\uff08CSV \u2194 JSON \u2194 Parquet\uff09<\/h2>\n\n\n\n

\u8be5\u811a\u672c\u652f\u6301\u5c06\u591a\u4e2a\u6587\u4ef6\u5728 CSV\u3001JSON\u3001Parquet \u683c\u5f0f\u95f4\u6279\u91cf\u8f6c\u6362\uff0c\u9002\u914d\u4e0d\u540c\u6570\u636e\u5206\u6790\u5de5\u5177\u7684\u683c\u5f0f\u9700\u6c42\u3002<\/p>\n\n\n\n

python<\/p>\n\n\n\n

\u8fd0\u884c<\/p>\n\n\n\n

import pandas as pd\nimport os\nfrom pathlib import Path\n\ndef batch_convert_files(input_dir, output_dir, input_format, output_format):\n    # \u521b\u5efa\u8f93\u51fa\u76ee\u5f55\uff08\u82e5\u4e0d\u5b58\u5728\uff09\n    Path(output_dir).mkdir(parents=True, exist_ok=True)\n    \n    # \u904d\u5386\u8f93\u5165\u76ee\u5f55\u4e0b\u6240\u6709\u5bf9\u5e94\u683c\u5f0f\u6587\u4ef6\n    for filename in os.listdir(input_dir):\n        if filename.endswith(f\".{input_format.lower()}\"):\n            file_path = os.path.join(input_dir, filename)\n            output_filename = Path(filename).stem + f\".{output_format.lower()}\"\n            output_path = os.path.join(output_dir, output_filename)\n            \n            # \u8bfb\u53d6\u8f93\u5165\u6587\u4ef6\n            try:\n                if input_format.lower() == \"csv\":\n                    df = pd.read_csv(file_path)\n                elif input_format.lower() == \"json\":\n                    df = pd.read_json(file_path)\n                elif input_format.lower() == \"parquet\":\n                    df = pd.read_parquet(file_path)\n                else:\n                    print(f\"\u4e0d\u652f\u6301\u7684\u8f93\u5165\u683c\u5f0f\uff1a{input_format}\")\n                    continue\n                \n                # \u5199\u5165\u8f93\u51fa\u6587\u4ef6\n                if output_format.lower() == \"csv\":\n                    df.to_csv(output_path, index=False)\n                elif output_format.lower() == \"json\":\n                    df.to_json(output_path, orient=\"records\")\n                elif output_format.lower() == \"parquet\":\n                    df.to_parquet(output_path, index=False)\n                else:\n                    print(f\"\u4e0d\u652f\u6301\u7684\u8f93\u51fa\u683c\u5f0f\uff1a{output_format}\")\n                    continue\n                \n                print(f\"\u6210\u529f\u8f6c\u6362\uff1a{filename} \u2192 {output_filename}\")\n            except Exception as e:\n                print(f\"\u8f6c\u6362\u5931\u8d25 {filename}\uff1a{str(e)}\")\n\n# \u4f7f\u7528\u793a\u4f8b\uff1a\u5c06 input \u6587\u4ef6\u5939\u7684 CSV \u8f6c\u6362\u4e3a Parquet \u5e76\u5b58\u5165 output \u6587\u4ef6\u5939\nbatch_convert_files(\n    input_dir=\"input_files\",\n    output_dir=\"output_files\",\n    input_format=\"CSV\",\n    output_format=\"Parquet\"\n)\n<\/code><\/pre>\n\n\n\n
\n\n\n\n
2. \u6570\u636e\u8d28\u91cf\u6821\u9a8c\u811a\u672c\uff08\u7f3a\u5931\u503c\u3001\u5f02\u5e38\u503c\u3001\u91cd\u590d\u503c\u68c0\u6d4b\uff09<\/h2>\n\n\n\n
\u5feb\u901f\u6821\u9a8c\u6570\u636e\u96c6\u5b8c\u6574\u6027\uff0c\u751f\u6210\u8be6\u7ec6\u8d28\u91cf\u62a5\u544a\uff0c\u907f\u514d\u810f\u6570\u636e\u6d41\u5165\u4e0b\u6e38\u6d41\u7a0b\u3002<\/p>\n\n\n\n
python<\/p>\n\n\n\n
\u8fd0\u884c<\/p>\n\n\n\n
import pandas as pd\nimport numpy as np\n\ndef data_quality_check(df, output_report_path=\"data_quality_report.txt\"):\n    report = []\n    report.append(\"=\"*50)\n    report.append(\"\u6570\u636e\u8d28\u91cf\u6821\u9a8c\u62a5\u544a\")\n    report.append(\"=\"*50)\n    \n    # 1. \u57fa\u672c\u4fe1\u606f\n    report.append(f\"\\n1. \u6570\u636e\u96c6\u57fa\u672c\u4fe1\u606f\uff1a\")\n    report.append(f\"   - \u603b\u884c\u6570\uff1a{df.shape[0]}\")\n    report.append(f\"   - \u603b\u5217\u6570\uff1a{df.shape[1]}\")\n    report.append(f\"   - \u6570\u636e\u7c7b\u578b\u5206\u5e03\uff1a\\n{df.dtypes.value_counts().to_string()}\")\n    \n    # 2. \u7f3a\u5931\u503c\u68c0\u6d4b\n    missing_stats = df.isnull().sum()\n    missing_percent = (missing_stats \/ len(df)) * 100\n    report.append(f\"\\n2. \u7f3a\u5931\u503c\u7edf\u8ba1\uff1a\")\n    for col, missing in missing_stats.items():\n        if missing > 0:\n            report.append(f\"   - {col}\uff1a{missing} \u4e2a\u7f3a\u5931\u503c\uff08{missing_percent[col]:.2f}%\uff09\")\n    \n    # 3. \u91cd\u590d\u503c\u68c0\u6d4b\n    duplicate_count = df.duplicated().sum()\n    report.append(f\"\\n3. \u91cd\u590d\u503c\u7edf\u8ba1\uff1a\")\n    report.append(f\"   - \u5b8c\u5168\u91cd\u590d\u884c\uff1a{duplicate_count} \u884c\uff08{duplicate_count\/len(df)*100:.2f}%\uff09\")\n    \n    # 4. \u6570\u503c\u578b\u5b57\u6bb5\u5f02\u5e38\u503c\u68c0\u6d4b\uff08IQR \u65b9\u6cd5\uff09\n    report.append(f\"\\n4. \u6570\u503c\u578b\u5b57\u6bb5\u5f02\u5e38\u503c\u7edf\u8ba1\uff08IQR \u6cd5\u5219\uff09\uff1a\")\n    numeric_cols = df.select_dtypes(include=[np.number]).columns\n    for col in numeric_cols:\n        q1 = df[col].quantile(0.25)\n        q3 = df[col].quantile(0.75)\n        iqr = q3 - q1\n        lower_bound = q1 - 1.5 * iqr\n        upper_bound = q3 + 1.5 * iqr\n        outliers = df[(df[col] < lower_bound) | (df[col] > upper_bound)].shape[0]\n        if outliers > 0:\n            report.append(f\"   - {col}\uff1a{outliers} \u4e2a\u5f02\u5e38\u503c\uff08\u8303\u56f4\uff1a[{lower_bound:.2f}, {upper_bound:.2f}]\uff09\")\n    \n    # \u4fdd\u5b58\u62a5\u544a\n    with open(output_report_path, \"w\", encoding=\"utf-8\") as f:\n        f.write(\"\\n\".join(report))\n    \n    print(f\"\u8d28\u91cf\u62a5\u544a\u5df2\u4fdd\u5b58\u81f3\uff1a{output_report_path}\")\n    return \"\\n\".join(report)\n\n# \u4f7f\u7528\u793a\u4f8b\ndf = pd.read_csv(\"your_dataset.csv\")\ndata_quality_check(df)\n<\/code><\/pre>\n\n\n\n
\n\n\n\n
3. \u6570\u636e\u5e93\u6279\u91cf\u6570\u636e\u8fc1\u79fb\u811a\u672c\uff08MySQL \u2192 PostgreSQL\uff09<\/h2>\n\n\n\n
\u5b9e\u73b0\u4e0d\u540c\u6570\u636e\u5e93\u95f4\u7684\u6570\u636e\u6279\u91cf\u8fc1\u79fb\uff0c\u81ea\u52a8\u5904\u7406\u5b57\u6bb5\u7c7b\u578b\u6620\u5c04\uff0c\u652f\u6301\u589e\u91cf\u540c\u6b65\u3002<\/p>\n\n\n\n
python<\/p>\n\n\n\n
\u8fd0\u884c<\/p>\n\n\n\n
import pandas as pd\nfrom sqlalchemy import create_engine\nfrom datetime import datetime\n\ndef migrate_data_mysql_to_postgres(\n    mysql_conn_str, pg_conn_str, \n    mysql_table, pg_table, \n    incremental_col=None, last_sync_time=None\n):\n    # \u521b\u5efa\u6570\u636e\u5e93\u8fde\u63a5\n    mysql_engine = create_engine(mysql_conn_str)\n    pg_engine = create_engine(pg_conn_str)\n    \n    try:\n        # \u6784\u5efa\u67e5\u8be2\u8bed\u53e5\uff08\u652f\u6301\u589e\u91cf\u540c\u6b65\uff09\n        query = f\"SELECT * FROM {mysql_table}\"\n        if incremental_col and last_sync_time:\n            query += f\" WHERE {incremental_col} > '{last_sync_time}'\"\n            print(f\"\u6267\u884c\u589e\u91cf\u540c\u6b65\uff1a\u4ec5\u8fc1\u79fb {incremental_col} > {last_sync_time} \u7684\u6570\u636e\")\n        \n        # \u6279\u91cf\u8bfb\u53d6 MySQL \u6570\u636e\uff08\u907f\u514d\u5185\u5b58\u6ea2\u51fa\uff09\n        chunk_size = 10000\n        chunk_iter = pd.read_sql(query, mysql_engine, chunksize=chunk_size)\n        \n        # \u6279\u91cf\u5199\u5165 PostgreSQL\n        total_rows = 0\n        for chunk in chunk_iter:\n            chunk.to_sql(\n                name=pg_table,\n                con=pg_engine,\n                if_exists=\"append\",\n                index=False,\n                chunksize=chunk_size\n            )\n            total_rows += len(chunk)\n            print(f\"\u5df2\u8fc1\u79fb {total_rows} \u884c\u6570\u636e\")\n        \n        print(f\"\u6570\u636e\u8fc1\u79fb\u5b8c\u6210\uff01\u5171\u8fc1\u79fb {total_rows} \u884c\u6570\u636e\u81f3 {pg_table}\")\n    \n    except Exception as e:\n        print(f\"\u8fc1\u79fb\u5931\u8d25\uff1a{str(e)}\")\n    finally:\n        mysql_engine.dispose()\n        pg_engine.dispose()\n\n# \u4f7f\u7528\u793a\u4f8b\nmysql_conn = \"mysql+pymysql:\/\/username:password@mysql_host:3306\/database_name\"\npg_conn = \"postgresql:\/\/username:password@pg_host:5432\/database_name\"\n\n# \u5168\u91cf\u8fc1\u79fb\nmigrate_data_mysql_to_postgres(\n    mysql_conn_str=mysql_conn,\n    pg_conn_str=pg_conn,\n    mysql_table=\"source_table\",\n    pg_table=\"target_table\"\n)\n\n# \u589e\u91cf\u8fc1\u79fb\uff08\u57fa\u4e8e\u66f4\u65b0\u65f6\u95f4\u5b57\u6bb5\uff09\n# migrate_data_mysql_to_postgres(\n#     mysql_conn_str=mysql_conn,\n#     pg_conn_str=pg_conn,\n#     mysql_table=\"source_table\",\n#     pg_table=\"target_table\",\n#     incremental_col=\"update_time\",\n#     last_sync_time=\"2025-01-01 00:00:00\"\n# )\n<\/code><\/pre>\n\n\n\n
\n\n\n\n
4. \u65e5\u5fd7\u89e3\u6790\u4e0e\u6570\u636e\u63d0\u53d6\u811a\u672c\uff08\u7ed3\u6784\u5316\u65e5\u5fd7\u8f6c CSV\uff09<\/h2>\n\n\n\n
\u4ece\u975e\u7ed3\u6784\u5316\u65e5\u5fd7\u6587\u4ef6\u4e2d\u63d0\u53d6\u5173\u952e\u4fe1\u606f\uff08\u5982\u65f6\u95f4\u6233\u3001\u8bf7\u6c42 ID\u3001\u72b6\u6001\u7801\uff09\uff0c\u8f6c\u4e3a\u7ed3\u6784\u5316\u6570\u636e\u4fbf\u4e8e\u5206\u6790\u3002<\/p>\n\n\n\n
python<\/p>\n\n\n\n
\u8fd0\u884c<\/p>\n\n\n\n
import re\nimport csv\nfrom pathlib import Path\n\ndef parse_logs_to_csv(log_file_path, output_csv_path, log_pattern):\n    # \u7f16\u8bd1\u65e5\u5fd7\u5339\u914d\u6b63\u5219\u8868\u8fbe\u5f0f\n    regex = re.compile(log_pattern)\n    \n    # \u63d0\u53d6\u65e5\u5fd7\u5b57\u6bb5\u540d\uff08\u4ece\u6b63\u5219\u8868\u8fbe\u5f0f\u5206\u7ec4\u4e2d\u83b7\u53d6\uff09\n    field_names = regex.groupindex.keys()\n    \n    # \u8bfb\u53d6\u65e5\u5fd7\u5e76\u63d0\u53d6\u6570\u636e\n    parsed_data = []\n    with open(log_file_path, \"r\", encoding=\"utf-8\") as log_file:\n        for line_num, line in enumerate(log_file, 1):\n            match = regex.match(line.strip())\n            if match:\n                parsed_data.append(match.groupdict())\n            else:\n                print(f\"\u7b2c {line_num} \u884c\u65e5\u5fd7\u683c\u5f0f\u4e0d\u5339\u914d\uff1a{line.strip()}\")\n    \n    # \u5199\u5165 CSV \u6587\u4ef6\n    Path(output_csv_path).parent.mkdir(parents=True, exist_ok=True)\n    with open(output_csv_path, \"w\", newline=\"\", encoding=\"utf-8\") as csv_file:\n        writer = csv.DictWriter(csv_file, fieldnames=field_names)\n        writer.writeheader()\n        writer.writerows(parsed_data)\n    \n    print(f\"\u65e5\u5fd7\u89e3\u6790\u5b8c\u6210\uff01\u5171\u63d0\u53d6 {len(parsed_data)} \u6761\u7ed3\u6784\u5316\u6570\u636e\uff0c\u4fdd\u5b58\u81f3 {output_csv_path}\")\n\n# \u4f7f\u7528\u793a\u4f8b\uff1a\u89e3\u6790 Nginx \u8bbf\u95ee\u65e5\u5fd7\nnginx_log_pattern = (\n    r\"(?P<ip>\\d+\\.\\d+\\.\\d+\\.\\d+) \"\n    r\"-\\s+-\\s+ \"\n    r\"\\[(?P<timestamp>.+?)\\] \"\n    r'\"(?P<method>\\w+) (?P<path>.+?) HTTP\/\\d+\\.\\d+\" '\n    r\"(?P<status_code>\\d+) \"\n    r\"(?P<response_size>\\d+) \"\n    r'\"(?P<referer>.+?)\" '\n    r'\"(?P<user_agent>.+?)\"'\n)\n\nparse_logs_to_csv(\n    log_file_path=\"access.log\",\n    output_csv_path=\"parsed_nginx_logs.csv\",\n    log_pattern=nginx_log_pattern\n)\n<\/code><\/pre>\n\n\n\n
\n\n\n\n
5. \u5b9a\u65f6\u6570\u636e\u5907\u4efd\u811a\u672c\uff08\u672c\u5730 + \u4e91\u5b58\u50a8\u540c\u6b65\uff09<\/h2>\n\n\n\n
\u81ea\u52a8\u5907\u4efd\u6307\u5b9a\u76ee\u5f55\u4e0b\u7684\u6570\u636e\u6587\u4ef6\uff0c\u652f\u6301\u672c\u5730\u5f52\u6863\u548c\u4e91\u5b58\u50a8\uff08\u4ee5 AWS S3 \u4e3a\u4f8b\uff09\u540c\u6b65\uff0c\u786e\u4fdd\u6570\u636e\u5b89\u5168\u3002<\/p>\n\n\n\n
python<\/p>\n\n\n\n
\u8fd0\u884c<\/p>\n\n\n\n
import os\nimport shutil\nimport boto3\nfrom datetime import datetime\nfrom pathlib import Path\n\ndef backup_data(\n    source_dir, local_backup_dir, \n    s3_bucket=None, s3_prefix=None, \n    aws_access_key=None, aws_secret_key=None\n):\n    # \u751f\u6210\u5907\u4efd\u6587\u4ef6\u540d\uff08\u5e26\u65f6\u95f4\u6233\uff09\n    timestamp = datetime.now().strftime(\"%Y%m%d_%H%M%S\")\n    backup_filename = f\"data_backup_{timestamp}.zip\"\n    local_backup_path = os.path.join(local_backup_dir, backup_filename)\n    \n    # \u521b\u5efa\u672c\u5730\u5907\u4efd\u76ee\u5f55\n    Path(local_backup_dir).mkdir(parents=True, exist_ok=True)\n    \n    try:\n        # \u538b\u7f29\u6e90\u76ee\u5f55\u6570\u636e\n        print(f\"\u5f00\u59cb\u672c\u5730\u5907\u4efd\uff1a{source_dir} \u2192 {local_backup_path}\")\n        shutil.make_archive(\n            base_name=os.path.splitext(local_backup_path)[0],\n            format=\"zip\",\n            root_dir=source_dir\n        )\n        print(\"\u672c\u5730\u5907\u4efd\u5b8c\u6210\uff01\")\n        \n        # \u540c\u6b65\u81f3 AWS S3\uff08\u53ef\u9009\uff09\n        if s3_bucket and s3_prefix:\n            s3_client = boto3.client(\n                \"s3\",\n                aws_access_key_id=aws_access_key,\n                aws_secret_access_key=aws_secret_key\n            )\n            s3_key = f\"{s3_prefix}\/{backup_filename}\"\n            print(f\"\u5f00\u59cb\u540c\u6b65\u81f3 S3\uff1a{local_backup_path} \u2192 s3:\/\/{s3_bucket}\/{s3_key}\")\n            \n            s3_client.upload_file(\n                Filename=local_backup_path,\n                Bucket=s3_bucket,\n                Key=s3_key\n            )\n            print(\"S3 \u540c\u6b65\u5b8c\u6210\uff01\")\n    \n    except Exception as e:\n        print(f\"\u5907\u4efd\u5931\u8d25\uff1a{str(e)}\")\n\n# \u4f7f\u7528\u793a\u4f8b\nbackup_data(\n    source_dir=\"data_to_backup\",  # \u9700\u8981\u5907\u4efd\u7684\u76ee\u5f55\n    local_backup_dir=\"local_backups\",  # \u672c\u5730\u5907\u4efd\u5b58\u50a8\u76ee\u5f55\n    s3_bucket=\"your-s3-bucket\",  # S3 \u6876\u540d\uff08\u53ef\u9009\uff09\n    s3_prefix=\"data_backups\",  # S3 \u5b58\u50a8\u524d\u7f00\uff08\u53ef\u9009\uff09\n    aws_access_key=\"your-aws-access-key\",  # AWS \u8bbf\u95ee\u5bc6\u94a5\uff08\u53ef\u9009\uff09\n    aws_secret_key=\"your-aws-secret-key\"  # AWS \u5bc6\u94a5\uff08\u53ef\u9009\uff09\n)\n\n# \u5b9a\u65f6\u6267\u884c\u5efa\u8bae\uff1a\u7ed3\u5408 crontab\uff08Linux\uff09\u6216\u4efb\u52a1\u8ba1\u5212\u7a0b\u5e8f\uff08Windows\uff09\uff0c\u4f8b\u5982\u6bcf\u5929\u51cc\u6668 2 \u70b9\u6267\u884c\n<\/code><\/pre>\n\n\n\n
\u539f\u6587\u94fe\u63a5\uff1ahttps:\/\/www.kdnuggets.com\/5-useful-python-scripts-for-busy-data-engineers<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
\u6570\u636e\u5de5\u7a0b\u5e08\u5e38\u9700\u5904\u7406\u91cd\u590d\u7684\u6570\u636e\u7ba1\u9053\u642d\u5efa\u3001\u683c\u5f0f\u8f6c\u6362\u7b49\u5de5\u4f5c\uff0c\u4ee5\u4e0b 5 \u4e2a ...<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-763","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/smartaiunion.com\/index.php\/wp-json\/wp\/v2\/posts\/763","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/smartaiunion.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/smartaiunion.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/smartaiunion.com\/index.php\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/smartaiunion.com\/index.php\/wp-json\/wp\/v2\/comments?post=763"}],"version-history":[{"count":1,"href":"https:\/\/smartaiunion.com\/index.php\/wp-json\/wp\/v2\/posts\/763\/revisions"}],"predecessor-version":[{"id":764,"href":"https:\/\/smartaiunion.com\/index.php\/wp-json\/wp\/v2\/posts\/763\/revisions\/764"}],"wp:attachment":[{"href":"https:\/\/smartaiunion.com\/index.php\/wp-json\/wp\/v2\/media?parent=763"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/smartaiunion.com\/index.php\/wp-json\/wp\/v2\/categories?post=763"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/smartaiunion.com\/index.php\/wp-json\/wp\/v2\/tags?post=763"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}