Sesame Http:scrapy Tips-mysql Storage

These two days to take over, other people left the crawler found a very fun SQL script stitching.

As long as your scrapy field name is the same as the name of the database field. Well, congratulations, you can copy this SQL stitching script. for MySQL inbound processing.

The specific stitching code is as follows:

? def process_item (self, item, spider):ifisinstance (item, WHOSCOREDNEWITEM): table_name= Item.pop ('table_name') Col_str="'Row_str="'             forKeyinchItem.keys (): Col_str= Col_str +" "+ key +","Row_str="{} ' {} ',". Format (ROW_STR, Item[key]if "'"NotinchItem[key]ElseItem[key].replace ("'","\ \ '")) SQL="insert INTO {} ({}) VALUES ({}) on DUPLICATE KEY UPDATE". Format (table_name, col_str[1:-1], row_str[:-1])             for(Key, value)inchSix.iteritems (item): SQL+="{} = ' {} ',". Format (Key, valueif "'"NotinchValueElseValue.replace ("'","\ \ '")) SQL= sql[:-2] Self.cursor.execute (SQL) #执行SQL self.cnx.commit () # write operation?

This SQL splicing implementation, if the database exists the same data is updated, does not exist the inserted SQL statement

The implementation is the first for loop, get key as the MySQL field name, values as SQL values (stitched into an inserted SQL statement)

The second for loop, which implements the concatenation of the field name = values.

And the SQL in the first for loop consists of the insert into XXXXX on duplicate key update. exists The SQL statement that is inserted if the update does not exist.

Sesame http:scrapy Tips-mysql storage