python快捷通道 MYSQL - Shuang0420/Shuang0420.github.io GitHub Wiki

连接 mysql

import MySQLdb
conn= MySQLdb.connect(
        host='127.0.0.1',
        port = 3306,
        user='root',
        passwd='',
        db ='FAQsys',
        charset='utf8',
        )
cur = conn.cursor()
cur.execute("USE yibotDB")

查询数据

try:
    cur.execute("SELECT Question FROM Questions")
    results=cur.fetchall()
    # result = cur.fetchone()
    # result = cur.fetchmany()
    for row in results:
        question = row[0]
        # print question
except:
   # Rollback in case there is any error
   conn.rollback()

插入数据

如果是插入数据，要执行commit操作，不然数据不会被写入数据库。最好是执行完所有的sql语句之后再commit，实践证明，这样会带来很大的速度提升。

def insertIntoOriginalQuestion():
    f = open('newfileWithIndex.txt', 'r')
    for line in f:
        try:
            cur.execute("INSERT IGNORE INTO `yibotDB`.`originalQuestion` (`originalQuestion`) VALUES ('" + line + "')")
        except:
           # Rollback in case there is any error
           conn.rollback()
    conn.commit()

乱码？

在连接数据库时设置了charset='utf8',数据库里表的编码也是'utf8'，然而在mysql查看数据时还是会乱码，怎么办？！之前以为是python的问题，找了很久bug才发现其实是mysql的问题！在sql里查询数据的时候一定要注意！！！

    mysql> select * from 15_04_14_node_rtt;
    +--------+------+---------+------+--------+
    | node   | rtt  | rtt_avg | vist | user   |
    +--------+------+---------+------+--------+
    | ?????? | ?    | ???     | ???? | ?????? |
    +--------+------+---------+------+--------+
    1 row in set (0.00 sec)

    mysql> set names utf8;     #原因由于终端和客户端所用绘画不一样
    Query OK, 0 rows affected (0.00 sec)

参考链接 http://www.runoob.com/python/python-mysql.html

⚠️ **GitHub.com Fallback** ⚠️