• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 585
  • Last Modified:

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

0
cpeters5
Asked:
cpeters5
  • 6
  • 4
1 Solution
 
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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now