Of course, what MySQL does not know is that the old iron self Baidu is wonderful, ha ha!

In short, MySQL is a kind of relational database management system. Different data contents are put into different data tables. Different tables can be related, corresponding or not.

The operation of data is nothing but CURD (add, delete, modify and search). Generally, the operation on the shell command line is boring, you know? What else can I do on the pycharm interface? It's not comfortable to encapsulate SQL statements into classes, call them directly when using them, and solve problems with python's object-oriented idea.

Look at the code!!!

First, encapsulate various statements into classes:

#Import class library
import pymysql
#Create classes to encapsulate the methods (sql statements) that operate the database
class MySql():

    def __init__(self,host,user,passwd,dbName):
        """
        //Initialize the necessary parameters:
        :param host: host
        :param user: user
        :param passwd: User password
        :param dbName: Database name
        """
        self.host = host
        self.user = user
        self.passwd = passwd
        self.dbName = dbName

    def connet(self):
        """
        //Encapsulate function, connect database
        self.cursor: Create cursor object
        :return:
        """
        self.db = pymysql.connect(self.host,self.user,self.passwd,self.dbName)
        self.cursor = self.db.cursor()

    def close(self):
        """
        //Close cursor, close database
        :return:
        """
        self.cursor.close()
        self.db.close()

    def get_one(self,sql):
        """
        //Query the contents of the database and return an object
        :param sql:
        :return:
        """
        res = None
        try:
            self.connet()
            self.cursor.execute(sql)
            res = self.cursor.fetchone()
            self.close()
        except:
            print('Query failed')
        return res

    def get_all(self,sql):
        """
        //Query database content and return all data
        :param sql:
        :return:
        """
        res=()
        try:
            self.connet()
            self.cursor.execute(sql)
            res = self.cursor.fetchall()
            self.close()
        except:
            print('Query failed')
        return res

    def insert(self,sql):
        """
        //How to insert content
        :param sql:
        :return: call_edit()Method, yes SQL Encapsulation of statements
        """
        return self._edit(sql)

    #to update
    def update(self,sql):
        return self._edit(sql)

    #delete
    def delete(self,sql):
        return self._edit(sql)

    #Encapsulate executed SQL statements
    def _edit(self,sql):
        count = 0
        try:
            self.connet()
            count = self.cursor.execute(sql)
            self.db.commit()
            self.close()
        except:
            print('Things failed to submit')
            self.db.rollback()
        return count

Let's look at the test code:

#Import the created class library
from MySQL_fengzhuang import MySql

#Create object, call class
s = MySql('localhost','root','caicai','cai')

#Call the method of the object and execute the relevant SQL command
re = s.insert('insert into fruits values(0,"Wang Yu Yan",10000)')

res = s.get_all('select * from fruits where money>200')

for row in res:
    print(row)

Be careful:

1. When using it elsewhere, we must import the class we created from the file;

2. When operating the database, such as query, database and data table, they should exist (local connection to localhost, connection to others to input others' IP);