Encoding Issues To Connect MySQL With Python

Encoding Issues To Connect MySQL With Python

Outline

    Original Post

    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;
    Python使用MySQLdb連線到MySQL的常見問題-2
    ALTER DATABASE database_name CHARACTER SET utf8 COLLATE utf8_general_ci;

    2) Check Charater Set

    SHOW FULL COLUMNS FROM table_name;
    Python使用MySQLdb連線到MySQL的常見問題-1
    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()


    Related Posts

    Aron

    A data scientist working in a retail company, with experience in web design and marketing. Recently dives into cryptocurrency and quantitative investing.

    facebook telegram

    Leave a Reply

    • Required fields are market * .
    • Your email address will not be published.
    • Please ensure your email address is correct, then you will get a notification once a new comment reply.

    Your email address will not be published.