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

max · Accepted Answer · 2016-03-18 15:58:31Z

1

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)

answered Mar 18, 2016 at 15:58

max

2,81725 silver badges21 bronze badges

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

2 Comments

HELPMEPLEASE Over a year ago

That seemed to fix it... can you tell me what the escape does to the program? I didnt have to use it in other queries....

max Over a year ago

It tells MySQL parser that it is a string literal, not a column name or integer value. I would suggest you reading more about SQL language, particularly about MySQL here.

Collectives™ on Stack Overflow

Having issues with using mysql SELECT in a function

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related