SQL Syntax Error on Python MySQLdb with execute() first

Question

I am using an ADMINLTE combined with Flask (or want to use it) but i get that error everytime.

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 'groups G ON G.id = M.gid WHERE user = 'admin'' at line 1"

MySQLdb._exceptions.ProgrammingError: execute() first

and that is the code where the error comes from

password = form['password']
    cur = self.db.query("SELECT pass,firstname,lastname, G.name FROM users LEFT JOIN groupmembers M ON M.uid = id LEFT JOIN groups G ON G.id = M.gid WHERE user = %s", [username])

    for row in cur.fetchall():
        pwbytes = password.encode('utf-8')
        saltbytes = row[0].encode('utf-8')
        if bcrypt.hashpw(pwbytes, saltbytes) == saltbytes:
            session['username'] = form['username']
            session['flname'] = row[1] + " " + row[2]
            session['group'] = row[3]
            session["notificationtype"] = "success"
            session["notification"] = "Logged in"
            return None

that is the Query from self.db.query

def query(self, sql, args=None):
    try:
        cursor = self.conn.cursor()
        cursor.execute(sql,args)
    except:
        self.connect()
        cursor = self.conn.cursor()
        try:
            cursor.execute(sql,args)
        except MySQLdb.Error as e:
            print(e)
    return cursor

Any ideas what there is wrong in the Syntax?

nbk · Accepted Answer · 2020-12-06 11:43:24Z

1

Mysql doesn't like groups as table name, so you have to use backticks

SELECT pass,firstname,lastname, G.name 
FROM users u 
LEFT JOIN groupmembers M ON M.uid = u.id 
LEFT JOIN `groups` G ON G.id = M.gid 
WHERE user = %s

answered Dec 6, 2020 at 11:43

nbk

49.6k8 gold badges32 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

togmer Over a year ago

yes thanks i foudn that now also... renamed the table to usergroups, thanks!

Collectives™ on Stack Overflow

SQL Syntax Error on Python MySQLdb with execute() first

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related