后端开发实战

Appearance

python django ​

https://mirrors.tuna.tsinghua.edu.cn/help/pypi/ python .\manage.py startapp orgs

sh
通过 django  创建 项目
django-admin startproject  项目名称
django-admin startproject  demo
 创建一个应用
python .\manage.py startapp app
命令行启动
python  .\manage.py  runserver
生成 表结构
python  .\manage.py  makemigrations
python  .\manage.py migrate
django admin 
python  .\manage.py createsuperuser
---------------------------------------
# django部署
www.cnblogs.com/freely/p/8027937.html
先安装  venv  虚拟环境 在虚拟环境中操作
修改settings.py
DEBUG = False
# uwsgi  + nginx 访问django

uwsgi.ini

properties
[uwsgi]
socket = 0.0.0.0:8000

chdir = /root/backendmanage


wsgi-file = /root/backendmanage/backendmanage/wsgi.py


home = /root/backendmanage/.venv


master = true


processes = 4


threads = 2

vacum = true

threads = 2

vacum = true
#使进程在后台运行，并将日志打到指定的日志文件 
daemonize= /root/backendmanage/uwsgi.log 
pidfile=uwsgi.pid

相关命令

sh
# 停止
uwsgi --stop uwsgi.pid
# 启动
uwsgi --ini /root/backendmanage/uwsgi.ini
# 重启
uwsgi  --reload  uwsgi.pid

#  必须关闭 SELINUX=disabled  重启 否则 不生效会有 静态资源403的问题

脚本

sh
#!/bin/bash

case "$1" in
    "start")
         echo "started"
		 uwsgi --ini /root/backendmanage/uwsgi.ini
   ;; 
   "stop")
       echo "stop"
	   uwsgi --stop uwsgi.pid
   ;;
  "restart")
    echo "restart" 
	uwsgi  --reload  uwsgi.pid
	;;
  *) 
    echo "your params is error (start ,stop, restart)"
  ;;
esac

如何导入指定目录的文件 ​

假设你的项目结构如下：

sh
project/
│
├── utils/
│   └── helper.py        # 你想导入的文件
│
└── subfolder/
    └── main.py          # 当前运行的脚本

在 main.py 中写入：

python
import sys
import os

# 获取当前文件所在目录的上级目录
parent_dir = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
sys.path.append(parent_dir)

# 现在可以导入上级目录中的模块
import utils.helper  # 或者 from utils import helper

方式2 推荐现代写法（使用 pathlib，Python 3.4+）

python
import sys
from pathlib import Path

# 将上级目录加入 sys.path
sys.path.append(str(Path(__file__).resolve().parent.parent))

import utils.helper

vscode code runner 中文乱码 ​

修改 settings.json .vscode 目录下

sh
{
    "terminal.integrated.env.windows": {
        "PYTHONIOENCODING": "utf-8",
        "LANG": "zh_CN.UTF-8"
    },
    "code-runner.executorMap": {
        "python": "set PYTHONIOENCODING=utf-8 && python -u"
    },
    "python.terminal.activateEnvironment": false,
    "python.terminal.executeInFileDir": true
}

脚本 生成sql insert ​

python
from __future__ import annotations

import datetime as dt
import math
import re
from decimal import Decimal, ROUND_HALF_UP
from pathlib import Path
from typing import Iterable

from openpyxl import load_workbook
from openpyxl.cell.cell import Cell
from openpyxl.styles.numbers import is_date_format

# ====== Configuration: change these values before running ======
EXCEL_PATH = Path(r'dat.xlsx')
SHEET_NAME = "Sheet1"
TABLE_NAME = "sys_user"
OUTPUT_SQL_PATH = Path(r'data.sql')
IGNORE_FIELDS = {
    
}
# ==============================================================

TEXT_FORMATS = {"@", "text"}
GENERAL_FORMAT = "General"
NUMERIC_SYMBOLS_RE = re.compile(r'"[^"]*"|\[[^\]]*\]|_.|\\.|\s')


def normalized_number_format(cell: Cell) -> str:
    return (cell.number_format or GENERAL_FORMAT).strip()


def sql_escape(value: str) -> str:
    return value.replace("'", "''")


def quote_sql_string(value: str) -> str:
    return f"'{sql_escape(value)}'"


def normalize_header(value: object) -> str:
    if value is None:
        return ""
    return str(value).strip()


def is_empty_cell(cell: Cell) -> bool:
    value = cell.value
    return value is None or (isinstance(value, str) and value.strip() == "")


def is_text_cell(cell: Cell) -> bool:
    number_format = normalized_number_format(cell).lower()
    return number_format in TEXT_FORMATS or cell.data_type == "s" or isinstance(cell.value, str)


def decimal_places_from_format(number_format: str) -> int:
    cleaned = NUMERIC_SYMBOLS_RE.sub("", number_format or "")
    if "." not in cleaned:
        return 0
    decimal_part = cleaned.split(".", 1)[1]
    return sum(1 for char in decimal_part if char in "0#?")


def decimal_from_excel_number(value: int | float | Decimal, places: int) -> str:
    decimal_value = Decimal(str(value))
    if places <= 0:
        return str(int(decimal_value.to_integral_value(rounding=ROUND_HALF_UP)))
    quantizer = Decimal("1").scaleb(-places)
    return format(decimal_value.quantize(quantizer, rounding=ROUND_HALF_UP), f".{places}f")


def format_date_value(value: dt.date | dt.datetime) -> str:
    if isinstance(value, dt.datetime):
        if value.time() == dt.time.min:
            return quote_sql_string(value.strftime("%Y-%m-%d"))
        return quote_sql_string(value.strftime("%Y-%m-%d %H:%M:%S"))
    return quote_sql_string(value.strftime("%Y-%m-%d"))


def format_sql_value(cell: Cell) -> str:
    value = cell.value

    if is_text_cell(cell):
        return quote_sql_string(str(value))

    if isinstance(value, (dt.datetime, dt.date)) or is_date_format(normalized_number_format(cell)):
        if isinstance(value, (dt.datetime, dt.date)):
            return format_date_value(value)
        return quote_sql_string(str(value))

    if isinstance(value, bool):
        return "1" if value else "0"

    if isinstance(value, int):
        return str(value)

    if isinstance(value, float):
        if not math.isfinite(value):
            return quote_sql_string(str(value))
        number_format = normalized_number_format(cell)
        if number_format == GENERAL_FORMAT:
            return str(int(value)) if value.is_integer() else str(Decimal(str(value)).normalize())
        places = decimal_places_from_format(number_format)
        if places == 0 and value.is_integer():
            return str(int(value))
        return decimal_from_excel_number(value, places)

    if isinstance(value, Decimal):
        number_format = normalized_number_format(cell)
        if number_format == GENERAL_FORMAT:
            return str(value.normalize())
        places = decimal_places_from_format(number_format)
        return decimal_from_excel_number(value, places)

    return quote_sql_string(str(value))


def build_insert_statement(table_name: str, field_values: list[tuple[str, str]]) -> str:
    columns = ", ".join(field for field, _ in field_values)
    values = ", ".join(value for _, value in field_values)
    return f"INSERT INTO {table_name} ({columns}) VALUES ({values});"


def iter_insert_statements(
    excel_path: Path,
    sheet_name: str,
    table_name: str,
    ignore_fields: Iterable[str] | None = None,
) -> list[str]:
    ignored = {field.strip() for field in (ignore_fields or set()) if field.strip()}
    workbook = load_workbook(excel_path, data_only=True)
    worksheet = workbook[sheet_name]

    headers = [normalize_header(cell.value) for cell in worksheet[1]]
    statements: list[str] = []

    for row in worksheet.iter_rows(min_row=2):
        field_values: list[tuple[str, str]] = []
        for header, cell in zip(headers, row):
            if not header or header in ignored or is_empty_cell(cell):
                continue
            field_values.append((header, format_sql_value(cell)))

        if field_values:
            statements.append(build_insert_statement(table_name, field_values))

    return statements


def generate_insert_sql(
    excel_path: Path,
    sheet_name: str,
    table_name: str,
    output_sql_path: Path,
    ignore_fields: Iterable[str] | None = None,
) -> list[str]:
    statements = iter_insert_statements(excel_path, sheet_name, table_name, ignore_fields)
    if output_sql_path.parent != Path("."):
        output_sql_path.parent.mkdir(parents=True, exist_ok=True)
    output_sql_path.write_text("\n".join(statements) + ("\n" if statements else ""), encoding="utf-8")
    return statements


def main() -> None:
    statements = generate_insert_sql(
        excel_path=EXCEL_PATH,
        sheet_name=SHEET_NAME,
        table_name=TABLE_NAME,
        output_sql_path=OUTPUT_SQL_PATH,
        ignore_fields=IGNORE_FIELDS,
    )
    print(f"Generated {len(statements)} SQL statement(s): {OUTPUT_SQL_PATH}")


if __name__ == "__main__":
    main()

脚本 生成sql update ​

python
from __future__ import annotations

import datetime as dt
import math
import re
from decimal import Decimal, ROUND_HALF_UP
from pathlib import Path
from typing import Iterable

from openpyxl import load_workbook
from openpyxl.cell.cell import Cell
from openpyxl.styles.numbers import is_date_format

# ====== Configuration: change these values before running ======
EXCEL_PATH = Path(r"dat.xlsx")
SHEET_NAME = "Sheet1"
TABLE_NAME = "sys_user"
OUTPUT_SQL_PATH = Path(r"update_data.sql")
ID_FIELD = "id"
IGNORE_FIELDS = {

}
# ==============================================================

TEXT_FORMATS = {"@", "text"}
GENERAL_FORMAT = "General"
NUMERIC_SYMBOLS_RE = re.compile(r'"[^"]*"|\[[^\]]*\]|_.|\\.|\s')


def normalized_number_format(cell: Cell) -> str:
    return (cell.number_format or GENERAL_FORMAT).strip()


def sql_escape(value: str) -> str:
    return value.replace("'", "''")


def quote_sql_string(value: str) -> str:
    return f"'{sql_escape(value)}'"


def normalize_header(value: object) -> str:
    if value is None:
        return ""
    return str(value).strip()


def is_empty_cell(cell: Cell) -> bool:
    value = cell.value
    return value is None or (isinstance(value, str) and value.strip() == "")


def is_text_cell(cell: Cell) -> bool:
    number_format = normalized_number_format(cell).lower()
    return number_format in TEXT_FORMATS or cell.data_type == "s" or isinstance(cell.value, str)


def decimal_places_from_format(number_format: str) -> int:
    cleaned = NUMERIC_SYMBOLS_RE.sub("", number_format or "")
    if "." not in cleaned:
        return 0
    decimal_part = cleaned.split(".", 1)[1]
    return sum(1 for char in decimal_part if char in "0#?")


def decimal_from_excel_number(value: int | float | Decimal, places: int) -> str:
    decimal_value = Decimal(str(value))
    if places <= 0:
        return str(int(decimal_value.to_integral_value(rounding=ROUND_HALF_UP)))
    quantizer = Decimal("1").scaleb(-places)
    return format(decimal_value.quantize(quantizer, rounding=ROUND_HALF_UP), f".{places}f")


def format_date_value(value: dt.date | dt.datetime) -> str:
    if isinstance(value, dt.datetime):
        if value.time() == dt.time.min:
            return quote_sql_string(value.strftime("%Y-%m-%d"))
        return quote_sql_string(value.strftime("%Y-%m-%d %H:%M:%S"))
    return quote_sql_string(value.strftime("%Y-%m-%d"))


def format_sql_value(cell: Cell) -> str:
    value = cell.value

    if is_text_cell(cell):
        return quote_sql_string(str(value))

    if isinstance(value, (dt.datetime, dt.date)) or is_date_format(normalized_number_format(cell)):
        if isinstance(value, (dt.datetime, dt.date)):
            return format_date_value(value)
        return quote_sql_string(str(value))

    if isinstance(value, bool):
        return "1" if value else "0"

    if isinstance(value, int):
        return str(value)

    if isinstance(value, float):
        if not math.isfinite(value):
            return quote_sql_string(str(value))
        number_format = normalized_number_format(cell)
        if number_format == GENERAL_FORMAT:
            return str(int(value)) if value.is_integer() else str(Decimal(str(value)).normalize())
        places = decimal_places_from_format(number_format)
        if places == 0 and value.is_integer():
            return str(int(value))
        return decimal_from_excel_number(value, places)

    if isinstance(value, Decimal):
        number_format = normalized_number_format(cell)
        if number_format == GENERAL_FORMAT:
            return str(value.normalize())
        places = decimal_places_from_format(number_format)
        return decimal_from_excel_number(value, places)

    return quote_sql_string(str(value))


def build_update_statement(table_name: str, assignments: list[tuple[str, str]], id_field: str, id_value: str) -> str:
    set_clause = ", ".join(f"{field} = {value}" for field, value in assignments)
    return f"UPDATE {table_name} SET {set_clause} WHERE {id_field} = {id_value};"


def iter_update_statements(
    excel_path: Path,
    sheet_name: str,
    table_name: str,
    id_field: str,
    ignore_fields: Iterable[str] | None = None,
) -> list[str]:
    ignored = {field.strip() for field in (ignore_fields or set()) if field.strip()}
    workbook = load_workbook(excel_path, data_only=True)
    worksheet = workbook[sheet_name]

    headers = [normalize_header(cell.value) for cell in worksheet[1]]
    statements: list[str] = []

    for row in worksheet.iter_rows(min_row=2):
        id_value = ""
        assignments: list[tuple[str, str]] = []

        for header, cell in zip(headers, row):
            if not header or header in ignored or is_empty_cell(cell):
                continue

            sql_value = format_sql_value(cell)
            if header == id_field:
                id_value = sql_value
                continue

            assignments.append((header, sql_value))

        if id_value and assignments:
            statements.append(build_update_statement(table_name, assignments, id_field, id_value))

    return statements


def generate_update_sql(
    excel_path: Path,
    sheet_name: str,
    table_name: str,
    output_sql_path: Path,
    id_field: str = ID_FIELD,
    ignore_fields: Iterable[str] | None = None,
) -> list[str]:
    statements = iter_update_statements(excel_path, sheet_name, table_name, id_field, ignore_fields)
    if output_sql_path.parent != Path("."):
        output_sql_path.parent.mkdir(parents=True, exist_ok=True)
    output_sql_path.write_text("\n".join(statements) + ("\n" if statements else ""), encoding="utf-8")
    return statements


def main() -> None:
    statements = generate_update_sql(
        excel_path=EXCEL_PATH,
        sheet_name=SHEET_NAME,
        table_name=TABLE_NAME,
        output_sql_path=OUTPUT_SQL_PATH,
        id_field=ID_FIELD,
        ignore_fields=IGNORE_FIELDS,
    )
    print(f"Generated {len(statements)} SQL statement(s): {OUTPUT_SQL_PATH}")


if __name__ == "__main__":
    main()