Python系列之入门篇——MYSQL
简介
python提供了两种mysql api, 一是MySQL-python(不支持python3),二是PyMYSQL(支持python2和python3)
代码示例
- 安装
pip install MySQL-python - 引入相关模块
import MySQLdb - 创建客户端
conn = MySQLdb.connect(host, user, passwd, database, charset='utf8')
cur = conn.cursor() - 插入
"""
it has two methods to insert, one is execute() one by one, and another is executemany() multi-row, it has same principle, both execute one by one
data : [()]
affects : int affect rownums
"""
sql = 'INSERT INTO student(no, name, sex, age) VALUES(%s, %s, %s, %s)'
data = [(001, 'john', 'male', '20'), (002, 'merry', 'female', '19')]
affects = cur.execute(sql, data)
conn.commit() - 查询
"""
fetchone() : get only one row
fetchmany(size=100) : get some rows as you set the size
fetchall() : get all rows
"""
sql = 'SELECT no, name, sex, age FROM student'
cur.execute(sql)
conn.commit()
rows = cur.fetchall() - 异常处理
ERROR 2006 (HY000): MySQL server has gone away
异常描述: 见 http://www.cnblogs.com/dzqk/p/8237030.html
解决方案: 在有大数据量分析的时候,执行sql前判断连接的状态,请看下面的连接重试方法
def conn_retry(logger, conn, retry_count, *args):
"""
Mysql reconnect
Parameters
----------
logger : Logger
conn : Connection
retry_count : int
args : tuple --> host, user, passwd, database
Returns
-------
conn : Connection
"""
try:
conn.ping(True)
return conn
except Exception as e:
logger.error(e)
logger.error('Mysql connection is closed, Now retry connect...')
retry = 0
while retry < retry_count:
try:
logger.debug('Retry times is %i' % (retry + 1))
return MySQLdb.connect(list(args)[0], list(args)[1], list(args)[2], list(args)[3], charset='utf8')
except Exception as e:
logger.error(repr(e))
retry += 1
else:
return None