Python Mysql 笔记

安装

如何MySql数据库，咱不说了，不会的自己百度。但是安装好了MySql还要安装python的MySql 库。通过下面命令安装：

pip install mysql

提示安装成功即可

• 验证下是否成功

class DBTest(object):
    def __init__(self):
        self.db = mysqldb.connect("localhost", "root", "123456", "test", charset='utf8')
        self.cursor = self.db.cursor()

    def get_version(self):
        # 使用cursor()方法获取操作游标

        # 使用execute方法执行SQL语句
        self.cursor.execute("SELECT VERSION()")

        # 使用 fetchone() 方法获取一条数据
        data = self.cursor.fetchone()

        print("Database version : %s " % data)

    def __del__(self):
        # 关闭数据库连接
        self.db.close()


if __name__ == "__main__":
    db_test = DBTest()
    db_test.get_version()

返回下面说明成功了

atabase version : 5.5.62

基本操作

我在之前代码基础上，实现了数据库的基本操作：
表结构如下：

这需要了解数据库的基本语句，

• 增

 def insert_data(self):
        insert_sql = "INSERT INTO douban_commits (id, commits_text, commits_score, commits_use_count," \
                     " commits_user_name, commits_date) VALUES (%s,%s,%s,%s,%s, %s)" % \
                     ("'sfs'", "'sdfs'", "'sdsdf'", "'sdsdf'", "'fsfdf'", "'7987'")
        try:
            self.cursor.execute(insert_sql)
            self.db.commit()
        except:
            print("insert error!")

• 删

    def delete_data(self):
        delete_sql = "delete from douban_commits where id like 'sfs' "
        try:
            self.cursor.execute(delete_sql)
            self.db.commit()
        except:
            print("delete error")

• 查

    def select_data(self):
        select_sql = "select * from douban_commits"
        try:
            self.cursor.execute(select_sql)
            results = self.cursor.fetchall()
            for row in results:
                print(row)
        except:
            print("insert error!")

• 改

    def update_data(self):
        update_sql = "update douban_commits set commits_text = 'test' where id like '111s'"
        try:
            self.cursor.execute(update_sql)
            self.db.commit()
        except:
            print("update error")

总的代码如下：

lass DBTest(object):
    def __init__(self):
        self.db = mysqldb.connect("localhost", "root", "123456", "test", charset='utf8')
        self.cursor = self.db.cursor()

    def get_version(self):
        # 使用cursor()方法获取操作游标

        # 使用execute方法执行SQL语句
        self.cursor.execute("SELECT VERSION()")

        # 使用 fetchone() 方法获取一条数据
        data = self.cursor.fetchone()

        print("Database version : %s " % data)

    def insert_data(self):
        insert_sql = "INSERT INTO douban_commits (id, commits_text, commits_score, commits_use_count," \
                     " commits_user_name, commits_date) VALUES (%s,%s,%s,%s,%s, %s)" % \
                     ("'sfs'", "'sdfs'", "'sdsdf'", "'sdsdf'", "'fsfdf'", "'7987'")
        try:
            self.cursor.execute(insert_sql)
            self.db.commit()
        except:
            print("insert error!")

    def select_data(self):
        select_sql = "select * from douban_commits"
        try:
            self.cursor.execute(select_sql)
            results = self.cursor.fetchall()
            for row in results:
                print(row)
        except:
            print("insert error!")

    def delete_data(self):
        delete_sql = "delete from douban_commits where id like 'sfs' "
        try:
            self.cursor.execute(delete_sql)
            self.db.commit()
        except:
            print("delete error")

    def update_data(self):
        update_sql = "update douban_commits set commits_text = 'test' where id like '111s'"
        try:
            self.cursor.execute(update_sql)
            self.db.commit()
        except:
            print("update error")

    def __del__(self):
        # 关闭数据库连接
        self.db.close()


if __name__ == "__main__":
    db_test = DBTest()
    db_test.update_data()

如何插入中文字符

• 到安装目录下，找到my.ini文件
  
• 设置编码为utf8
  分别设置下面的参数：

default-character-set=utf8
character-set-server=utf8

• 创建table时，设置编码格式为utf8

每个字段都要设置编码格式

• 设置整张表的编码格式：

期待您的关注

注意：
update_sql = “update douban_commits set commits_text = ‘test’ where id like ‘111s’”，
拿这条语句来说，like ‘111s’ 而不是 like 111s，不然误以为是关键字，导致报错。