## Excel导入数据到MySQL

    import pymysql
    import openpyxl
    wb = openpyxl.load_workbook('员工表.xlsx')
    ws = wb.active
    params = []
    for row_idx in range(2, ws.max_row + 1):
    values = []
    for col_idx in range(1, ws.max_column):
    values.append(ws.cell(row_idx, col_idx).value)
    params.append(values)
    conn = pymysql.connect(host='', port=3306,
    user='', passwd='',
    database='', charset='utf8mb4')
    try:
    with conn.cursor() as cursor:
    # 批量插入操作
    cursor.executemany(
    'inser into tb (no, name) values (%s, %s)',
    params
    )
    conn.commit()
    except pymysql.MySQLError as err:
    print(err)
    conn.rollback()
    finally:
    conn.close()

## MySQL导出数据到Excel

    import pymysql
    import openpyxl
    conn = pymysql.connect(host='', port=3306,
    user='', passwd='',
    database='', charset='utf8mb4')
    try:
    with conn.cursor() as cursor:
    cursor.execute(
    'select no from tb'
    )
    wb = openpyxl.Workbook()
    ws = wb.active
    title = ('工号', '姓名')
    for col_idx, col_name in enumerate(title):
    ws.cell(1, col_idx + 1, col_name)
    for row_idx, row in enumerate(cursor.fetchall()):
    for col_idx, col_value in enumerate(row):
    ws.cell(row_idx + 2, col_idx + 1, col_value)
    wb.save('员工表.xlsx')
    except pymysql.MySQLError as err:
    print(err)
    finally:
    conn.close()


Python实现Excel与MySQL数据库导入导出数据

    import pandas as pd
    import matplotlib.pyplot as plt
    import seaborn as sns
    from sklearn.datasets import load_iris
    from sklearn.model_selection import train_test_split
    from sklearn.linear_model import LogisticRegression
    from sklearn import metrics
    
    data = load_iris()
    iris_target = data.target
    iris_features = pd.DataFrame(data=data.data, columns=data.feature_names)
    
    ## 选择类别为0和1的样本
    iris_features_part = iris_features.iloc[:100]
    iris_target_part = iris_target[:100]
    
    ## 将数据集划分为训练集与测试集
    x_train, x_test, y_train, y_test = train_test_split(iris_features_part, iris_target_part, test_size=0.2, random_state=2020)
    
    ## 模型搭建
    model = LogisticRegression()
    model.fit(x_train, y_train)
    
    ## 查看w0
    print("The intercept(w0) of Logistic Regression:", model.intercept_)
    ## 查看w
    print("The weight of Logistic Regression:", model.coef_)
    print("The score of Logistic Regression:", model.score(x_train, y_train))
    
    print("n", "=*" * 30, "n")
    ## 用训练好的模型进行预测
    y_predict = model.predict(x_test)
    
    ## 用accuracy(准确度)预测正确的样本数占总预测样本数目的比例，评估模型效果
    print("The accuracy of the Logistic Regression:", metrics.accuracy_score(y_test, y_predict))
    ## 准确度为1，代表所有的样本都预测正确
    
    ## 查看混淆矩阵 (预测值和真实值的各类情况统计矩阵)
    confusion_matrix_result = metrics.confusion_matrix(y_predict, y_test)
    print("The confusion matrix result:n", confusion_matrix_result)
    df = pd.DataFrame()
    df["预测值"] = list(y_predict)
    df["实际值"] = list(y_test)
    df["是否预测正确"] = df["预测值"] == df["实际值"]
    print(df)
    
    ## 利用热力图对结果进行可视化
    plt.figure(figsize=(8, 6))
    sns.heatmap(confusion_matrix_result, annot=True, cmap="Blues")
    plt.xlabel("Predicted labels")
    plt.ylabel("True labels")
    plt.show()


Python实现逻辑回归Logistic Regression

## menu.py

    class MenuItem:
        """Models each Menu Item."""
        def __init__(self, name, water, milk, coffee, cost):
            self.name = name
            self.cost = cost
            self.ingredients = {
                "water": water,
                "milk": milk,
                "coffee": coffee
            }
    class Menu:
    """Models the Menu with drinks."""
    def init(self):
    self.menu = [
    MenuItem(name="espresso", water=50, milk=0, coffee=18, cost=1.5),
    MenuItem(name="latte", water=200, milk=150, coffee=24, cost=2.5),
    MenuItem(name="cappuccino", water=250, milk=50, coffee=24, cost=3),
    ]
    def get_items(self):
        &quot;&quot;&quot;Returns all the names of the available menu items&quot;&quot;&quot;
        options = &quot;&quot;
        for item in self.menu:
            options += f&quot; - {item.name}t${item.cost}n&quot;
        return options
    
    def find_drink(self, order_name):
        &quot;&quot;&quot;Searches the menu for a particular drink by name. Returns that item if it exists, otherwise returns None&quot;&quot;&quot;
        for item in self.menu:
            if item.name == order_name:
                return item
        print(&quot;Sorry that item is not available.&quot;)</code></pre><h2>coffee_maker.py</h2><pre><code class="lang-python">class CoffeeMaker:
    &quot;&quot;&quot;Models the machine that makes the coffee&quot;&quot;&quot;
    def __init__(self):
        self.resources = {
            &quot;water&quot;: 300,
            &quot;milk&quot;: 200,
            &quot;coffee&quot;: 100,
        }
    
    def report(self):
        &quot;&quot;&quot;Prints a report of all resources.&quot;&quot;&quot;
        print(f&quot;Water: {self.resources['water']}ml&quot;)
        print(f&quot;Milk: {self.resources['milk']}ml&quot;)
        print(f&quot;Coffee: {self.resources['coffee']}g&quot;)
    
    def is_resource_sufficient(self, drink):
        &quot;&quot;&quot;Returns True when order can be made, False if ingredients are insufficient.&quot;&quot;&quot;
        can_make = True
        for item in drink.ingredients:
            if drink.ingredients[item] &gt; self.resources[item]:
                print(f&quot;Sorry there is not enough {item}.&quot;)
                can_make = False
        return can_make
    
    def make_coffee(self, order):
        &quot;&quot;&quot;Deducts the required ingredients from the resources.&quot;&quot;&quot;
        for item in order.ingredients:
            self.resources[item] -= order.ingredients[item]
        print(f&quot;Here is your {order.name} ☕️. Enjoy!&quot;)
        print('*' * 30)</code></pre><h2>money_machine.py</h2><pre><code class="lang-python">class MoneyMachine:
    CURRENCY = &quot;$&quot;
    COIN_VALUES = {
        &quot;quarters&quot;: 0.25,
        &quot;dimes&quot;: 0.10,
        &quot;nickles&quot;: 0.05,
        &quot;pennies&quot;: 0.01
    }
    
    def __init__(self):
        self.profit = 0
        self.money_received = 0
    
    def report(self):
        &quot;&quot;&quot;Prints the current profit&quot;&quot;&quot;
        print(f&quot;Money: {self.CURRENCY}{self.profit}&quot;)
    
    def process_coins(self):
        &quot;&quot;&quot;Returns the total calculated from coins inserted.&quot;&quot;&quot;
        print(&quot;Please insert coins.&quot;)
        for coin in self.COIN_VALUES:
            self.money_received += int(input(f&quot;How many {coin}?: &quot;)) * self.COIN_VALUES[coin]
        return self.money_received
    
    def make_payment(self, cost):
        &quot;&quot;&quot;Returns True when payment is accepted, or False if insufficient.&quot;&quot;&quot;
        self.process_coins()
        if self.money_received &gt;= cost:
            change = round(self.money_received - cost, 2)
            print(f&quot;Here is {self.CURRENCY}{change} in change.&quot;)
            self.profit += cost
            self.money_received = 0
            return True
        else:
            print(&quot;Sorry that's not enough money. Money refunded.&quot;)
            self.money_received = 0
            return False</code></pre><h2>main.py</h2><pre><code class="lang-python">from menu import Menu
    
    from coffee_maker import CoffeeMaker
    from money_machine import MoneyMachine
    money_machine = MoneyMachine()
    coffee_maker = CoffeeMaker()
    menu = Menu()
    is_on = True
    while is_on:
    options = menu.get_items()
    choice = input(f"What would you like?n{options} I like: ")
    if choice == "off":
    is_on = False
    elif choice == "report":
    coffee_maker.report()
    money_machine.report()
    else:
    drink = menu.find_drink(choice)
        if coffee_maker.is_resource_sufficient(drink) and money_machine.make_payment(drink.cost):
            coffee_maker.make_coffee(drink)</code></pre>
    
    


Python实现咖啡机功能

    import matplotlib.pyplot as plt
    import numpy as np
    
    # 图片左右翻转
    img_arr = plt.imread("1.jpg")
    img = img_arr[:, ::-1, :]  # [行, 列, 颜色]
    plt.imshow(img)
    plt.show()
    
    # 图片上下翻转
    img = img_arr[::-1, :, :]
    plt.imshow(img)
    plt.show()
    
    # 对颜色翻转：RGB=>BGR
    img = img_arr[:, :, ::-1]
    plt.imshow(img)
    plt.show()
    
    # 图片裁剪
    img = img_arr[20:100, 20:100, :]
    plt.imshow(img)
    plt.show()
    
    # axis=1横向拼图，axis=0纵向拼图
    img = np.concatenate((img_arr, img_arr, img_arr), axis=1)
    plt.imshow(img)
    plt.show()


Python实现图片翻转图片裁剪图片拼接

bisect内置模块，用于维护已排序序列。Bisect是二分法的意思，这里使用二分法来排序，它会将一个元素插入到一个有序列表的合适位置，这使得不需要每次调用sort的方式维护有序列表。在一些情况下，这比反复排序列表或构造一个大的列表再排序的效率更高。

## 以排序顺序插入

    import bisect
    lst = []
    bisect.insort(lst, 2)
    bisect.insort(lst, 5)
    bisect.insort(lst, 3)
    bisect.insort(lst, 6)
    bisect.insort(lst, 3)
    print(lst)  # [2, 3, 3, 5, 6]

insort()用于按排序顺序将项目插入列表。

## 处理重复

bisect模块提供了两种处理重复的方法：可以将新值插入现有值的左侧，也可以插入右侧。

insort()函数实际上是insort_right()的别名，它在现有值之后插入一个项目。相应的函数insort_left()，在现有值之前插入。


Python内置模块bisect 维护已排序序列

这里是 aboss.top 的自留地。写了十几年野路子代码，攒了些摸爬滚打的经验，做成系列课程慢慢讲；也留了一小块地方，记记代码之外的胡思乱想。