Python and MySQL Incorrect string value

Question

I am aware this question has been asked before, but the solutions presented don't seem to work. I am trying to insert text to a table but I keep getting this:

Warning: Incorrect string value: '\xEBrt of...' for column 'title' at row 1

The character in question is: ë

This is how the table looks:

CREATE TABLE `rstest_article` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `source_id` int(11) NOT NULL,
  `title` varchar(200) NOT NULL DEFAULT '',
  `link` varchar(200) NOT NULL DEFAULT '',
  `content` longtext NOT NULL,
  `description` longtext NOT NULL,
  PRIMARY KEY (`id`),
  KEY `rstest_article_89f89e85` (`source_id`),
  CONSTRAINT `source_id_refs_id_c47b907b` FOREIGN KEY (`source_id`) REFERENCES `rstest_source` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=606 DEFAULT CHARSET=utf8;

I am grabbing the text from another database with the same charset, like so:

_cursor.execute('SELECT title,link,content,descr,channel FROM articles')
    rows = _cursor.fetchall()
    for row in rows:
        article = Article(source=src,title=row[0],link=row[1],content=row[2],description=row[3])
        article.save()

Any help would be greatly appreciated.

You're going to have to let us know what that string was supposed to be, and show the code you are using. — Ned Batchelder
– Ned Batchelder, Commented Jun 18, 2012 at 19:42
Where is the text coming from? How are you trying to insert it in the database? What's missing from the previous times the question has been asked? (My guess is that you're trying to insert iso8859-1 text into a utf-8 database column) — Wooble
– Wooble, Commented Jun 18, 2012 at 19:42

Pavel Vlasov · Accepted Answer · 2012-06-18 19:48:33Z

4

Try to change collation of your table.

ALTER TABLE rstest_article CHARACTER SET utf8 COLLATE UTF8_general_ci;

or change collation only for one column.

ALTER TABLE rstest_article MODIFY COLUMN title varchar(200) DEFAULT '' CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;

And you can check also in your python code, how you store title.

title = u'unicode_string'.encode('unicode_escape')

answered Jun 18, 2012 at 19:48

Pavel Vlasov

3,47523 silver badges21 bronze badges

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

2 Comments

pnsilva Over a year ago

I changed the collation, but got the same results. The encoding got rid of that error, but now I get a UnicodeDecodeError: 'ascii' codec can't decode byte 0x95 in position 3625: ordinal not in range(128) Any more tips?

PsyKzz Over a year ago

You need to decode the string again when you go to use it, your trying to either print or write to file and python defaults to system default (ascii)

Kratos85 · Accepted Answer · 2012-06-19 02:35:31Z

0

You need to escape special characters,Mysqldb has escape_string function for escaping special characters as:
Assuming a variable title storing string that needs to be entered in title column of db.

title = MySQLdb.escape_string(title)

In order to escape ' and " from the title string you have to create query something like:

q = ''' Insert into rstest_article(title) values("%s") ''' % (title)

This should solve the problem :)

answered Jun 19, 2012 at 2:35

Kratos85

1932 silver badges6 bronze badges

Collectives™ on Stack Overflow

Python and MySQL Incorrect string value

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related