Sqlalchemy is the next powerful ORM in Python and has just been used. Of course, since I just got into touch with it, I only learned to use his dB API, but I still have some trouble with mapper.
A recently written
Program MySQL databases are used, and some database reads and writes have some knowledge of sqlalchemy. It is natural to be trained, not to mention that it can greatly improve the efficiency of program development. As a result, sqlalchemy was used to write the first database application.
Code Speaker: # Coding = UTF-8
From sqlalchemy import * connstr = 'mysql: // uid: Pwd @ localhost/mydb' DB = create_engine (connstr, Echo = true)
Metadata = metadata (db)
Table = TABLE ('mytable', metadata, autoload = true) I = table. insert ()
I .exe cute (C1 = 'value') I connected to the database through the above Code, open the table and insert data. Execute the program, check the results, OK, no problem, and feel a bit dark.
However, when I added this code to the program, the problem came out. All the Chinese characters inserted are garbled characters. According to the general idea, the simple practice is to encode and convert Chinese characters. my database table uses UTF-8 encoding, so I wrote this code: 'Chinese '. decode ('gbk '). encode ('utf8'), the result is still garbled.
Search on Google and find that some people have encountered similar problems (everyone is Chinese). There are two articles:
Article A solution is proposed. One method is to add two parameters to create_engine: create_engine (connstr, encoding = 'utf8', convert_unicode = true). If you try it, it is invalid. Another article titled Unicode solution for MySQL + sqlalchemy + wxpython mentioned modifying the connection of mysqldb. the set_character_set method of the py file is directly set to UTF-8 encoding. I tried it and it does. But... it seems a little too violent, so I decided to find the reason.
According to the above idea, we found that because the charset parameter passed to set_character_set is null, this function automatically sets the default encoding to connect to MySQL, that is, Latin-1. Because sqlalchemy does not pass charset to this mysqldb, the above author is also depressing: Always Latin-1. The methods mentioned in the previous article are irrelevant.
Debugging found that create_engine called strategies. the defaultenginestrategy class of Py connects to the database. The create method of this class has a cparams variable, which stores the database connection parameters parsed from the connection URL, for example, host, username, and pwssword. Read the code of mysqldb and know that it uses the charset parameter to specify the connection encoding. Therefore, the charset parameter (cparams) is added to this variable. update ({'charset': 'utf8'}). You can save Chinese characters as well. The breakthrough is here. It seems that we should make some articles on this URL.
Continue to read the code. In the MySQL module under the database, we found such a comment: Login MySQL Server installations default to a ''latin1' encoding for client connections. all data sent through the connection will be converted into ''latin1', even if you have ''utf8'' or another character set on your tables and columns. with versions 4.1
And higher, you can change the connection character set either through server configuration or by passing the ''charset'' parameter to ''create _ engine ''. the ''charset'' option is passed through to MySQL-Python and has the side-effect of also enabling ''use _ Unicode''
In the driver by default. For regular encoded strings, also pass ''use _ Unicode = 0 ''In the connection arguments. This also proves that the preceding entry point is correct. The URL definition is found in the create_engine comment: the URL is a string in the form ''Dialect: // User: password @ host/dbname [? Key = value...] '', where ''did''' is a name such as ''mysql'', ''oracle'', ''locale s', etc. alternatively, the URL can be an instance of ''sqlalchemy. engine. URL. URL ''. I understand that this URL still supports "querystring", so I changed connstr to connstr = 'mysql: // uid: Pwd @ localhost/mydb? Charset = utf8', after reading so much nonsense :), as you wish, the Chinese characters are correctly saved in the database, O (character _ character) O Haha ~. It's really depressing. Why is it not described in this document for such an important usage? Or I cannot find it? Many people in Google group have encountered utf8 Encoding Problems, but none of them seem to be correct. Therefore, I will write down the entire process of troubleshooting.
This article from the "Rivers and Lakes" blog, please be sure to keep this source http://firefish.blog.51cto.com/298258/112794
