Python系列之入门篇——MYSQL

Python系列之入门篇——MYSQL

简介

python提供了两种mysql api, 一是MySQL-python(不支持python3),二是PyMYSQL(支持python2和python3)

代码示例

  1. 安装
    pip install MySQL-python
  2. 引入相关模块
    import MySQLdb
  3. 创建客户端
    conn = MySQLdb.connect(host, user, passwd, database, charset='utf8')
    cur = conn.cursor()
  4. 插入
    """
    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()
  5. 查询
    """
    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()
  6. 异常处理

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
上一篇:Python系列之入门篇——HDFS


下一篇:Pandas系列之入门篇——HDF5