python 连接MySQLdb

Pypi:https://pypi.org/project/MySQL-python/#history  ,最新版 MySQL-python-1.2.5.zip (2014-01-03)  推荐

SourceForge:下载 MySQL for Python ,最新版 MySQL-python-1.2.4b4.tar.gz(2012-10-08)

SetupTools:https://pypi.org/project/setuptools/#history  下载 setuptools-36.4.0.zip   (2017-09-04)

 

CentOS 7.8 安装 MySQL-python-1.2.5.zip 

1、首先安装依赖 setuptools (解决 ImportError: No module named setuptools)

wget https://files.pythonhosted.org/packages/28/4f/889339f38da415e49cff15b21ab27becbf4c017c79fbfdeca663f5b33b36/setuptools-36.4.0.zip

unzip setuptools-36.4.0.zip
cd setuptools-36.4.0/
python2.7 setup.py install

2、安装 MySQL-python-1.2.5,解决报错信息

wget https://files.pythonhosted.org/packages/a5/e9/51b544da85a36a68debe7a7091f068d802fc515a3a202652828c73453cad/MySQL-python-1.2.5.zip

unzip MySQL-python-1.2.5.zip
cd MySQL-python-1.2.5/
python2.7 setup.py install

2.1 报错信息:fatal error: Python.h: No such file or directory

building '_mysql' extension
gcc -pthread -fno-strict-aliasing -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic -D_GNU_SOURCE -fPIC -fwrapv -DNDEBUG -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic -D_GNU_SOURCE -fPIC -fwrapv -fPIC -Dversion_info=(1,2,5,'final',1) -D__version__=1.2.5 -I/usr/include/mysql -I/usr/include/mysql/mysql -I/usr/include/python2.7 -c _mysql.c -o build/temp.linux-x86_64-2.7/_mysql.o
_mysql.c:29:20: fatal error: Python.h: No such file or directory
 #include "Python.h"
                    ^
compilation terminated.
error: command 'gcc' failed with exit status 1

分析原因:查看gcc编译参数 -I/usr/include/mysql -I/usr/include/mysql/mysql -I/usr/include/python2.7,发现 Python.h 头文件路径在 /usr/include/python2.7/Python.h 找不到,需安装 yum install python-devel 

解决方案:安装 yum install mysql-devel yum install python-devel

 

2.2 报错信息:error: command 'gcc' failed with exit status 1

_mysql.c: In function ‘_mysql_ConnectionObject_ping’:
_mysql.c:2005:41: error: ‘MYSQL’ has no member named ‘reconnect’
  if ( reconnect != -1 ) self->connection.reconnect = reconnect;
                                         ^
error: command 'gcc' failed with exit status 1

解决方案:

编辑文件 vim _mysql.c

修饰掉第2005行的代码:// if ( reconnect != -1 ) self->connection.reconnect = reconnect;

解决以上setuptools依赖、找不到Python.h、reconnect代码报错,最后编译成功!

 

2.3 报错信息:ERROR 2026 (HY000): SSL connection error: protocol version mismatch

分析原因:错误2026(hy000) SSL连接错误,协议版本不匹配

解决方案:

方法一: 在/etc/my.cnf配置文件里的[client-server]模块下添加一行:skip_ssl     推荐
方法二:命令行启动添加 --skip-ssl 参数,即 mariadb -uroot -p -h 192.168.1.16 --skip-ssl
方法三:命令行启动添加 --ssl-mode 参数,即 mariadb -uroot -p -h 192.168.1.16 --ssl-mode=DISABLED

参数–ssl-mode取值

DISABLED :与参数skip-ssl一样,不使用SSL/TLS建立加密连接
PREFERRED :优先尝试使用SSL/TLS建立加密连接,如果无法建立则尝试建立非SSL/TLS连接
REQUIRED :只会尝试使用SSL/TLS建立加密连接
VERIFY_CA: 与REQUIRED行为一样,与此同时会验证server端的证书是否有效
VERIFY_IDENTITY: 与VERIFY_CA行为一样,与此同时验证server端证书的host是否与实际连接的hostname一致

参考官方文档https://dev.mysql.com/doc/refman/8.0/en/connection-options.html#option_general_ssl-mode

# 查询MySQL的ssl配置
MariaDB [mysql]> show variables like '%ssl%';
+---------------------+----------------------------------+
| Variable_name       | Value                            |
+---------------------+----------------------------------+
| have_openssl        | YES                              |
| have_ssl            | DISABLED                         |
| ssl_ca              |                                  |
| ssl_capath          |                                  |
| ssl_cert            |                                  |
| ssl_cipher          |                                  |
| ssl_crl             |                                  |
| ssl_crlpath         |                                  |
| ssl_key             |                                  |
| version_ssl_library | OpenSSL 1.0.2k-fips  26 Jan 2017 |
+---------------------+----------------------------------+
10 rows in set (0.001 sec)

 

Ubuntu 安装 MySQL-python-1.2.2.tar.gz

1) 提前安装:mysql_config 环境

否则后面 python setup.py build 会提示找不到 “EnvironmentError: mysql_config not found”,安装命令如下:

sudo apt-get install libmysqlclient-dev

sudo apt-get install python-dev (解决fatal error: Python.h: No such file or directory)

CentOS 安装 yum install mysql-devel yum install python-devel (解决error: command 'gcc' failed with exit status 1)

2) 然后,再安装MySQLdb

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

Mac OS 安装:

pip install MySQL-python

3) 验证成功安装

homer@ubuntu:~/myCode/python$ python
Python 2.7.3 (default, Aug  1 2012, 05:14:39)
[GCC 4.6.3] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>>
import MySQLdb
>>>

import MySQLdb 没有出错,说明安装成功!

 

测试示例:

import MySQLdb
db = MySQLdb.connect("localhost","myusername","mypassword","mydb" )
cursor = db.cursor()
cursor.execute("SELECT VERSION()")
data = cursor.fetchone()    
print "Database version : %s " % data    
db.close()

python 连接mysql示例:

####################
# IT-Homer
# 2013-05-10
####################


import MySQLdb


db = MySQLdb.connect(host="localhost", user="root", passwd="abcd1234", db="testDB")

cursor = db.cursor()

cursor.execute("Select * from gameTestDB limit 10")
result = cursor.fetchall()

for row in result:
  #print row
  #print row[0], row[1], row[2]
  #print '%s, %s, %s' % (row[0], row[1], row[2])
  print ', '.join([str(row[0]), str(row[1]), str(row[2])])

cursor.close()



'''
import sys
import MySQLdb

reload(sys)
sys.setdefaultencoding('utf-8')


db = MySQLdb.connect(user='root', passwd='abcd1234', charset='utf8')
cur = db.cursor()
cur.execute('use testDB')
cur.execute('select * from gameTestDB limit 10')

f = file("/home/homer/tmp_mysql.txt", 'w')

for row in cur.fetchall():
  f.write(str(row))
  f.write("\n")

f.close()
cur.close()
'''
####################
# IT-Homer
# 2013-05-10
####################


import MySQLdb

# local mysql
# db = MySQLdb.connect(host="localhost", user="root", passwd="abcd1234", db="testDB")

# aws rds mysql
db = MySQLdb.connect(host="ithomer.aliyun.com", user="ithomer", passwd="abcd1234", db="dman")

cursor = db.cursor()

cursor.execute("Select * from score limit 10")
result = cursor.fetchall()

for row in result:
  #print row
  #print row[0], row[1], row[2]
  #print '%s, %s, %s' % (row[0], row[1], row[2])
  print ', '.join([str(row[0]), str(row[1]), str(row[2])])

cursor.close()



'''
import sys
import MySQLdb

reload(sys)
sys.setdefaultencoding('utf-8')


db = MySQLdb.connect(user='root', passwd='abcd1234', charset='utf8')
cur = db.cursor()
cur.execute('use testDB')
cur.execute('select * from gameTestDB limit 10')

f = file("/home/homer/tmp_mysql.txt", 'w')

for row in cur.fetchall():
  f.write(str(row))
  f.write("\n")

f.close()
cur.close()

 

python 连接mongodb

1) 安装pymongo

pymongo 下载 ,最新 pymongo-2.6.tar.gz

安装

$ tar zxvf pymongo-2.6.tar.gz
$ cd pymongo-2.6
$ sudo python setup.py build
$ sudo python setup.py install

2)连接mongodb

#!/usr/bin/python

import pymongo
import random

HOST = '172.27.22.21'
PORT = 27017

_DB='test'
_TABLE='testuser'


conn = pymongo.Connection("172.27.22.21", 27017)
db = conn[_DB]  # get db
db.authenticate("yanggang", "123456")

table = db[_TABLE]      # get collection
table.drop()
table.save({"id":1, "name":"homer", "age":18})

for id in range(2,10):
  name = random.choice(['it', 'homer', 'sunboy', 'yanggang'])
  age = random.choice([10, 20, 30, 40, 50, 60])

  table.insert({"id":id, "name":name, "age":age})

cursor = table.find()
for user in cursor:
  print user



'''
conn = pymongo.Connection("172.27.22.21", 27017)
db = conn.test
db.authenticate("yanggang", "123456")

db.testuser.drop()
db.testuser.save({"id":1, "name":"homer", "age":18})

for id in range(2,10):
  name = random.choice(['it', 'homer', 'sunboy', 'yanggang'])
  age = random.choice([10, 20, 30, 40, 50, 60])

  db.testuser.insert({"id":id, "name":name, "age":age})

cursor = db.testuser.find()
for user in cursor:
  print user
'''

运行结果

python 连接 Redis

1)前往 redis-py 下载发布版本 release ,最新发布版本: redis-py-2.8.0.zip

2)解压 redis-py-2.8.0.zip: unzip  redis-py-2.8.0.zip , 安装: sudo python setup.py install

3)验证安装成功:

# python
>>> import redis
>>>

redis 设置密码

a) 修改配置文件

viim  redis.conf

b) 添加一行

requirepass '123456'

c)重启redis服务

/usr/local/bin/redis-server  /etc/redis.conf

d)登陆 redis-cli

$ redis-cli
127.0.0.1:6379>
set foo bar
(error) NOAUTH Authentication required. // 设置了密码,操作没有权限
127.0.0.1:6379> help auth // 查看auth命令帮助
AUTH password
summary: Authenticate to the server
since: 1.0.0
group: connection

127.0.0.1:6379> auth '123456' // 输入密码,权限认证
OK
127.0.0.1:6379> set foo bar // 密码权限认证成功后,可以操作
OK
127.0.0.1:6379> get foo
"bar"

redis-cli 远程连接

$ redis-cli --help
redis-cli 2.8.12
Usage: redis-cli [OPTIONS] [cmd [arg [arg ...]]]
-h <hostname>      Server hostname (default: 127.0.0.1).
-p <port>          Server port (default: 6379).
-s <socket>        Server socket (overrides hostname and port).
-a <password>      Password to use when connecting to the server.

redis-cli 远程连接命令

redis-cli -h 123.10.78.100 -p 6379 -a '123456'

注意:为了安全,redis不要用默认端口(6379),强烈推荐使用密码( requirepass 'xxx' ),否则很容易被别人访问!

4)简单示例:

>>> import redis
>>> r = redis.StrictRedis(host='localhost', port=6379, db=0)
>>> r.set('foo', 'bar')
True
>>> r.get('foo')
'bar'

5)python脚本示例

#!/usr/bin/python
# -*- coding: utf-8 -*-

import sys
reload(sys)
sys.setdefaultencoding('utf-8')

import redis

_REDIS_HOST = '172.27.9.104'
_REDIS_PORT = 6379
_REDIS_DB = 0


def read_redis():
    r = redis.Redis(host=_REDIS_HOST, port=_REDIS_PORT, db=_REDIS_DB)

    # 删除当前数据库的所有数据
    r.flushdb()             

    r.set('foo', 'bar')
    print(r.get('foo'))         # bar

    r.set('blog', 'ithomer.net')
    r.set('name', 'yanggang')

    # 查询没有key,返回 None
    print(r.get('none123'))     # None

    # 库里有多少key,就多少条数据
    print(r.dbsize())           # 3

    # 列出所有键值
    print(r.keys())             # ['blog', 'foo', 'name']

if __name__ == "__main__":
    read_redis()

运行结果:

bar
None
3
['blog', 'foo', 'name']

 

 

参考推荐:

Python+Selenium2 搭建自动化测试环境

Python连接MySQL、MongoDB、Redis、memcache

Linux环境变量 C_INCLUDE_PATH、LD_LIBRARY_PATH等

Python 連接 MySQL

MySQLdb User's Guide

Python 字符串操作

mysql_config not found (stackover flow)

python 创建mysql数据库

python连接mongodb并操作

redis-py (github)

 

原文: Python学习入门(4)——连接MySQL