0

I am querying my SQL table using the code below and converted the result to a list. Why is the list having unwanted commas and parenthesis?

The query result

[(34830,), (34650,), (35050,), (34500,), (35050,), (34500,), (34725,), (34550,), (34725,), (34760,), (34760,)]

It should just return a list with just numbers on it. Right?

The schema is simple (link text, price int);

What is the problem here? Is there something wrong with my code?


    import pymysql

    connection = pymysql.connect(host='localhost',
                             user='root',
                             password='passme',
                             db='hpsize')  # connection obhect to pass the database details


    sql =  "SELECT price FROM dummy WHERE link ='https://www.flipkart.com/bose-noise-cancelling-700-anc-enabled-bluetooth-headset/p/itma57a01d3bd591?pid=ACCFGYZEVVGYM8FP'"


    my_cursor = connection.cursor()

    my_cursor.execute(sql)  

    result = list(my_cursor.fetchall())

    print(result)

    connection.close()

The query result

[(34830,), (34650,), (35050,), (34500,), (35050,), (34500,), (34725,), (34550,), (34725,), (34760,), (34760,)]

Majid Hajibaba
  • 3,105
  • 6
  • 23
  • 55
Kevin Menon
  • 11
  • 1
  • 2
    Those are the representation of `tuple`s. Since a query can return multiple columns, the result is a `list` of `tuple` even if the `tuple` is only one element in length. – Axe319 Jun 10 '21 at 10:24
  • 1
    Because `cursor.fetchall()` fetches all the rows of a query result. It returns all the rows as a list of tuples. – Indra Kumar S Jun 10 '21 at 10:24
  • [Related](https://stackoverflow.com/questions/7011291/how-to-get-a-single-result-from-a-sql-query-in-python?r=SearchResults&s=3|17.7034), though focussed more on single-row results. – snakecharmerb Jun 10 '21 at 10:28
  • 1
    To answer your question you could convert it with a simple comprehension. `[i[0] for i in result]` – Axe319 Jun 10 '21 at 10:34
  • @Axe319 Thanks for the help. I appreciate it. – Kevin Menon Jun 10 '21 at 10:48

1 Answers1

0

try

connection.row_factory = lambda cursor, row: row[0]

instead of list(my_cursor.fetchall())

then

result = connection.execute("""SELECT * FROM dummy""").fetchall()

or you can also use strip() to cut the unwanted part

Josef
  • 2,869
  • 2
  • 22
  • 23
user16335562
  • 5
  • 1
  • 6