How to output data from database to python variable?

Question

Python

Part of the code where the question was raised:

global ID
ID = cursor.execute("""SELECT OrderNumber FROM orders ORDER BY OrderNumber DESC LIMIT 1;""").fetchall()
ID = ID[0] #list to tuple
ID = ID [0] #tuple to char
ID = int(ID) #char to int cursor.execute
("UPDATE orders SET cost = ? WHERE OrderNumber = ?", (cost, ID))
connect.commit()

I managed to put the data from the database into a variable, which must be Integer, so that the next update request would work correctly. The most important thing is that in the database this field is already Integer. The code works, but I want to figure out if it can be simplified and made right?

Answer 1

1. stop using global, that's all
2. why do you need the last OrderNumber? An obvious problem in the logic of work
3. if you need one line from the base, use fetchone, not fetchall, then you will immediately have a tuple
4 # list to tuple first tuple from lisr, # tuple to char first element from tuple and you can simply replace it with 5. OrderNumber does not need to be made int, the required type is already there ID = ID[0][0]

Answer 2

In addition to what @Dr. Bacon, you can do this.
connect.row_factory = sqlite3.Row
Then, instead of simple tuples, objects similar to dictionaries will be returned, and it will be possible to refer to fields by name.

cursor.execute("""SELECT OrderNumber FROM orders ORDER BY OrderNumber DESC LIMIT 1;""")
row = cursor.fetchone()
ID = row['OrderNumber']

The only thing is that changing the row_factory will only affect cursors created later. Cursors created earlier will use the previous row factory. So if you want to do this, it's better to change the row_factory immediately after opening the connection.