The difference between fetchone and fetchall in python operation database to get results

 

Every time I use python to get query results, I will struggle for a while to use fetchone and fetchall. It is easy to report errors if they are not used properly. The key is to not understand the difference and usage scenarios between them.

Difference between fetchone and fetchall

Environment: python3

fetchone

Regardless of whether the query result is multiple pieces of data or a single piece of data, using fetchone is always a tuple .

If the query result is a single piece of data: a tuple of a single piece of data obtained by fetchone;

If the query result is multiple pieces of data: fetchone is a tuple consisting of the first piece of data in the result by default;

This determines that if you need to fetch the value in the tuple, you need to use cur.fetchone[0]

fetchall

Regardless of whether the query result is multiple pieces of data or a single piece of data, using fetchall is always a list of tuples.

If the query result is a single piece of data: fetchall gets a list consisting of a single tuple, and the list is a tuple consisting of a single piece of data, that is, the list contains tuples;

If the query result is multiple pieces of data: fetchall gets a list consisting of multiple tuples;

This determines that if you need to fetch the value in the tuple, you need to use cur.fetchone[0][0]

scenes to be used

Generally speaking, the query result set is a single piece of data, use fetchone to get the data

Generally speaking, the query result set contains multiple pieces of data, use fetchall to get the data

Simple example

  1. import cx_Oracle
  2. conn = cx_Oracle.connect( "username/[email protected] address" )
  3. cur = conn.cursor()
  4. sql_3 = "select id from CZEPT_BSDT t WHERE name='{}'" . format ( "Basic Expenditure Adjustment" )
  5. cur.execute(sql_3)
  6. result_3 = cur.fetchone() # single data result set
  7. print(result_3) # (1,)
  8. print(type(result_3)) # <class 'tuple'>
  9. result_3= result_3[0]
  10. print(result_3) # 1
  11. print(type(result_3)) # <class 'int'>
  12. print("*" * 50)
  13. sql_2 = "select * from CZEPT_BSDT "
  14. cur.execute(sql_2)
  15. result_2 = cur.fetchall() # Multiple data result sets
  16. print (result_2) # [(1,'Basic expenditure adjustment'),(3,'Bank account approval'),(5,'Project expenditure adjustment')]
  17. print(type(result_2)) # <class 'list'>
  18. result_2= result_2[0][0]
  19. print(result_2) # 1
  20. print(type(result_2)) # <class 'int'>

Precautions

For the results obtained by using fetchone and fetchall, it is best to judge non-null before using, otherwise, when the data in the tuple is obtained when there is a null value, an exception of "exceeding the index" will be reported. Stepped on the mine pit many times.

  1. import cx_Oracle
  2. connection = cx_Oracle.connect( 'username/[email protected] address' )
  3. cur = connection.cursor()
  4. for j in data_list:
  5. sql = "select guid from jczl.division where name='{}'".format(j['DIVISIONNAME'])
  6. cur.execute(sql)
  7. result = cur.fetchone()
  8. # Because the focal point room is empty, it always reports that it exceeds the index range when slicing, which takes a long time
  9. if result is not None:
  10. j['DIVISIONGUID'] = str(result[0])

 

Related: The difference between fetchone and fetchall in python operation database to get results