如何使用python将utf-8字符正确插入MySQL表中 [英] How to correctly insert utf-8 characters into a MySQL table using python

问题描述

我对如何在其中存储带有不寻常字符的字符串(给习惯于使用英国英语字符集的人)感到困惑和困惑.

I am extremely confused and puzzled by how I store strings with unusual characters (to someone who is used to dealing with a UK English character set) in them.

这是我的例子.

我有这个名字:Bientôt l'été

这是我创建表格的方式:

This is how I created my table:

CREATE TABLE MyTable(
    'my_id' INT(10) unsigned NOT NULL,
    'my_name' TEXT CHARACTER SET utf8 NOT NULL,
    PRIMARY KEY(`my_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

使用这个简化的python脚本，我试图将字符串插入MySQL数据库和表中:

Using this simplified python script I am trying to insert the string into a MySQL database and table:

#!/usr/bin/python
# -*- coding: utf-8 -*-

import MySQLdb

mystring = "Bientôt l'été"

myinsert = [ { "name" : mystring.encode("utf-8").strip()[:65535], "id" : 1 } ]

con = None
con = MySQLdb.connect('localhost', 'abc', 'def', 'ghi');
cur = con.cursor()
sql = "INSERT INTO 'MyTable' ( 'my_id', 'my_name' ) VALUES ( %(id)s, %(name)s ) ; "
cur.executemany( sql, myinsert )
con.commit()
if con: con.close()

如果我随后尝试读取数据库中的名称，则将其存储为:Bientôt l'été

If I then try to read the name in the database it is stored as: Bientôt l'été

我希望它显示为:Bientôt l'été

如何获取python脚本/MySQL数据库来执行此操作?我认为这与字符集及其设置有关，但是我找不到没有任何技术术语的简单网页来解释这一点.我已经为此挣扎了好几个小时！

How do I get the python script/MySQL database to do this? I think this is something to do with the character set and how it is set but I can't find a simple web page that explains this without any technical jargon. I've been struggling with this for hours!

我已经看过了，发现character_set_server设置为latin1，但是我不知道这是问题还是如何更改:

I have looked at this and I see character_set_server is set as latin1 but I don't know if this is the problem or how to change it:

mysql> show variables like 'char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

推荐答案

您尝试过此查询set names utf8;

#!/usr/bin/python
# -*- coding: utf-8 -*-

import MySQLdb

mystring = "Bientôt l'été"

myinsert = [{ "name": mystring.encode("utf-8").strip()[:65535], "id": 1 }]

con = MySQLdb.connect('localhost', 'abc', 'def', 'ghi');
cur = con.cursor()

cur.execute("set names utf8;")     # <--- add this line,

sql = "INSERT INTO 'MyTable' ( 'my_id', 'my_name' ) VALUES ( %(id)s, %(name)s ) ; "
cur.executemany( sql, myinsert )
con.commit()
if con: con.close()

这篇关于如何使用python将utf-8字符正确插入MySQL表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！