• C++/python Connects to MySql Database

This article is written by the blogger after consulting and summarizing the online information. If there are errors or inappropriateness, please leave a message for correction. The content is for your reference only.

1.C++ calls MySQL API to connect:

First of all, it should be clear that in the installation path of MySQL database, the official has provided us with API functions related to the operation of the database, and has been encapsulated in libmysql.dll and libmysql.lib, which can be directly invoked in C++ projects or other projects.

In order to enable the new project to use the function interfaces provided by ibmysql.dll and libmysql.lib, it is necessary to tell the compiler where to read the two files (either by adding a path containing the two files in the compiler or by copying the two files to the current worker). Cheng Zhong-Recommendation)

In the first way, mysql function interface path method is added to the compiler.

Project Properties - > C/C++ - > General - > Additional Containment Directory: include Path of mysql Installation Directory

2. Project Properties - > Linker - > General - > Additional Library Directory: The lib path of the mysql installation directory

3. Project properties - > linker - > Input - > additional dependencies: add libmysql.lib

4. Make sure that the platform used is the same as mysql: X64 if MySQL is 64-bit, win32 or x86 if it is 32-bit

5. Add header files #include <mysql.h>, #include <WinSock.h>// must include this, or Winsock 2.h

Test demo code

#include "pch.h"
#include <iostream>
#include <WinSock.h>
#include <mysql.h>

int main()
{
    const char user[] = "root";         
    const char pswd[] = "123456";        
    const char host[] = "localhost";    
    const char table[] = "test";       
    unsigned int port = 3306;                
    MYSQL myCont;
    MYSQL_RES *result;
    MYSQL_ROW sql_row;
    int res;
    mysql_init(&myCont);   //Initialize cont handle, indispensable
    if (mysql_real_connect(&myCont, host, user, pswd, table, port, NULL, 0))
    {
        mysql_query(&myCont, "SET NAMES GBK"); //Setting the encoding format
        res = mysql_query(&myCont, "select * from track_detials");//query
        if (!res)
        {
            result = mysql_store_result(&myCont);
            if (result)
            {
                while (sql_row = mysql_fetch_row(result))//Get specific data
                {
                    cout<<"name:" << sql_row[0] << endl;
                    cout<<"index:" << sql_row[1] << endl;
                }
            }
        }
        else
        {
            cout << "query sql failed!" << endl;
        }
    }
    else
    {
        cout << "connect failed!" << endl;
    }
    if (result != NULL) 
        mysql_free_result(result);
    mysql_close(&myCont);
    return 0;
}

In the second way, the mysql function interface file is copied to the project:

Advantages of this method: When your project runs on other computers, it can run directly without having to reconfigure and change the path of mysql

First: Find the include folder in your MySQL installation directory and copy it all into your project folder.

Second: Find the Lib folder in your MySQL installation directory, open the Lib folder, and copy libmysql.dll and libmysql.lib into your project folder.

After completing the above operations, there is no need to add path steps as mentioned above, because the compiler can find the relevant files in its own project.

Next: Add a header file

# Include <includemysql.h>// Introduce mysql header file (one way is to set it in vc directory, the other way is to copy the folder to the project directory, and then include it like this)

# Include <WinSock.h>// Be sure to include this, or Winsock 2.h

Test demo code

#include "pch.h"
#include <iostream>
#include <WinSock.h>
#include <include\mysql.h>

int main()
{
    const char user[] = "root";         
    const char pswd[] = "123456";        
    const char host[] = "localhost";    
    const char table[] = "test";       
    unsigned int port = 3306;                
    MYSQL myCont;
    MYSQL_RES *result;
    MYSQL_ROW sql_row;
    int res;
    mysql_init(&myCont);   //Initialize cont handle, indispensable
    if (mysql_real_connect(&myCont, host, user, pswd, table, port, NULL, 0))
    {
        mysql_query(&myCont, "SET NAMES GBK"); //Setting the encoding format
        res = mysql_query(&myCont, "select * from track_detials");//query
        if (!res)
        {
            result = mysql_store_result(&myCont);
            if (result)
            {
                while (sql_row = mysql_fetch_row(result))//Get specific data
                {
                    cout<<"name:" << sql_row[0] << endl;
                    cout<<"index:" << sql_row[1] << endl;
                }
            }
        }
        else
        {
            cout << "query sql failed!" << endl;
        }
    }
    else
    {
        cout << "connect failed!" << endl;
    }
    if (result != NULL) 
        mysql_free_result(result);
    mysql_close(&myCont);
    return 0;
}

1.Python calls MySQL's API to connect:

"""
//Encapsulating a class that operates on MYSQL data
"""
import pymysql
class MYSQLClass(object):
    def __init__(self, host, port, user, password, database):
        # Establish a connection
        self.con = pymysql.connect(host=host,  #Or write it as localhost
            port=port,
            user=user,
            password=password,
            database=database,)
        # Create a cursor
        self.cur =self.con.cursor()

    def __del__(self):
        # Destructive method
        self.cur.close()
        self.con.close()

    def find_one(self,sql):
        """
        :return:Query a piece of data
        """
        self.res=self.cur.execute(sql)
        return self.cur.fetchone()

    def find_all(self,sql):
        """
        :return:Query all data
        """
        self.res=self.cur.execute(sql)
        return self.cur.fetchall()

    def insert(self,sql):
        """
        :param sql:
        :return: Insert data into the database
        """
        self.res=self.cur.execute(sql)
        self.con.commit()

    def delet(self,sql):
        """
        :param sql:
        :return: Delete data in database
        """
        self.res=self.cur.execute(sql)
        self.con.commit()

    def updata(self,sql):
        """
        :param sql:
        :return: Update data in database
        """
        self.res=self.cur.execute(sql)
        self.con.commit()

s = MYSQLClass('127.0.0.1', 3306, 'XNKF', 'XNKF123', 'test01')
sql = "SELECT * FROM student"
res=s.find_one(sql)
print(res)