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

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

公司主营业务:网站制作、成都网站建设、移动网站开发等业务。帮助企业客户真正实现互联网宣传,提高企业的竞争能力。创新互联建站是一支青春激扬、勤奋敬业、活力青春激扬、勤奋敬业、活力澎湃、和谐高效的团队。公司秉承以“开放、自由、严谨、自律”为核心的企业文化,感谢他们对我们的高要求,感谢他们从不同领域给我们带来的挑战,让我们激情的团队有机会用头脑与智慧不断的给客户带来惊喜。创新互联建站推出元谋免费做网站回馈大家。

PyMysqlThmysql(C++)Thmysql(Python)
connectconnectconnect
cursor————
executeexecuteexecute
fetchonefetchonefetchone
fetchallfetchallfetchall
closecloseclose

一.开发环境

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

二.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()

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

三.开发步骤

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

四.代码实现

// 文件名:thmysql.h
#include 
#include "mysql.h"
#include 
#include 
#include 

#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> fetchall();
  vector fetchone();
  void close();
 private:
  MYSQL mysql;
  MYSQL_RES * mysql_res;
  MYSQL_FIELD * mysql_field;
  MYSQL_ROW mysql_row;
  int columns;
  vector> mysql_data;
  vector first_line;
};
// 文件名:thmysql.cpp
#include 
#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> 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 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 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_(m, "MysqlInfo")
  .def(py::init())
  .def(py::init(),
    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_(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数据库查询

#文件名:test.py
from thmysql import Thmysql, MysqlInfo

info = MysqlInfo("localhost", "root", "123456", "", 3306)
conn = Thmysql()
# 连接database
conn.connect(info)
# 执行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)
# 关闭数据库连接
conn.close()

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

总结

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


分享文章:C++实现PyMysql的基本功能实例详解
文章来源:http://pwwzsj.com/article/ppodsj.html