Having issues with using mysql SELECT in a function
Question: Started learning mySQL and got stuck on why this command is not working. I had
success with UPDATE commands and SELECT * outside the function so I am guess I
am making a mistake in calling the function or perhaps the %s needs to be
different... My google foo did not find anything so I hope you all can help
me!

Thank you so much for looking!

CODE:

    
    
    def CheckBalance(UserName, BetAmount): #checks to make sure they can afford the bet.  Returns 0 for no 1 for yes
    import mysql.connector
    
    cnx = mysql.connector.connect(user='root', password='Password',
                          host='127.0.0.1',
                          database='crapsdatabase')
    c = cnx.cursor()
    
    BankRoll = c.execute("SELECT PlayerBank FROM player WHERE PlayerName = %s", UserName)
    
    if(BankRoll < BetAmount) or (BetAmount < 0):
        c.close()
        return 0
    if(BankRoll >= BetAmount):
        c.close()
        return 1
    

From our main program I import the UpdateDatabase and call it

    
    
    from plugins.database.UpdateDatabase import UpdateBets
    
    a = UpdateBets.CheckBalance("bob", 100)
    
    print(a)
    

This gives the following error:

    
    
    C:\python\python.exe C:/Users/Ray/Desktop/bot/plugins/CRAPS/CrapsUpdated.py
    Traceback (most recent call last):
      File "C:/Users/Ray/Desktop/bot/plugins/CRAPS/CrapsUpdated.py", line 3, in <module>
        a = UpdateBets.CheckBalance("bob", 100)
    
      File "C:\Users\Ray\Desktop\bot\plugins\database\UpdateDatabase.py", line 16, in CheckBalance
        BankRoll = c.execute("SELECT PlayerBank FROM player WHERE PlayerName = %s", UserName)
    
      File "C:\python\lib\site-packages\mysql\connector\cursor.py", line 515, in execute
        self._handle_result(self._connection.cmd_query(stmt))
      File "C:\python\lib\site-packages\mysql\connector\connection.py", line 488, in cmd_query
        result = self._handle_result(self._send_cmd(ServerCmd.QUERY, query))
    
      File "C:\python\lib\site-packages\mysql\connector\connection.py", line 395, in _handle_result raise errors.get_exception(packet)
    mysql.connector.errors.ProgrammingError: 1064 (42000): 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 '%s' at line 1
    


Answer: You should escape string literal in the query with apostrophes, so it should
be like this:

    
    
    c.execute("SELECT PlayerBank FROM player WHERE PlayerName = '%s'", UserName)
    

