Error in inserting new data with Oracle and sqlalchemy

Question

I have a 1-1 relationship of two tables/objects, using flask-sqlalchemy. I am trying to insert a new row for one of them (referencing through a FK a row in the first table) and it fails. Same code used to work with sqlite but stopped working with Oracle 12.2.x.

class SomeData(db.Model):
     id = db.Column("id", db.Integer, db.Sequence('id_seq', start=1), 
                           primary_key=True)
     company_data_label = db.Column(db.String(24), 
     db.ForeignKey('company.dataLabel'), nullable=False)
     ...more data

     @staticmethod
     def new_from_dict(data):
           data = SomeData(
                 company=data["company"],
                 more fields           
     )
     return data

class Company(db.Model):
     id = db.Column("id", db.Integer, db.Sequence('id_seq', start=1), 
               primary_key=True)
     employee_id = db.Column(db.Integer, db.ForeignKey('analyst.id'), 
                 nullable=True)
     data = db.relationship('SomeData', backref='company', lazy=True)
     dataLabel = db.Column(db.String(24), nullable=False, unique=True)

I have a row in the Company table where I can refer and I am simply trying to add a row in the SomeData:

new_datum = SomeData.new_from_dict(row_dict)
current_app.logger.debug(new_datum)
get_db().session.add(new_datum)
get_db().session.commit()

where row_dict is a dictionary which contains a "company"= a retrieved Company object from the DB.

The error I am getting is:

 ERROR in models: There were database errors: data not written in DB: 
(cx_Oracle.DatabaseError) DPI-1043: invalid number [SQL: 'INSERT INTO 
some_data (id, company_data, ....truncated.... , ***'ret_0': 
<cx_Oracle.STRING with value [[], [], [], [], [], [], [], [], [], [], [], 
[], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], 
[], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], 
[]]>,*** '"date"': datetime.datetime(2019, 5, 10, 1, 4, 5)}] (Background on 
 this error at: http://sqlalche.me/e/4xp6).

Any ideas please?

I'm not an expert, but I'd imagine that SQL cannot interpret raw datetime objects. — moltarze
– moltarze, Commented May 10, 2019 at 10:44
thanks for the response. hmm ok I will check but sqlite was working fine out of the box with same schema. — staonas
– staonas, Commented May 10, 2019 at 10:56
No I turn all dates to iso strings so this should be fine. I think it has to do with a NULL ID. For some reason it seams that insert doesn't provide an ID for the newly created object. — staonas
– staonas, Commented May 10, 2019 at 11:02
I doubled checked the dates issue, it is not. Oracle can handle datetime objects. It is this error that seems to be off: ret_0': <cx_Oracle.STRING with value [[], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], []]> — staonas
– staonas, Commented May 10, 2019 at 16:40

staonas · Accepted Answer · 2019-05-13 10:06:59Z

1

Solved after some digging. It was due to trying to insert NaN values.

answered May 13, 2019 at 10:06

staonas

6575 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Aman Over a year ago

Thanks for posting the solution. Indeed, having Nan in your insert statement is the main cause of getting DPI-1043 error

Collectives™ on Stack Overflow

Error in inserting new data with Oracle and sqlalchemy

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related