时间戳

首页 > 解决方案 > 使 Python 的 MariaDB 更新速度更快

python - 使 Python 的 MariaDB 更新速度更快

问题描述

出于技术原因，我有一个 python 脚本将来自多个来源的数据聚合到一个。

在这个脚本中，我创建了一个用数据填充员工表，然后在第二步中，从另一个数据源获取每个员工的姓名/姓氏。我的代码如下：

创建表并用数据填充它：

def createIdentite(mariaConnector, fmsConnector):
    print('Creating table "Identite"...')
    mariadbCursor = mariaConnector.cursor()

    # verify we have the destination tables we need
    print(' Checking for table Identite...')
    if mariaCheckTableExists(mariadbConnector, 'Identite') == False:
        print(' Table doesn\'t exist, creating it...')
        mariadbCursor.execute("""
            CREATE TABLE Identite (
                PK_FP VARCHAR(50) NOT NULL,
                LieuNaissance TEXT, 
                PaysNaissance TEXT, 
                Name TEXT,
                LastName TEXT,

                Nationalite TEXT, 
                PaysResidence TEXT, 
                PersonneAPrevenir TEXT, 
                Tel1_PAP TEXT, 
                Tel2_PAP TEXT,
                CategorieMutuelle TEXT,
                Ep1_MUTUELLE BOOLEAN,
                TypeMutuelle BOOLEAN,
                NiveauMutuelle BOOLEAN,
                NiveauMutuelle2 BOOLEAN,
                NiveauMutuelle3 BOOLEAN,
                PartMutuelleSalarie FLOAT,
                PartMutuelleSalarieOption FLOAT,
                PRIMARY KEY (PK_FP)
            )
            """)
        mariadbCursor.execute("CREATE INDEX IdentitePK_FP ON Identite(PK_FP)")
    else:
        # flush the table
        print(' Table exists, flushing it...')
        mariadbCursor.execute("DELETE FROM Identite")    

    # now fill it with fresh data
    print(' Retrieving the data from FMS...')
    fmsCursor = fmsConnector.cursor()
    fmsCursor.execute("""
        SELECT 
            PK_FP,
            Lieu_Naiss_Txt, 
            Pays_Naiss_Txt, 
            Nationalite_Txt, 
            Pays_Resid__Txt, 
            Pers_URG, 
            Tel1_URG, 
            Tel2_URG,
            CAT_MUTUELLE,
            CASE WHEN Ep1_MUTUELLE = 'OUI' THEN 1 ELSE 0 END as Ep1_MUTUELLE,
            CASE WHEN TYPE_MUT = 'OUI' THEN 1 ELSE 0 END as TYPE_MUT, 
            CASE WHEN Niv_Mutuelle IS NULL THEN 0 ELSE 1 END as Niv_Mutuelle,
            CASE WHEN NIV_MUTUELLE[2] IS NULL THEN 0 ELSE 1 END as Niv_Mutuelle2,
            CASE WHEN NIV_MUTUELLE[3] IS NULL THEN 0 ELSE 1 END as Niv_Mutuelle3,
            PART_MUT_SAL,
            PART_MUT_SAL_Option
        FROM B_EMPLOYE
        WHERE PK_FP IS NOT NULL
        """)

    print(' Transferring...')
    #for row in fmsCursor:
    insert = """INSERT INTO Identite (
                PK_FP,
                LieuNaissance, 
                PaysNaissance, 
                Nationalite, 
                PaysResidence,
                PersonneAPrevenir, 
                Tel1_PAP,
                Tel2_PAP, 
                CategorieMutuelle,
                Ep1_MUTUELLE,
                TypeMutuelle, 
                NiveauMutuelle,
                NiveauMutuelle2,
                NiveauMutuelle3,
                PartMutuelleSalarie,
                PartMutuelleSalarieOption
            ) VALUES (
                %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s
            )"""
    values = fmsCursor.fetchall()
    mariadbCursor.executemany(insert, values)
    mariaConnector.commit()
    print(' Inserted '+str(len(values))+' values')
    return len(values)

我检索名字和姓氏的部分：

def updateEmployeeNames(mariaConnector, mssqlConnector):
    print("Updating employee names...")
    mariadbCursor = mariaConnector.cursor()
    mssqlCursor = mssqlConnector.cursor()

    mssqlCursor.execute("SELECT Name, LastName, PK_FP FROM F_Person")
    rows = mssqlCursor.fetchall()

    query = """
        UPDATE Identite
        SET Name = %s, LastName = %s
        WHERE PK_FP = %s
    """
    mariadbCursor.executemany(query, rows)

    mariadbConnector.commit()

正如您可能已经猜到的，第一个函数几乎不需要执行时间（少于 2 秒），而第二个函数几乎需要 20 秒。

Python 不是我的强项，但可能还有另一种方法，目的是让它更快。

我已经尝试在 executeMany 之前向 createIdentite 的每个元组添加值，但 Mysql 连接器不允许我这样做。

非常感谢你的帮助。

标签： pythonsqlsql-servermariadbpyodbc

解决方案

在所有 MySQL Python 驱动程序中，execute_many 被重写，因为 MySQL 不支持批量操作，自 10.2 以来，它们仅通过 MariaDB 中的二进制协议支持，后来添加了完全支持（包括删除和更新），并且在最新的 10.2 中可用， MariaDB 服务器的 10.3 和 10.4 版本。

python驱动程序正在重写一个插入查询，迭代行数并将语句转换为

INSERT INTO t1 VALUES (row1_id, row1_data), (row2_id, row2_data),....(rown_id, row_n data)

这相当快，但 SQL 语法不允许UPDATE或DELETE这样做。在这种情况下，驱动程序需要执行语句 n 次（n= 行数），在单个语句中传递每一行的值。

MariaDB 二进制协议允许准备语句，通过一次发送所有数据来执行它（执行包也包含数据）。

如果 C 是替代方案，请查看MariaDB Connector/C 的 Github 存储库上的批量单元测试。否则你必须等待，MariaDB 可能会在明年发布它自己的 python 驱动程序。

推荐阅读