[Python]使用MySQLdb連線到MySQL的常見問題

原始文章

import MySQLdb

db = MySQLdb.connect(
      host = '主機IP或網址',
      user = '資料庫帳號',
      passwd = '資料庫密碼',
      db = '資料庫名稱')

cursor = db.cursor()
cursor.execute("select * from table_name;")

編碼問題

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) 檢查Collation

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

ALTER DATABASE database_name CHARACTER SET utf8 COLLATE utf8_general_ci;

2) 檢查Charater Set

SHOW FULL COLUMNS FROM table_name;

ALTER TABLE table_name CHARACTER SET utf8, COLLATE utf8_general_ci;

3) 直接在MySQL Insert看看

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

4) 在MySQLdb.connect中增加參數

db = MySQLdb.connect(
    host = '主機IP或網址',
    user = '資料庫帳號',
    passwd = '資料庫密碼',
    db = '資料庫名稱',
    charset = 'utf8')

fetch all

table = cursor.fetchall()

commit

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

db.commit()

推薦文章