CODEWITHSUNDEEP
pythonmysqlcharacter-encoding
pnsilva
pnsilva

Reputation: 665

Python and MySQL Incorrect string value

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.

Upvotes: 1

Views: 4131

Answers (2)

Kratos85
Kratos85

Reputation: 193

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 :)

Upvotes: 0

Pavel Vlasov
Pavel Vlasov

Reputation: 3465

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')

Upvotes: 4

Related Questions