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 ?

Not 100% sure, but does your database support Unicode encoding? — Chuck
– Chuck, Commented May 5, 2016 at 6:33
For collation, type SHOW TABLE STATUS FROM database; To check the default character set type SHOW CREATE TABLE table; — Chuck
– Chuck, Commented May 5, 2016 at 6:35
Change the character set collation to utf8_general_ci or any other utf8_* value. — hjpotter92
– hjpotter92, Commented May 5, 2016 at 6:40

Rick James · Accepted Answer · 2016-05-05 14:05:35Z

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

answered May 5, 2016 at 14:05

Rick James

144k15 gold badges144 silver badges254 bronze badges

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

Comments

AKS · Accepted Answer · 2016-05-05 06:57:24Z

0

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

edited May 5, 2016 at 6:57

answered May 5, 2016 at 6:51

AKS

20k3 gold badges47 silver badges55 bronze badges

1 Comment

sap Over a year ago

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

Collectives™ on Stack Overflow

inserting unicode values in mysql using python

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related