C++实现PyMysql的基本功能实例详解分享！

用C++实现一个Thmysql类，实现Python标准库PyMysql的基本功能，并提供与PyMysql类似的API，并用pybind11将Thmysql封装为Python库。

一.开发环境

二.PyMysql数据库查询

  #文件名：python_test.py  import pymysql  # 连接database  conn = pymysql.connect(host="localhost",user="root",password="123456",        database="test",charset="utf8")    # 得到一个可以执行SQL语句的光标对象  cursor = conn.cursor() # 执行完毕返回的结果集默认以元组显示  # 执行SQL语句  cursor.execute("use information_schema")    cursor.execute("select version();")  first_line = cursor.fetchone()  print(first_line)    cursor.execute("select * from character_sets;")  res = cursor.fetchall()  print(res)  # 关闭光标对象  cursor.close()  # 关闭数据库连接  conn.close()

三.开发步骤

1. 将mysql安装目录下的include和lib文件夹拷到project目录中；
2. 将lib文件夹中的libmysql.dll文件拷到system32路径下；
3. 定义MysqlInfo结构体，实现C++版的Thmysql类；
4. 编写封装函数；
5. 通过setuptools将C++代码编译为Python库。

四.代码实现

  // 文件名：thmysql.h  #include <Windows.h>  #include "mysql.h"  #include <iostream>  #include <string>  #include <vector>    #pragma comment(lib, "lib/libmysql.lib")  using namespace std;    typedef struct MysqlInfo{   string m_host;   string m_user;   string m_passwd;   string m_db;   unsigned int m_port;   string m_unix_socket;   unsigned long m_client_flag;     MysqlInfo(){}   MysqlInfo(string host, string user, string passwd, string db, unsigned int port,       string unix_socket, unsigned long client_flag){    m_host = host;    m_user = user;    m_passwd = passwd;    m_db = db;    m_port = port;    m_unix_socket = unix_socket;    m_client_flag = client_flag;   }  }MysqlInfo;    class Thmysql{   public:    Thmysql();    void connect(MysqlInfo&);    void execute(string);    vector<vector<string>> fetchall();    vector<string> fetchone();    void close();   private:    MYSQL mysql;    MYSQL_RES * mysql_res;    MYSQL_FIELD * mysql_field;    MYSQL_ROW mysql_row;    int columns;    vector<vector<string>> mysql_data;    vector<string> first_line;  };  // 文件名：thmysql.cpp  #include <iostream>  #include "thmysql.h"    Thmysql::Thmysql(){   if(mysql_library_init(0, NULL, NULL) != 0){    cout << "MySQL library initialization failed" << endl;   }   if(mysql_init(&mysql) == NULL){    cout << "Connection handle initialization failed" << endl;   }  }    void Thmysql::connect(MysqlInfo& msInfo){   string host = msInfo.m_host;   string user = msInfo.m_user;   string passwd = msInfo.m_passwd;   string db = msInfo.m_db;   unsigned int port = msInfo.m_port;   string unix_socket = msInfo.m_unix_socket;   unsigned long client_flag = msInfo.m_client_flag;     if(mysql_real_connect(&mysql, host.c_str(), user.c_str(), passwd.c_str(), db.c_str(),    port, unix_socket.c_str(), client_flag) == NULL){    cout << "Unable to connect to MySQL" << endl;   }  }    void Thmysql::execute(string sqlcmd){   mysql_query(&mysql, sqlcmd.c_str());     if(mysql_errno(&mysql) != 0){    cout << "error: " << mysql_error(&mysql) << endl;   }  }    vector<vector<string>> Thmysql::fetchall(){   // 获取 sql 指令的执行结果   mysql_res = mysql_use_result(&mysql);   // 获取查询到的结果的列数   columns = mysql_num_fields(mysql_res);   // 获取所有的列名   mysql_field = mysql_fetch_fields(mysql_res);   mysql_data.clear();   while(mysql_row = mysql_fetch_row(mysql_res)){    vector<string> row_data;    for(int i = 0; i < columns; i++){     if(mysql_row[i] == nullptr){      row_data.push_back("None");     }else{      row_data.push_back(mysql_row[i]);     }    }    mysql_data.push_back(row_data);   }   // 没有mysql_free_result会造成内存泄漏：Commands out of sync; you can't run this command now   mysql_free_result(mysql_res);   return mysql_data;  }    vector<string> Thmysql::fetchone(){   // 获取 sql 指令的执行结果   mysql_res = mysql_use_result(&mysql);   // 获取查询到的结果的列数   columns = mysql_num_fields(mysql_res);   // 获取所有的列名   mysql_field = mysql_fetch_fields(mysql_res);   first_line.clear();   mysql_row = mysql_fetch_row(mysql_res);   for(int i = 0; i < columns; i++){    if(mysql_row[i] == nullptr){     first_line.push_back("None");    }else{     first_line.push_back(mysql_row[i]);    }   }   mysql_free_result(mysql_res);   return first_line;  }    void Thmysql::close(){   mysql_close(&mysql);   mysql_library_end();  }

  // 文件名：thmysql_wrapper.cpp  #include "pybind11/pybind11.h"  #include "pybind11/stl.h"  #include "thmysql.h"    namespace py = pybind11;    PYBIND11_MODULE(thmysql, m){   m.doc() = "C++操作Mysql";   py::class_<MysqlInfo>(m, "MysqlInfo")    .def(py::init())    .def(py::init<string, string, string, string, unsigned int, string, unsigned long>(),      py::arg("host"), py::arg("user"), py::arg("passwd"), py::arg("db"),py::arg("port"),      py::arg("unix_socket") = "NULL", py::arg("client_flag")=0)    .def_readwrite("host", &MysqlInfo::m_host)    .def_readwrite("user", &MysqlInfo::m_user)    .def_readwrite("passwd", &MysqlInfo::m_passwd)    .def_readwrite("db", &MysqlInfo::m_db)    .def_readwrite("port", &MysqlInfo::m_port)    .def_readwrite("unix_socket", &MysqlInfo::m_unix_socket)    .def_readwrite("client_flag", &MysqlInfo::m_client_flag);     py::class_<Thmysql>(m, "Thmysql")    .def(py::init())    .def("connect", &Thmysql::connect)    .def("execute", &Thmysql::execute, py::arg("sql_cmd"))    .def("fetchall", &Thmysql::fetchall)    .def("fetchone", &Thmysql::fetchone)    .def("close", &Thmysql::close);  }  #文件名：setup.py  from setuptools import setup, Extension    functions_module = Extension(   name='thmysql',   sources=['thmysql.cpp', 'thmysql_wrapper.cpp'],   include_dirs=[r'D:softwarepybind11-masterinclude',       r'D:softwareAnacondainclude',       r'D:projectthmysqlinclude'],  )    setup(ext_modules=[functions_module])    

• 1
• 2
• 下一页