inserting unicode values in mysql using python

Question

I want to insert unicode text to mysql table, so for this I have written below code

I am using flask framework

import MySQLdb as ms
db = ms.connect("localhost","username","password","dbname")
cursor = db.cursor()
my_text = "का" #this is marathi lang word
enc = my_text.encode('utf-8') #after encoding still it shows me marathi word
db_insert = "INSERT INTO TEXT(text) VALUES '{0}'"
cursor.execute(db_insert,(enc))
db.commit()

It gives me following error

TypeError: not all arguments converted during string formatting on line cursor.execute()

How to remove this error ?

• Chuck: Not 100% sure, but does your database support Unicode encoding?
• sap: @ChuckLoganLim how to check if db supports or not ?
• Chuck: For collation, type SHOW TABLE STATUS FROM database; To check the default character set type SHOW CREATE TABLE table;
• sap: @ChuckLoganLim charset latin1
• hjpotter92: Change the character set collation to utf8_general_ci or any other utf8_* value.

Answer 1

Put this in the beginning of the source code:

# -*- coding: utf-8 -*-

And don't encode something that is already encoded - remove my_text.encode('utf-8')

Use charset="utf8", use_unicode=True in the connection call.

The CHARACTER SET in the table/column must be utf8 or utf8mb4. latin1 will not work correctly.

Python checklist

Answer 2

You need to pass a sequence (a list or tuple) to the params in cursor.execute statement:

db_insert = "INSERT INTO TEXT(text) VALUES (%s)"
# notice the comma at the end to convert it to tuple
cursor.execute(db_insert,(enc,)) 

You can read more in the documentation:

Why the tuple? Because the DB API requires you to pass in any parameters as a sequence.

---

Alternatively, you could even use named parameters:

db_insert = "INSERT INTO TEXT(text) VALUES (%(my_text)s)"
#and then you can pass a dict with key and value
cursor.execute(db_insert, {'my_text': enc}) 

• sap: not all arguments converted during string formatting ... this error is thrown