0

I am executing a select query which would return a single column and I am trying to convert the returned Row object to tuple. But I seem to be getting below error:

    ids = sum(tuple(conn.execute('select id from some_database.some_table')), ()) 
TypeError: can only concatenate tuple (not "RowProxy") to tuple

Sample Code:

with sqlalchemy_engine.connect() as conn:
    ids = sum(tuple(conn.execute('select id from some_database.some_table')), ())   # statement causing error
    print(ids)

Expected Output:

('123','456','789')

I can iterate output of select query and append/print the values one by one, but would rather use the one-liner

Edit:

The column id in query select id from some_database.some_table has alphanumeric values. So the expected output could be: ('ff123', 'df456', 'gv789')

s.k
  • 193
  • 1
  • 2
  • 15
  • Does [this](https://stackoverflow.com/questions/9486180/sql-alchemy-orm-returning-a-single-column-how-to-avoid-common-post-processing) answer your question? (If I understand your question correctly, you want to flatten the collection of tuples into a collection of `int`s) – snakecharmerb Mar 04 '21 at 09:15
  • Hi, @snakecharmerb, Output of `tuple(conn.execute('select id from some_database.some_table')` is `(('123',), ('456',))`. I want to convert it to `('123','456')` without changes it from string to int as I am using these value in later sql query. Example: `select *** from *** where id in ('123','456')` – s.k Mar 04 '21 at 09:26
  • So `ids = tuple(r for r, in conn.execute(...))`? – snakecharmerb Mar 04 '21 at 09:28
  • Yeah, like I mentioned in my question, I can use for loop, but what I want to know is, why this didn't work - `sum(tuple(conn.execute('select id from some_database.some_table')), ())` – s.k Mar 04 '21 at 09:30

1 Answers1

0

First of all, you have wrong parenthesis placement.

sum(tuple(conn.execute('select id from some_database.some_table')), ())

Should be fixed to

sum(tuple(conn.execute('select id from some_database.some_table'), ()))

This will make the tuple call successful. Since you were calling tuple(conn.execute(...), ()) it indeed tried to concatenate the result of conn.execute and a ().

NOTE that converting your RowProxy to a tuple, will make a result mentioned in the comments - (('123',), ('456',)), so you could better use a map to unpack the values and make them flat:


value = sum(
    map(
        lambda x: x[0],
        conn.execute(..., ())
    )
)
Icebreaker454
  • 1,031
  • 7
  • 12
  • Hi @Icebreaker454, your solution `sum(tuple(conn.execute('select id from some_database.some_table'), ()))` caused error: `TypeError: tuple() takes at most 1 argument (2 given)` – s.k Mar 06 '21 at 14:54
  • And the lambda solution u mentioned caused error: `TypeError: unsupported operand type(s) for +: 'int' and 'str'` This is the code I tried: `sum(map(lambda x: x[0], conn.execute('select id from some_database.some_table', ())))` – s.k Mar 06 '21 at 15:00
  • I suppose for lambda you should try to make the result an integer (or whatever datatype you have on your db) like this `lambda x: int(x[0])` – Icebreaker454 Mar 06 '21 at 19:22
  • Hi, please check the edit, output column has alphanumeric values, so casting is not an option. – s.k Mar 07 '21 at 06:31
  • You can't possibly sum string values, so you have to write some kind of filtering function. E.g. having a value `ab123`, this function can decide how to extract any number info from it. It would be named like `def predicate(value):`, and you can feed it in place of lambda to the map. – Icebreaker454 Mar 07 '21 at 15:03