C++實現(xiàn)PyMysql的基本功能實例詳解
用C++實現(xiàn)一個Thmysql類,實現(xiàn)Python標(biāo)準(zhǔn)庫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 |
一.開發(fā)環(huán)境
- Windows64位操作系統(tǒng);
- mysql 5.5.28 for Win64(x86);
- pycharm 2019.1.1。
二.PyMysql數(shù)據(jù)庫查詢
#文件名:python_test.py import pymysql # 連接database conn = pymysql.connect(host="localhost",user="root",password="123456", database="test",charset="utf8") # 得到一個可以執(zhí)行SQL語句的光標(biāo)對象 cursor = conn.cursor() # 執(zhí)行完畢返回的結(jié)果集默認以元組顯示 # 執(zhí)行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) # 關(guān)閉光標(biāo)對象 cursor.close() # 關(guān)閉數(shù)據(jù)庫連接 conn.close()
三.開發(fā)步驟
- 將mysql安裝目錄下的include和lib文件夾拷到project目錄中;
- 將lib文件夾中的libmysql.dll文件拷到system32路徑下;
- 定義MysqlInfo結(jié)構(gòu)體,實現(xiàn)C++版的Thmysql類;
- 編寫封裝函數(shù);
- 通過setuptools將C++代碼編譯為Python庫。
四.代碼實現(xiàn)
// 文件名: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 指令的執(zhí)行結(jié)果 mysql_res = mysql_use_result(&mysql); // 獲取查詢到的結(jié)果的列數(shù) 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會造成內(nèi)存泄漏:Commands out of sync; you can't run this command now mysql_free_result(mysql_res); return mysql_data; } vector<string> Thmysql::fetchone(){ // 獲取 sql 指令的執(zhí)行結(jié)果 mysql_res = mysql_use_result(&mysql); // 獲取查詢到的結(jié)果的列數(shù) 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:\software\pybind11-master\include', r'D:\software\Anaconda\include', r'D:\project\thmysql\include'], ) setup(ext_modules=[functions_module])
五.Thmysql數(shù)據(jù)庫查詢
#文件名:test.py from thmysql import Thmysql, MysqlInfo info = MysqlInfo("localhost", "root", "123456", "", 3306) conn = Thmysql() # 連接database conn.connect(info) # 執(zhí)行SQL語句 conn.execute("use information_schema") conn.execute("select version();") first_line = conn.fetchone() print(first_line) conn.execute("select * from character_sets;") res = conn.fetchall() print(res) # 關(guān)閉數(shù)據(jù)庫連接 conn.close()
總結(jié)
到此這篇關(guān)于C++實現(xiàn)PyMysql的基本功能的文章就介紹到這了,更多相關(guān)c++ pymysql內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
相關(guān)文章
C++實現(xiàn)高性能轉(zhuǎn)換大小寫算法示例
大小寫轉(zhuǎn)換是我們作為一名程序員經(jīng)常會遇到,也必須要會的一個功能,下面這篇文章主要給大家介紹了關(guān)于C++實現(xiàn)高性能轉(zhuǎn)換大小寫算法的相關(guān)資料,文中通過示例代碼介紹的非常詳細,需要的朋友可以參考借鑒,下面來一起看看吧。2018-01-01C++?拷貝構(gòu)造函數(shù)與賦值的區(qū)別
拷貝構(gòu)造函數(shù)和賦值函數(shù)非常容易混淆,本文主要介紹了C++?拷貝構(gòu)造函數(shù)與賦值的區(qū)別,具有一定的參考價值,感興趣的可以了解一下2024-04-04c++基礎(chǔ)學(xué)習(xí)之如何區(qū)分引用和指針
C語言中只有指針,C++加入了引用,能夠起到跟指針類似的作用,下面這篇文章主要給大家介紹了關(guān)于c++基礎(chǔ)學(xué)習(xí)之區(qū)分引用和指針的相關(guān)資料,文中通過示例代碼介紹的非常詳細,需要的朋友可以參考下2021-08-08