【跟着Head First学python】7、使用数据库：具体使用Python的DB-API

1、安装mysql

我的服务器上已经有了mysql，因此这里只进行win平台上的mysql安装。

很简单，进入该网址，下载并安装即可。

安装之后并不能直接用，这个安装其实是解压，要想真正安装成功，需要利用cmd进入mysql解压后的文件夹，进入bin文件夹，在bin文件夹中运行以下命令：

mysqld -install

出现安装成功才算是安装完毕。如图：

注意，如果出现Install/Remove of the Service Denied，说明运行cmd时未用管理员模式，进入管理员模式重新运行上述命令即可。

PS：win10进入管理员模式很麻烦，我用的方法是在左下角搜索命令提示符，然后右键以管理员身份运行：

然后mysql就安装好了。

接下来在bin文件夹中运行如下命令：

mysql -u root -p

报错如下：

这是由于mysql服务未能启动。直接启动服务可能失败，若未能成功启动，报错无法启动，则按如下方式运行：

输入以下代码以初始化mysql，创建data文件夹：

mysqld --initialize-insecure

之后重新启动服务即可。如图：

然后即可登录mysql，如下：

2、安装mysql驱动

windows前往该网址安装驱动即可。linux参考该网址安装并验证。

python提供了一整套api供人使用，从而对于不同的数据库，都可用同一种方法实现功能。这个驱动就是把api和mysql联系起来的。

3、使用mysql

①、创建用户

在mysql文件夹的bin文件夹中，按如下输入命令：

mysql -u root -p

会让你输入密码，因为我们连用户都没有，无法输入密码，因此两次回车之后可以直接进入mysql。

②、创建数据库

我们将创建一个名为vsearch的数据库。书中使用如下命令：

grant all on vsearchlogDB.* to 'vsearch' identified by 'vsearchpasswd';

然而以上代码则报错如下：

开始我以为是符号问题，查阅了一下发现sql中有用到`这个符号，也就是键盘上1左侧的那个，于是我把单引号改成这个：

嗯，还是一样报错，于是采用迂回方法，先建用户，再给权限：

这样就正常了。推测是mysql8以后不允许直接建立用户并给权限的操作了。

③、新建表

使用如下命令来新建一个拥有多项的名为log的表。

create table log(
    -> id int auto_increment primary key,
    -> ts timestamp default current_timestamp,
    -> phrase varchar(128) not null,
    -> letters varchar(32) not null,
    -> ip varchar(16) not null,
    -> browser_string varchar(256) not null,
    -> results varchar(64) not null);

使用如下命令来查看已建立好的表。

discribe log;

效果如下：

还不错。

4、python与mysql的连接

python与mysql的连接，本质上可以把python这端看成一个人（用户端），mysql这端看成另一个人（服务器端），然后通过翻译机（cursor）进行联系。因为python的语法和SQL不同，它们互相听不懂对方说什么，因此需要间接联系。这里用的cursor，中文翻译成游标。

首先我们要python端与SQL端取得联系。

这需要创建一个连接，用到数据库的驱动程序connector。

为创建连接，我们要建立一个字典，字典里面写明连接的属性：服务器地址、数据库用户、数据库密码、数据库名字。

有了这四个属性，SQL就可以建立一条确定的唯一的连接，使得用户端和sql端建立连接。

建立字典如下：

dbconfig={'host':'127.0.0.1',
          'user':'vsearch',
          'password':'vsearchpasswd',
          'database':'vsearchlogDB',}

然后使用驱动建立连接，代码如下：

import mysql.connector
conn=mysql.connector.connect(**dbconfig)

这里使用到了一个特殊的记法，**。这表示它后面的变量是一个参数字典，connect函数会把这个字典拆成四个单独的参数，然后使用这四个参数来建立连接。

接下来需要一个翻译机，也就是游标（cursor），代码如下：

cursor=conn.cursor()

然后就可以用这个游标，使用SQL语言与服务器端的数据库通信了。

举例如下，我们要查看数据库中的表，SQL语句为

show tables

那么，python中的代码如下：

_SQL="""show tables"""
cursor.execute(_SQL)

注意，SQL代码是存在一个字符串中的，这样python就不会试图去理解这句代码的意思，而是老老实实通过cursor把这句代码传到服务器端，并运行。

然而它并不会返回一个结果，需要我们自己去请求结果。

请求结果有三种方式：

fetchone：请求一行结果；

fetchmany：请求指定行结果；

feychall：请求所有结果。

我们选择请求所有结果。代码如下：

res=cursor.fetchall()
res

效果如下：

成了，就一个log。注意返回的是一个列表，列表中的元素是元组。也就是说。fetchall返回值的类型一定是一个元组列表，即使只有一个元素也不例外。由于我们在3.3中只建了一张名为log的表，因此这里只有log一个结果。

如果想要查看表中的内容怎么办呢？用SQL的describe语句。如下：

_SQL="""describe log"""
cursor.execute(_SQL)
res=cursor.fetchall()
res

效果如下：

感觉有点乱。但还是可以看出，返回的仍是一个列表（最外面是方括号[]），列表的每个元素都是元组（次外层是括号()），元组元素为字符串，若元组中元素为空，则元组元素不显示。

表中每一行对应一个元组。这样来看，按行输出比较合适，效果如下：

还不错。res中就保存了log表中的所有数据。

下面来看如何在表中添加数据。使用SQL的insert语句。如下：

_SQL="""insert into log
        (phrase,letters,ip,browser_string,results)
        values
        ('hitch-hicker','aeiou','127.0.0.1','Firefox',"{'e','i'}")"""
cursor.execute(_SQL)

然后查看表中内容是否添加成功。使用select命令。注意describe是用于描述一个表的，它描述了表的属性，而不管表的内容；select则用于显示表的内容，而不显示表的属性。代码如下：

_SQL="""select * from log"""
cursor.execute(_SQL)
for row in cursor.fetchall():
	print(row)

select * from log，意思就是从log中选出所有元素，没有条件，*表示无条件。还是很容易理解的。

效果如下：

注意，可能什么都不显示。因为对于SQL来说，插入操作是一个消耗很大的操作，很多数据库系统可能会把一次插入操作先写入缓存，然后等到有多次插入操作之后再同一执行插入。这样一来，我们select时就可能无法得到想要的结果，因为实际的插入操作还未发生。可以使用如下语句进行强制将缓存中的数据全部插入：

conn.commit()

这样就可以保证数据库中的数据是最新的。

然而这种插入操作很蠢，为什么呢？观察_SQL这个变量，它是一句SQL代码，对于insert操作，每次都要重新生成一条代码来插入不同的数据，这太低效了，如果有一种方法，可以将insert语句与待插入内容分开，要用的时候再合并，也就是说，把insert语句看成一个函数，可重用，这就好了。有这种方法。代码如下：

_SQL="""insert into log
        (phrase,letters,ip,browser_string,results)
        values
        (%s,%s,%s,%s,%s)"""
cursor.execute(_SQL,('hitch-hiker','xyz','127.0.0.1','Safari','set()'))

看如下代码，_SQL并没有具体说明要插入的内容，而是用五个占位符%s来进行指定。在实际应用时，只需要在execute后加入一个元组，元组内容对应占位符即可。

接下来再看效果：

ok，实现了期待的功能。

在操作之后，要进行清理，关闭游标以及连接。代码如下：

先关闭游标，再关闭连接即可。

5、创建代码处理Web应用的数据库和表

由于有了以上的铺垫，我们现在要实现以下功能：修改Webapp的log_request函数，让它保存的数据不是存在一个txt里，而是存在SQL数据库中。

原代码如下：

def log_request(req:'flask_request',res:str)->None:
    with open('vsearch.log','a') as log:
        print(req.form,req.remote_addr,req.user_agent,res,file=log,sep='|')

修改之后，要实现与数据库的连接，实现写入等功能，其实蛮简单，如下：

import mysql.connector
def log_request(req:'flask_request',res:str)->None:
    dbconfig={'host':'127.0.0.1',
              'user':'vsearch',
              'password':'vsearchpasswd',
              'database':'vsearchlogDB',}
    conn=mysql.connector.connect(**dbconfig)
    cursor=conn.cursor()
    _INSERT="""insert into log
	           (phrase,letters,ip,browser_string,results)
	           values
	           (%s,%s,%s,%s,%s)"""
    cursor.execute(_INSERT,(req.form['phrase'],
                            req.form['letters'],
                            req.remote_addr,
                            req.user_agent.browser,
                            res,))
    conn.commit()
    cursor.close()
    conn.close()

有两点要注意，其一，是import的位置，不要在函数内部；其二，对于数据的写入，req.form是一个字典，有两项，一项是phrase，即输入；一项是letters，即输出。

另外，注意第四项写入的元素req.user_agent.browser，如果只是req.user_agent，则会报错如下：

猜测是由于req.user_agent的类型与表中的默认类型不匹配造成的。

完成之后，可以试验几次，并查看其效果：

后三次即是测试样例，说明已经写入成功。

现在我们发现了一个新的问题：

对于那些可能会用到多次的功能，如建立连接、断开连接等，是否可以实现重用呢？如果每次修改SQL都需要这样繁琐的一步步建立和断开连接，一点都不优美。联想到之前的打开-关闭文件，我们也可以用with来实现这一点。如下所示：

def log_request(req:'flask_request',res:str)->None:
    #with open('vsearch.log','a') as log:
        #print(req.form,req.remote_addr,req.user_agent,res,file=log,sep='|')
    dbconfig={'host':'127.0.0.1',
              'user':'vsearch',
	      'password':'vsearchpasswd',
	      'database':'vsearchlogDB',}
    with UseDatabase(dbconfig) as cursor:
        _INSERT="""insert into log
                (phrase,letters,ip,browser_string,results)
                values
                (%s,%s,%s,%s,%s)"""
        cursor.execute(_INSERT,(req.form['phrase'],
                                req.form['letters'],
                                req.remote_addr,
                                req.user_agent.browser,
                                res,))

这是使用with版本的代码。但是还不能工作，因为我们还没有写Usedatabase这一函数的上下文管理器，这里看看就好。

使用上下文管理器需要创建类，在下面几章，我们将学习如何创建类。