0

I have been trying to return the values from two different tables, but can't seem to get the c.execute(query) function to return what I want it to. Currently my code will return the first c.fetchone()[0], but the second fetchone()[5] gives an error that it's out of range, which means it is probably still trying to get data from my 'clients' table which does not have 6 columns. I don't think I fully understand how MySQLdb works it's magic, but can't find any good examples of multi-table queries. Here is my code snippet below! Thanks!

c, conn = connection()

            #check if already exists
            x = c.execute("SELECT * FROM clients WHERE email = (%s)", (thwart(email),))

            if int(x) > 0:
                flash("That email already has an account, please try a new email or sign in.")
                return render_template('register.html', form=form)
            else:
                c.execute("INSERT INTO clients (email, phone, password) VALUES (%s, %s, %s)", (thwart(email), thwart(phone), thwart(password)))
                c.execute("SELECT cid FROM clients WHERE email = (%s)", (thwart(email),))
                clientcid = c.fetchone()[0]
                c.execute("INSERT INTO cpersonals (first_name, last_name, address, zip) VALUES (%s, %s, %s, %s)", (thwart(first_name), thwart(last_name), thwart(address), czip))
                c.execute("SELECT reg_date FROM cpersonals WHERE cid = (%s)", (clientcid,))
                reg_date = c.fetchone()[5]
                rating = c.execute("SELECT rating FROM clients WHERE email = (%s)", (thwart(email),))
                conn.commit()
                flash("Thanks for registering!")
                c.close()
                conn.close()
douglasrcjames_old
  • 87
  • 13

1 Answers1

1

Your query is SELECT reg_date FROM cpersonals .... You are only selecting one column. The reason fetchone()[5] fails is, there is no 6th column in the fetched record. Try 0 in place of 5.

Why were you using 5?

Chris Johnson
  • 20,650
  • 6
  • 81
  • 80
  • My understanding is that fetchone()[5] selects the 6th column from the tuple returned by the last SQL query statement. Doing fetchone()[0] would return the client id number, as that is the column the id number is stored in. – douglasrcjames_old Oct 07 '17 at 21:14
  • The column number in question is the left-to-right order of columns in your select statement. It sounds like you are attempting to relate this to the column position in the table you're querying. The table column number has no bearing on the issue. The query included only one column, so you can grab field `[0]`, no others because there are no others. – Chris Johnson Oct 07 '17 at 21:17
  • I see now, that did the trick! I feel now that using the fetchone()[0] is not the best way of pulling one field of data in the tuple, but I tried to do `reg_date = c.execute("SELECT reg_date FROM cpersonals WHERE cid = (%s)", (clientcid,))` , but I kept getting the value '1' inputted into reg_date. Presumably because it was grabbing how many variables were in that set. – douglasrcjames_old Oct 07 '17 at 21:25
  • If my answer solved you problem, please click the green check mark. – Chris Johnson Oct 07 '17 at 21:47
  • I truly appreciate the help! That last comment was more of a question as well if you know a better way than the method I am using? – douglasrcjames_old Oct 08 '17 at 00:14
  • When you call `execute` it returns the number of rows modified or returned. You need to call a `fetch` function to get the data from your result set. You can't get `reg_date` from the `execute` call. – Chris Johnson Oct 08 '17 at 00:52