Python Postgres - psycopg2.ProgrammingError: no results to fetch

Question

I have been stuck at this since an hour. I am new to postgres. The CRUD operations are too wierd in postgres. I cannot fetch the id of latest inserted row I used the cursor.fetchone()[0] after cur.execute()

My error is : -

   Traceback (most recent call last):
  File "main.py", line 79, in <module>
    shipping_bill_id = cur.fetchone()[0]
psycopg2.ProgrammingError: no results to fetch

I want to get the latest inserted id to make relation between the two tables i want to join. My SQL schema is :-

  create_table "companies", force: :cascade do |t|
    t.string   "iec"
    t.string   "party_name"
    t.boolean  "added_to_crm"
    t.datetime "created_at"
    t.datetime "updated_at"
    t.boolean  "is_active",       default: true
    t.datetime "last_updated_at"
  end
  create_table "shipping_bills", force: :cascade do |t|
    t.integer  "company_id"
    t.float    "fob_value"
    t.string   "fob_currency"
    t.string   "origin_port_name"
    t.string   "destination_port_name"
    t.integer  "origin_port_code"
    t.integer  "destination_port_code"
    t.string   "invoice_number"
    t.string   "shipping_bill_number"
    t.datetime "created_at",            null: false
    t.datetime "updated_at",            null: false
  end
  create_table "shipping_bills_products", force: :cascade do |t|
    t.integer  "shipping_bill_id"
    t.float    "total_price"
    t.text     "name"
    t.float    "unit_price"
    t.string   "unit_currency"
    t.string   "hss_code"
    t.integer  "quantity"
    t.datetime "created_at",       null: false
    t.datetime "updated_at",       null: false
  end

My code :-

    data = {    "products" :
                            {
                              "name": "",
                              "hs_code": "",
                              "unit_price": "",
                              "unit_currency": "",
                              "quantity": "",
                              "total_price": ""
                            }
        }

data["FOB_currency"] = obj["Currency "]
data["origin_port_name"] = "MUNDRA"
data["destination_port_name"] = obj["Port of Discharge"]
data["origin_port_code"] = "INMUN1"
data["destination_port_code"] = get_port_code(obj["Port of Discharge"])
iec = str(int(obj["IEC"]))
if len(iec) == 9 :
    data["IEC"] = "0"+iec
else:
    data["IEC"] = iec

data["products"]["quantity"]  = obj["Quantity "]
data["products"]["unit_price"] = obj["Item rate "]
data["products"]["name"] = obj["Item Desc "]
data["products"]["hs_code"] = int(obj["RITC Code "])
data["products"]["unit_currency"] = obj["Currency "]
data["FOB_value"] = obj["FOB "]
data["shipping_bill_date"] = obj["SB.Date"]
data["shipping_bill_no"] = int(obj["SB.No."])
data["invoice_number"] = obj["Invoice No "]

company_id = None
for e in company_rows:
    if e[1] == data["IEC"]:
        company_id = e[0]

if company_id != None :
    cur.execute("INSERT INTO shipping_bills (company_id,fob_value,fob_currency,origin_port_name,destination_port_name,origin_port_code,destination_port_code,invoice_number,shipping_bill_number,created_at,updated_at) \
                 VALUES  (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)",(company_id,data["FOB_value"],str(data["FOB_currency"]),data["origin_port_name"],data["destination_port_name"],data["origin_port_code"],data["destination_port_code"],data["invoice_number"],data["shipping_bill_no"],datetime.datetime.now(),datetime.datetime.now())) ;

    shipping_bill_id = cur.fetchone()[0]
    total_price = data["products"]["unit_price"]*data["products"]["quantity"]
    cur.execute("INSERT INTO shipping_bills_products (shipping_bill_id,total_price,name,unit_price,unit_currency,hss_code,quantity)  \
                VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s)",(shipping_bill_id,total_price,str(data["products"]["name"]),data["products"]["unit_price"],str(data["products"]["unit_currency"]),data["products"]["hs_code"],data["products"]["quantity"],datetime.datetime.now(),datetime.datetime.now())) ;

    conn.commit()
    print(company_id)

Solution 1

You are missing returning:

cur.execute('''
    INSERT INTO shipping_bills (
        company_id,
        fob_value,
        fob_currency,
        origin_port_name,
        destination_port_name,
        origin_port_code,
        destination_port_code,
        invoice_number,
        shipping_bill_number,
        created_at,
        updated_at
    ) values  (%s,%s,%s,%s,%s,%s,%s,%s,%s,current_timestamp,current_timestamp)
    returning shipping_bill_number
    ''', (
        company_id,data["FOB_value"],
        str(data["FOB_currency"]),
        data["origin_port_name"],
        data["destination_port_name"],
        data["origin_port_code"],
        data["destination_port_code"],
        data["invoice_number"],
        data["shipping_bill_no"]
    )
) ;

Solution 2

For others that come across this:

I had a similar issue of getting this exception on fetchone() for a 'select' statement:

cursori = conn.cursor()
cursori.execute("select * from table where col = 'string'")
while 1:
    data = cursori.fetchone()
    if not data:
        break

    # other code...

Turns out I had made the simple mistake of executing another query with 'cursori' in the 'other code'.

Comments

[Vivek Iyer]

Hey..thanks for the answer ! but i should be returning shipping_bill_id..........right ?!!...because thats what i want to insert it in the shipping_bills_products column

[Clodoaldo Neto]

@VivekIyer: return what you need. I just did not see that column in the shipping_bills table definition.

[sborpo]

Worked for me, specially relevant in multi-threaded application