1

I am reading a csv and saving(trying) certain columns into the my database. I did that and wanted to double check a row in my database, to see if everything was ok. However one of my columns was saved as b'd\x00\x00\x00\x00\x00\x00\x00'. I was unsure what that meant and googled and found that it's binary(is that finding correct?) This really confused me. The value in that specific column and row in the csv file is 100 and in my model that specific column was indicated as an Integer. edit I'm using Flask with sqlalchemy.

Here's the relavant code:

my model

class DesiredClass(db.model):
    id = db.Column(db.Integer, primary_key=True)
    created = db.Column(db.DateTime, nullable=False, default=datetime.utcnow)

    speed = db.Column(db.Float, nullable=False)  # in m/s
    direction = db.Column(db.Float, nullable=False)  # in degrees
    different_value = db.Column(db.Integer, nullable=False)  # in percent

example row in csv with their respective columns

speed; direction; different_value

32.2; 52.4; 100

reading csv and saving to db

 df = pd.read_csv(filename, sep=';')

        for i in df.index:
            m = DesiredClass(
                speed=df.loc[i, 'SPEED'],
                direction=df.loc[i, 'DIRECTION'],
                different_value=df.loc[i, 'DIFFERENT_VALUE'],
            )

            db.session.add(m)  # add to db
        db.session.commit()  # perform them all

I then checked on my notebook to see what had been saved. And saw b'd\x00\x00\x00\x00\x00\x00\x00' in the column for different_value. All the other one's were saved in the correct format.

Therefore, 1) I'm confused why it shows that way, and that didn't happen to the other columns. 2) Where to even start? I doubled checked the csv and looked at that specific row that I was looking at to see if there was something weird. However it just has the value 100.

Thanks in advance.

Update I'm using flask_sqlalchemy and sqlite. To check I used the jupyter notebook, ran what I had and then did the following there result = DesiredClass.query.with_entities(DesiredClass.id, DesiredClass.speed, DesiredClass.direction, DesiredClass.different_value). I then did result.first()

nrvaller
  • 353
  • 6
  • 18
  • you are using flask with sqlalchemy? – Pabasara Ranathunga Apr 06 '18 at 09:21
  • @IljaEverilä I'm using flask_sqlalchemy. To check I used the jupyter notebook, ran what I had and then did the following there `result = DesiredClass.query.with_entities(DesiredClass.id, DesiredClass.speed, DesiredClass.direction, DesiredClass.different_value)`. I then did `result.first()` Yes I am @PabasaraRanathunga – nrvaller Apr 06 '18 at 09:27
  • I am using sqlite @IljaEverilä. I havent done anything with `result`. I did the `result` just to verify what I had just saved in the databse before I kept on going saving other columns from that file. Okay, I will do so and update my code when I have completed that. Thank you – nrvaller Apr 06 '18 at 09:57
  • Just checked, it is just that. `numpy.int64` values are stored as BLOB. In other words this is almost a duplicate of [inserting numpy integer types into sqlite with python3](https://stackoverflow.com/questions/38753737/inserting-numpy-integer-types-into-sqlite-with-python3) – Ilja Everilä Apr 06 '18 at 10:24
  • On the other hand, since you're using pandas, why arent you using its [`to_sql`](https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.to_sql.html) and the like? – Ilja Everilä Apr 06 '18 at 10:29
  • Thank you for your responses @IljaEverilä . I understand now why this happened. I haven't used to_sql before but will look into that. – nrvaller Apr 06 '18 at 11:44

0 Answers0