Python querying mysql iteratively

Question

I have a list of column names that I want to pull from a mysql database. It is good practice to loop through it and query each time?

When I do this, sometimes/randomly it seems to hang; when I restart the script, it hangs at different iterations. Was wondering whether this is bad practice to do in the first place before I continue trying to decipher what's the issue.

import mysql.connector
import numpy as np

cnx = mysql.connector.connect(user='user', password='pw', host='ip_here')
for i,j in enumerate(columnList):
   cursor.execute('SELECT `' + j + '` FROM `table_name`')
   iValues = cursor.fetchall()
   cursor.close
   if i == 0:
      extractedValues = iValues
   else:
      extractedValues = np.hstack((extractedValues, iValues))

cnx.close

Dont know who told you to loop through database. Its not a good idea. Fetchall all the data needed local or in memory and loop through the fetched data. 200 items in the column means 400 trips to and back from the db verses just two. This should clear up the problem. — Merlin
– Merlin, Commented Jun 10, 2016 at 21:36

Chris Johnson · Accepted Answer · 2016-06-10 20:33:10Z

2

It's very odd to query individual columns one at a time, since you won't be able to relate the values in one column to another. Unless your table is a grab-bag of unrelated data (not really records, i.e. your table is not at all normalized) this will remove all meaning from the data. If you're trying to deal with dynamic or variable columns, you could build a dynamic query string (append column name, comma, column name, comma ...) then execute that query. I'm hard-pressed to think of scenarios where querying individual columns rather than rows/records is useful.

answered Jun 10, 2016 at 20:33

Chris Johnson

22.3k6 gold badges84 silver badges82 bronze badges

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

1 Comment

dter Over a year ago

Thanks Chris for bringing this up; didn't think about it before. It makes more sense for me to have the current columns as rows and vice versa but unfortunately my data doesn't fit the current column limitations of innodb (since I would have over 20,000 columns). Guess I should switch to cassandra or so.

Merlin · Accepted Answer · 2016-06-10 21:40:37Z

0

Try something like this...not tested:

cnx = mysql.connector.connect(user='user', password='pw', host='ip_here')
cursor.execute('SELECT * FROM database.`table_name`')
iValues = cursor.fetchall()
cursor.close
cnx.close

#for i,j in enumerate(iValues):
#   if i == 0:
#      extractedValues = iValues
#   else:
#      extractedValues = np.hstack(())
extractedValues =  list(iValues)

answered Jun 10, 2016 at 21:40

Merlin

25.9k44 gold badges141 silver badges213 bronze badges

Comments

Roberto · Accepted Answer · 2016-06-10 21:53:32Z

0

You should try this, it will get only the columns you need, each together in a single row with only one select:

  import mysql.connector
  import numpy as np

  cnx = mysql.connector.connect(user='user', password='pw', host='ip_here')

  cursor.execute('SELECT ' + ['`' + col + '`' for col in columnList].join(',') + ' FROM `table_name`')
  values = cursor.fetchall()

  # now values variable has the data in the columns you need

no need to for because of list comprehension, it will generate a single select like

  SELECT `COLa`, `COLb`, `COLc` FROM `table_name`;

and then execute it once and get the data in values variable

answered Jun 10, 2016 at 21:53

Roberto

2,18525 silver badges33 bronze badges

1 Comment

Roberto Over a year ago

is there something to be improved in the answer? Why the downvote?

Collectives™ on Stack Overflow

Python querying mysql iteratively

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related