SQLAlchemy : object.id getting returned as int in brackets not int

Question

The code is supposed to take a dictionary produced by another method from a CSV - and produce all the necessary customer and order entities.

   def loadOrderData(fileName,session):
    print("Orders loading")
    liDict = Loader.csv2liDict(fileName, {1: "date", 16: "customerName", 22: "customerPostcode", 23: "salesOrderNo",
                                   25: "worksOrderNo", 35: "productCode", 38: "width", 39: "length",
                                   53: "quantity"})
    for i in liDict:
        customerId = -1
        for j in session.query(Customer.id). \
                filter(Customer.name == i["customerName"]). \
                filter(Customer.postcodeDistrict == i["customerPostcode"].split(" ")[0]):
            customerId = j
        if customerId == -1:
            newCustomer = Customer(name=i["customerName"], postcodeDistrict=i["customerPostcode"].split(" ")[0])
            session.add(newCustomer)
            session.commit()
            customerId = newCustomer.id
        print("CUSTOMER ID : ",customerId)
        newOrder = Order(date=str2date(i["date"]), customerId=customerId, salesOrderNo=i["salesOrderNo"],
                         worksOrderNo=i["worksOrderNo"], productCode=i["productCode"], width=int(i["width"]),
                         length=int(i["length"]), quantity=int(i["quantity"]))
        session.add(newOrder)
        session.commit()

I keep getting the following error:

 sqlalchemy.exc.InterfaceError: (raised as a result of Query-invoked autoflush; consider using a session.no_autoflush block if this flush is occurring prematurely) (sqlite3.InterfaceError) Error binding parameter 1 - probably unsupported type. [SQL: 'INSERT INTO "order" (date, "customerId", "worksOrderNo", "salesOrderNo", "productCode", width, length, quantity, assigned) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)'] [parameters: ('2016-10-26 00:00:00.000000', (1,), '', 'S/O269155', 'BKT1', 724, 1769, 0, None)]

Basically, I've deduced it to be due to customerId equalling (1,) instead of being an int. I don't understand why this happening and how to fix it, however. Advice, please.

I don't use sqlalchemy, so I can't be definitive in a fix. But you're not getting an "int in brackets", you're getting a _tuple_. This is standard when you SELECT from SQL databases, so in this case, where ever you pull a value for customerID, you need to take the `[0]` index of what's returned. — roganjosh, Dec 31 '16 at 18:14
With the previous caveat in mind, I _think_ you need to change `customerId = j` to `customerId = j[0]` — roganjosh, Dec 31 '16 at 18:18
I tried that I got the following error : customerId = newCustomer.id[0] TypeError: 'int' object is not subscriptable — Kinando, Dec 31 '16 at 18:36
I didn't suggest putting the index on `newCustomer.id` but rather on `j` — roganjosh, Dec 31 '16 at 18:38
Also, you have a `for` loop that assigns a value to `j` but does nothing with it in its own scope. In which case, if you're getting multiple results, `j` will only assume the last value from your loop. — roganjosh, Dec 31 '16 at 18:40

score 2 · Answer 1 · edited May 23 '17 at 12:25

2

As can be seen here, tuples (1,) are created by separating items with commas. A one item tuple can be created with a trailing comma, and this can often cause confusion.

customerId = 1,

When trying to debug these situations, it is best to concentrate on where the item was created. In the case of your example I would suggest starting in the csv parsing, but since I can't run this code, I can't give anymore specific advice.

Update:

Per roganjosh's comment, the item is being returned by the query. The query interface it setup such that multiple results can be returned, and is thus likely returning a tuple. You should investigate Query.one() or Query.one_or_none.

edited May 23 '17 at 12:25

Community

1
1

answered Dec 31 '16 at 18:22

Stephen Rauch

47,830
31
106
135

3

See my comments under the question. Since it looks like things are being selected from the database via SQLAlchemy, it is standard for this to be returned as a tuple. So I would say it was _much_ more likely to come from that than from the CSV. I'm interpreting `for j in session.query(Customer.id)` as a `SELECT` query, so it wouldn't be at all unexpected to me that it was the cause. – roganjosh Dec 31 '16 at 18:25

score 0 · Accepted Answer · answered Jan 02 '17 at 12:37

I found the answer myself: I duplicated the query for loop and put it at the end of the if customerId == -1 statement, like so:

                for j in session.query(Customer.id). \
                filter(Customer.name == i["customerName"]). \
                filter(Customer.postcodeDistrict == i["customerPostcode"].split(" ")[0]):
            customerId = j[0]
        if customerId == -1:
            newCustomer = Customer(name=i["customerName"], postcodeDistrict=i["customerPostcode"].split(" ")[0])
            session.add(newCustomer)
            session.commit()
            for j in session.query(Customer.id). \
                    filter(Customer.name == i["customerName"]). \
                    filter(Customer.postcodeDistrict == i["customerPostcode"].split(" ")[0]):
                customerId = j[0]

SQLAlchemy : object.id getting returned as int in brackets not int

2 Answers2