Python + Scrapy + MySQL UTF8 Encoding error

Question

I have a scrapy code that scrapes a website and writes to MySQL

import MySQLdb.cursors

def __init__(self,stats):
    self.dbpool = adbapi.ConnectionPool(<dbnam>,host=<host>,user=<user>,port=<port>,passwd=<pwd>, db=<dbname>, cursorclass=MySQLdb.cursors.DictCursor, charset='utf8', use_unicode=True)

def process_item(self, item, spider):
    query = self.dbpool.runInteraction(self._conditional_insert, item)
    query.addErrback(self.handle_error)

Scrapy script for a list of numbers in table

item['numbers'] = sites.xpath('//*[@id="numbers-0"]/tbody/tr/td/text()').extract()

I'm scraping the following content: 10″ 11″ 12″ etc. My code returns the following:

'numbers': [u'10\u2033', u'11\u2033', u'12\u2033'],

Inserting this into a MySQL db throws an error message - I'm guessing due to unicode issue.

tx.execute("""INSERT INTO numbers ('{0}').format(", ".join(item['numbers'])))

Could you please help get the insert to succeed. Better still, how can I remove the special character '\u2033' from the list?

Thanks in advance!

No worries, would you mind trying to use PyMySQL as opposed to the MySQL connector? — Bernardo Meurer
– Bernardo Meurer, Commented Mar 12, 2016 at 22:01
No problem at all with moving from MYSQL connector. I am new to Python and Scrapy. Just need to figure out how to use PyMySQL — user6055239
– user6055239, Commented Mar 12, 2016 at 22:04
Do exactly the same as you are with the connector, just put pymysql in place. And to install it run sudo pip install PyMySQL. — Bernardo Meurer
– Bernardo Meurer, Commented Mar 12, 2016 at 22:06

ekhumoro · Accepted Answer · 2016-03-13 02:01:03Z

1

You're probably getting a UnicodeEncodeError because you are trying to insert unicode strings containing non-ascii characters into a byte-string.

To fix that, make sure your query string has a u prefix:

tx.execute(u"""INSERT INTO numbers ('{0}')""".format(", ".join(item['numbers'])))

If you really want to get get rid of those double-prime characters, I suppose you could just replace them with double-quotes:

item['numbers'] = [s.replace(u'\u2033', '"') for s in item['numbers']]

But I think it's better to ensure your code can handle whatever unicode characters are thrown at it - which is to say, you should always use unicode strings within your program.

answered Mar 13, 2016 at 2:01

ekhumoro

122k23 gold badges272 silver badges400 bronze badges

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

2 Comments

user6055239 Over a year ago

I cant upvote your answer because I'm new to stackoverflow. Will be back once I earn some credibility! :)

ekhumoro Over a year ago

@user6055239. Thanks :) NB: you can always accept answers, which will also earn you a little bit of rep.

Collectives™ on Stack Overflow

Python + Scrapy + MySQL UTF8 Encoding error

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related