Python Scrapy maintain SSH MySQL connection on every process_item

Question

I am still new to Scrapy and Python.

I was able to connect on my remote DB couple with SSH credentials but...

would like to prevent this error from happening on every item being scraped.

Error: 2055: Lost connection to MySQL server at '127.0.0.1:3306', system error: 10053 An established connection was aborted by the software in your host machine

Below is my MySQL Pipeline Object

import mysql.connector
import sshtunnel

class MySQLStorePipeline(object):

def __init__(self):
    with sshtunnel.SSHTunnelForwarder(
        ('13.***.***.***', 22),
        ssh_username='***',
        ssh_password='***',
        remote_bind_address=('db1.prod.***.***.net.***', 3306),
        local_bind_address=('127.0.0.1', 3306)
    ) as tunnel:

        self.dbcon = mysql.connector.connect(
            host='127.0.0.1', 
            port=3306,
            user='***', 
            database='***', 
            password='***',
            charset='utf8'
        )
        self.cursor = self.dbcon.cursor() 

def process_item(self, item, spider):
    try:
        tables = self.cursor.execute('SHOW TABLES')
        print tables.fetchall()

        self.dbcon.commit()            
    except mysql.connector.Error as err:
        print('Error: {}'.format(err))

    return item

I just don't know how to maintain a database connection inside process_item function

gangabass · Accepted Answer · 2018-08-24 12:36:52Z

0

You're using with ... that's why you get this behavior SSH tunnel from Python is closing automatically

answered Aug 24, 2018 at 12:36

gangabass

10.7k2 gold badges26 silver badges36 bronze badges

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

1 Comment

user1441797 Over a year ago

Can you structure a code for me? I am still new to python. Thanks!

Collectives™ on Stack Overflow

Python Scrapy maintain SSH MySQL connection on every process_item

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related