Two ways to store data in mysql

The following mainly brings you two ways to store data in mysql. I hope these contents can bring you practical use, which is also the main purpose of this article. All right, don't talk too much nonsense, let's just read the following.

There are generally two ways to save data to MySQL, synchronous mode and asynchronous mode.

Synchronous mode

The synchronization mode uses SQL statements to insert data into the database. However, it should be noted that the parsing speed of Scrapy is much faster than that of MySQL, which may block when there is a large amount of parsing.

Import MySQLdbclass MysqlPipeline (object): def _ init__ (self): self.conn = MySQLdb.connect ('127.0.0.1) self.cursor = self.conn.cursor () def process_item (self, item, spider): insert_sql = "" insert into jobbole_article (title,create_date,url) Url_object_id) VALUES (% surl_object_id% s) "" self.cursor.execute (insert_sql, (item ["title"], item ["create_date"], item ["url"], item ["url_object_id"]) self.conn.commit ()

Asynchronous mode

Using synchronous mode may cause blocking, and we can use Twisted to turn MySQL loading and parsing into asynchronous operations rather than simple execute,commit synchronous operations.

For the configuration of MySQL, we can configure the database directly in the configuration file:

MYSQL_HOST = "127.0.0.1" MYSQL_DBNAME = "article_spider" MYSQL_USER = "root" MYSQL_PASSWORD = "root"

In the configuration in settings, we can get the value in the settings configuration file directly by defining the from_settings in pipeline to get the settings object.

Connect to MySQL using the asynchronous container provided by Twisted:

Import MySQLdbimport MySQLdb.cursorsfrom twisted.enterpriseimport adbapi

Using adbapi, you can turn some operations of mysqldb into asynchronized operations

Use cursors to execute and submit sql statements

Code part:

Class MysqlTwistedPipline (object): def _ init__ (self,dbpool): self.dbpool = dbpool @ classmethod def from_settings (cls,settings): dbparms = dict (host = settings ["MYSQL_HOST"], db = settings ["MYSQL_DBNAME"], user = settings ["MYSQL_USER"], passwd = settings ["MYSQL_PASSWORD"] Charset = 'utf8', cursorclass = MySQLdb.cursors.DictCursor, use_unicode=True,) dbpool = adbapi.ConnectionPool ("MySQLdb", * * dbparms) return cls (dbpool) def process_item (self, item Spider): # use Twisted to turn mysql inserts into asynchronous execution # runInteraction can turn incoming functions into asynchronous query = self.dbpool.runInteraction (self.do_insert,item) # handle exception query.addErrback (self.handle_error,item,spider) def handle_error (self,failure,item Spider): # handle asynchronously inserted exception print (failure) def do_insert (self,cursor,item): # take cursor from dbpool # perform specific insertion insert_sql = "insert into jobbole_article (title,create_date,url,url_object_id) VALUES (% s% s% s % s) "" cursor.execute (insert_sql, (item ["title"], item ["create_date"], item ["url"], item ["url_object_id"])) # execute the incoming cursor And automatically complete the commit operation

The above code can be reused except do_insert.

For the above two ways of storing data in mysql, do you think it is very helpful? If you need to know more, please continue to follow our industry information. I'm sure you'll like it.