V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
推荐学习书目
Learn Python the Hard Way
Python Sites
PyPI - Python Package Index
http://diveintopython.org/toc/index.html
Pocoo
值得关注的项目
PyPy
Celery
Jinja2
Read the Docs
gevent
pyenv
virtualenv
Stackless Python
Beautiful Soup
结巴中文分词
Green Unicorn
Sentry
Shovel
Pyflakes
pytest
Python 编程
pep8 Checker
Styles
PEP 8
Google Python Style Guide
Code Style from The Hitchhiker's Guide
qazwsxkevin
V2EX  ›  Python

这个 pymysql 避免 Insert 表中“已有的”的条目代码,应该如何优化?

  •  
  •   qazwsxkevin · 2020-08-29 23:34:12 +08:00 · 2209 次点击
    这是一个创建于 1553 天前的主题,其中的信息可能已经有所发展或是发生改变。

    1.准备 INSERT 的字典,其实有三个字段(物品名称,型号,序号)内容,与表中已有的条目三个相同,这个准备插入的字典已经可以被认为是重复的,不需 INSERT,
    字典全部字段有 32 个.
    2.for 循环字典列表,逐条判断写入,字典列表有 900 个字典的话,需时 79 秒,虽然是练手学习,也无法接受
    3.用

    from concurrent.futures import ThreadPoolExecutor  
    with ThreadPoolExecutor(50) as executor:
       for each in testDictList:
       executor.submit(thDictSQL, SQLServerInfo,'testTBL',each,checkField)
    

    仅需 48 秒能写入.
    改成 ThreadPoolExecutor(500),39 秒,但是很大几率会丢了一些条目

    # dict 转换成 SQL 语句
    def DicttoSQLText(obj, tblName, SQLcmd):
        returnText = ''
        FiledStr = ''
        ValueStr = ''
        ccount = 0
    
        if isinstance(obj, list):
            for i in obj:
                FiledStr = ', '.join(list(i.keys()))
                ValueStr = "'" + '\', \''.join(list([str(x) for x in i.values()])) + "'"
                SQLText = SQLcmd.format(tblName, FiledStr, ValueStr)
                returnText += SQLText
                ccount += 1
            return returnText
    
        if isinstance(obj, dict):
            FiledStr = ', '.join(list(obj.keys()))
            ValueStr = "'" + '\', \''.join(list([str(x) for x in obj.values()])) + "'"
            SQLText = SQLcmd.format(tblName, FiledStr, ValueStr)
            returnText += SQLText
            ccount += 1
            return returnText
    
    
    # 执行 SQL 语句,返回字典结果
    def SQLcmdData(cur, sqlcmd):
        cur.execute(sqlcmd)
        data = cur.fetchall()
        if len(data) == 1:
            return data[0]
        else:
            return data
    
    
    # Insert 字典数据(函数可以用在 thread)
    def thDictSQL(SerInfo, tblName, dataDict, checkField):
        thSQLconn = pymysql.connect(host=SQLServerInfo['ip'], port=SQLServerInfo['port'], user=SQLServerInfo['user'],
                                    password=SQLServerInfo['password'], database=SQLServerInfo['database'],
                                    charset=SQLServerInfo['charset'])
        thSQLCursor = thSQLconn.cursor(cursor=pymysql.cursors.DictCursor)
    
        # 检查有无相同项目
        checkStr = ''
        # 组装检查 SQL 语句
        for i in checkField:
            checkStr += f"`{i}`='{dataDict.get(i)}' AND "
        checkStr = checkStr.rstrip(checkStr[-4:])
        thSQLSelectText = "SELECT id FROM {} WHERE ({})".format(tblName, checkStr)
        ret = thSQLCursor.execute(thSQLSelectText)
    
        # 根据检查结果写入
        if not thSQLCursor.fetchall():
            thSQLInsertText = DicttoSQLText(dataDict, "INSERT INTO {} ({}) VALUE ({});")
            ret = SQLcmdData(thSQLCursor, thSQLInsertText)
            thSQLCursor.close()
            thSQLconn.close()
        return thResult
    
    
    if __name__ == '__main__':
        testDictList = None
        with open('h:/dd.dict', 'r') as f:
            testDictList = eval(f.read())
    
        StartTime = time.clock()
    
        checkField = ['物品名称', '型号', '序号']
        for i in testDictList:
            i['DL'] = 0
            i['DB'] = 0
            i['UpdateTime'] = myFunc.nowTimeForStr()
            thDictSQL(SQLServerInfo, 'cangku', i, checkField)
    
        print(time.clock() - StartTime)
    
    16 条回复    2020-09-01 16:37:50 +08:00
    dorothyREN
        1
    dorothyREN  
       2020-08-29 23:40:37 +08:00
    三个字段设置唯一,然后直接插入,有重复的数据插入会返回异常,然后忽略异常。。。。。
    chihiro2014
        2
    chihiro2014  
       2020-08-29 23:44:03 +08:00
    先查再插
    zhangjiale
        3
    zhangjiale  
       2020-08-29 23:47:28 +08:00
    ```sql
    insert ignore
    ```
    qile1
        4
    qile1  
       2020-08-30 00:19:43 +08:00 via Android
    用 sql 语句 if not exect
    johnsona
        5
    johnsona  
       2020-08-30 02:22:20 +08:00
    一次性提交
    singerll
        6
    singerll  
       2020-08-30 03:40:09 +08:00 via Android
    单条 insert,神仙也救不了。
    用不重复的字典读,一条 insert 提交
    widewing
        7
    widewing  
       2020-08-30 03:56:05 +08:00 via Android
    Upsert 或 merge into 吧这种 case
    msg7086
        8
    msg7086  
       2020-08-30 10:06:20 +08:00
    拿到 900 条数据,从数据库里用这 900 条记录搜索,返回现有的记录。
    删掉重复的记录,然后一次性插入。
    估摸着两三秒撑死了。

    当然,这是不考虑 race condition 的情况。
    chaogg
        9
    chaogg  
       2020-08-30 11:19:48 +08:00
    才 900 条,物品名称,型号,序号建联合索引,逐条插入,如果用 ORM,每次插入前先查询一下是否存在。如果用 sql 语句,则可以用 if not exect
    xiaolinjia
        10
    xiaolinjia  
       2020-08-30 14:13:25 +08:00
    2020 了,还用 pymysql 。不知道 pymysql 是纯 py 实现的,c 实现的 mysqlclient 会快不少吗。
    Tompes
        11
    Tompes  
       2020-08-30 14:33:52 +08:00 via Android
    整个布隆过滤器
    guanhui07
        12
    guanhui07  
       2020-08-30 14:54:52 +08:00 via iPhone
    if not exect
    chenqh
        13
    chenqh  
       2020-08-30 16:12:58 +08:00
    @xiaolinjia 虽然不想说,但是我跑我的测试的时候 mysqlclient 并没有比我用 pymysql 快
    chenqh
        14
    chenqh  
       2020-08-30 16:14:11 +08:00
    如果数据库的数量不大的,直接把数据库的记录读到本地,建立一个 set, 这样可能会快点
    13936
        15
    13936  
       2020-08-31 09:35:35 +08:00
    on duplicate 呗。这也不知道?
    TEwrc
        16
    TEwrc  
       2020-09-01 16:37:50 +08:00
    应该有参数控制的,我在写`mongodb`也遇到这种问题,我是这么解决的:

    `client[db_name][collection_name].update_one({"name":info['name']},{'$setOnInsert':{"value":info["value"]}},True)`
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   2586 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 24ms · UTC 11:18 · PVG 19:18 · LAX 03:18 · JFK 06:18
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.