Encoding Issues To Connect MySQL With Python

Connecting to a database

There are many types of databases, such as MySQL, and Oracle. Even within the same database type, many Python packages are available, such as MySQLdb, PyMySQL, SQLAlchemy, MySQL Connector Python. Overall, the syntax is similar. The information needed to connect to a database is nothing more than the IP, database name, user account, and password, etc. The basic syntax for using MySQLdb in Python is as follows, remember to add a colon in the SQL syntax in the execute; otherwise, it will go wrong. I also fell for this stupid mistake.

import MySQLdb
db = MySQLdb.connect(
      host = {host IP},
      user = {user},
      passwd = {password},
      db = {database})
cursor = db.cursor()
cursor.execute("SELECT * FROM table_name;")

Encoding issues

Encoding issues are common in database management, and encoding consists of two parts: collation and character set. For the difference between the two, you can refer to this article: What is the difference between collation and character set?

When using MySQLdb’s Insert to import data, because Chinese and English encoding is different, you may see an error message like this:

cursor.execute("INSERT INTO table_name (col1, col2, col3) VALUES ('台北', '台南', '高雄');")
> UnicodeEncodeError: 'latin-1' codec can't encode characters in position 50-51: ordinal not in range(256)

1) Check Collation

USE database_name;
SELECT @@character_set_database, @@collation_database;

ALTER DATABASE database_name CHARACTER SET utf8 COLLATE utf8_general_ci;

2) Check Charater Set

SHOW FULL COLUMNS FROM table_name;

ALTER TABLE table_name CHARACTER SET utf8, COLLATE utf8_general_ci;

3) Try to insert

You can directly input the SQL Query into the MySQL interface and execute it, rather than through Python. If it succeeds, it means that the database encoding is actually correct.

INSERT INTO table_name (col1, col2, col3) VALUES ('台北', '台南', '高雄');

4) Add parameters to MySQLdb.connect

db = MySQLdb.connect(
    host = {host IP},
    user = {user},
    passwd = {password},
    db = {database}
    charset = 'utf8')

fetch all

When executing query, the SQL below will only return the rows in the table.

cursor.execute("SELECT * FROM table_name;")

Don’t be panic. Actually, everything is fine. Run this line can show the result.

table = cursor.fetchall()

commit

The method to insert data into the table is:

cursor.execute("INSERT INTO table_name (col1, col2, col3) VALUES (3, 4, 5);")

After inserting, Python doesn’t throw any errors, but the new data doesn’t show in the table, either. Again, don’t panic, just add this line. Note that db here corresponds to the variable name used in MySQLdb.connect to establish the connection at the top.

db.commit()

---