英文:
SqlAlchemy concat two columns for `ilike` query
问题
I'm trying to recreate the following (PostgreSQL) query in SqlAlchemy:
select
u.first_name,
u.last_name,
from users u
where ((u.first_name ||' '|| u.last_name) ilike '%Mark Zuckerberg%')
Essentially I'm concatenating the two columns then searching by the full name, which in my case is passed in via a user query.
Is there a way to do this in SqlAlchemy?
英文:
I'm trying to recreate the following (PostgreSQL) query in SqlAlchemy:
select
u.first_name,
u.last_name,
from users u
where ((u.first_name ||' '|| u.last_name) ilike '%Mark Zuckerberg%')
Essentially I'm concatenating the two columns then searching by the full name, which in my case is passed in via a user query.
Is there a way to do this in SqlAlchemy?
答案1
得分: 3
以下是翻译好的部分：
from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class User(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True)
first_name = Column(String)
last_name = Column(String)
# 创建SQLAlchemy引擎
engine = create_engine('postgresql://username:password@host:port/database_name')
# 创建一个会话
Session = sessionmaker(bind=engine)
session = Session()
# 连接first_name和last_name列
full_name = User.first_name + ' ' + User.last_name
# 执行查询
query = session.query(User.first_name, User.last_name).filter(full_name.ilike('%Mark Zuckerberg%'))
# 检索结果
results = query.all()
# 打印结果
for first_name, last_name in results:
print(f'First Name: {first_name}, Last Name: {last_name}')
英文:
Example, how you can do it
from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class User(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True)
first_name = Column(String)
last_name = Column(String)
# Create the SQLAlchemy engine
engine = create_engine('postgresql://username:password@host:port/database_name')
# Create a session
Session = sessionmaker(bind=engine)
session = Session()
# Concatenate the first_name and last_name columns
full_name = User.first_name + ' ' + User.last_name
# Perform the query
query = session.query(User.first_name, User.last_name).filter(full_name.ilike('%Mark Zuckerberg%'))
# Retrieve the results
results = query.all()
# Print the results
for first_name, last_name in results:
print(f'First Name: {first_name}, Last Name: {last_name}')
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论