用C++实现一个Thmysql类,实现Python标准库PyMysql的基本功能,并提供与PyMysql类似的API,并用pybind11将Thmysql封装为Python库。
PyMysql | Thmysql(C++) | Thmysql(Python) |
---|---|---|
connect | connect | connect |
cursor | —— | —— |
execute | execute | execute |
fetchone | fetchone | fetchone |
fetchall | fetchall | fetchall |
close | close | close |
一.开发环境
二.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()
三.开发步骤
- 将mysql安装目录下的include和lib文件夹拷到project目录中;
- 将lib文件夹中的libmysql.dll文件拷到system32路径下;
- 定义MysqlInfo结构体,实现C++版的Thmysql类;
- 编写封装函数;
- 通过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])
本文来自网络收集,不代表计算机技术网立场,如涉及侵权请联系管理员删除。
ctvol管理联系方式QQ:251552304
本文章地址:https://www.ctvol.com/c-cdevelopment/483962.html