def process_item(self, item, spider):
self._connection = MySQLHelper.get_instance().get_connection(
) # type: MySQLConnection
cnx = None
try:
self._connection.connect()
cnx = self._connection.cursor()
cnx.executemany(self._insert_query, item['data'])
self._connection.commit()
except Exception as e:
self._connection.rollback()
logging.error(e)
finally:
cnx = None
return item
def insert_data(self, data):
conn = MySQLHelper.connection_inst()
conn.connect()
query = 'insert into `unit` (`base_url`, `url`, `title`)' \
' values (%(base_url)s, %(url)s, %(title)s)'
cnx = None # type: typing.Optional[MySQLConnection]
try:
cnx = conn.cursor()
cnx.executemany(query, data)
conn.commit()
except Exception as e:
conn.rollback()
logging.error(e)
finally:
cnx = None
conn.close()
conn = None
def insert_data(self, data):
conn = MySQLHelper.connection_inst()
conn.connect()
cnx = None
query = 'insert into ' \
' `property` (`base_url`, `own_id`, `key`, `value`) ' \
' values(%(base_url)s, %(own_id)s, %(key)s, %(value)s)'
try:
cnx = conn.cursor()
cnx.executemany(query, data)
conn.commit()
except Exception as e:
if conn.in_transaction:
conn.rollback()
logging.error(e)
finally:
cnx = None
conn.close()
conn = None
def get_urls(self) -> typing.List[str]:
self._mark_urls()
conn = MySQLHelper.connection_inst()
conn.connect()
cnx = None
urls = []
query = 'select `url` from `unit` where `guid` = %s'
try:
cnx = conn.cursor()
cnx.execute(query, (str(self._uuid), ))
urls = [self._base_url + row[0] for row in cnx.fetchall()]
except Exception as e:
logging.error(e)
finally:
cnx = None
conn.close()
conn = None
return urls
def _mark_urls(self):
conn = MySQLHelper.connection_inst()
conn.connect()
cnx = None
try:
query = 'update `unit` set `guid` = %s ' \
' where `state` = %s'
cnx = conn.cursor()
cnx.execute(query, (
str(self._uuid),
ProcessState.CREATED.value,
))
conn.commit()
except Exception as e:
if conn.in_transaction:
conn.rollback()
logging.error(e)
finally:
cnx = None
conn.close()
conn = None
def _mark_urls(self):
conn = None
cnx = None # type: typing.Optional[MySQLConnection]
try:
conn = MySQLHelper.connection_inst()
conn.connect()
query = 'update `category` set `guid` = %s where `state` in (%s, %s)'
cnx = conn.cursor()
cnx.execute(query, (
str(self._uuid),
str(ProcessState.CREATED.value),
str(ProcessState.FAILED.value)))
conn.commit()
except Exception as e:
conn.rollback()
logging.error(e)
finally:
cnx = None
if conn is not None:
conn.close()
conn = None
def finish_process(self, is_failed: bool = False):
query = 'update `category` set `state` = %s where `guid` = %s'
conn = MySQLHelper.connection_inst()
cnx = None # type: MySQLConnection
try:
cnx = conn.cursor()
cnx.execute(
query,
(ProcessState.PROCESSED.value if not is_failed else ProcessState.FAILED.value,
str(self._uuid))
)
conn.commit()
except Exception as e:
if conn.in_transaction:
conn.rollback()
logging.error(e)
finally:
cnx = None
if conn is not None:
conn.close()
conn = None
def clean_data(self, is_deleting: bool = False):
if not is_deleting:
return
conn = MySQLHelper.connection_inst()
query = 'delete from `property` where `base_url` in (' \
' select `url` from `unit` where `state` = %s)'
cnx = None
try:
cnx = conn.cursor()
cnx.execute(query, (ProcessState.FAILED.value, ))
query = 'delete from `unit` where state = %s'
cnx.execute(query, (ProcessState.FAILED.value, ))
conn.commit()
except Exception as e:
if conn is not None:
conn.rollback()
logging.error(e)
finally:
cnx = None
if conn is not None:
conn.close()
conn = None
def finish_process(self, is_failed: bool = False):
if is_failed:
return
conn = MySQLHelper.connection_inst()
conn.connect()
cnx = None
query = 'update `property` set' \
' `state` = %s,' \
' `guid` = NULL,' \
' where `guid` = %s'
try:
cnx = conn.cursor()
cnx.execute(query, (ProcessState.PROCESSED.value, str(self._uuid)))
conn.commit()
except Exception as e:
if conn.in_transaction:
conn.rollback()
logging.error(e)
finally:
cnx = None
conn.close()
conn = None