用Pyton操作数据库(1):建立连接和游标,并insert and commit

最后更新于:2022-04-01 01:05:57

在[上一讲](https://github.com/qiwsir/ITArticles/blob/master/BasicPython/302.md)中已经连接了数据库。就数据库而言,连接之后就要对其操作。但是,目前那个名字叫做qiwsirtest的数据仅仅是空架子,没有什么可操作的,要操作它,就必须在里面建立“表”,什么是数据库的表呢?下面摘抄自维基百科对数据库表的简要解释,要想详细了解,需要看官在找一些有关数据库的教程和书籍来看看。 > 在关系数据库中,**数据库表**是一系列二维数组的集合,用来代表和储存数据对象之间的关系。它由纵向的列和横向的行组成,例如一个有关作者信息的名为 authors 的表中,每个列包含的是所有作者的某个特定类型的信息,比如“姓氏”,而每行则包含了某个特定作者的所有信息:姓、名、住址等等。 > > 对于特定的数据库表,列的数目一般事先固定,各列之间可以由列名来识别。而行的数目可以随时、动态变化,每行通常都可以根据某个(或某几个)列中的数据来识别,称为候选键。 我打算在qiwsirtest中建立一个存储用户名、用户密码、用户邮箱的表,其结构用二维表格表现如下: | username | password | email | | --- | --- | --- | | qiwsir | 123123 | [qiwsir@gmail.com](mailto:qiwsir@gmail.com) | 特别说明,这里为了简化细节,突出重点,对密码不加密,直接明文保存,虽然这种方式是很不安全的。但是,有不少网站还都这么做的,这么做的目的是比较可恶的。就让我在这里,仅仅在这里可恶一次。 ## 建数据库表并插入数据 为了在数据库中建立这个表,需要进入到`mysql>`交互模式中操作。道理在于,如果qiwsirtest这个屋子里面没有类似家具的各种数据库表,即使进了屋子也没有什么好操作的东西,因此需要先到`mysql>`模式下在屋子里面摆家具。 进入数据库交互模式: ~~~ qw@qw-Latitude-E4300:~$ mysql -u root -p Enter password: ~~~ 调用已经建立的数据库:qiwsirtest ~~~ mysql> use qiwsirtest; Database changed mysql> show tables; Empty set (0.00 sec) ~~~ 用`show tables`命令显示这个数据库中是否有数据表了。查询结果显示为空。 下面就用如下命令建立一个数据表,这个数据表的内容就是上面所说明的。 ~~~ mysql> create table users(id int(2) not null primary key auto_increment,username varchar(40),password text,email text)default charset=utf8; Query OK, 0 rows affected (0.12 sec) ~~~ 建立的这个数据表名称是:users,其中包含上述字段,可以用下面的方式看一看这个数据表的结构。 ~~~ mysql> show tables; +----------------------+ | Tables_in_qiwsirtest | +----------------------+ | users | +----------------------+ 1 row in set (0.00 sec) ~~~ 查询显示,在qiwsirtest这个数据库中,已经有一个表,它的名字是:users。 ~~~ mysql> desc users; +----------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+----------------+ | id | int(2) | NO | PRI | NULL | auto_increment | | username | varchar(40) | YES | | NULL | | | password | text | YES | | NULL | | | email | text | YES | | NULL | | +----------+-------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec) ~~~ 显示表users的结构: * id:每增加一个用户,id号自动增加一个。 * username:存储用户名,类型是varchar(40) * password:存储用户密码,类型是text * email:存储用户的邮箱,类型是text 特别提醒:在这里,我没有对每个字段做注入不得为空等设置,在真正的开发中,或许必须让username和password不得为空。 这个结构和上面所期望的结构是一样的,只不过这个表中还没有任何数据,是一个空表。可以查询一下看看: ~~~ mysql> select * from users; Empty set (0.01 sec) ~~~ 目前表是空的,为了能够在后面用python操作这个数据表,需要向里面插入点信息,就只插入一条吧。 ~~~ mysql> insert into users(username,password,email) values("qiwsir","123123","qiwsir@gmail.com"); Query OK, 1 row affected (0.05 sec) mysql> select * from users; +----+----------+----------+------------------+ | id | username | password | email | +----+----------+----------+------------------+ | 1 | qiwsir | 123123 | qiwsir@gmail.com | +----+----------+----------+------------------+ 1 row in set (0.00 sec) ~~~ 到目前为止,在`mysql>`中的工作已经完成了,接下来就是用python操作了。 ## python操作数据库 要对数据库进行操作,需要先连接它。上一讲看官连接过了,但是,随后你关闭了python的交互模式,所以还要从新连接。这也是交互模式的缺点。不过在这里操作直观,所以暂且忍受一下,后面就会讲解如何在程序中自动完成了。 ~~~ >>> import MySQLdb >>> conn = MySQLdb.connect(host="localhost",user="root",passwd="123123",db="qiwsirtest",charset="utf8") ~~~ 完成连接的过程,其实是建立了一个`MySQLdb.connect()`的实例对象conn,那么这个对象有哪些属性呢? * commit():如果数据库表进行了修改,提交保存当前的数据。当然,如果此用户没有权限就作罢了,什么也不会发生。 * rollback():如果有权限,就取消当前的操作,否则报错。 * cursor([cursorclass]):游标指针。下面详解。 连接成功之后,开始操作。注意:MySQLdb用游标(指针)cursor的方式操作数据库,就是这样: ~~~ >>> cur = conn.cursor() ~~~ 因该模块底层其实是调用CAPI的,所以,需要先得到当前指向数据库的指针。这也就提醒我们,在操作数据库的时候,指针会移动,如果移动到数据库最后一条了,再查,就查不出什么来了。看后面的例子就明白了。 下面用cursor()提供的方法来进行操作,方法主要是: 1. 执行命令 2. 接收结果 ### cursor执行命令的方法: * execute(query, args):执行单条sql语句。query为sql语句本身,args为参数值的列表。执行后返回值为受影响的行数。 * executemany(query, args):执行单条sql语句,但是重复执行参数列表里的参数,返回值为受影响的行数 例如,要在数据表users中插入一条记录,使得:username="python",password="123456",email="[python@gmail.com](mailto:python@gmail.com)",这样做: ~~~ >>> cur.execute("insert into users (username,password,email) values (%s,%s,%s)",("python","123456","python@gmail.com")) 1L ~~~ 没有报错,并且返回一个"1L"结果,说明有一行记录操作成功。不妨用"mysql>"交互方式查看一下: ~~~ mysql> select * from users; +----+----------+----------+------------------+ | id | username | password | email | +----+----------+----------+------------------+ | 1 | qiwsir | 123123 | qiwsir@gmail.com | +----+----------+----------+------------------+ 1 row in set (0.00 sec) ~~~ 咦,奇怪呀。怎么没有看到增加的那一条呢?哪里错了?可是上面也没有报错呀。 在这里,特别请列位看官注意,通过"cur.execute()"对数据库进行操作之后,没有报错,完全正确,但是不等于数据就已经提交到数据库中了,还必须要用到"MySQLdb.connect"的一个属性:commit(),将数据提交上去,也就是进行了"cur.execute()"操作,要将数据提交,必须执行: ~~~ >>> conn.commit() ~~~ 在到"mysql>"中运行"select * from users"试一试: ~~~ mysql> select * from users; +----+----------+----------+------------------+ | id | username | password | email | +----+----------+----------+------------------+ | 1 | qiwsir | 123123 | qiwsir@gmail.com | | 2 | python | 123456 | python@gmail.com | +----+----------+----------+------------------+ 2 rows in set (0.00 sec) ~~~ good,very good。果然如此。这就如同编写一个文本一样,将文字写到文本上,并不等于文字已经保留在文本文件中了,必须执行"CTRL-S"才能保存。也就是在通过python操作数据库的时候,以"execute()"执行各种sql语句之后,要让已经执行的效果保存,必须运行"commit()",还要提醒,这个属性是"MySQLdb.connect()"实例的。 再尝试一下插入多条的那个命令"executemany(query,args)". ~~~ >>> cur.executemany("insert into users (username,password,email) values (%s,%s,%s)",(("google","111222","g@gmail.com"),("facebook","222333","f@face.book"),("github","333444","git@hub.com"),("docker","444555","doc@ker.com"))) 4L >>> conn.commit() ~~~ 到"mysql>"里面看结果: ~~~ mysql> select * from users; +----+----------+----------+------------------+ | id | username | password | email | +----+----------+----------+------------------+ | 1 | qiwsir | 123123 | qiwsir@gmail.com | | 2 | python | 123456 | python@gmail.com | | 3 | google | 111222 | g@gmail.com | | 4 | facebook | 222333 | f@face.book | | 5 | github | 333444 | git@hub.com | | 6 | docker | 444555 | doc@ker.com | +----+----------+----------+------------------+ 6 rows in set (0.00 sec) ~~~ 成功插入了多条记录。特别请列位注意的是,在"executemany(query,args)"中,query还是一条sql语句,但是args这时候是一个tuple,这个tuple里面的元素也是tuple,每个tuple分别对应sql语句中的字段列表。这句话其实被执行多次。只不过执行过程不显示给我们看罢了。 已经会插入了,然后就可以有更多动作。且看下一讲吧。
';