4月 12

C 语言 读取mysql

测试环境:CentOS 5.4 64位 mysql5.5.20
mysql使用bin安装,安装在/program/mysql目录

代码

#include 
#include 
#include 
#define GET_ARRAY_LEN(array,len) {len = (sizeof(array) / sizeof(array[0]));}

int main(int argc,char **argv )
{
    MYSQL *sql_p;
    MYSQL_RES *result_p;
    MYSQL_ROW row_p;
    int res;
    int len;
    sql_p=mysql_init(NULL);
    if(sql_p==NULL){
        printf("Init error!\n");
        exit(1);
    }

    sql_p=mysql_real_connect(sql_p,"192.168.1.41","root","test",
                    "mysql",0,NULL,0);
    if(!sql_p){
        fprintf(stderr,"%d:%s\n",mysql_errno(sql_p),mysql_error(sql_p));
        exit(1);
    }

    res=mysql_query(sql_p,"SELECT * FROM admin");
    if(res){
        printf("Select error!\n");
        exit(1);
    }
    
    result_p=mysql_use_result(sql_p);
    if(!result_p){
        printf("mysql_use error!\n");
        exit(1);
    }

    row_p=mysql_fetch_row(result_p);
    GET_ARRAY_LEN(row_p,len);
    printf("%d\n",len);
    int i;
    for (i=0 ;i<20 ;i++)
      {if (row_p[i][1])
         printf("%s\n",row_p[i]);
       }
    mysql_close(sql_p);
    return 0;
}

编译语句
gcc -Wall cmysql.c -o cmysql -L /program/mysql/lib -I /program/mysql/include/ -lmysqlclient -lz

问题:
运行报错
./cmysql: error while loading shared libraries: libmysqlclient.so.18: cannot open shared object file: No such file or directory

解决办法:
32位操作系统运行下面一句
ln -s /usr/local/mysql/lib/libmysqlclient.so.18 /usr/lib/
64位操作系统运行下面一句
ln -s /usr/local/mysql/lib/libmysqlclient.so.18 /usr/lib64/
再次编译通过。
在读取数据时还出现一个问题,如果没有数据,程序显示错误,这个我没有处理。

4月 10

linux下测试python连接mysql

[整理人 :张子萌 2011-4-10]
测试环境:
centos,Python 2.6.5,setuptools-0.6c11,MySQL-python-1.2.2,mysql 5.1.44
首先需要安装zlib、zlib-devl、python和python-devl。
yum install zlib zlib-devl

安装使用包,如果已有可以不用安装
wget http://pypi.python.org/packages/source/s/setuptools/setuptools-0.6c11.tar.gz
获得连接连接mysql的包
wget http://downloads.sourceforge.net/project/mysql-python/mysql-python/1.2.2/MySQL-python-1.2.2.tar.gz
可以选择高版本1.2.3就不会遇到错误1和错误2
wget http://downloads.sourceforge.net/project/mysql-python/mysql-python/1.2.3/MySQL-python-1.2.3.tar.gz

具体操作命令:
# tar zxvf setuptools-0.6c11.tar.gz
# cd setuptools-0.6c11
# python setup.py build
# python setup.py install

# tar zxvf MySQL-python-1.2.2.tar.gz
# cd MySQL-python-1.2.2
# python setup.py build
# python setup.py install

测试脚本
#!/usr/bin/env python
#-*- coding: utf-8 -*-
import os,sys
import MySQLdb
try:
db = MySQLdb.connect(host = ‘localhost’, user=’root’ passwd = ‘xxxxx’, db = ‘mysql’)
except MySQLdb.ERROR,e:
print “Error %d:%s”%(e.args[0],e.args[1])
exit(1)
cursor = db.cursor()
cursor.execute( ‘select * from user’)
result_set=cursor.fetchall()
print result_set
cursor.close()
db.close()
如果能查到结果,恭喜正确了。

错误1.
>>> import MySQLdb
Traceback (most recent call last):
File ““, line 1, in
File “build/bdist.linux-i686/egg/MySQLdb/__init__.py”, line 19, in
File “build/bdist.linux-i686/egg/_mysql.py”, line 7, in
File “build/bdist.linux-i686/egg/_mysql.py”, line 6, in __bootstrap__
ImportError: libmysqlclient_r.so.16: cannot open shared object file: No such file or directory

解决方法
首先查找模块
# find / -name libmysqlclient_r.so.16
/usr/local/src/mysql-5.1.44-linux-i686-glibc23/lib/libmysqlclient_r.so.16
/usr/local/mysql/lib/libmysqlclient_r.so.16
将模块路径加入动态链接库设置为共享
vim /etc/ld.so.conf
加入:libmysqlclient_r.so.16所在目录
ldconfig -v

错误2.
>>> import MySQLdb
/usr/local/lib/python2.6/site-packages/MySQL_python-1.2.2-py2.6-linux-i686.egg/MySQLdb/__init__.py:34: DeprecationWarning: the sets module is deprecated

解决方法
进入./MySQL-python-1.2.2/MySQLdb目录中
1) 在文件 “__init__” 中将:
from sets import ImmutableSet
class DBAPISet(ImmutableSet):
替换为
class DBAPISet(frozenset)
3) 在文件 “converters.py”, 把 “Set” 改为 “set”
将return set([ i for i in s.split(‘,’) if i ])中”Set” 改为 “set”
将set: Set2Str,中”Set” 改为 “set”
2) 在文件 “converters.py”中移除:
from sets import BaseSet, Set
修改完毕后在做编译和安装。

错误3.
_mysql.c:36:23: 错误:my_config.h:没有那个文件或目录

解决方法
设置include 头文件路径即可,如下
export C_INCLUDE_PATH=/usr/local/mysql/include/

错误4.
>>> cur.execute(‘select * from user’)
Segmentation fault

解决方法
将语句写为“select user,host from user”可以正常查出。
在64位的服务器上安装发现此问题。不知道为什么会有段错误。是否所有64位服务
器均有此问题,我没有测试