PyMySQL

In the following Python code, the first query works correctly but the second query doesn't return any result.  What did I do wrong?  The second query is supposed to return a unique number.  What is the proper syntax to access that unique result?


# -*- coding: utf-8 -*-

import pymysql							#-- Load ODBC connecting tool pypyodbc
conn = pymysql.connect(host='localhost', port=3306, user='root', passwd='', db='orchidsoup')
cur = conn.cursor()
stmt = "SELECT id,genus,species,is_hybrid,author FROM kew_synonym"
cur.execute(stmt)

res = cur.fetchall()
for row in res:
    print(row[1],row[2])
    stmt = "SELECT id FROM kew_accepted where genus='row[1]' and species='row[2]'"
    cur.execute(stmt)
    res1 = cur.fetchall()
    for row1 in res1:
        print('  genus id =',row1[0])

Open in new window

cpeters5Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dave BaldwinFixer of ProblemsCommented:
I believe what you're seeing is that the mysql driver does not allow you to 'reuse' a connection while it is still open.  In PHP, I would open a second connection to the server to use while looping thru the first results.
# -*- coding: utf-8 -*-

import pymysql		#-- Load ODBC connecting tool pypyodbc
conn = pymysql.connect(host='localhost', port=3306, user='root', passwd='', db='orchidsoup')
cur = conn.cursor()
stmt = "SELECT id,genus,species,is_hybrid,author FROM kew_synonym"
cur.execute(stmt)

conn2 = pymysql.connect(host='localhost', port=3306, user='root', passwd='', db='orchidsoup')
cur2 = conn2.cursor()

res = cur.fetchall()
for row in res:
    print(row[1],row[2])
    stmt2 = "SELECT id FROM kew_accepted where genus='row[1]' and species='row[2]'"
    cur2.execute(stmt2)
    res1 = cur2.fetchall()
    for row1 in res1:
        print('  genus id =',row1[0])

Open in new window

0
cpeters5Author Commented:
Dave Baldwin
No difference after the change you suggested.
0
Dave BaldwinFixer of ProblemsCommented:
Interesting.  I can't seem to find a way to install 'pymysql'.  I have two other MySQL drivers that work.  What OS and what version of Python are you using?
0
OWASP: Forgery and Phishing

Learn the techniques to avoid forgery and phishing attacks and the types of attacks an application or network may face.

cpeters5Author Commented:
Windows 8.1 with Python 3.3.
There are not many choices of mySQL drivers for python 3
0
Dave BaldwinFixer of ProblemsCommented:
Try MySQL Connector/Python 2.0.3 from MySQL.  http://dev.mysql.com/downloads/connector/python/
0
Dave BaldwinFixer of ProblemsCommented:
What do you get if you run the second query by itself?
0
cpeters5Author Commented:
The second query runs correctly if taken out of the loop.
I think the problem is the way I parsed the second statement is probably incorrect. (It works in Perl though)

"SELECT id FROM kew_accepted where genus='row[1]' and species='row[2]'"

row[1] and row[2] may be treated as string instead of variable.  I am not familiar with python syntax at all.  What would be the right way to parsed this statement?  For instance if row[1] = 'Ophrys' and row[2] = 'omegaifera' I want the statement to be

"SELECT id FROM kew_accepted where genus='Ophrys' and species='omegaifera' "
0
Dave BaldwinFixer of ProblemsCommented:
I would first 'print' those variables to see what I was getting.  This is a copy from a program using a different driver showing variable substitution in the string.  I don't think your version works now that you mention it.
# Prepare SQL query to INSERT a record into the database.
sql = "SELECT * FROM EMPLOYEE \
       WHERE INCOME > '%d'" % (1000)

Open in new window

Also look at the INSERT and other strings on this page: http://www.tutorialspoint.com/python/python_database_access.htm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
cpeters5Author Commented:
This works

"SELECT id FROM kew_accepted where genus='" + row[1] + "' and species='" + row[2] + "'"
0
Dave BaldwinFixer of ProblemsCommented:
Good!!  Does the whole thing work now?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Python

From novice to tech pro — start learning today.