在Ubuntu下安装MySQLdb模块
因为要使用Python操作数据库，所以需要安装MySQLdb模块，但是第一次在Ubuntu下安装的时候失败了，但后来我想着以后可能会经常需要，所以还是得安装好了，以后才会方便一些。
- 安装PIP
http://pip.readthedocs.org/en/latest/installing.html
wget --no-check-certificate https://bootstrap.pypa.io/get-pip.py python get-pip.py
- 安装MySQLdb
先使用PIP安装，报错如下：
# pip install MySQLdb Downloading/unpacking MySQLdb Real name of requirement MySQLdb is mysqldb Could not find any downloads that satisfy the requirement MySQLdb Cleaning up... No distributions at all found for MySQLdb Storing debug log for failure in /root/.pip/pip.log
然后尝试源码安装：
# wget --no-check-certificate https://pypi.python.org/packages/source/M/MySQL-python/MySQL-python-1.2.5.zip # unzip MySQL-python-1.2.5.zip
错误1：
# python setup.py install
sh: 1: mysql_config: not found
Traceback (most recent call last):
File “setup.py”, line 17, in <module>
metadata, options = get_config()
File “/root/downLoads/MySQL-python-1.2.5/setup_posix.py”, line 43, in get_config
libs = mysql_config(“libs_r”)
File “/root/downLoads/MySQL-python-1.2.5/setup_posix.py”, line 25, in mysql_config
raise EnvironmentError(“%s not found” % (mysql_config.path,))
EnvironmentError: mysql_config not found
解决办法1：
安装libmysqld-dev（# apt-get install libmysqld-dev）
错误2：
_mysql.c:29:20: fatal error: Python.h: No such file or directory
#include “Python.h”
^
compilation terminated.
error: command ‘i686-linux-gnu-gcc’ failed with exit status 1
解决办法2：
安装python-dev（# apt-get install python2.7-dev）
然后再次安装的时候，虽然中途有warning，但是已经不碍事了：
Adding MySQL-python 1.2.5 to easy-install.pth file
Installed /usr/local/lib/python2.7/dist-packages/MySQL_python-1.2.5-py2.7-linux-i686.egg
Processing dependencies for MySQL-python==1.2.5
Finished processing dependencies for MySQL-python==1.2.5
安装成功。
参考链接：
在Windows上安装MySQLdb
如果你安装了VS2010及以上版本，可以考虑源码安装，如果图方便的话，就去下载别人编译好的exe然后安装吧。{http://www.lfd.uci.edu/~gohlke/pythonlibs/#mysql-python}
使用Python操作MySQLdb的样例
>>> import MySQLdb
>>> conn = MySQLdb.connect(host="localhost", user="root", passwd="123123", db="test", charset="utf8") #localhost有时需要写成127.0.0.1，否则会报错，具体原因不详
>>> cur = conn.cursor()
>>> cur.execute("insert into users (username,password,email) values (%s,%s,%s)", ("python","123456","[email protected]"))
>>> conn.commit()
>>> cur.executemany("insert into users (username,password,email) values (%s,%s,%s)",(("google","111222","[email protected]"),("facebook","222333","[email protected]"),("github","333444","[email protected]"),("docker","444555","[email protected]")))
4L
>>> conn.commit()
参考链接：
Windows下MySQL忘记密码的处理办法
Console1: mysqld –skip-grant-tables
Console2: mysql -uroot -p
update mysql.user set password=PASSWORD('xxx') where User='root';
flush privileges;
=========Login again=============
《 “Python的MySQLdb模块” 》 有 4 条评论
使用Python的MySQLdb模块连接MySQL有时会报错：`
_mysql_exceptions.OperationalError: (2002, “Can’t connect to local MySQL server through socket ‘/var/lib/mysql/mysql.sock’ (2)”)
`
解决办法：
将 `localhost` 修改成 `127.0.0.1`
http://stackoverflow.com/a/6482408
在使用Python的MySQLdb模块进行操作时，要特别注意 fetchall() 函数的返回值类型，要使用某个特定的查询结果时，要注意进行强制类型转换，比如将 unicode 转换成 str 类型
`
sql = ‘select user_name from db_name.tbl_name’
cur.execute(sql)
rows = cur.fetchall()
print type(rows) # tuple
print type(rows[0]) # tuple
print type(rows[0][0]) # unicode
In [30]: sql = ‘select user_name from db_name.tbl_name’
In [31]: cur.execute(sql)
Out[31]: 3L
In [32]: rows = cur.fetchall()
In [33]: print type(rows)
In [34]: print type(rows[0])
In [35]: print type(rows[0][0])
In [36]: print rows
((u’user1′,), (u’user2′,), (u’user3′,))
In [37]: print rows[0]
(u’user1′,)
In [38]: print rows[0][0]
user1
`
pymysql.err.ProgrammingError: 1064 (Python字符串转义问题)
http://www.cnblogs.com/hejunlin1992/p/8194284.html
`
报错信息：
ProgrammingError: (1064, u’You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near \’s MacBook Pro”}\’),(…,\’…\’ at line 1′)
sql = “””INSERT INTO video_info(video_id, title) VALUES(“%s”,”%s”)””” % (video_info[“id”], video_info[“title”])
改为：
sql = “””INSERT INTO video_info(video_id, title) VALUES(“%s”,”%s”)””” % (video_info[“id”], pymysql.escape_string(video_info[“title”]))
`
python MySQLdb中转义字符串的问题
http://blog.sina.com.cn/s/blog_5357c0af0100uxzb.html
Python equivalent of mysql_real_escape_string, for getting strings safely into MySQL?
https://stackoverflow.com/questions/2561178/python-equivalent-of-mysql-real-escape-string-for-getting-strings-safely-into-m
`
>>> import MySQLdb
>>> MySQLdb.escape_string(“‘”)
“\\’”
# 或者
>>> import pymysql
>>> pymysql.escape_string(“‘”)
“\\’”
`
Python-MySQL使用心得
http://huiliu.github.io/2016/01/19/pymysql.html