Initialize SQLAlchemy table with file data: empty object

Question

I just started using SQLAlchemy for creating tables in PostgreSQL and I would really do with some help from experienced folks here:

I have a problem initializing an table with data read from a file. Here's an example of what I tried, in part based on this post.

schema = "spectr" # schema exists in the database
connstr = "postgresql+psycopg2://postgres:postgres@localhost/postgres"
mymetadata = MetaData(schema=schema)
Base = declarative_base(metadata=mymetadata)
engine = create_engine(connstr, echo=True)

class File(Base):
""" File metadata """
__tablename__ = "files"
id = Column(Integer, primary_key=True)
altitude = Column(Numeric(precision=6, scale=1))
average_ref = Column(Integer, nullable=False)
average_target = Column(Integer, nullable=False)

def __init__(self, filename=None):
    if filename != None:
        dic = read_sed(filename)

        for k in File.__table__.columns.keys():
            if k != 'id':
                File.k = dic.get(k, None)

# 'read_sed' is a function that returns a dictionary of the data in filename

File().__table__.create(engine, checkfirst=True)
file = File(filename)

Session = sessionmaker(bind=engine)
session = Session()
session.add(file)
session.commit()

# This is the point where I get an error since file is empty and many columns are nullable=False.

Here's the end of the error output:

sqlalchemy.exc.IntegrityError: (psycopg2.IntegrityError) null value in column "average_ref" violates not-null constraint
DETAIL:  Failing row contains (1, null, null, null).
[SQL: 'INSERT INTO spectro.files (altitude, average_ref, average_target)  VALUES (%(altitude)s, %(average_ref)s, %(average_target)s) RETURNING spectro.files.id'] [parameters: {'average_target': None, 'average_ref': None, 'altitude': None}]

Any idea what I do wrong?

Thanks a lot for your help.

@zero323 this I know. My question was why they are set to null when they actually have values inside dic. — Guilôme, Apr 28 '15 at 10:37

score 0 · Answer 1 · answered Apr 28 '15 at 07:18

0

I am not sure what the line is supposed to do:

File.k = dic.get(k, None)

As it looks, it will set a property k (not value of variable k) to a File class (not an instance). Assuming that you want to set the field such as altitude, average_ref, average_target from those in the loaded file on the current instance of File, you probably should set them on self using setattr instead:

setattr(self, k, dic.get(k, None))

Also if dic is an instance of dict, , None is redundant and you can use just setattr(self, k, dic.get(k))

answered Apr 28 '15 at 07:18

van

74,297
13
168
171

Good catch! I should indeed have written self.k and not File.k. I guess it had been a a long day yesterday and I couldn't think clearly. I think I had changed it after I had tried this version without success: `(setattr(self, k, getattr(dic, k, "None")) for k in self.__table__.columns.keys() if k != 'id')`. But this may have been due to another problem related to my incomplete understanding of generators. Or else I don't know... – Guilôme Apr 28 '15 at 10:41
Just figured out that, in my last example, I was trying to access an object attribute with `getattr(dic, k, "None")` while dict object has no such attribute. – Guilôme Apr 28 '15 at 10:54

Initialize SQLAlchemy table with file data: empty object

1 Answers1