Python连接MySQL数据库

UPDATE: 其实目前没有用到多少Python知识来写,基本都是直接调用的已有的命令来完成备份和邮件通知功能 @@
为了写个简单的备份程序,我决定使用Python来实现,几年前有看过点Python,今天就来试试看。通常我们连接数据库会用到对应的连接API,比如Java的JDBC(Java_Database_Connectivity‎),顶多中间增加个连接池之类的,因为我们这里很简单,所以只需要一个类似的API,我们这里用MySQL-python来完成。先安装这个东西,我采用1.2.3版本,当然我这里宿主机器是CentOS。

[guohai@Knight-on-the-Cloud installation]$ wget http://sourceforge.net/projects/mysql-python/files/mysql-python/1.2.3/MySQL-python-1.2.3.tar.gz/download
[guohai@Knight-on-the-Cloud installation]$ tar xvf MySQL-python-1.2.3.tar.gz
[guohai@Knight-on-the-Cloud installation]$ cd MySQL-python-1.2.3

vim site.cfg

# The path to mysql_config.
# Only use this if mysql_config is not on your PATH, or you have some weird
# setup that requires it.
mysql_config = /usr/bin/mysql_config

正如这上面所说,之前我安装MySQL有改动默认位置,所以这里要单独指定下。

[guohai@Knight-on-the-Cloud MySQL-python-1.2.3]$ python setup.py build

if errors(“no setuptools”) showing

cd ..
wget https://pypi.python.org/packages/2.6/s/setuptools/setuptools-0.6c10-py2.6.egg --no-check-certificate
sh setuptools-0.6c10-py2.6.egg

然后

cd MySQL-python-1.2.3

[guohai@Knight-on-the-Cloud MySQL-python-1.2.3]$ python setup.py build

if errors(“Python.h: No such file or directory”) showing

[root@Knight-on-the-Cloud installation]# yum -y install python-devel

继续

[guohai@Knight-on-the-Cloud MySQL-python-1.2.3]$ python setup.py build
running build
running build_py
copying MySQLdb/release.py -> build/lib.linux-x86_64-2.6/MySQLdb
running build_ext
building '_mysql' extension
gcc -pthread -fno-strict-aliasing -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector --param=ssp-buffer-size=4 -m64 -mtune=generic -D_GNU_SOURCE -fPIC -fwrapv -DNDEBUG -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector --param=ssp-buffer-size=4 -m64 -mtune=generic -D_GNU_SOURCE -fPIC -fwrapv -fPIC -Dversion_info=(1,2,3,'final',0) -D__version__=1.2.3 -I/usr/include/mysql -I/usr/include/python2.6 -c _mysql.c -o build/temp.linux-x86_64-2.6/_mysql.o -g -pipe -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector --param=ssp-buffer-size=4 -m64 -D_GNU_SOURCE -D_FILE_OFFSET_BITS=64 -D_LARGEFILE_SOURCE -fno-strict-aliasing -fwrapv -fPIC -DUNIV_LINUX -DUNIV_LINUX
_mysql.c:36:23: error: my_config.h: No such file or directory
_mysql.c:38:19: error: mysql.h: No such file or directory
_mysql.c:39:26: error: mysqld_error.h: No such file or directory
_mysql.c:40:20: error: errmsg.h: No such file or directory
[root@Knight-on-the-Cloud include]# yum -y install mysql-devel

[root@Knight-on-the-Cloud mysql]# pwd
/usr/include/mysql
[guohai@Knight-on-the-Cloud MySQL-python-1.2.3]$ python setup.py build
running build
running build_py
copying MySQLdb/release.py -> build/lib.linux-x86_64-2.6/MySQLdb
running build_ext
building '_mysql' extension
gcc -pthread -fno-strict-aliasing -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector --param=ssp-buffer-size=4 -m64 -mtune=generic -D_GNU_SOURCE -fPIC -fwrapv -DNDEBUG -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector --param=ssp-buffer-size=4 -m64 -mtune=generic -D_GNU_SOURCE -fPIC -fwrapv -fPIC -Dversion_info=(1,2,3,'final',0) -D__version__=1.2.3 -I/usr/include/mysql -I/usr/include/python2.6 -c _mysql.c -o build/temp.linux-x86_64-2.6/_mysql.o -g -pipe -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector --param=ssp-buffer-size=4 -m64 -D_GNU_SOURCE -D_FILE_OFFSET_BITS=64 -D_LARGEFILE_SOURCE -fno-strict-aliasing -fwrapv -fPIC -DUNIV_LINUX -DUNIV_LINUX
In file included from /usr/include/mysql/my_config.h:14,
                 from _mysql.c:36:
/usr/include/mysql/my_config_x86_64.h:1082:1: warning: "HAVE_WCSCOLL" redefined
In file included from /usr/include/python2.6/pyconfig.h:6,
                 from /usr/include/python2.6/Python.h:8,
                 from pymemcompat.h:10,
                 from _mysql.c:29:
/usr/include/python2.6/pyconfig-64.h:808:1: warning: this is the location of the previous definition
gcc -pthread -shared build/temp.linux-x86_64-2.6/_mysql.o -L/usr/lib64/mysql -L/usr/lib64 -lmysqlclient_r -lz -lpthread -lcrypt -lnsl -lm -lpthread -lssl -lcrypto -lpython2.6 -o build/lib.linux-x86_64-2.6/_mysql.so

编译好了就安装

[root@Knight-on-the-Cloud MySQL-python-1.2.3]# python setup.py install
running install
running bdist_egg
running egg_info
writing MySQL_python.egg-info/PKG-INFO
writing top-level names to MySQL_python.egg-info/top_level.txt
writing dependency_links to MySQL_python.egg-info/dependency_links.txt
reading manifest file 'MySQL_python.egg-info/SOURCES.txt'
reading manifest template 'MANIFEST.in'
warning: no files found matching 'MANIFEST'
warning: no files found matching 'ChangeLog'
warning: no files found matching 'GPL'
writing manifest file 'MySQL_python.egg-info/SOURCES.txt'
installing library code to build/bdist.linux-x86_64/egg
running install_lib
running build_py
copying MySQLdb/release.py -> build/lib.linux-x86_64-2.6/MySQLdb
running build_ext
creating build/bdist.linux-x86_64
creating build/bdist.linux-x86_64/egg
copying build/lib.linux-x86_64-2.6/_mysql.so -> build/bdist.linux-x86_64/egg
copying build/lib.linux-x86_64-2.6/_mysql_exceptions.py -> build/bdist.linux-x86_64/egg
creating build/bdist.linux-x86_64/egg/MySQLdb
copying build/lib.linux-x86_64-2.6/MySQLdb/__init__.py -> build/bdist.linux-x86_64/egg/MySQLdb
copying build/lib.linux-x86_64-2.6/MySQLdb/cursors.py -> build/bdist.linux-x86_64/egg/MySQLdb
copying build/lib.linux-x86_64-2.6/MySQLdb/connections.py -> build/bdist.linux-x86_64/egg/MySQLdb
copying build/lib.linux-x86_64-2.6/MySQLdb/times.py -> build/bdist.linux-x86_64/egg/MySQLdb
copying build/lib.linux-x86_64-2.6/MySQLdb/release.py -> build/bdist.linux-x86_64/egg/MySQLdb
creating build/bdist.linux-x86_64/egg/MySQLdb/constants
copying build/lib.linux-x86_64-2.6/MySQLdb/constants/REFRESH.py -> build/bdist.linux-x86_64/egg/MySQLdb/constants
copying build/lib.linux-x86_64-2.6/MySQLdb/constants/CLIENT.py -> build/bdist.linux-x86_64/egg/MySQLdb/constants
copying build/lib.linux-x86_64-2.6/MySQLdb/constants/FIELD_TYPE.py -> build/bdist.linux-x86_64/egg/MySQLdb/constants
copying build/lib.linux-x86_64-2.6/MySQLdb/constants/FLAG.py -> build/bdist.linux-x86_64/egg/MySQLdb/constants
copying build/lib.linux-x86_64-2.6/MySQLdb/constants/__init__.py -> build/bdist.linux-x86_64/egg/MySQLdb/constants
copying build/lib.linux-x86_64-2.6/MySQLdb/constants/CR.py -> build/bdist.linux-x86_64/egg/MySQLdb/constants
copying build/lib.linux-x86_64-2.6/MySQLdb/constants/ER.py -> build/bdist.linux-x86_64/egg/MySQLdb/constants
copying build/lib.linux-x86_64-2.6/MySQLdb/converters.py -> build/bdist.linux-x86_64/egg/MySQLdb
byte-compiling build/bdist.linux-x86_64/egg/_mysql_exceptions.py to _mysql_exceptions.pyc
byte-compiling build/bdist.linux-x86_64/egg/MySQLdb/__init__.py to __init__.pyc
byte-compiling build/bdist.linux-x86_64/egg/MySQLdb/cursors.py to cursors.pyc
byte-compiling build/bdist.linux-x86_64/egg/MySQLdb/connections.py to connections.pyc
byte-compiling build/bdist.linux-x86_64/egg/MySQLdb/times.py to times.pyc
byte-compiling build/bdist.linux-x86_64/egg/MySQLdb/release.py to release.pyc
byte-compiling build/bdist.linux-x86_64/egg/MySQLdb/constants/REFRESH.py to REFRESH.pyc
byte-compiling build/bdist.linux-x86_64/egg/MySQLdb/constants/CLIENT.py to CLIENT.pyc
byte-compiling build/bdist.linux-x86_64/egg/MySQLdb/constants/FIELD_TYPE.py to FIELD_TYPE.pyc
byte-compiling build/bdist.linux-x86_64/egg/MySQLdb/constants/FLAG.py to FLAG.pyc
byte-compiling build/bdist.linux-x86_64/egg/MySQLdb/constants/__init__.py to __init__.pyc
byte-compiling build/bdist.linux-x86_64/egg/MySQLdb/constants/CR.py to CR.pyc
byte-compiling build/bdist.linux-x86_64/egg/MySQLdb/constants/ER.py to ER.pyc
byte-compiling build/bdist.linux-x86_64/egg/MySQLdb/converters.py to converters.pyc
creating stub loader for _mysql.so
byte-compiling build/bdist.linux-x86_64/egg/_mysql.py to _mysql.pyc
creating build/bdist.linux-x86_64/egg/EGG-INFO
copying MySQL_python.egg-info/PKG-INFO -> build/bdist.linux-x86_64/egg/EGG-INFO
copying MySQL_python.egg-info/SOURCES.txt -> build/bdist.linux-x86_64/egg/EGG-INFO
copying MySQL_python.egg-info/dependency_links.txt -> build/bdist.linux-x86_64/egg/EGG-INFO
copying MySQL_python.egg-info/top_level.txt -> build/bdist.linux-x86_64/egg/EGG-INFO
writing build/bdist.linux-x86_64/egg/EGG-INFO/native_libs.txt
zip_safe flag not set; analyzing archive contents...
creating dist
creating 'dist/MySQL_python-1.2.3-py2.6-linux-x86_64.egg' and adding 'build/bdist.linux-x86_64/egg' to it
removing 'build/bdist.linux-x86_64/egg' (and everything under it)
Processing MySQL_python-1.2.3-py2.6-linux-x86_64.egg
Copying MySQL_python-1.2.3-py2.6-linux-x86_64.egg to /usr/lib64/python2.6/site-packages
Adding MySQL-python 1.2.3 to easy-install.pth file

Installed /usr/lib64/python2.6/site-packages/MySQL_python-1.2.3-py2.6-linux-x86_64.egg
Processing dependencies for MySQL-python==1.2.3
Finished processing dependencies for MySQL-python==1.2.3

接着再来试下

[guohai@Knight-on-the-Cloud MySQL-python-1.2.3]$ python
Python 2.6.6 (r266:84292, Jul 10 2013, 22:48:45) 
[GCC 4.4.7 20120313 (Red Hat 4.4.7-3)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import MySQLdb
/usr/lib64/python2.6/site-packages/MySQL_python-1.2.3-py2.6-linux-x86_64.egg/_mysql.py:3: UserWarning: Module _mysql was already imported from /usr/lib64/python2.6/site-packages/MySQL_python-1.2.3-py2.6-linux-x86_64.egg/_mysql.pyc, but /home/guohai/apps/installation/MySQL-python-1.2.3 is being added to sys.path

上面这些UserWarning是说MySQL模块已经引入了,但是你在的目录又被加入到sys.path,也就是重复引入。这个时候你只要跳出我们刚刚编译模块的目录就好了。
一切都正常之后我们来写个简单的程序试试看。

[guohai@Knight-on-the-Cloud npc]$ python
Python 2.6.6 (r266:84292, Jul 10 2013, 22:48:45) 
[GCC 4.4.7 20120313 (Red Hat 4.4.7-3)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import MySQLdb

>>> help(MySQLdb) // 不懂的话就可以这样查询

>>> conn = MySQLdb.connect('localhost', 'wp_user', 'wp_passwd', 'wp_db')
>>> cur = conn.cursor()
>>> cur.execute('select user_login, user_registered from wp_users')
1L
>>> rst = cur.fetchall()
>>> print rst
(('admin', datetime.datetime(2011, 3, 19, 0, 8, 9)),)
>>> cur.close()
>>> conn.close()

与我们通过

[guohai@Knight-on-the-Cloud npc]# mysql -h localhost -u wp_user -p

查询出来的一样

mysql> select * from wp_users \G
*************************** 1. row ***************************
                 ID: 1
         user_login: admin
          user_pass: #CLASSIFIED#
      user_nicename: #CLASSIFIED#
         user_email: #CLASSIFIED#
           user_url: #CLASSIFIED#
    user_registered: 2011-03-19 00:08:09
user_activation_key: #CLASSIFIED#
        user_status: #CLASSIFIED#
       display_name: #CLASSIFIED#
1 row in set (0.00 sec)

也就是说我们简单的连接数据库成功了,到此基础环境算是搭建好了。

另外简单写了个备份程序(https://github.com/guohai/my-conf/blob/master/wp_backup_npc.py),待完善。。。

然后编写一个crontab就可以让它定时执行(不会crontab的请参见http://linux.vbird.org/linux_basic/redhat6.1/linux_11cron.php)

9 16 * * 0 cd /home/guohai/apps/npc/ && python wp_backup_npc.py &> /dev/null

就是在每周日的16点9分会运行一次后面的命令(跳转到npc文件夹,执行这个python命令,如果有一些信息输出的话就把信息重定向到/dev/null,如果有信息输出而不重定向这些信息的话,crontab就会认为这个命令是执行失败的,所以就会给你的邮箱发封提醒邮件,时间久了你的/var/spool/mail/账户下就会有很多邮件,所以这里我把一切输出信息都重定向到/dev/null了)。

当然你也可以阅读简单的介绍了解crontab脚本如何编写,如下:

# Edit this file to introduce tasks to be run by cron.
#
# Each task to run has to be defined through a single line
# indicating with different fields when the task will be run
# and what command to run for the task
#
# To define the time you can provide concrete values for
# minute (m), hour (h), day of month (dom), month (mon),
# and day of week (dow) or use ‘*’ in these fields (for ‘any’).#
# Notice that tasks will be started based on the cron’s system
# daemon’s notion of time and timezones.
#
# Output of the crontab jobs (including errors) is sent through
# email to the user the crontab file belongs to (unless redirected).
#
# For example, you can run a backup of all your user accounts
# at 5 a.m every week with:
# 0 5 * * 1 tar -zcf /var/backups/home.tgz /home/
#
# For more information see the manual pages of crontab(5) and cron(8)
#
# m h dom mon dow command

Leave a Reply

Your email address will not be published. Required fields are marked *