Python学习入门(4)——连接MySQL
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连接MySQL、MongoDB、Redis、memcache
Linux环境变量 C_INCLUDE_PATH、LD_LIBRARY_PATH等
mysql_config not found (stackover flow)
redis-py (github)
版权所有: 本文系米扑博客原创、转载、摘录,或修订后发表,最后更新于 2024-07-29 18:29:01
侵权处理: 本个人博客,不盈利,若侵犯了您的作品权,请联系博主删除,莫恶意,索钱财,感谢!