英文:
Why does my query to AWS Keyspaces using the Cassandra Python driver return an empty list?
问题
以下是翻译好的部分：
我正在尝试使用预准备语句通过Python的Cassandra驱动程序查询keyspaces。
这是查询及其准备部分...
from cassandra.cluster import Cluster
cluster = Cluster(
**conn_details
)
session = cluster.connect("mykeyspace")
query = ("SELECT timestamp "
"FROM mykeyspace.mytable "
"WHERE t_id='123' "
"AND p_id='321' "
"AND timestamp IN ? "
)
prepared_statement = session.prepare(query)
session.execute(prepared_statement, parameters=[(1677145736507, 1677145728972)]).current_rows
输出是一个空列表。存在一些与语句绑定相关的问题，因为我可以在以下三种情况下成功运行CQL IN...
如果我通过`session.execute(<raw query string>)`运行以下原始查询，我可以获得响应...
SELECT timestamp
FROM mykeyspace.mytable
WHERE t_id='123'
AND p_id='321'
AND collection_event_timestamp IN (1677145728972, 1677145736507)
如果我在AWS的keyspaces查询编辑器中运行，我会得到预期的响应
[![enter image description here][1]][1]
我唯一能够让IN与任何参数化一起使用的方式是通过字符串格式化...
id_tuples = (1677145736507, 1677145728972)
query = "SELECT timestamp FROM mykeyspace.mytable WHERE t_id='123' AND p_id='321' AND timestamp IN %s "
session.execute(query, parameters=[ValueSequence(id_tuples)]).current_rows
[![enter image description here][2]][2]
是否有人可以提供一些建议，解释这里出了什么问题？为什么预准备语句方法不起作用？
[1]: https://i.stack.imgur.com/Ba3uq.png
[2]: https://i.stack.imgur.com/dIe59.png
请注意，我已经将原文中的HTML编码转义字符还原为相应的字符，以便更好地理解翻译。
英文:
I am trying to use a prepared statement to query keyspaces via python's cassandra driver.
This is the query and it's preparation...
from cassandra.cluster import Cluster
cluster = Cluster(
**conn_details
)
session = cluster.connect("mykeyspace")
query = ("SELECT timestamp "
"FROM mykeyspace.mytable "
"WHERE t_id='123' "
"AND p_id='321' "
"AND timestamp IN ? "
)
prepared_statement = session.prepare(query)
session.execute(prepared_statement, parameters=[ (1677145736507, 1677145728972) ]).current_rows
The output is an empty list. There is some issue with the statement binding as I'm able to run the CQL IN with success in three scenarios below... i.e. if I run the below raw query via session.execute(<raw query string>) I can get a response..
SELECT timestamp
FROM mykeyspace.mytable
WHERE t_id='123'
AND p_id='321'
AND collection_event_timestamp IN (1677145728972, 1677145736507)
If I run inside the keyspaces query editor on AWS I get the expected response
The only way I can get IN to work with any parameterisation is via string formatting..
id_tuples = (1677145736507, 1677145728972)
query = "SELECT timestamp FROM mykeyspace.mytable WHERE t_id='123' AND p_id='321' AND timestamp IN %s "
session.execute(query, parameters=[ValueSequence(id_tuples)]).current_rows
Does anyone have any advice as to what is going wrong here? Why is the prepared statement approach not working?
答案1
得分: 2
在Python中使用IN运算符创建预定义语句时，您需要将变量定义为字典，而不是元组：
pStatement = session.prepare("""
SELECT * FROM sales.emp WHERE empid IN ?;
""")
employees = {99, 68}
rows = session.execute(pStatement, [employees])
print("Employee data:\n")
for row in rows:
print(row)
% python testDBIN.py
Employee data:
Row(empid=99, first_name='Wayne', last_name='Gretzky')
Row(empid=68, first_name='Jaromir', last_name='Jagr')
IN运算符只在分区列上工作吗？我尝试在聚集列上进行过滤
好的，这意味着您试图运行一个表不支持的查询。IN运算符可以在分区列和聚集列上使用。
但是，它仅在指定了所有先前的键时才能在聚集列上使用。我看不到您上面的表定义，但我看到您在过滤t_id和p_id。不知道它们是否是主键定义中的先前键。
另一种可能性是，也许这是亚马逊Keyspaces与Apache Cassandra不同的领域之一。在Cassandra中有效但在Keyspaces中失败的语法和编程方法非常常见。不幸的是，这两者并不完全兼容。
英文:
When creating a prepared statement in Python using the IN operator, you'll need to define your variable as a dictionary instead of a tuple:
pStatement = session.prepare("""
SELECT * FROM sales.emp WHERE empid IN ?;
""")
employees = {99, 68}
rows = session.execute(pStatement,[employees])
print("Employee data:\n")
for row in rows:
print(row)
% python testDBIN.py
Employee data:
Row(empid=99, first_name='Wayne', last_name='Gretzky')
Row(empid=68, first_name='Jaromir', last_name='Jagr')
> Does IN only work on partition columns? I'm trying to filter on a clustering column
Ok, so that means that your're trying to run a query which the table wasn't built to support. The IN operator does work on both partition and clustering columns.
But, it only works on clustering columns IF all preceeding keys are also specified. I don't see your table definition above, but I do see that you're filtering on t_id and p_id. No idea if those are the preceeding keys in the PRIMARY KEY definition or not.
The other possibility, is that perhaps this is one of those areas where Amazon Keyspaces != Apache Cassandra. Seeing syntax and programatic approaches that work in Cassandra but fail in Keyspaces is quite common. Unfortunately, the two are not 100% compatible.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论