MySQL Connector/Python

Connect Once, Query Lots (updated)

Asked by Jay

Hello,

I had followed your examples and from that I had set it up so that all I had to do was pass my query to one function in an imported module and I would get back the sql result. That worked great until I wanted to run through a large csv file checking against the database and then writing results to another csv. I ended up opening and closing too many ports and hit the port limit, at which my script failed.

Since I do not have administrator access to change the number of ports or the wait time for the ports to become accessible, the only choice was to change my code. I know that I need to create one connection and then use it for several queries, instead of reconnecting everytime rapidly. I've tried two different approaches to integrating the mysqlconnector code into mine and each time I think I have everything ironed out I get :

File "E:\Python\App\lib\site-packages\mysql\connector\cursor.py", line 311, in execute
    if self.db().unread_result is True:
TypeError: 'NoneType' object is not callable

I didn't see an example of where there was one function for the connection and one function for the queries and it seems a bit tricky to get that to work, but now I can't figure out how to work around this error.

Any help would be appreciated. Thanks!

Question information

Language:
English Edit question
Status:
Solved
For:
MySQL Connector/Python Edit question
Assignee:
No assignee Edit question
Solved by:
TinBane
Solved:
Last query:
Last reply:
Revision history for this message
Geert JM Vanderkelen (geertjmvdk) said : 		#1

If I understood correctly, you are:
1) Reading a CSV file
2) Check data read from CSV with database
3) Based on the result from database, write something back to another CSV

This is not working code, but I would do something like this:

def check_data():
  cnx = mysql.connector.connect( .. your connection data )
  csv_in = open("/path/to/input.csv")
  csv_out = open("/path/to/output.csv")

  data_in = .... # Read the data from CSV in a list or dictionary ..
  csv_in.close()

  cursor = cnx.cursor()

  for data in data_in:
      # data[0] contains an ID of some sort
      cursor.execute("SELECT something FROM somewhere WHERE your_id = %s", (data[0],)
      row = cursor.fetchone() # I expect one row
      if row[0] != data[1]:
         # write something to csv_out
  csv_out.close()
  cursor.close()
  cnx.close()

Hope this helps.

Revision history for this message
Jay (calgaryjay33) said : 		#2

So if I have thousands of queries to make to the DB, then the Mysql Connector code and all of the related logic have to/should be in a single function?

I think my problem is that I had stuff going on in different functions and that presents the problem of either separating the cursor from the connection code or calling the function with the connection and cursor each time, which again creates too many port openings in too short a time frame.

Would that be a correct statement? Thanks for the help!!

Revision history for this message
Best TinBane (tinbane) said : 		#3

In pseudocode, it's probably best to do it like this:

Start Program
Open Connection
Load Data
Iterate through data
 - Call function to update mysql, pass connection object as one of the variables.

So for the above example:

def execute_sql(SQLcode = "SELECT * from somewhere", cursor):
       cursort.execute(SQLcode)
       return cursor.fetchall()

See what I mean?
You don't have to have it all in one function, you can pass the cursor object!

Revision history for this message
Jay (calgaryjay33) said : 		#4

Thanks TinBane, that solved my question.