服务器之家

服务器之家 > 正文

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

时间:2021-08-20 12:15     来源/作者:追忆

用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

一.开发环境

  • Windows64位操作系统;
  • mysql 5.5.28 for Win64(x86);
  • pycharm 2019.1.1。

二.PyMysql数据库查询

  1. #文件名:python_test.py
  2. import pymysql
  3. # 连接database
  4. conn = pymysql.connect(host="localhost",user="root",password="123456",
  5. database="test",charset="utf8")
  6.  
  7. # 得到一个可以执行SQL语句的光标对象
  8. cursor = conn.cursor() # 执行完毕返回的结果集默认以元组显示
  9. # 执行SQL语句
  10. cursor.execute("use information_schema")
  11.  
  12. cursor.execute("select version();")
  13. first_line = cursor.fetchone()
  14. print(first_line)
  15.  
  16. cursor.execute("select * from character_sets;")
  17. res = cursor.fetchall()
  18. print(res)
  19. # 关闭光标对象
  20. cursor.close()
  21. # 关闭数据库连接
  22. conn.close()

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

三.开发步骤

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

四.代码实现

  1. // 文件名:thmysql.h
  2. #include <Windows.h>
  3. #include "mysql.h"
  4. #include <iostream>
  5. #include <string>
  6. #include <vector>
  7.  
  8. #pragma comment(lib, "lib/libmysql.lib")
  9. using namespace std;
  10.  
  11. typedef struct MysqlInfo{
  12. string m_host;
  13. string m_user;
  14. string m_passwd;
  15. string m_db;
  16. unsigned int m_port;
  17. string m_unix_socket;
  18. unsigned long m_client_flag;
  19.  
  20. MysqlInfo(){}
  21. MysqlInfo(string host, string user, string passwd, string db, unsigned int port,
  22. string unix_socket, unsigned long client_flag){
  23. m_host = host;
  24. m_user = user;
  25. m_passwd = passwd;
  26. m_db = db;
  27. m_port = port;
  28. m_unix_socket = unix_socket;
  29. m_client_flag = client_flag;
  30. }
  31. }MysqlInfo;
  32.  
  33. class Thmysql{
  34. public:
  35. Thmysql();
  36. void connect(MysqlInfo&);
  37. void execute(string);
  38. vector<vector<string>> fetchall();
  39. vector<string> fetchone();
  40. void close();
  41. private:
  42. MYSQL mysql;
  43. MYSQL_RES * mysql_res;
  44. MYSQL_FIELD * mysql_field;
  45. MYSQL_ROW mysql_row;
  46. int columns;
  47. vector<vector<string>> mysql_data;
  48. vector<string> first_line;
  49. };
  50. // 文件名:thmysql.cpp
  51. #include <iostream>
  52. #include "thmysql.h"
  53.  
  54. Thmysql::Thmysql(){
  55. if(mysql_library_init(0, NULL, NULL) != 0){
  56. cout << "MySQL library initialization failed" << endl;
  57. }
  58. if(mysql_init(&mysql) == NULL){
  59. cout << "Connection handle initialization failed" << endl;
  60. }
  61. }
  62.  
  63. void Thmysql::connect(MysqlInfo& msInfo){
  64. string host = msInfo.m_host;
  65. string user = msInfo.m_user;
  66. string passwd = msInfo.m_passwd;
  67. string db = msInfo.m_db;
  68. unsigned int port = msInfo.m_port;
  69. string unix_socket = msInfo.m_unix_socket;
  70. unsigned long client_flag = msInfo.m_client_flag;
  71.  
  72. if(mysql_real_connect(&mysql, host.c_str(), user.c_str(), passwd.c_str(), db.c_str(),
  73. port, unix_socket.c_str(), client_flag) == NULL){
  74. cout << "Unable to connect to MySQL" << endl;
  75. }
  76. }
  77.  
  78. void Thmysql::execute(string sqlcmd){
  79. mysql_query(&mysql, sqlcmd.c_str());
  80.  
  81. if(mysql_errno(&mysql) != 0){
  82. cout << "error: " << mysql_error(&mysql) << endl;
  83. }
  84. }
  85.  
  86. vector<vector<string>> Thmysql::fetchall(){
  87. // 获取 sql 指令的执行结果
  88. mysql_res = mysql_use_result(&mysql);
  89. // 获取查询到的结果的列数
  90. columns = mysql_num_fields(mysql_res);
  91. // 获取所有的列名
  92. mysql_field = mysql_fetch_fields(mysql_res);
  93. mysql_data.clear();
  94. while(mysql_row = mysql_fetch_row(mysql_res)){
  95. vector<string> row_data;
  96. for(int i = 0; i < columns; i++){
  97. if(mysql_row[i] == nullptr){
  98. row_data.push_back("None");
  99. }else{
  100. row_data.push_back(mysql_row[i]);
  101. }
  102. }
  103. mysql_data.push_back(row_data);
  104. }
  105. // 没有mysql_free_result会造成内存泄漏:Commands out of sync; you can't run this command now
  106. mysql_free_result(mysql_res);
  107. return mysql_data;
  108. }
  109.  
  110. vector<string> Thmysql::fetchone(){
  111. // 获取 sql 指令的执行结果
  112. mysql_res = mysql_use_result(&mysql);
  113. // 获取查询到的结果的列数
  114. columns = mysql_num_fields(mysql_res);
  115. // 获取所有的列名
  116. mysql_field = mysql_fetch_fields(mysql_res);
  117. first_line.clear();
  118. mysql_row = mysql_fetch_row(mysql_res);
  119. for(int i = 0; i < columns; i++){
  120. if(mysql_row[i] == nullptr){
  121. first_line.push_back("None");
  122. }else{
  123. first_line.push_back(mysql_row[i]);
  124. }
  125. }
  126. mysql_free_result(mysql_res);
  127. return first_line;
  128. }
  129.  
  130. void Thmysql::close(){
  131. mysql_close(&mysql);
  132. mysql_library_end();
  133. }
  1. // 文件名:thmysql_wrapper.cpp
  2. #include "pybind11/pybind11.h"
  3. #include "pybind11/stl.h"
  4. #include "thmysql.h"
  5.  
  6. namespace py = pybind11;
  7.  
  8. PYBIND11_MODULE(thmysql, m){
  9. m.doc() = "C++操作Mysql";
  10. py::class_<MysqlInfo>(m, "MysqlInfo")
  11. .def(py::init())
  12. .def(py::init<string, string, string, string, unsigned int, string, unsigned long>(),
  13. py::arg("host"), py::arg("user"), py::arg("passwd"), py::arg("db"),py::arg("port"),
  14. py::arg("unix_socket") = "NULL", py::arg("client_flag")=0)
  15. .def_readwrite("host", &MysqlInfo::m_host)
  16. .def_readwrite("user", &MysqlInfo::m_user)
  17. .def_readwrite("passwd", &MysqlInfo::m_passwd)
  18. .def_readwrite("db", &MysqlInfo::m_db)
  19. .def_readwrite("port", &MysqlInfo::m_port)
  20. .def_readwrite("unix_socket", &MysqlInfo::m_unix_socket)
  21. .def_readwrite("client_flag", &MysqlInfo::m_client_flag);
  22.  
  23. py::class_<Thmysql>(m, "Thmysql")
  24. .def(py::init())
  25. .def("connect", &Thmysql::connect)
  26. .def("execute", &Thmysql::execute, py::arg("sql_cmd"))
  27. .def("fetchall", &Thmysql::fetchall)
  28. .def("fetchone", &Thmysql::fetchone)
  29. .def("close", &Thmysql::close);
  30. }
  31. #文件名:setup.py
  32. from setuptools import setup, Extension
  33.  
  34. functions_module = Extension(
  35. name='thmysql',
  36. sources=['thmysql.cpp', 'thmysql_wrapper.cpp'],
  37. include_dirs=[r'D:\software\pybind11-master\include',
  38. r'D:\software\Anaconda\include',
  39. r'D:\project\thmysql\include'],
  40. )
  41.  
  42. setup(ext_modules=[functions_module])

五.Thmysql数据库查询

  1. #文件名:test.py
  2. from thmysql import Thmysql, MysqlInfo
  3.  
  4. info = MysqlInfo("localhost", "root", "123456", "", 3306)
  5. conn = Thmysql()
  6. # 连接database
  7. conn.connect(info)
  8. # 执行SQL语句
  9. conn.execute("use information_schema")
  10.  
  11. conn.execute("select version();")
  12. first_line = conn.fetchone()
  13. print(first_line)
  14.  
  15. conn.execute("select * from character_sets;")
  16. res = conn.fetchall()
  17. print(res)
  18. # 关闭数据库连接
  19. conn.close()

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

总结

到此这篇关于C++实现PyMysql的基本功能的文章就介绍到这了,更多相关c++ pymysql内容请搜索服务器之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持服务器之家!

原文链接:https://segmentfault.com/a/1190000021875615

标签:

相关文章

热门资讯

yue是什么意思 网络流行语yue了是什么梗
yue是什么意思 网络流行语yue了是什么梗 2020-10-11
2020微信伤感网名听哭了 让对方看到心疼的伤感网名大全
2020微信伤感网名听哭了 让对方看到心疼的伤感网名大全 2019-12-26
背刺什么意思 网络词语背刺是什么梗
背刺什么意思 网络词语背刺是什么梗 2020-05-22
苹果12mini价格表官网报价 iPhone12mini全版本价格汇总
苹果12mini价格表官网报价 iPhone12mini全版本价格汇总 2020-11-13
2021年耽改剧名单 2021要播出的59部耽改剧列表
2021年耽改剧名单 2021要播出的59部耽改剧列表 2021-03-05
返回顶部