How manipulate query results from cx_Oracle

Question

I've trying to create a list with the results from a column. I execute the query and the result are okay, but when I input the data in a list using 'list.append' they return just '[]'.

import cx_Oracle
con = cx_Oracle.connect('user/psw@localhost')

cur = con.cursor()
list = []

cur.execute('select column from table')
for result in cur.fetchall():
    list.append
    print (list)

cur.close()
con.close()

Return:

[]
[]
[]

When I put the results in to a list and return an index, like results[0] they just print the first letter of the results like:

E
X
A
M
P
L
E

When I just expecting:

Example

There's something wrong with my code? Best Regards, community!


Show source
| oracle   | python   | cx-oracle   2017-08-08 22:08 2 Answers

Answers to How manipulate query results from cx_Oracle ( 2 )

  1. 2017-08-08 23:08

    If you attempt to run the sql select statement from some tool like DB visualizer what does your results look like? That would simply confirm that there is nothing wrong with your sql statement.

    In a project I use cx_Oracle on I have a function used for querying my oracle db which is as shown below. You can pass the sql statment as sql to the function.

    def execute_query_result_set(sql, args=(), one=False):
        print "execute_query_result_set()"
        con = ""
        con = cx_Oracle.connect(_connection_string)
    
        try:
            cursor = con.cursor()
            cursor.execute(sql)
            results = cursor.fetchall()
            return results
        finally:
            if con != "":
                con.close()
    

    This function returns a list of the results of your query. If you are selecting more than just one column it will return as a list of tuples [('value', 'other'), ('another', 'row'), ('some', 'more'), ('etc', 'etc')]

    If are not looking to have a separate function to call, you could simply just do

    con = cx_Oracle.connect(your_db_connection_string)
    cursor = con.cursor()
    cursor.execute(your_sql_select_statement)
    results = cursor.fetchall()
    con.close()
    

    Then the variable results should be a list of your results, and then you can do whatever you need with that list. Hopefully that will help

  2. 2017-08-08 23:08

    There are a couple different issues with your code.

    1)

    list.append
    

    Append what? append is a method on list objects, and you need to call it with an argument. In your case,

     list.append(result)
    

    would do what you would expect - it appends result to the list called list.

    Note: The reason this doesn't produce an error is that you don't even call the append function, you just reference it. And in Python it's legal for an instruction to be just an expression - the result of that expression then will get silently discarded when running as a script (as opposed to the interactive interpreter, where the results of expressions will be automatically printed for you).

    So for example having 42 == 7 on a single line in a script is perfectly legal. It won't do anything (the result of that expression, False, will be silently discarded), but it also won't cause an error.

    2)

        print (list)
    

    You're doing this inside the loop, meaning this will be executed once for every iteration of the loop - 3 times in your example, because apparently select column from table gives three results. Number 1) above is why you get an empty list ([]), and this is why you get the empty list three times ([] [] []).

    You'll probably want to move this outside the loop by decreasing it's indenation (move it 4 spaces to the left) - then the list's contents will only be printed once, at the end of the loop.

    3)

    When I put the results in to a list and return an index, like results[0] they just print the first letter of the results like [...]

    I don't fully understand what exactly you mean by that, but:

    In Python, strings, just like lists, are also sequences (a string is a sequence of characters). That means that some operations can be done on both, like indexing (the sequence[index] syntax) or looping over them (for item in sequence).

    Now, if you ever get a result like

    E
    X
    A
    M
    P
    L
    E
    

    in Python, that means that you treated an object that really is a string in a way that suggests you were assuming it was a list (or some other kind of sequence). For example, if

    for item in results:
        print(item)
    

    or

    for i in range(len(results)):
        print(results[0])
    

    leads to this behavior, then results is actually a string, and not a list as you expected.


    So to summarize, you probably want something like this:

    # ...
    
    results = []
    
    cur.execute('select column from table')
    for row in cur.fetchall():
        results.append(row)
    
    # print the entire list
    print(results) 
    
    # print the first row by addressing it via index
    print(results[0])
    
    # print the first column of the third row
    print(results[2][0])
    
    # ...
    

    Note though, that cursor results always will be database rows - even if you just select one column.

Leave a reply to - How manipulate query results from cx_Oracle

◀ Go back