SQLAlchemy 反射:如何从特定列查询数据?

2021-12-30 00:00:00 python sqlalchemy mysql

使用 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()

上述查询为我提供了 members 表中所有列中的所有数据.然而，我想要的是从列中获取特定数据.例如.我想检索 username 和 password 列，但我无法获得正确的语法.以下是我目前所拥有的:

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!")

相关文章