mysql does not insert correct value

Question

I am trying to insert a new value into a database but it inserts a number that is 1 to 5 places off. for ex. (inserting 1000900 instead of 1000985). the new_stat value does not get inserted properly

query = '''SELECT `%s` FROM `memberstats` WHERE `member_id` = %s''' % (cat, mem_id)
                cursor.execute(query)

                for sql_stat in cursor:
                    
                    if df['member_id'].str.contains(mem_id).any():
                        cursor1 = db.cursor()
                        query = '''UPDATE `memberstats` SET `%s` = %s WHERE (`member_id` = %s)''' % (cat, new_stat, mem_id)
                        cursor1.execute(query)
                        db.commit()
                        cursor.close()
                        cursor1.close()
                        db.close()

                        db = mysql.connect(
                            host = "localhost",
                            user = "root",
                            password = "xxxxx",
                            database = "xxxxx"
                        )


                        cursor2 = db.cursor()
                        query = '''SELECT `%s` FROM `memberstats` WHERE `member_id` = %s''' % (cat, mem_id)
                        cursor2.execute(query)

                        for new_sql_stat in cursor2:
                            await message.channel.send(f':white_check_mark:\n\nYour new record for *{cat}* is now: {new_sql_stat[0]}!')

Also I am converting `new_stat` to a float from regex group search — Jean Armenier, Oct 12 '20 at 03:37
Don't use string formatting on SQL strings. Give the arguments to `execute()` as it is documented. — Klaus D., Oct 12 '20 at 04:07
ok but now the problem is that its inputting column name (`cat`) as a string which messes up my insert statement — Jean Armenier, Oct 12 '20 at 06:26
"Also I am converting new_stat to a float from regex group search" is probably the issue here, see e.g. [When I add xx to mysql float column, it is wrong result, Is it a bug?](https://stackoverflow.com/q/41647422) or [Wrong value returned from mysql float](https://stackoverflow.com/q/38997372), although your question isn't really clear about your float usage. — Solarflare, Oct 12 '20 at 07:31

score 0 · Answer 1 · answered Oct 12 '20 at 07:57

0

Column was a FLOAT in mysql table which does not keep precision in digits over 1 million. Changed column to INT fixed it. Could have also used DECIMAL to keep precision and digit after period.

answered Oct 12 '20 at 07:57

Jean Armenier

1
4

mysql does not insert correct value

1 Answers1