乘风原创程序

  • Python操作数据库之 MySQL
  • 2020/8/4 6:01:20
  • Python操作数据库之MySQL

     

     

     

    一、安装Python-MySQLdb模块

    Python-MySQLdb是一个操作数据库的模块,Python 通过它对 mysql 数据实现各种操作。

    如果要源码安装,可以这里下载: https://pypi.Python.org/pypi/MySQL-Python/

    解压安装包,进入解压目录执行以下命令安装

    python setup.py install

     

    ubuntu 下可以这么做:

    sudo apt-get install build-essential Python-dev libmysqlclient-dev
    sudo apt-get install Python-MySQLdb

     

     

    pip安装:

    pip install mysql-Python

     

    安装之后,在 python 交互模式下:

    >>> import MySQLdb

    如果不报错,恭喜你,已经安装好了。

     

     

    二、操作数据库

    操作数据库流程:

    1、导入MySQLdb模块

    2、创建数据库连接

    3、执行SQL语句和存储过程

    4、关闭数据库连接

     

     

    创建MySQL连接对象

    >>> import MySQLdb
    >>> conn = MySQLdb.connect(host="localhost",user="root",passwd="123456",db="python",port=3306,charset="utf8")
    >>>

     

    命令含义解释:

    host:等号的后面应该填写 mysql 数据库的地址,因为就数据库就在本机上(也称作本地),所以使用 localhost,注意引号。如果在其它的服务器上,这里应该填写 ip 地址。一般中小型的网站,数据库和程序都是在同一台服务器(计算机)上,就使用 localhost 了。

    user:登录数据库的用户名,这里一般填写"root",还是要注意引号。当然,如果读者命名了别的用户名,数据库管理者提供了专有用户名,就更改为相应用户。但是,不同用户的权限可能不同,所以,在程序中,如果要操作数据库,还要注意所拥有的权限。在这里用 root,就放心了,什么权限都有啦。不过,这样做,在大型系统中是应该避免的。

    passwd:上述 user 账户对应的登录 mysql 的密码。我在上面的例子中用的密码是"123123"。不要忘记引号。

    db:就是刚刚通 create 命令建立的数据库,我建立的数据库名字是"qiwsirtest",还是要注意引号。看官如果建立的数据库名字不是这个,就写自己所建数据库名字。

    port:一般情况,mysql 的默认端口是 3306,当 mysql 被安装到服务器之后,为了能够允许网络访问,服务器(计算机)要提供一个访问端口给它。

    charset:这个设置,在很多教程中都不写,结果在真正进行数据存储的时候,发现有乱码。这里我将 qiwsirtest 这个数据库的编码设置为 utf-8 格式,这样就允许存入汉字而无乱码了。注意,在 mysql 设置中,utf-8 写成 utf8,没有中间的横线。但是在 Python 文件开头和其它地方设置编码格式的时候,要写成 utf-8。切记!

     

     

    Python 建立了与数据的连接,其实是建立了一个 MySQLdb.connect() 的实例对象,或者泛泛地称之为连接对象,Python 就是通过连接对象和数据库对话。这个对象常用的方法有:

     

    commit():如果数据库表进行了修改,提交保存当前的数据。当然,如果此用户没有权限就作罢了,什么也不会发生。

    rollback():如果有权限,就取消当前的操作,否则报错。

    cursor([cursorclass]):返回连接的游标对象。通过游标执行 SQL 查询并检查结果。游标比连接支持更多的方法,而且可能在程序中更好用。

    close():关闭连接。此后,连接对象和游标都不再可用了。

     

     

    创建游标

    Python 和数据之间的连接建立起来之后,要操作数据库,就需要让 Python 对数据库执行 SQL 语句。Python 是通过游标执行 SQL 语句的。所以,连接建立之后,就要利用连接对象得到游标对象,方法如下:

    >>> cur = conn.cursor()

     

    此后,就可以利用游标对象的方法对数据库进行操作。那么还得了解游标对象的常用方法:

    名称

    描述

    close()

    关闭游标。之后游标不可用

    execute(query[,args])

    执行一条 SQL 语句,可以带参数

    executemany(query, pseq)

    对序列 pseq 中的每个参数执行 sql 语句

    fetchone()

    返回一条查询结果

    fetchall()

    返回所有查询结果

    fetchmany([size])

    返回 size 条结果

    nextset()

    移动到下一个结果

    scroll(value,mode='relative')

    移动游标到指定行,如果 mode='relative',则表示从当前所在行移动 value ,如果 mode='absolute',则表示从结果集的第一行移动 value

     

     

    插入数据

    >>> cur.execute("insert into user (name,age,mail) values (%s,%s,%s)",("lulu",18,"lulu@gmail.com"))
    1L
    >>>

    没有报错,并且返回一个"1L"结果,说明有一n 行记录操作成功。

     

    登录MySQL,验证数据有没有添加成功

    mysql> select * from user;
    Empty set (0.00 sec)
     
    mysql>

    奇怪,并没有看到插入的那条数据!到底哪里错了

     

    特别注意,通过"cur.execute()"对数据库进行操作之后,没有报错,完全正确,但是不等于数据就已经提交到数据库中了,还必须要用到"MySQLdb.connect"连接对象的一个方法:commit(),将数据提交上去,也就是进行了"cur.execute()"操作,要将数据提交,必须执行:

    >>> conn.commit()

     

    再次登录MySQL,看数据有没有添加成功

    mysql> select * from user;
    +----+------+------+----------------+
    | id | name | age  | mail           |
    +----+------+------+----------------+
    |  1 | lulu |   18 | lulu@gmail.com |
    +----+------+------+----------------+
    1 row in set (0.00 sec)
     
    mysql>

     

    果然如此。这就如同编写一个文本一样,将文字写到文本上,并不等于文字已经保留在文本文件中了,必须执行"CTRL-S"才能保存。也就是在通过 Python 操作数据库的时候,以"execute()"执行各种 sql 语句之后,要让已经执行的效果保存,必须运行连接对象的"commit()"方法。

     

     

     

    插入多条数据

    >>> cur.executemany("insert into user (name,age,mail) values (%s,%s,%s)",(("google",25,"g@gmail.com"),("facebook",18,"f@face.book"),("github",20,"git@hub.com"),("docker",10,"doc@ker.com")))
    4L
    >>>

     

    mysql> select * from user;
    +----+----------+------+----------------+
    | id | name     | age  | mail           |
    +----+----------+------+----------------+
    |  1 | lulu     |   18 | lulu@gmail.com |
    |  2 | google   |   25 | g@gmail.com    |
    |  3 | facebook |   18 | f@face.book    |
    |  4 | github   |   20 | git@hub.com    |
    |  5 | docker   |   10 | doc@ker.com    |
    +----+----------+------+----------------+
    5 rows in set (0.00 sec)
     
    mysql>

    成功插入了多条记录。在"executemany(query, pseq)"中,query 还是一条 sql 语句,但是 pseq 这时候是一个 tuple,这个 tuple 里面的元素也是 tuple,每个 tuple 分别对应 sql 语句中的字段列表。这句话其实被执行多次。只不过执行过程不显示给我们看罢了。

     

     

     

    查询

    如果要从数据库中查询数据,也用游标方法来操作了。

    >>> cur.execute("select * from user")
    5L
    >>> print cur.fetchall()
    ((1L, u'lulu', 18L, u'lulu@gmail.com'), (2L, u'google', 25L, u'g@gmail.com'), (3L, u'facebook', 18L, u'f@face.book'), (4L, u'github', 20L, u'git@hub.com'), (5L, u'docker', 10L, u'doc@ker.com'))
    >>>

     

    cur.execute() 从数据库查询出来的东西,被“保存在了 cur 所能找到的某个地方”,要找出这些被保存的东西,需要用cur.fetchall()(或者 fechone 等),并且找出来之后,做为对象存在。从上面的实验探讨发现,被保存的对象是一个 tuple 中,里面的每个元素,都是一个一个的 tuple。因此,用 for 循环就可以一个一个拿出来了。

     

     

    再次执行一次上面的操作

    >>> print cur.fetchall()
    ()
    >>>

     

    为什么结果是空的?

     

    原来是通过游标找出来的对象,在读取的时候有一个特点,就是那个游标会移动。在第一次操作了 print cur.fetchall() 后,因为是将所有的都打印出来,游标就从第一条移动到最后一条。当 print 结束之后,游标已经在最后一条的后面了。接下来如果再次打印,就空了,最后一条后面没有东西了。

     

     

    再看一个实验

    >>> cur.execute("select * from user")
    5L
    >>> print cur.fetchone()
    (1L, u'lulu', 18L, u'lulu@gmail.com')
    >>> print cur.fetchone()
    (2L, u'google', 25L, u'g@gmail.com')
    >>> print cur.fetchone()
    (3L, u'facebook', 18L, u'f@face.book')
    >>> print cur.fetchone()
    (4L, u'github', 20L, u'git@hub.com')
    >>> print cur.fetchone()
    (5L, u'docker', 10L, u'doc@ker.com')
    >>>

     

    这次不一次全部打印出来了,而是一次打印一条,可以从结果中看出来,果然那个游标在一条一条向下移动

     

    既然在操作存储在内存中的对象时候,游标会移动,能不能让游标向上移动,或者移动到指定位置呢?当然可以,这就是scroll()

    >>> print cur.fetchone()
    (5L, u'docker', 10L, u'doc@ker.com')
    >>> cur.scroll(-3)
    >>> print cur.fetchone()
    (3L, u'facebook', 18L, u'f@face.book')
    >>> cur.scroll(1)
    >>> print cur.fetchone()
    (5L, u'docker', 10L, u'doc@ker.com')>>>

    果然,这个函数能够移动游标,不过请仔细观察,上面的方式是让游标相对与当前位置向上或者向下移动。即:

    cur.scroll(n),或者,cur.scroll(n,"relative"):意思是相对当前位置向上或者向下移动,n 为正数,表示向下(向前),n 为负数,表示向上(向后)

    还有一种方式,可以实现“绝对”移动,不是“相对”移动:增加一个参数"absolute"

    特别提醒看官注意的是,在 Python 中,序列对象是的顺序是从 0 开始的。

     

    >>> cur.scroll(1,"absolute")               回到序号1,指向第2条数据
    >>> print cur.fetchone()
    (2L, u'google', 25L, u'g@gmail.com')
    >>> 
     
    >>> cur.scroll(0,"absolute")               回到序号0,指向第1条数据
    >>> print cur.fetchone()
    (1L, u'lulu', 18L, u'lulu@gmail.com')
    >>>

     

    承接上面操作,继续

    >>> print cur.fetchmany(3)
    ((2L, u'google', 25L, u'g@gmail.com'), (3L, u'facebook', 18L, u'f@face.book'), (4L, u'github', 20L, u'git@hub.com'))
    >>>

    上面这个操作,就是实现了从当前位置(游标指向 tuple 的序号为 1 的位置,即第二条记录)开始,含当前位置,向下列出 3 条记录。

     

     

     

    更新数据

    更新和插入一样,都需要commit()来提交保存。

     

    >>> cur.execute("update user set name=%s where id=5",("apple",))
    1L
    >>> cur.execute("select * from user where id=5")
    1L
    >>> print cur.fetchone()
    (5L, u'apple', 10L, u'doc@ker.com')
    >>>

     

    提交更新

    >>> conn.commit()
    >>>

     

    最后,关闭游标,关闭连接对象

    >>> cur.close()
    >>> conn.close()
    >>>