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
wuwukai007
V2EX  ›  Python

被 ORM 折磨, sql 1 分钟写好, ORM 想了一个小时,没写出来😭

  •  
  •   wuwukai007 · 2021-09-14 15:24:34 +08:00 · 6673 次点击
    这是一个创建于 926 天前的主题,其中的信息可能已经有所发展或是发生改变。

    救救孩子吧

    select d.id,d.created_time,(select count(*) from word where word.dictionary_id=d.id)
        as word_count,
           (select account from user where user.id = d.creator_id) as username,
           d.dictionary_name
            from dictionary
        as d where d.thesaurus_name='professional'
    
    第 1 条附言  ·  2021-09-14 16:24:23 +08:00

    word_count = select(func.count(Word.id)).where(Word.dictionary_id==Dictionary.id).label('word_count') username = select(User.account).where(User.id == Dictionary.creator_id).as_scalar().label('username') s = select(Dictionary.created_time,word_count,username).where(Dictionary.thesaurus_name=='professional')

    SELECT dictionary.created_time, (SELECT count(word.id) AS count_1 
    FROM word 
    WHERE word.dictionary_id = dictionary.id) AS word_count, (SELECT "user".account 
    FROM "user" 
    WHERE "user".id = dictionary.creator_id) AS username 
    FROM dictionary 
    WHERE dictionary.thesaurus_name = :thesaurus_name_1
    
    第 2 条附言  ·  2021-09-14 18:43:35 +08:00

    格式化一下

    from sqlalchemy import select ,func
    word_count = select(func.count(Word.id)).where(Word.dictionary_id==Dictionary.id).label('word_count')
    username = select(User.account).where(User.id == Dictionary.creator_id).as_scalar().label('username')
    sql = select(Dictionary.created_time,word_count,username).where(Dictionary.thesaurus_name=='professional')
    print(s)
    '''
    SELECT dictionary.created_time, (SELECT count(word.id) AS count_1 
    FROM word 
    WHERE word.dictionary_id = dictionary.id) AS word_count, (SELECT "user".account 
    FROM "user" 
    WHERE "user".id = dictionary.creator_id) AS username 
    FROM dictionary 
    WHERE dictionary.thesaurus_name = :thesaurus_name_1
    '''
    
    39 条回复    2021-09-16 15:39:09 +08:00
    JKeita
        1
    JKeita  
       2021-09-14 15:39:56 +08:00
    那就别用呗,orm 性能又不是说有多好。又不直观。维护又麻烦。
    AoEiuV020
        2
    AoEiuV020  
       2021-09-14 15:40:18 +08:00
    会 sql 的话 orm 也都能直接执行 sql 的吧,或者能随便写写转成 sql 对比一下再调整,
    kingfalse
        3
    kingfalse  
       2021-09-14 15:44:21 +08:00 via Android
    不要为了用而用
    Hstar
        4
    Hstar  
       2021-09-14 15:45:03 +08:00
    里面那个 select count(*) from word where word.dictionary_id=d.id 子查询不好写,建议先查了变成一个字典再拼接
    honkki
        5
    honkki  
       2021-09-14 15:45:24 +08:00
    orm 也可以直接执行 sql 原生语句的呀
    Pursue9
        6
    Pursue9  
       2021-09-14 15:49:55 +08:00
    你这样用 SQL,真的不怕数据库蹦吗

    建议先查` dictionary `
    ```sql
    SELECT
    d.id,
    d.created_time
    FROM dictionary AS d
    WHERE d.thesaurus_name='professional'
    LIMIT 1000
    ```
    再去查 word_count user
    ```sql
    SELECT id,COUNT(1)
    FROM word_count
    WHERE id IN ()
    ```

    ```sql
    SELECT account FROM `user`
    WHERE id IN ()
    ```

    然后返回的结果再用程序处理后返回
    qW7bo2FbzbC0
        7
    qW7bo2FbzbC0  
       2021-09-14 15:52:23 +08:00
    不要为了用而用
    thetbw
        8
    thetbw  
       2021-09-14 15:52:33 +08:00   ❤️ 1
    如果数据量不是很大的话分成三个查询如何,
    第一个查询查 select d.id,d.created_time d.dictionary_name from dictionary
    as d where d.thesaurus_name='professional'
    后两个查询分别为上面的子查询,然后组装数据
    wuwukai007
        9
    wuwukai007  
    OP
       2021-09-14 16:23:26 +08:00
    顿悟了
    word_count = select(func.count(Word.id)).where(Word.dictionary_id==Dictionary.id).label('word_count')
    username = select(User.account).where(User.id == Dictionary.creator_id).as_scalar().label('username')
    s = select(Dictionary.created_time,word_count,username).where(Dictionary.thesaurus_name=='professional')
    Latin
        10
    Latin  
       2021-09-14 18:01:37 +08:00
    @wuwukai007 这不还是一次查询分了三次吗 (狗头
    Rwing
        11
    Rwing  
       2021-09-14 18:07:51 +08:00
    不是我说。。。这也叫 ORM 吗。。。。不就是把 sql 关键字替换成了函数。。。。。。
    clf
        12
    clf  
       2021-09-14 18:10:56 +08:00
    换个 ORM 吧,理想的 ORM 应该是封装了单表查询的方法,多表通过注解关联两个类的字段或者是配置文件配置,进一步的甚至可以用 lambda 方法来构造跨表查询。
    Rwing
        13
    Rwing  
       2021-09-14 18:23:52 +08:00   ❤️ 1
    优秀的 orm 应该是这样的

    from dict in db.dictionary
    where dict.Thesaurus_name == 'professional'
    select new
    {
    dict = dict,
    wordCount = dict.Word.Count(),
    userName = dict.User.Name
    };

    或者 S
    db.dictionary
    .Where(d => d.Thesaurus_name == 'professional')
    .Select(d =>{
    new {
    dict = d,
    wordCount = d.Word.Count(),
    userName = d.User.Name
    }
    })
    Pursue9
        14
    Pursue9  
       2021-09-14 18:41:47 +08:00   ❤️ 1
    @Rwing EFCore 啊,应该除了 C# /F#,别的语言应该实现不了这种 linq 这种伪 SQL
    Trim21
        15
    Trim21  
       2021-09-14 18:48:08 +08:00 via Android   ❤️ 1
    你可以直接执行 SQL 然后把结果序列化成 ORM 的类啊…
    wuwukai007
        16
    wuwukai007  
    OP
       2021-09-14 18:50:14 +08:00
    @Trim21 @AoEiuV020 @JKeita 直接用 sql 在参数不固定时,会有恶心的 最后一个 and 的问题,要处理 where 1= 1 这种
    Thinklong
        17
    Thinklong  
       2021-09-14 19:17:49 +08:00
    所有连表查询都应该被干掉,再好的 ORM 也不是你作案的工具
    spediacn
        18
    spediacn  
       2021-09-14 20:00:50 +08:00 via iPhone
    牛叉组件都是绕开 ORM 的,比如 ms 的 identity,直接底层调用存储过程
    cp19890714
        19
    cp19890714  
       2021-09-14 20:14:37 +08:00
    1. 简单查询用 ORM, 稍复杂的都直接写 sql
    2. 把复杂查询拆成多个简单查询.
    kevinonepiece
        20
    kevinonepiece  
       2021-09-14 20:20:01 +08:00
    @Pursue9 连表查只用一次 MySQL 连接,分开查得三次连接,我之前把三次简单查询合并成一次,速度快了,所以该怎么权衡呢?
    kevinonepiece
        21
    kevinonepiece  
       2021-09-14 20:21:01 +08:00
    @cp19890714
    @Thinklong 为啥要干掉连表查
    cyrivlclth
        22
    cyrivlclth  
       2021-09-14 20:26:16 +08:00
    @kevinonepiece 这次快了,请求多了,数据库遭不住。。。好多子查询。。。
    ragnaroks
        23
    ragnaroks  
       2021-09-14 21:41:01 +08:00
    @Pursue9
    linq 好像 csharp 独一家,不过转成方法的话基本上所有语言都有。
    比如这样的语法 ↓
    var ageList=table().select(e=>e.age).where(e=>e.age>18).list()
    512357301
        24
    512357301  
       2021-09-14 22:20:53 +08:00 via Android
    原始 SQL 的写法本质上还是 left join 吧,你的那个写法应该是 mysql 独有或者不算特别标准的写法,
    你可以把那两个子查询从 select 那里挪到 from 后面,用 left join 关联,这样会不会更好用 orm 实现呢。
    cp19890714
        25
    cp19890714  
       2021-09-14 22:25:59 +08:00   ❤️ 1
    @kevinonepiece
    以我个人的使用经验, 我觉得多次简单查询对比关联查询有以下好处:
    * 有效使用数据库缓存
    * 关联的表多了,且没有用好索引, 一次查询的时间就更长. 这种并发查询多了, 就会导致数据库压力骤增. 例如:一次查询要 100ms,那么在未来很可能成为慢查询,进而可能导致雪崩.
    * 减少锁的竞争
    * 尽量降低数据库压力, 毕竟数据库的扩容比服务器扩容难多了. 在开发时,就让 sql 足够简单, 未来一旦出现数据库瓶颈, 大部分的代码不用考虑 sql 优化了, 直接升级数据库吧.
    * 随着数据量的增加, mysql 的执行逻辑也会变化. 虽然开发时不是慢查询,但以后可能就会变成慢查询.
    但并不是所有的关联查询都拆分, 对于效率非常高的关联查询, 还是不要拆分.
    crystom
        26
    crystom  
       2021-09-14 22:31:33 +08:00
    做 olap 分析能连表就连表,跟 oltp 场景不同
    Rocketer
        27
    Rocketer  
       2021-09-14 22:33:44 +08:00 via iPhone
    不用死脑筋,.Net 程序员也不是非 linq 不可,复杂查询还是会用 sql 的。

    问题是你这 sql 不敢直接在生产环境用,怕是要被 dba 打死的。
    Mithril
        28
    Mithril  
       2021-09-14 22:47:08 +08:00
    你需要一个 Linq 。。。
    hushao
        29
    hushao  
       2021-09-14 23:44:29 +08:00
    你都用 sqlalchemy,并且 sql 都写好了,实在写不出 orm 直接执行 sql 语句就行啊,要求必须使用 orm 的另说。
    iseki
        30
    iseki  
       2021-09-15 00:21:29 +08:00
    一般 ORM 都支持 raw sql 的吧,ORM 不适合不 O 的场景,统计之类的就非常不 O,强用 ORM 就是给自己找不痛快
    gjquoiai
        31
    gjquoiai  
       2021-09-15 01:01:59 +08:00
    我超喜欢 sqlalchemy 的,我来给你写:
    d = dictionary.alias()
    w = word.alias()
    u = user.alias()
    select(
    d.c.id,
    d.c.created_time,
    select(func.count("*").label("word_count")).select_from(w).where(w.c.dictionary_id == d.c.id).subquery(),
    select(u.c.account).where(u.c.id == d.c.creator_id).subquery(),
    ).select_from(d).where(d.c.thesaurus_name == "professional")
    gjquoiai
        32
    gjquoiai  
       2021-09-15 01:07:01 +08:00
    @gjquoiai #31 似乎应该把 subquery 换成 scalar_subquery,如果找不到关联表的话再加一个 correlate (
    yalin
        33
    yalin  
       2021-09-15 03:46:07 +08:00
    sql 一时爽
    thtznet
        34
    thtznet  
       2021-09-15 08:58:35 +08:00
    查询和命令分离,只是查询可以不用 ORM 直接运行 SQL 效率还高,命令涉及到领域持久化,用 ORM 比较适合。
    jakehu
        35
    jakehu  
       2021-09-15 10:58:54 +08:00
    用这个呗,即集成了 SQLAlchemy Core,又可以用原生写法。一个字,好用
    https://github.com/encode/databases
    IvanLi127
        36
    IvanLi127  
       2021-09-15 12:45:51 +08:00 via Android
    从 orm 中提取表名字段名,然后拼接 sql 的时候用上这些值,重构还有希望
    pythonee
        37
    pythonee  
       2021-09-15 15:07:27 +08:00
    @Rwing
    @Pursue9

    我就说,这种写法看着怎么这么爽
    seakingii
        38
    seakingii  
       2021-09-16 10:48:03 +08:00
    复杂的查询可以建存储过程,再调用
    NCZkevin
        39
    NCZkevin  
       2021-09-16 15:39:09 +08:00
    @jakehu 想咨询一下,databases 插入的时候值一定要是字典吗,网上搜 databases 这个词太容易搜到别的东西了,很难搜到相关的。官方例子是
    ```
    values = [
    {"text": "example2", "completed": False},
    {"text": "example3", "completed": True},
    ]
    ```
    能不能支持这种?
    ```
    [
    ["example2",False] ,["example2",True]
    ]
    ```
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   1541 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 30ms · UTC 17:15 · PVG 01:15 · LAX 10:15 · JFK 13:15
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.