Executing multi-table queries with Python MySQLdb

Question

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()

Chris Johnson · Accepted Answer · 2017-10-07 21:11:27Z

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?

answered Oct 7, 2017 at 21:11

Chris Johnson

22.2k6 gold badges84 silver badges82 bronze badges

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

6 Comments

douglasrcjames_old Over a year ago

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.

Chris Johnson Over a year ago

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.

douglasrcjames_old Over a year ago

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.

Chris Johnson Over a year ago

If my answer solved you problem, please click the green check mark.

douglasrcjames_old Over a year ago

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?

|

Collectives™ on Stack Overflow

Executing multi-table queries with Python MySQLdb

1 Answer 1

6 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Related