BEGIN NOW

fix no From Error in sqlalchemy

代码

'''
  Note: Code execute in this environment:
    python version: 3.8.5
    sqlalchemy version: 2.0.27
'''

from typing import List
from typing import Optional
from sqlalchemy import ForeignKey
from sqlalchemy import String
from sqlalchemy.orm import DeclarativeBase
from sqlalchemy.orm import Mapped
from sqlalchemy.orm import mapped_column
from sqlalchemy.orm import relationship

class Base(DeclarativeBase):
    pass

class User(Base):
    __tablename__ = "user_account"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(30))
    fullname: Mapped[Optional[str]]
    addresses: Mapped[List["Address"]] = relationship(
        back_populates="user", cascade="all, delete-orphan"
    )
    def __repr__(self) -> str:
        return f"User(id={self.id!r}, name={self.name!r}, fullname={self.fullname!r})"

class Address(Base):
    __tablename__ = "address"
    id: Mapped[int] = mapped_column(primary_key=True)
    email_address: Mapped[str]
    user_id: Mapped[int] = mapped_column(ForeignKey("user_account.id"))
    user: Mapped["User"] = relationship(back_populates="addresses")
    def __repr__(self) -> str:
        return f"Address(id={self.id!r}, email_address={self.email_address!r})"


from sqlalchemy import create_engine
engine = create_engine("sqlite://", echo=False)

Base.metadata.create_all(engine)

from sqlalchemy.orm import Session

with Session(engine) as session:
    spongebob = User(
        name="spongebob",
        fullname="Spongebob Squarepants",
        addresses=[Address(email_address="spongebob@sqlalchemy.org")],
    )
    sandy = User(
        name="sandy",
        fullname="Sandy Cheeks",
        addresses=[
            Address(email_address="sandy@sqlalchemy.org"),
            Address(email_address="sandy@squirrelpower.org"),
        ],
    )
    patrick = User(name="patrick", fullname="Patrick Star")
    session.add_all([spongebob, sandy, patrick])
    session.commit()

# with Session(engine) as session:
    # print(session.query(User).filter(User.name == 'sandy').all())

def raise_error():
    from sqlalchemy import exists, and_

    print(session.query(User).join(Address, Address.user_id == User.id)
            .filter(exists().where(
                and_(Address.email_address.like('%sqlalchemy%'), Address.user_id == User.id)
            )).all())

def fix_error():
    # The answer: https://stackoverflow.com/questions/35690518/sqlalchemy-query-filter-returned-no-from-clauses-due-to-auto-correlation
    from sqlalchemy import exists, and_
    from sqlalchemy.orm import aliased

    aliasedAddr = aliased(Address)
    print(session.query(User).join(Address, Address.user_id == User.id)
            .filter(exists().where(
                and_(aliasedAddr.email_address.like('%sqlalchemy%'), aliasedAddr.user_id == User.id)
            )).all())

if __name__ == "__main__":
    try:
        raise_error()
    except Exception as e:
        print(e)
        fix_error()

说明

执行raise_error函数会报错

sqlalchemy.exc.InvalidRequestError: Select statement '' returned no FROM clauses due to auto-correlation; specify correlate() to control correlation manually.

解释

实际上exist语句是一个子查询(subquery), 在subquery中不允许重复查询主查询中存在的表(Calling the same table twice (FROM Revision) in the same query is not allowed unless you use an alias.)

在windows wsl2 中使用代理

创建proxy.sh

touch ~/proxy.sh

# The content of proxy.sh

export hostip=$(ip route | grep default | awk '{print $3}')
export hostport=10810
alias proxy='
    export HTTPS_PROXY="socks5://${hostip}:${hostport}";
    export HTTP_PROXY="socks5://${hostip}:${hostport}";
    export ALL_PROXY="socks5://${hostip}:${hostport}";
    echo -e "Acquire::http::Proxy \"http://${hostip}:${hostport}\";" | sudo …

在zsh中配置fzf

Published: Fri 31 March 2023
By luyangong

In article.

fzf简介

fzf是一个命令行模糊查询的工具。主要使用go语言实现。它提供一个友好的命令行模糊查询的界面，按照它的格式要求传参后，可以很好实现先搜索再操作，即"search …
read more

Vim配置文件.Vimrc

" 底部操作指令Tab自动补全

set wildmenu
set wildmode=longest:list,full

set smarttab
set showmode
set number
set nocompatible

" 设置tab符长度为4个空格
set tabstop=4

" 设置换行自动缩进长度为4个空格
set shiftwidth=4

" 设置tab符自动转换为空格
set expandtab

" 设置 …

Python中的迭代器、生成器

from collections import Iterable, Iterator

1. 可迭代(iterable)对象

参考官网链接

class I:
    def __init__(self, v):
        self.v = v

    def __iter__(self):
        return iter([self.v])  # 返回的应该是迭代器对象

i = I(2)
print(isinstance …

BEGIN NOW

Other articles

收集记录有用的链接

about vim

1. How to config the ctags in vim

2. awesome vim plugins

about python

1. inside the python virtual machine(a book)

about compiler or interpreter

在windows wsl2 中使用代理

创建proxy.sh

在zsh中配置fzf

fzf简介

Vim配置文件.Vimrc

Python中的迭代器、生成器

1. 可迭代(iterable)对象

Other articles

about vim

1. How to config the ctags in vim

2. awesome vim plugins

about python

1. inside the python virtual machine(a book)

about compiler or interpreter

创建proxy.sh

fzf简介

1. 可迭代(iterable)对象

links

social