1

I am trying to check for a string that is being passed from a form in an html page. So the form picks up the user name and then checks the database if it already has been made. If it hasn't, it goes ahead and creates it. My errors are in the part of the logic that looks up that user name.

Note, I have commented out some areas where various errors have popped up:

import mysql.connector
import web

from mysql.connector import Error
import cgi, cgitb

cgitb.enable()

conn = mysql.connector.connect(host='localhost', database='database', user='root', password='root')
cursor = conn.cursor()
form = cgi.FieldStorage()
username = form.getvalue('username')
password = form.getvalue('password')


# check_existence = """
#   SELECT username FROM members WHERE username = '%s'
# """

check_existence = """
    SELECT username FROM members WHERE username = %s
"""

# cursor.execute(check_existence, username)
#    "Wrong number of arguments during string formatting")


cursor.execute(check_existence, (username))

#   ^pushes down to con.commit
# cursor.execute(check_existence, (username,))
#    ^wrpmg number of arguments during string formatting
#   with comma, the error is in commit, with comma, its in execute


conn.commit()  

matches = cursor.rowcount()

Now the error is pointing to conn.commit. Though this is depending on the syntax, sometimes it points to the line above it. Error:

=>  203 conn.commit()  
<class 'mysql.connector.errors.InternalError'>: Unread result found. 
      args = (-1, 'Unread result found.', None) 
      errno = -1 
      message = '' 
      msg = 'Unread result found.'
munchschair
  • 1,593
  • 3
  • 19
  • 43

2 Answers2

1

In my limited experience, commit() is only used to save (commit) updates to the database. It looks like you're executing a select query, but doing nothing with the results, and the error is related to that. Try moving the commit to the end, or doing away with it. Try using/doing something with the results stored in the cursor. I believe the latter is the solution.

Redbeard011010
  • 954
  • 6
  • 20
  • Ah, I thought .commit had to follow every query. – munchschair Dec 20 '15 at 00:19
  • According to https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlconnection-commit.html It's just after every statement which alters data. – Redbeard011010 Dec 20 '15 at 00:20
  • I took it out but now I am getting `"Wrong number of arguments during string formatting")"` on the `cursor.execute` – munchschair Dec 20 '15 at 00:28
  • I found the solution but it is totally different then what I have right now up there. I'll put it up momentarily – munchschair Dec 20 '15 at 01:11
  • Some of my previous comments suggested replacing your string formatting of '%s' with a directly concatenated variable. Apparently, this is much more vulnerable to SQL injection, so I'm deleting those comments. You can view this post for more information about that. http://stackoverflow.com/questions/7929364/python-best-practice-and-securest-to-connect-to-mysql-and-execute-queries – Redbeard011010 Dec 20 '15 at 01:28
1

The .commit method was off to a start but it wasn't the only problem with the code. I had two problems though one of them is not posted in the original post, I will explain both.

A) cursor.rowcount returns -1. Not sure why but it does. My understanding of it was that it will return the number of rows. But you can use cursor.fetchall() instead. This will return matches in an array....but if the array is empty, it'll return an empty array.

So I used this logic:

 if not(cursor.fetchall()):
    the set/array is empty>> Create user
 else:
     something was found >>dont create user

B) This was in the rest of my code. I was checking if the connection was connected:

 if conn.is_connected():

The problem with doing this is that if you do this after a .execute, it will return false. So I put it higher up in the logic, to check right when it attempts to connect to the database.

munchschair
  • 1,593
  • 3
  • 19
  • 43
  • In non-buffered queries cursor.rowcount defaults to -1 before rows are being fetched. To avoid that use buffered queries => mysql.connector.connect(buffered=True) – Kudehinbu Oluwaponle Sep 20 '17 at 08:21