http://blog.csdn.net/wklken/article/details/7253245
但安装起来稍微复杂一点,下面给出一个自己写Python访问MySQL的办法,主要是:借用c访问MySQL的接口封装一下,然后Python调用c接口:
1、写好c接口:
C接口程序本身是有的,但是需要封装一下,直接上程序:
首先是h文件:
#ifndef CMYSQL_H
#define CMYSQL_H
#include "mysql.h"
class CDataBase {
public:
CDataBase() {}
CDataBase(const char *host, const char *user, const char *passwd, const char *db);
~CDataBase();
void InitDatabase (const char *host, const char *user, const char *passwd, const char *db);
bool ExecuteSql(const char* chSql);
MYSQL_RES *OpenRecordset(const char* chSql);
void FreeResult(MYSQL_RES *result);
MYSQL GetHandle ();
bool Open();
void Close();
bool GetConState();
private:
MYSQL mysql;
bool _bOpen;
const char *_host;
const char *_user;
const char *_passwd;
const char *_db;
};
#endif
然后是cpp文件:
#include "cppmysql.h"
#include <iostream>
#include <string.h>
#include <python2.7/Python.h>
using namespace std;
CDataBase::CDataBase(const char *host, const char *user, const char *passwd, const char *db) {
InitDatabase(host, user, passwd, db);
}
CDataBase::~CDataBase() {
Close();
}
void CDataBase::InitDatabase(const char * host, const char * user, const char * passwd, const char * db) {
_host = host;
_user = user;
_passwd = passwd;
_db = db;
_bOpen = false;
Open();
}
bool CDataBase::GetConState() {
return _bOpen;
}
bool CDataBase::Open() {
if(!mysql_init(&mysql)) {
std::cout<<std::endl<<"Failed to initate MySQL connection"<<std::endl;
return false;
}
if (!mysql_real_connect(&mysql, _host, _user, _passwd,_db, 0, 0, 0)) {
std::cout<<"Failed to connect to MySQL: Error: "<<mysql_error(&mysql)<<std::endl;
return false;
}
//std::cout<<"Logged on to database sucessfully"<<std::endl;
_bOpen = true;
return _bOpen;
}
void CDataBase::Close() {
if(_bOpen) {
mysql_close(&mysql);
_bOpen = false;
}
}
bool CDataBase::ExecuteSql(const char* chSql) {
if (!GetConState())
return false;
if (mysql_real_query(&mysql, chSql, strlen(chSql)) == 0)
return true;
//std::cout<<mysql_error(&mysql)<<std::endl;
return false;
}
MYSQL_RES *CDataBase::OpenRecordset(const char *chSql) {
MYSQL_RES *rs = 0;
if(ExecuteSql(chSql)) {
rs = mysql_store_result(&mysql);
} else {
//std::cout<<mysql_error(&mysql)<<std::endl;
}
return rs;
}
MYSQL CDataBase::GetHandle () {
return mysql;
}
void CDataBase::FreeResult(MYSQL_RES *result) {
if(result)
mysql_free_result(result);
}
可以加上cpp的测试程序:
int main () {
const char *host = "10.99.29.41";
const char *user = "map";
const char *pwd = "map";
const char *dbn = "liud_dandu";
CDataBase *db = new CDataBase(host, user, pwd, dbn);
db->ExecuteSql("create table tt1(id int, name varchar(20))");
db->ExecuteSql("insert into tt1(id, name)values('31', 'abc')");
MYSQL_RES *res = 0;
MYSQL_ROW row;
res = db->OpenRecordset("select * from tt1");
if(res) {
std::cout<<"OpenRecordset"<<std::endl;
while(row = mysql_fetch_row(res)) {
for(int t = 0; t < mysql_num_fields(res); t++)
{
std::cout<<row[t]<<" ";
}
std::cout<<std::endl;
}
}
db->FreeResult(res);
delete db;
db = 0;
return 0;
}
经测试是ok的,然后加上Python调用c的接口部分:
CDataBase db;
///cpplib api
PyObject * mysql_get_error (PyObject *self, PyObject *args) {
if (true == db.GetConState()) {
MYSQL mysql = db.GetHandle();
return Py_BuildValue("s", mysql_error(&mysql));
}
return Py_BuildValue("s", "handle already closed");
}
PyObject * CloseMysql_connect (PyObject *self, PyObject *args) {
db.Close();
return Py_BuildValue("s", "closed.");
}
PyObject * CreDelIncUpdCmd (PyObject *self, PyObject *args) {
const char *cmd;
if (!PyArg_ParseTuple(args, "s", &cmd)) {
return 0;
}
std::string res = "fail";
if (true == db.ExecuteSql(cmd))
res = "ok";
return Py_BuildValue("s", res.c_str());
}
PyObject * SelectCmd (PyObject *self, PyObject *args) {
const char *cmd;
if (!PyArg_ParseTuple(args, "s", &cmd)) {
return 0;
}
std::string result;
MYSQL_RES *res = 0;
MYSQL_ROW row;
res = db.OpenRecordset(cmd);
if(res) {
while(row = mysql_fetch_row(res)) {
for(int t = 0; t < mysql_num_fields(res); t++) {
result += row[t];
result += " ";
}
result += "\n\r";
}
} else
return 0;
return Py_BuildValue("s", result.c_str());
}
PyObject * InitMysql_connect (PyObject *self, PyObject *args) {
const char *host, *user, *passwd, *dbn;
if (!PyArg_ParseTuple(args, "ssss", &host, &user, &passwd, &dbn)) {
return 0;
}
if (true == db.GetConState())
db.Close();
db.InitDatabase(host, user, passwd, dbn);
std::string res = "connect fail";
if (true == db.GetConState())
res = "connect ok";
return Py_BuildValue("s", res.c_str());
}
PyObject *test_addsum (PyObject *self, PyObject *args) {
int a, b, c;
if (!PyArg_ParseTuple(args, "ii", &a, &b)) {
return 0;
}
c = a + b;
return Py_BuildValue("i", c);
}
PyObject *test_getsum (PyObject *self, PyObject *args) {
int i = 10;
return Py_BuildValue("i", i);
}
PyMethodDef mysqlMethods[] = {
{"InitMysql_connect", InitMysql_connect, METH_VARARGS, "use for InitMysql_connect"},
{"mysql_get_error", mysql_get_error, METH_NOARGS, "use for mysql_get_error"},
{"CloseMysql_connect", CloseMysql_connect, METH_NOARGS, "use for CloseMysql_connect"},
{"CreDelIncUpdCmd", CreDelIncUpdCmd, METH_VARARGS, "use for CreDelIncUpdCmd"},
{"SelectCmd", SelectCmd, METH_VARARGS, "use for SelectCmd"},
{"test_addsum", test_addsum, METH_VARARGS, "use for test_addsum"},
{"test_getsum", test_getsum, METH_VARARGS, "use for test_getsum"}
};
PyMODINIT_FUNC initcppmysql(void) {
(void)Py_InitModule("cppmysql", mysqlMethods);
}
注意,函数initcppmysql为必需品,整个函数照抄,它调用的Py_InitModule函数的参数1尽量写成所编译成的so的名字即可,参数2必须是全局数组变量mysqlMethods的名字一致。mysqlMethods,规定了Python都能调用哪些c接口函数,里面的格式是:
参数1:接口函数名,Python调用的c接口函数的名字必须是这个名字
参数2:c接口函数指针
参数3:参数类型,有常数就写成METH_VARARGS,没有参数写成METH_NOARGS(在:你的python安装目录/include/python2.7/methodobject.h)
参数4:接口函数说明,自己随便写
所有的接口函数,以test_addsum 为例:
PyObject *test_addsum (PyObject *self, PyObject *args) {
int a, b, c;
if (!PyArg_ParseTuple(args, "ii", &a, &b)) {
return 0;
}
c = a + b;
return Py_BuildValue("i", c);
}
PyArg_ParseTuple函数,用于解析Python传入的参数给C程序,关键是第二参数标识参数类型,比如传了一个参数是字符串,那么就是“s”,如果传了两个参数,第一个是字符串第二个是整型,那么就是“si”,以此类推。
Py_BuildValue函数,用于把C程序的参数解析给Python,第一个参数一样是标识参数类型,多用于返回值,注意可以是多个参数的即多个返回值的,事实上Python和C交互的参数,是一个tuple元组,都以PyObject 类型表示。
2、编译链接问题:
要-IPython安装目录/include
要-LPython安装目录/lib
要-lmysqlclient和-lPython2.7 (第一个动态库是因为c访问MySQL需要,第二个根据自己安装的Python版本而定,注意是个静态库)
注意编译目标是动态库,编译选项是-shared -fPIC切不可少
至此再给出对应的经过测试的Python程序:
import cppmysql
mysql_conn = cppmysql.InitMysql_connect("10.55.23.48", "user", "pwd", ",“mytestdb")
if mysql_conn == "connect fail":
exit(0)
else:
create_sql = "create table tt2(id int, name varchar(20))"
res = cppmysql.CreDelIncUpdCmd(create_sql)
print res
insert_sql = "insert into tt2(id, name) values('35', 'abcde')"
res = cppmysql.CreDelIncUpdCmd(insert_sql)
print res
select_sql = "select * from tt2"
result = cppmysql.SelectCmd(select_sql)
print result
print cppmysql.CloseMysql_connect()
注意,这里不是用的import ctypes,xxx=ctypes.CDLL("yyy.so"),而是直接把动态库复制到Python程序所在目录,直接import,前一种办法貌似会出现运行时未定义问题,还没搞清楚原因。另外直接import 库的办法感觉比较清晰一些。