我创建了一个字典,如下所示:
dict = { 'NASDAQ': {'AMZN%', 'AAPL%', 'ABNB%'}, 'TSX': {'SHOP%', 'L%', 'RY%', 'XIF%'}}
我想在查询中编写一个for循环,从表TICKER_TABLE
中获取数据,其中包含dict
值的列TICKER
。
我正在编写的查询是一个更广泛的Python循环的一部分,看起来像这样:
for key in dict.keys():
query = """SELECT * FROM "TICKER_TABLE"
WHERE "TICKER" LIKE (FOR ITEM IN dict.VALUES)""""
有办法做到这一点吗?
如果TICKER_TABLE和TICKER是变量,使用f在string和{}之前添加变量。我不是很精通sql,所以我将在python中使用一个额外的for循环,虽然它可能可以做得更好,但我不知道你实际需要什么:
for key in dict:
for value in dict[key]:
query = f"SELECT * FROM {TICKER_TABLE}
WHERE {TICKER} LIKE {value}"
second for循环的每一次迭代到AMZN%, AAPL%, SHOP%等
也许这对你有帮助。
如果您真的想使用SQLAlchemy完成此操作,您可以这样做,将"like "将每个键转换为单个查询:
import sqlalchemy as sa
engine = sa.create_engine(<connection_url>, echo=True, future=True)
# Reflect the database table into an object.
tbl = sa.Table('ticker_table', sa.MetaData(), autoload_with=engine)
with engine.connect() as conn:
for k, v in dict_.items():
q = sa.select(tbl).where(sa.or_(tbl.c.ticker.like(t) for t in v))
rows = conn.execute(q)
for row in rows:
print(row)
生成如下SQL:
SELECT ticker_table.id, ticker_table.ticker
FROM ticker_table
WHERE ticker_table.ticker LIKE ?
OR ticker_table.ticker LIKE ?
OR ticker_table.ticker LIKE ?
OR ticker_table.ticker LIKE ?
如果您不需要按键处理结果,您可以在单个查询中获得所有结果:
import functools
import sqlalchemy as sa
# Merge the dictionary values into a single set.
vals = functools.reduce(set.union, dict_.values(), set())
with engine.connect() as conn:
q = sa.select(tbl).where(sa.or_(tbl.c.ticker.like(t) for t in vals))
rows = conn.execute(q)
for row in rows:
print(row)
print()