0

I have a data which contains student's age. For some students, the age is not available, therefore I want to store empty values for those in the databse. I am trying the below code but it is giving me error on passing the null value.

Column('studentAge', NUMERIC, nullable=True)

I am getting the below error:

DataError: (psycopg2.DataError) invalid input syntax for type numeric: ""

I am executing the below query for inserting the data which is giving me error:

student_id= "1234", student_name="Timothy", student_age=""

ins = table.insert().values(
  studentId=student_id,
  studentName=student_name,
  studentAge=student_age)
conn = engine.connect()
conn.execute(ins)
Ilja Everilä
  • 50,538
  • 7
  • 126
  • 127
Slickmind
  • 442
  • 1
  • 7
  • 15
  • You may want to set default value for the column. If that is not what you want you must share how you are executing the queries and errors. FYI by default, all columns allow them to be null if they are not primary key field so you may also remove nullable param. – mad_ Aug 28 '18 at 15:35
  • edited the question and added the required information. – Slickmind Aug 28 '18 at 16:02
  • Fun fact: [you could actually use an empty string as NULL in Oracle](https://stackoverflow.com/questions/203493/why-does-oracle-9i-treat-an-empty-string-as-null). – Ilja Everilä Aug 28 '18 at 16:19
  • My question is different. It is not a duplicate. I have already check the question which you posted as reason for duplicate. I cannot solve my problem with that solution. If you can then please post an answer to it. Do not mark it as duplicate. – Slickmind Aug 28 '18 at 16:22
  • where and how am I supposed to use this? This is my table definition: Column('studentAge', NUMERIC, nullable=True) – Slickmind Aug 28 '18 at 16:31
  • Instead of an empty string, use `None` or `null()`. – Ilja Everilä Aug 28 '18 at 16:32
  • so I can use like, if student_age="": student_age=sqlalchemy.sql.null() Am I right? – Slickmind Aug 28 '18 at 17:19
  • For example. You could also use the `or` operation as a "default operator": `...values(..., studentAge=student_age or None)`, but in the end just pick something that suits your use case. In fact the simple `student_age or None` does not handle numeric value zero too well, but students rarely are that young. – Ilja Everilä Aug 28 '18 at 17:23
  • ok, Thank you very much. Seems like my query has been resolved. Should I delete my question as it is duplicate you said? – Slickmind Aug 28 '18 at 17:28
  • 1
    No need, unless you want to. As noted before dupes work as good sign posts. For example the error message in this one is something people might search for. – Ilja Everilä Aug 28 '18 at 17:38

0 Answers0