CODEWITHSUNDEEP
pythonmysqlflasksqlalchemyflask-sqlalchemy
demongman
demongman

Reputation: 67

mysql column does not reflect decimals as expected and instead rounds

I have two columns that are intended to reflect latitude and longitude, and I've set them up based on the sqlalchemy docs as Numeric. However, in the database, the values are saved as whole numbers. I'm aware there have been several similar questions asked on SO (A,B,C), but the common suggestions have not worked for me. Specifically, in my class, I've specified two columns as shown below, with the expectation that my saved value would include 8 numbers, 6 of which are decimals.

xcoord = db.Column(db.Numeric(8,6),nullable=False)
ycoord = db.Column(db.Numeric(8,6), nullable=False)

Directly before I commit the values, decimals are passed as expected:

 activity = Activity(xcoord=req['data']['xcoord'],ycoord=req['data']['ycoord'])
    print(activity)
    print('pre-db commit') #results in 40.267299 and -74.618776
    db.session.add(activity) 
    db.session.commit()
    print('committed to db')

However, given the above syntax, in my table, this is saved as:

'xcoord': -75, 'ycoord': 40,

From my reading of the above sources/documentation, I'm under the impression that using Numeric is preferred, and setting the precision/scale should suffice for my interests. I've attempted variations of the arguments available, including decimal_return_scale=None and asdecimal=False to no avail. It's worth noting that using asdecimal=False results in a single decimal (ex. 75.0) but still doesn't yield the expected result of 6 decimal places. Thanks for reading this far, and appreciate any suggestions.

Upvotes: 0

Views: 393

Answers (1)

demongman
demongman

Reputation: 67

For anyone with a similar issue in the future, this ended up being a result of the issue as suggest by @Akina. Specifically, because I'd previously created the table with these columns as Int type, they weren't modified appropriately to match my Flask operators. The syntax in my python script was appropriate, and when I ran the following in MySQL shell, the problem was resolved.

ALTER TABLE mytablename MODIFY COLUMN xcoord decimal(8,6)
ALTER TABLE mytablename MODIFY COLUMN ycoord decimal(8,6)

Upvotes: 1

Related Questions