Python mysql 基本操作

准备工作:

     1. mysql 数据库安装

    2.  mysql-connector 模块安装 

easy_install mysql-connector-python

1. 简单的数据库操作：

假设我们已经完成上面的安装，我们先来看一段数据库操作的基本代码：

import mysql
from mysql import connector
try:
    connect = mysql.connector.connect(user='root', password='password', database='kobe')
    cursor = connect.cursor()
    cursor.execute('select * from User_user')
    connect.close()
except mysql.connector.Error as e:
    print(e)

上面的代码我们在数据库中创建了一个pythonData的表。

由于我们要用mysql 及安装的mysql-connector，我们先导入这两个模块。如果不导入connector模块运行时会出现AttributeError: 'module' object has no attribute 'connector'

接下来创建一个数据库连接 mysql.connector.connect(数据库用户名，数据库密码，数据库名称，数据库ip地址，数据库端口),还可以有其他的参数，但只有前三个是必须的参数，其他的都有相应的默认值。如 host的默认值为127.0.0.1， port的默认值是3306。用户名和密码是在安装数据库的时候设置的，可以更改。database必须是一个存在的数据库，否则会出现Unknown database 'databasename'.

查看现有的数据库：打开终端，输入 mysql-u[用户名] -p，然后输入密码。如过出现下面的界面就表明连接数据库成功。

输入 show databases; (;是必须的)，如果有已存在的数据库则会列出来

如果没有那就让我们现在创建一个吧，执行 create database dataBaseName;  show databases;我们就可以看到当前的数据库了。

执行  use databaseName  就可以选择使用当前数据库

show tables; 显示当前数据库下所有的表

desc User_user ; 显示当前表的结构。

继续上面的连接函数，如果连接成功就会返回一个数据库的connect，如果不成功,我们已经把这个操作放在try...catch中catch 会捕获失败的原因并打打印出来。

对数据库的操作我们需要得到connect的游标cursor，通过connect的curosr()方法获得。获得cursor后通过cursor的execute方法来执行sql语句，执行成功后结果会保存在cursor中。

通过cursor.description 属性 获得查询结果中返回的column信息，

[(u'user_id', 253, None, None, None, None, 0, 20483), (u'user_name', 253, None, None, None, None, 0, 4097), (u'password', 253, None, None, None, None, 1, 4096), (u'email', 253, None, None, None, None, 0, 4097), (u'age', 2, None, None, None, None, 1, 32), (u'sex', 253, None, None, None, None, 0, 4097), (u'avatar', 253, None, None, None, None, 1, 0), (u'online_status', 1, None, None, None, None, 0, 4097), (u'register_time', 10, None, None, None, None, 0, 4225), (u'friend_ids', 252, None, None, None, None, 1, 16), (u'local', 253, None, None, None, None, 1, 0), (u'birthDay', 10, None, None, None, None, 1, 128), (u'mood', 253, None, None, None, None, 1, 0)]。 

这些就是我们定义的列的信息，我们只要取列表就ok 

columns = [x[0] for x in cursor.description]

cursor.fetchall()  与 fetchone() 获取结果信息。all 获取全部结果，返回一个列表， one只获取第一个元组

values = cursor.fetchall()  结果会是一个list，list中的元组就是每一行的信息。

是时候把column信息跟结果结合起来了，使用zip就相当简单了

    columns = [x[0] for x in cursor.description]
    values = cursor.fetchone()
    print(columns)
    print(values)
    results = zip(columns, values)

输出结果:

[u'name', u'age', u'sex']
(u'xiaoming', 16, 0)
[(u'name', u'xiaoming'), (u'age', 16), (u'sex', 0)]   zip就是可以把两个迭代对象的下标对应项合成一个元组，然后把这些元组合成一个列表。

看起来还不错，但如果能返回一个字典就更完美了。我们现在就来自定义一个字典，把zip的值传入然后返回一个字典

class DBDict(dict):
    def __init__(self, names=(), values=(), **kw):
        super(DBDict, self).__init__(**kw)
        for key, value in zip(names, values):
            self[key] = value

    def __getattr__(self, key):
        try:
            return self[key]
        except KeyError:
            raise AttributeError("has no attribute %s" % key)

    def __setattr__(self, key, value):
        self[key] = value

更改后代码如下：

try:
    connect = mysql.connector.connect(user='root', password='password', database='PythonPractice')
    cursor = connect.cursor()
    cursor.execute('select * from user')
    columns = [x[0] for x in cursor.description]
    values = cursor.fetchone()
    result = DBDict(columns, values)
    print(result)
    connect.close()
except mysql.connector.Error as e:
    print(e)

输出结果：

{u'age': 16, u'name': u'xiaoming', u'sex': 0}   看起来达到目标了。

最后一个问题，关于buffered，看一段代码

try:
    connect = mysql.connector.connect(user='root', password='password', database='PythonPractice', buffered=True)
    cursor = connect.cursor()
    cursor.execute('select * from user')
    cursor.execute("select name from user")
    print(cursor.fetchall())
    connect.close()
except mysql.connector.Error as e:
    print(e)

执行结果报错 Unread result found.

原因是buffered，如果我们在connect的参数中添加 buffered = True 就不会报错了 返回结果[(u'xiaoming',)]

bufferd不仅可以在connect的参数中指定，也可以在cursor的参数中指定。

如果buffered == True,表示在请求端会对结果就行缓冲，当我们执行fetch的时候就是从cursor的缓冲信息中拿到数据，而buffered == False 表示请求端不会进行缓冲， mysql的server端进行缓冲，当fetch的时候再从mysql的server端把数据拿下来。

我们上面的情况，当执行第一个 select * from user的时候，mysql server会为这个connect保存一份请求结果的缓冲，所以当我们再次执行 select name from user的时候，mysql server 会说‘你大爷的上一次结果还没拿走呢，先拿走再说‘，所以呢如果我们不把buffered设成YES，只要fetch一次把数据取走就ok了。要不然就报错了。

当设成YES的时候，每一次执行sql语句的结果都会直接拿回来自己缓冲，而不是等fetch的时候再去拿，但这样会消耗机器的内存。