Pymysql is a module used to operate MySQL database in Python3.x. It is compatible with MySQLdb, and the use method is almost the same as MySQLdb, but the performance is not as good as MySQLdb, but because it is easy to install and use, and better compatibility with Chinese, etc. This, coupled with python3.x supported versions (MySQLdb only supports Python2), is widely used. You can install it using PIP Install Pymysql.

use

Simple to use

import pymysql

Create a connection
con = pymysql.connect(
                        host='localhost',
                        port=3306,
                        user='root',
                        password='123456',
                        database='test',
                        charset='utf8'
)
Create a cursor
cursor = con.cursor()

Return the number of affected rows
row1 = cursor.execute("insert into user (username, password) values ('username3','password3')")
print(row1)

Return the number of affected rows
row2 = cursor.execute("update user set password = '123456' where id > 2;")
SQL > execute query SQL
res = cursor.execute("SELECT * FROM user;")
result = cursor.fetchall()
for info in result:
    print(info[0], info[1])
Add or update data cannot be saved
con.commit()
Close the cursor
cursor.close()
# close the connection
con.close()
Copy the code

Note: If Chinese characters exist in the database table, you need to specify charset=’utf8′ to create a connection. Otherwise, Chinese characters will be garbled. Fetchall () fetches all result sets. If there is only one result set, use cursor.fetchone().

Encapsulated utility class

For ease of use, it can be directly encapsulated into a tool class:

import pymysql


class MysqlHelper:
    def __init__(self, config) :
        self.host = config["host"]
        self.port = config["port"]
        self.user = config["user"]
        self.password = config["password"]
        self.db = config["db"]
        self.charset = config["charset"]
        self.con = None
        self.cursor = None

    def create_con(self) :
        """ Create a connection """
        try:
            self.con = pymysql.connect(host=self.host, port=self.port, user=self.user, password=self.password,
                                       database=self.db, charset='utf8')
            self.cursor = self.con.cursor()
            return True
        except Exception as e:
            print(e)
            return False

    #
    def close_con(self) :
        """ Close the link """
        if self.cursor:
            self.cursor.close()
        if self.con:
            self.con.close()

    # SQL execution
    def execute_sql(self, sql) :
        Execute insert/update/delete statement
        try:
            self.create_con()
            print(sql)
            self.cursor.execute(sql)
            self.con.commit()
        except Exception as e:
            print(e)
        finally:
            self.close_con()

    def select(self, sql, *args) :
        """ Execute query statement """
        try:
            self.create_con()
            print(sql)
            self.cursor.execute(sql, args)
            res = self.cursor.fetchall()
            return res
        except Exception as e:
            print(e)
            return False
        finally:
            self.close_con()

Copy the code

Using utility classes:

config = {
    "host": 'localhost'."port": 3306."user": 'root'."password": '123456'."db": 'test'."charset": 'utf8'
}
db = MysqlHelper(config)
db.execute_sql("insert into user (username, password) values ('username4','password4')")
db.select("SELECT * FROM user;")
Copy the code

Finished!

Finally, thank my girlfriend for her tolerance, understanding and support in work and life!