python 连接 mysql 及其注意事项

发表于 2016-02-24 标签 python ，数据库

本文主要讲述利用 python 连接数据库的过程和部分注意事项。文章不会涉及到连接的原理，只是简单介绍连接的步骤，以及 mysql 不同的引擎连接的过程的细微区别。

基本上任何语言与数据库进行交互都需要引入外部的数据库驱动，在 python 操作 mysql 数据库中常用的就是 MySQLdb 这个驱动，后面也会以这个为例子进行讲解。

数据库的最常见的操作就是 “增删查改”，实现这几个功能需要对应的 SQL 语句。而通过程序连接数据库实际上就是获得与数据库的连接，通过这个连接执行 SQL 命令，得到返回结果（如果有返回结果的话）。

因此，通过 python 操作 mysql 的步骤可以概括为下面 3 个步骤：
1. 获得连接
2. 获取游标
3. 通过游标执行 SQL 语句，获取返回结果（如果有）

获得连接

获得连接的代码如下

import MySQLdb

HOST = XXXX  
PORT = XXXX  
USER = XXXX  
PASSWD = XXXX  
DB = XXXX  
CHARSET = XXXX

conn = MySQLdb.connect(host=HOST,port=PORT,user=USER,passwd=PASSWD,db=DB,charset=CHARSET)

```  
一开始声明的几个常量表示要连接到哪台机器的哪个数据库以及采用的编码，**注意除了PORT为整数类型，其余都为字符类型。**

假如在某个工程中有多个地方需要操作数据库，可以将这个写成一个函数。如下所示：

```py  
def connectDB():  
    try:  
        conn = MySQLdb.connect(passwd=PASSWD, host=HOST, user=USER, port=PORT, db=DB, charset='utf8')  
        return conn  
    except:  
        return 0  
```  
可通过返回值是不是0判断是否建立了正常连接。

## **获取游标**  
获得连接后，我们希望做的就是执行我们的 SQL 语句，但是在MySQLdb中，conn并不能执行此操作。**需要通过游标（cursor）来执行命令并保存执行的结果，而游标可通过第一步得到的连接获取**。代码如下：

```  
cursor = conn.cursor()

执行 SQL 语句，获得返回结果

可以通过 cursor.execute(SQL) 来执行 SQL 语句，通过 cursor.fetchall() 获取返回的结果（针对 select 语句）
这里获取返回结果针对的是查询（select）语句。

查询的代码代码如下

SQL = "select * from table"  
try:  
    cursor.execute(SQL)  
    resultSet = cursor.fetchall()  
    for row in resultSet:  
       for i in row:  
           print i  
except:  
    print 'error while querying'  
    return 0  
finally:  
```  
执行的SQL语句返回的结果集可以认为是一个嵌套的两级列表，数据库中每一条记录是一级列表中的一个元素。

**插入新纪录**的代码如下：  
```py  
SQL = 'insert into table(field) values(%s) ' %record  
try:  
    cursor.execute(SQL)  
except:  
    print 'error while querying'  
    return 0

另外删除和更新的代码也类似，只是 SQL 语句不同而已。

这里需要注意的一点就是上面的代码有可能执行成功，但是数据库中不会更新记录。

原因是 mysql 的引擎问题，假如 mysql 的引擎是 MyISAM，那么上面的代码就没问题，但是假如 mysql 的引擎是 InnoDB，那么上面的插入新纪录的代码将不会执行成功。因为 InnoDB 的是支持事务处理的，执行更新的操作会在 mysql 事先分配的缓存中进行，只有提交后，修改才能生效。提交的操作也很简单，就是在 cursor.execute () 后加上这句：

conn.commit()  
```  
关于这个问题，[MySQLdb官网说明][1]如下：

>commit()  
If the database and the tables support transactions, this commits the current transaction; otherwise this method successfully does nothing.  
rollback()  
If the database and tables support transactions, this rolls back (cancels) the current transaction; otherwise a NotSupportedError is raised.

从说明中可以看到，commit()函数对于支持事务的引擎生效，对于不支持事务的引擎也不会报错，所以**建议在代码中均使用**。除了commit()函数外，对于支持事务的引擎还有一个rollback()函数用于执行失败后的回滚，但是这个只能在支持事务的引擎上使用。

最后，上个完整的代码  
```py  
# encoding:utf-8  
# 将数据文件的数据导入到mysql数据库中

import MySQLdb  
import ReadDirFiles

HOST = XXXX  
PORT = XXXX  
USER = XXXX  
PASSWD = XXXX  
DB = XXXX  
CHARSET = XXXX

def connectDB():  
    try:  
        conn = MySQLdb.connect(host=HOST, user=USER, passwd=PASSWD, port=PORT, db=DB, charset='utf8')  
        return conn  
    except:  
        print 'error connecting the database'  
        return 0

def importData():  
    conn = connectDB()  
    if conn == 0:  
        return 0  
    cursor = conn.cursor()  
    SQL = 'insert into `ad_log` values("%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s") ' %tuple  
    # print SQL # 检查SQL语句是否正确  
    # 下面的代码是一个事务  
    try:  
        cursor.execute(SQL)  
        conn.commit()  
        print 'successfully insert the record'  
    except:  
        conn.rollback() #引擎不支持事务时会报错  
    finally:  
        cursor.close()  
        conn.close()

** 获得连接 **

执行 SQL 语句，获得返回结果

获得连接