SQLAlchemy反思:如何查询特定列中的数据? [英] SQLAlchemy Reflection: How do I query data from specific columns?
问题描述
使用SQLAlchemy反射,如何查询特定列中的数据?
Using SQLAlchemy reflection, how do I query for data in specific column?
testtable = Table('member', Metadata, autoload=True)
def TestConnection():
data = None
loopCounter = 0
for data in session.query(testtable).filter_by(is_active=1, is_deleted=0):
print(loopCounter + 1, data)
loopCounter += 1
if data is None:
raise Exception ("Could not find any data that matches your query")
else:
print("It worked!")
TestConnection()
上面的查询为我提供了成员表中所有列的所有数据.但是我想要从列中获取特定数据.例如.我想检索用户名和密码列,但我无法正确获取语法.以下是我到目前为止的内容:
The above query gives me all the data in all columns in the members table. What I want however is to get specific data from columns. E.g. I want to retrieve the username and password columns but I just can't get the syntax right. Below is what I have so far:
def TestConnection():
loopCounter = 0
for password, username in session.query(testtable).filter_by(is_active=1, is_deleted=0):
print(loopCounter + 1, data)
loopCounter += 1
if data is None:
raise Exception ("Could not find any data that matches your query")
else:
print("It worked!")
失败并显示以下错误:
Traceback (most recent call last):
File "/home/workspace/upark/src/monitor.py", line 36, in <module>
TestConnection()
File "/home/workspace/upark/src/monitor.py", line 26, in TestConnection
for password, username in session.query(testtable).filter_by(is_active=1, is_deleted=0):
ValueError: too many values to unpack (expected 2)
我正在使用Oracle的Python3.2,SQLAchemy0.8和mysqlconnector.
Am working with Python3.2, SQLAchemy0.8 and mysqlconnector from Oracle.
一些轻微的进展
刚刚发现在返回所有结果之后,我可以过滤"出这些列,如下所示:
Just discovered that I can "filter" out the columns after all results have been returned as follows:
def TestConnection():
data = None
loopCounter = 0
for data in session.query(testtable).filter_by(is_active=1, is_deleted=0):
print(loopCounter + 1, data.password, data.username)
loopCounter += 1
if data is None:
raise Exception ("Could not find any data that matches your query")
else:
print("It worked!")
这将给出:
1 pass1 userone
2 pass2 usertwo
但是正如您所看到的,那是在我把所有列都拿回来之后.我想要的是仅从我需要的列中获取数据.例如.成员表有10列.我只需要从其中两个中获取数据即可.
But as you can see, that is after I've gotten all the columns back. What I want is to fetch data from only the columns I need. E.g. The Members table has got 10 columns. I only need to get data from two of those for efficiency.
推荐答案
只需指定要选择[session.query(testtable.c.password, testtable.c.username)
]的列而不是整个表[session.query(testtable)
]:
Just specify the columns to select [session.query(testtable.c.password, testtable.c.username)
] instead of the whole table [session.query(testtable)
]:
def TestConnection():
data = None
loopCounter = 0
for data in session.query(testtable.c.password, testtable.c.username).filter_by(is_active=1, is_deleted=0):
pwd, usr = data
print(loopCounter + 1, pwd, usr)
loopCounter += 1
if data is None:
raise Exception ("Could not find any data that matches your query")
else:
print("It worked!")
这篇关于SQLAlchemy反思:如何查询特定列中的数据?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!