1

Here is the python code I am running.

def queryOrg(self, OrgID):
        session = mysqlx.get_session(
            {'host': db.HOST, 'port': db.PORT, 'user': db.USER, 'password': db.PASSWORD})
        org_schema = session.get_schema('Organizations')
        org_table = org_schema.get_table('Organizations')
        result = org_table.select(["*"]).where('Organization_ID = ' + str(OrgID)).execute()
        print(result)

And here is the error output

mysqlx.errors.OperationalError: 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS `*` FROM `Organizations`.`Organizations` WHERE (`Organization_ID` = 2)' at line 1

When I run...

def queryOrg(self, OrgID):
        session = mysqlx.get_session(
            {'host': db.HOST, 'port': db.PORT, 'user': db.USER, 'password': db.PASSWORD})
        org_schema = session.get_schema('Organizations')
        org_table = org_schema.get_table('Organizations')
        result = org_table.select(["*"]).where('Organization_ID = ' + str(OrgID)).get_sql()
        print(result)

I get this output.

SELECT * FROM Organizations.Organizations WHERE Organization_ID = 2

So, it seems to be generating the correct mysql query when I use the .get_sql() method, but when I call the .execute() method somehting changes the query to 'AS * FROM Organizations.Organizations WHERE (Organization_ID = 2)'

I have no idea why this would be happening. I am running a MySQL server on a ubuntu rasberry pi, version 8.031-0ubuntu0.22.04.1 and I am running python 3.8.10 with pip3 mysql-connector-python 8.0.30.

This function is getting called through a flask app. I am running flask on version 2.2.2

Any help at all would be very welcome.

davidism
  • 121,510
  • 29
  • 395
  • 339
Nathaniel Reeves
  • 11
  • 3
  • you could try to use `("Organization_ID = '%s'") % str(OrgID)` since I have found different references to this type of error https://stackoverflow.com/questions/45665819/mysql-connector-errors-programmingerror-1064-4200-you-have-an-error-in-your – user11717481 Nov 16 '22 at 01:47
  • I changed the code to your suggestion and unfortunately, that didn't solve the issue. Thanks for the reminder that I should parse my strings better though! – Nathaniel Reeves Nov 16 '22 at 16:16

1 Answers1

0

Oh! I think I found a solution. the .select() method sets the '*' as a default when nothing is passed into it. Trying to pass ["*"] into .select() caused the mysqlx to generate something like 'SELECT * AS * ...' which was causing the problem. This new code works perfectly.

def queryOrg(self, OrgID):
        session = mysqlx.get_session(
            {'host': db.HOST, 'port': db.PORT, 'user': db.USER, 'password': db.PASSWORD})
        org_schema = session.get_schema('Organizations')
        org_table = org_schema.get_table('Organizations')
        result = org_table.select().where(("Organization_ID = '%s'") % str(OrgID)).execute()
        print(result.fetch_one())
Nathaniel Reeves
  • 11
  • 3