Is it possible to assign cursor.fetchall() to a variable?-CodePudding

rows_order = "SELECT COUNT (*) FROM 'Order'"
            cursor.execute(rows_order)
            ordernum = cursor.fetchall()
            connection.commit()

cursor.execute("INSERT INTO 'Order' (OrderNo, CustomerID, Date, TotalCost) VALUES (?,?,?,?)", (
                [ordernum], custid_Sorder, now, total_item_price))

This is what I am trying but this error popped up;

sqlite3.InterfaceError: Error binding parameter 0 - probably unsupported type.

How do I fix this? I want to make it so the OrderNo is = to the amount of orders before it, hence why I want to assign the orderno to it. (I am using sqlite3)

CodePudding user response：

as you have only one value you need only fetchone

import sqlite3
con = sqlite3.connect("tutorial.db")
cursor = con.cursor()
rows_order = "SELECT COUNT (*) FROM 'Order'"
cursor.execute(rows_order)
ordernum  = cursor.fetchone()[0]
cursor.execute("INSERT INTO 'Order' (OrderNo, CustomerID, Date, TotalCost) VALUES (?,?,?,?)", (
                ordernum, custid_Sorder, now, total_item_price))

CodePudding user response：

tl;dr Don't do this. Use an auto-incremented primary key.

fetchall returns all rows as a list, even if there is only one row.

Instead, use fetchone. This will return a single tuple which you can then select the first item. ordernum = cursor.fetchone()[0]

However, you appear to be writing a query to get the next ID. Using count(*) is wrong. If there are any gaps in OrderNo, for example if something gets deleted, it can return a duplicate. Consider [1, 3, 4]; count(*) will return 3. Use max(OrderNo) instead.

Furthermore, if you try to insert two orders at the same time you might get a race condition and one will try to duplicate the other.

process 1                  process 2
select max(orderNo)
fetchone # 4
                           select max(orderNo)
                           fetchone # 4
insert into orders...
                           insert into orders...  # duplicate OrderNo

To avoid this, you have to do both the select and insert in a transaction.

process 1                  process 2
begin
select max(orderNo)...
fetchone # 4               begin
                           select max(orderNo)
                           fetchone
insert into orders...      # wait
commit                     # wait
                           # 5
                           insert into orders...
                           commit

Better yet, do them as a single query.

insert into "Order" (OrderNo, CustomerID, Date, TotalCost)
select max(orderNo), ?, ?, ?
from "order"

Even better don't do it at all. There is a built-in mechanism to do this use an auto-incremented primary keys.

-- order is a keyword, pluralizing table names helps to avoid them
create table orders (
  -- It is a special feature of SQLite that this will automatically be unique.
  orderNo integer primary key
  customerID int,
  -- date is also a keyword, and vague. Use xAt.
  orderedAt timestamp,
  totalCost int
)

-- orderNo will automatically be set to a unique number
insert into orders (customerID, orderedAt, totalCost) values (...)