jacobcline
jacobcline

Reputation: 27

PostgreSQL with SQLAlchemy won't allow empty entries

I have spent a lot of time trying to figure this one out but just cannot seem to find anything. I want to allow empty entries in my database since not all fields are necessary and what is necessary will change depending on the input. I thought nullable entries could be empty...

This is the error I get out. Note that the values are just some dummy values I put in and have no significance. In the list of VALUES in the error, there is a little carrot pointing to the empty '' after '133' as the issue:

(psycopg2.errors.InvalidTextRepresentation) invalid input syntax for type real: "" LINE 1: ...mit_date) VALUES ('3645', '654', 'Change', '133', '', '0', '...

Here is the code I have:

The Flask+SQLAlchemy:

class Request_Table(db.Model):
  __tablename__ = 'request_table'

  id = db.Column('id',db.Integer, primary_key=True)
  serial_num = db.Column('serial_num', db.String(10), nullable=False)
  meter_reading = db.Column('meter_reading', db.Float(5), nullable=False)
  request_type = db.Column('request_type', db.String(10), nullable=False)
  second_feet = db.Column('second_feet', db.Float(5), nullable=True)
  change_from = db.Column('change_from', db.Float(5), nullable=True)
  change_to = db.Column('change_to', db.Float(5), nullable=True)
  form_date = db.Column('form_date', db.String(10), nullable=False)
  laterals = db.Column('laterals', db.String(10), nullable=False)
  turnout = db.Column('turnout', db.String(10), nullable=True)
  remarks = db.Column('remarks', db.String(200), nullable=True)
  name = db.Column('name', db.String(20), nullable=True)
  submit_date = db.Column('submit_date', db.DateTime, default=datetime.utcnow)

  # Might not be needed but makes sense that it should be here.
  def __init__(self, id, serial_num : str = None, meter_reading : float = None, request_type : str = None, second_feet : float = None, change_from : float = None, change_to : float = None, form_date : str = None, laterals : str = None, turnout : str = None, remarks : str = None, name : str = None, submit_date : str = None):
    self.id = id
    self.serial_num = serial_num
    self.meter_reading = meter_reading
    self.request_type = request_type
    self.second_feet = second_feet
    self.change_from = change_from
    self.change_to = change_to
    self.form_date = form_date
    self.laterals = laterals
    self.turnout = turnout
    self.remarks = remarks
    self.name = name
    self.submit_date = submit_date

  # this is what gets returned when an entry is made
  def __repr__(self):
    return 'Success, I guess?'

And here is what PGAdmin says the code for the table is:

CREATE TABLE public.request_table
(
  id integer NOT NULL DEFAULT nextval('request_table_id_seq'::regclass),
  serial_num character varying(10) NOT NULL,
  meter_reading real NOT NULL,
  request_type character varying(10) NOT NULL,
  second_feet real,
  change_from real,
  change_to real,
  form_date character varying(10) NOT NULL,
  laterals character varying(10) NOT NULL,
  turnout character varying(10),
  remarks character varying(200),
  name character varying(20),
  submit_date timestamp without time zone,
  CONSTRAINT request_table_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);

It seems like the fields I specified as nullable should allow for no entry, but when I try it fails. I only works when I fill in all fields. What am I doing wrong? Also, if anyone sees any really stupid formatting on my part (this is my first attempt at database stuff), let me know and I'll fix it.

Upvotes: 1

Views: 442

Answers (1)

Danilo Moura
Danilo Moura

Reputation: 321

The problem actually appears to be that you are trying to insert a value of type String (Varchar) into a field of type Float (Real)

Try this:

INSERT INTO request_table
(serial_num, meter_reading, request_type, form_date, laterals, submit_date)
VALUES
("12345", 10.0, "POST", "2019-01-01", "Something", "2020-01-01");

or this:

Request_Table(
  id=123,
  serial_num='12345',
  meter_reading=10.0,
  request_type='Change',
  form_date='2019-01-01',
  laterals='something',
  submit_date='2020-01-01'
)

Upvotes: 1

Related Questions