15

I have a main Python script which connects to a MySQL database and pulls out few records from it. Based on the result returned it starts as many threads (class instances) as many records are grabbed. Each thread should go back to the database and update another table by setting one status flag to a different state ("process started").

To achieve this I tried to:

1.) Pass the database connection to all threads 2.) Open a new database connection from each thread

but none of them were working.

I could run my update without any issue in both cases by using try/except, but the MySQL table has not been updated, and no error was generated. I used commit in both cases.

My question would be how to handle MySQL connection(s) in such a case?

Update based on the first few comments:

MAIN SCRIPT
-----------

#Connecting to DB
db = MySQLdb.connect(host = db_host,
                         db = db_db,
                         port = db_port,
                         user = db_user,
                         passwd = db_password,
                         charset='utf8')

# Initiating database cursor
cur = db.cursor()

# Fetching records for which I need to initiate a class instance

cur.execute('SELECT ...')

for row in cur.fetchall() :
    # Initiating new instance, appending it to a list and
    # starting all of them 



CLASS WHICH IS INSTANTIATED
---------------------------

# Connecting to DB again. I also tried to pass connection
# which has been opened in the main script but it did not
# work either.

db = MySQLdb.connect(host = db_host,
                         db = db_db,
                         port = db_port,
                         user = db_user,
                         passwd = db_password,
                         charset='utf8')

# Initiating database cursor
cur_class = db.cursor()
cur.execute('UPDATE ...')
db.commit()
Improve this question
3
  • difficult to say anything without knowing how you connect to your DB and how do you implement the updates. Commented Jan 12, 2015 at 23:41
  • I don't fully understand your question. Is the most simple case working, like single thread python connect to mysql and update a table? Commented Jan 13, 2015 at 6:09
  • @Ashalynd Thanks for taking the time! And sorry but I was AFK. I have updated my question with a code snipet from my main and class codes. This is the way how I initiate instances and open database connections. I tried to catch error when opening connection and executing query from instance with try/except without any luck. Commented Jan 14, 2015 at 12:39

3 Answers 3

Reset to default
18

Here is an example using multithreading deal mysql in Python, I don't know your table and data, so, just change the code may help:

import threading
import time
import MySQLdb

Num_Of_threads = 5

class myThread(threading.Thread):

    def __init__(self, conn, cur, data_to_deal):
        threading.Thread.__init__(self)
        self.threadID = threadID
        self.conn = conn
        self.cur = cur
        self.data_to_deal

    def run(self):

        # add your sql 
        sql = 'insert into table id values ({0});'
        for i in self.data_to_deal:
            self.cur.execute(sql.format(i))
            self.conn.commit()

threads = []
data_list = [1,2,3,4,5]

for i in range(Num_Of_threads):
    conn = MySQLdb.connect(host='localhost',user='root',passwd='',db='')
    cur = conn.cursor()
    new_thread = myThread(conn, cur, data_list[i])

for th in threads:
    th.start()

for t in threads:
    t.join()
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Sorry for my late reply. Referring to your example above I have my class and my main script in two different files. This should not be a problem I guess. Another thing that I do differently is I don't pass my data_list to my thread because I need my thread to query the data from my database on the fly. So what I do is: 1.) Open a database connection (main script) 2.) Query records (main script) 3.) Initiate as many class instance as many records I have (main script) 4.) Try to update a table record in DB from each instance (class instance)
Shouldn't you append new_thread to threads?
Is this thread safe? Accessing the same cursor object from 2 different threads seems very dangerous.
4

It seems there's no problem with my code but with my MySQL version. I'm using MySQL standard community edition and based on the official documentation found here :

The thread pool plugin is a commercial feature. It is not included in MySQL community distributions.

I'm about to upgrade to MariaDB to solve this issue.

Improve this answer

Comments

4

Looks like mysql 5.7 does support multithreading.

As you tried previously - absolutely make sure to pass the connection within the def worker(). defining the connections globally was my mistake

Here's sample code that prints 10 records via 5 threads, 5 times

import MySQLdb
import threading


def write_good_proxies():    
    local_db = MySQLdb.connect("localhost","username","PassW","DB", port=3306 )
    local_cursor = local_db.cursor (MySQLdb.cursors.DictCursor)
    sql_select = 'select http from zproxies where update_time is null order by rand() limit 10'
    local_cursor.execute(sql_select)
    records = local_cursor.fetchall()
    id_list = [f['http'] for f in records]
    print id_list
def worker():
    x=0
    while x< 5:
        x = x+1
        write_good_proxies()

threads = []


for i in range(5):
    print i
    t = threading.Thread(target=worker)
    threads.append(t)
    t.start()
Improve this answer

1 Comment

It would create duplicate connection, I think this is unreasonable！

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.