同福

鬼谷子叔叔
2020-10-08 12:10:11 87

Python操作MySQL数据库【20201007】

介绍

介绍

今天我们学习如何通过Python操作MySQL数据库。学会使用Python操作MySQL数据库之后，我们就可以使用Python制作更为强大的应用程序了。有了数据库操作能力的应用程序，就有了数据存档的能力，能够存档的程序可以达到的功能就非常多了。

在Python2.7下使用的是pymysql这个软件包，这个pymysql库的原生对象设计的还是非常简单的，相信大家一看就能够学会，那么就跟着福哥来学习它吧~~

安装

安装pymysql

直接使用pip安装即可

pip insall pymysql

6bc273249a84c46f.jpg

授权

MySQL数据库服务器默认只能在安装了数据库服务器的本机操作，也就是在TFLinux上操作，但是我们的python是安装在TFWindows上的啊！所以，我们要授权任何电脑都可以操作TFLinux上的MySQL数据库。当然了，肯定是需要正确的用户名和密码的啦！

在TFLinux上登录mysql

[root@tfdev ~]# mysql -uroot -pabcdef
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.28 MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

创建test数据库，使用utf-8编码作为test库的默认编码

mysql> create database test default charset utf8;
Query OK, 1 row affected (0.01 sec)

创建test数据表，在test库里面创建一个test数据表，设置id、label、dt三个字段，其中id字段为主键并且设置为自动增加列，label字段为约束列不可重复

mysql> use test
Database changed
mysql> CREATE TABLE test(
    -> id INT NOT NULL AUTO_INCREMENT,
    -> label VARCHAR(45) NOT NULL,
    -> dt DATETIME NOT NULL,
    -> PRIMARY KEY (id),
    -> UNIQUE u_label (label)
    -> );
Query OK, 0 rows affected (0.01 sec)

授权tfpy用户可以在任意电脑上连接MySQL数据库服务器，且对数据库test有完全控制权限

mysql> grant all on test.* to tfpy@'%' identified by 'abcdef';
Query OK, 0 rows affected, 1 warning (0.00 sec)

测试

建立MySQLDemo.py用来进行pymysql的测试

测试连接

写入如下代码，测试mysql的连接

import pymysql

cn = pymysql.connect("192.168.1.168","tfpy","abcdef")

如果没有报错，证明连接成功了！

测试写入数据

写入如下代码，向test表写入一条数据

import pymysql

try:
    # connection
    cn = pymysql.connect("192.168.1.168","tfpy","abcdef")
    cursor = cn.cursor()

    # select database
    cn.select_db('test')

    # change charset
    cursor.execute("SET NAMES utf8");
except Exception as e:
    print(e)
    exit()

try:
    # insert data into test
    cursor.execute("INSERT INTO test (label, dt) VALUES (%s,now())", ["福哥".decode("gbk").encode("utf-8")])

    # commit
    cn.commit()
except Exception as e:
    print(e)

    # rollback
    cn.rollback()
finally:
    # close
    cursor.close()
    cn.close()

如果没有报错，就表示写入成功了！

这里面有个地方需要解释一下，在执行INSERT语句时候福哥将“福哥”这个字符串进行了一次从gbk到utf-8的编码转换，因为py程序文件是GBK编码，而test数据库是UTF-8编码，直接写入会出现乱码情况。

查看数据

到TFLinux上查看刚刚写入的数据

mysql> select * from test;
+----+--------+---------------------+
| id | label  | dt                  |
+----+--------+---------------------+
|  1 | 福哥   | 2021-02-24 22:20:12 |
+----+--------+---------------------+
1 row in set (0.00 sec)

测试查询数据

写入如下代码，查询test里的数据

import pymysql

try:
    # connection
    cn = pymysql.connect("192.168.1.168","tfpy","abcdef")
    cursor = cn.cursor()

    # select database
    cn.select_db('test')

    # change charset
    cursor.execute("SET NAMES utf8")
except Exception as e:
    print(e)
    exit()

try:
    # query
    cursor.execute("SELECT * FROM test")
    print ("总记录数量：" + str(cursor.rowcount))

    rows = cursor.fetchall()
    print ("记录信息")
    for row in rows:
        print(row)
        print(row[1].decode("utf-8").encode("gbk"))

    # commit
    cn.commit()
except Exception as e:
    print(e)

    # rollback
    cn.rollback()
finally:
    # close
    cursor.close()
    cn.close()

46719a00e2b502a9.jpg

这里福哥还要说明一下，因为test库是UTF-8编码，而py程序文件是GBK编码，直接打印是unicode编码，需要从utf-8转换成gbk才能正常显示。

教程

接下来我们系统学习一下pymysql这个软件包的功能

写入操作

写入操作包括很多，常见的命令包括：CREATE DATABASE/DROP DATABASE、CREATE TABLE/DROP TABLE/ALTER TABLE、INSERT INTO/UPDATE/DELETE等等。

写入操作都需要通过pymysql的cursor.execute方法来运行，如果运行出现异常会抛出except，我们捕获它进行后面的处理即可。

这里我们着重讲讲插入数据，更新数据，删除数据的操作

插入单行数据

插入一行数据，强烈建议使用指定字段名称的方式写入数据

import pymysql

try:
    # connection
    cn = pymysql.connect("192.168.1.168","tfpy","abcdef")
    cursor = cn.cursor()

    # select database
    cn.select_db('test')

    # change charset
    cursor.execute("SET NAMES utf8")
except Exception as e:
    print(e)
    exit()

try:
    # insert data into test
    cursor.execute("INSERT INTO test (label, dt) VALUES (%s,now())", ['tongfu.net'])

    # id of insert data
    id = cursor.lastrowid
    print ("插入数据的id是：" + str(id))

    # commit
    cn.commit()
except Exception as e:
    print(e)

    # rollback
    cn.rollback()
finally:
    # close
    cursor.close()
    cn.close()

6ff87fc417c6fd8a.jpg

插入多行数据

可以传入数组到cursor.executemany实现多行数据插入的操作

import pymysql

try:
    # connection
    cn = pymysql.connect("192.168.1.168","tfpy","abcdef")
    cursor = cn.cursor()

    # select database
    cn.select_db('test')

    # change charset
    cursor.execute("SET NAMES utf8")
except Exception as e:
    print(e)
    exit()

try:
    # insert data into test
    rows = [
        ('www.baidu.com'),
        ('www.taobao.com'),
        ('www.jd.com')
    ]
    cursor.executemany("INSERT INTO test (label, dt) VALUES (%s,now())", rows)

    # commit
    cn.commit()
except Exception as e:
    print(e)

    # rollback
    cn.rollback()
finally:
    # close
    cursor.close()
    cn.close()

更新数据

使用UPDATE语句进行数据的修改操作

import pymysql

try:
    # connection
    cn = pymysql.connect("192.168.1.168","tfpy","abcdef")
    cursor = cn.cursor()

    # select database
    cn.select_db('test')

    # change charset
    cursor.execute("SET NAMES utf8")
except Exception as e:
    print(e)
    exit()

try:
    # update data in test
    cursor.execute("UPDATE test SET dt = now() WHERE label = %s", 'tongfu.net')

    # commit
    cn.commit()
except Exception as e:
    print(e)

    # rollback
    cn.rollback()
finally:
    # close
    cursor.close()
    cn.close()

删除数据

使用DELETE语句删除数据

import pymysql

try:
    # connection
    cn = pymysql.connect("192.168.1.168","tfpy","abcdef")
    cursor = cn.cursor()

    # select database
    cn.select_db('test')

    # change charset
    cursor.execute("SET NAMES utf8")
except Exception as e:
    print(e)
    exit()

try:
    # delete data from test
    cursor.execute("DELETE FROM test WHERE label = %s", 'www.baidu.com')

    # commit
    cn.commit()
except Exception as e:
    print(e)

    # rollback
    cn.rollback()
finally:
    # close
    cursor.close()
    cn.close()

读取操作

读取操作只的就是SELECT语句了，这个SELECT语句可以有非常复杂的变化，设计到多表联查的时候会更加复杂，有时候一个SQL语句几千个字符都很正常。

读取一行数据

通过cursor.fetchone读取一行数据

import pymysql

try:
    # connection
    cn = pymysql.connect("192.168.1.168","tfpy","abcdef")
    cursor = cn.cursor()

    # select database
    cn.select_db('test')

    # change charset
    cursor.execute("SET NAMES utf8")
except Exception as e:
    print(e)
    exit()

try:
    # select data from test
    cursor.execute("SELECT * FROM test WHERE label = %s", 'tongfu.net')

    # fetch one
    result = cursor.fetchone()
    print(result)

    # commit
    cn.commit()
except Exception as e:
    print(e)

    # rollback
    cn.rollback()
finally:
    # close
    cursor.close()
    cn.close()

4faaf3a74b6c0c04.jpg

读取全部数据

通过cursor.fetchall读取查询结果集的全部数据

import pymysql

try:
    # connection
    cn = pymysql.connect("192.168.1.168","tfpy","abcdef")
    cursor = cn.cursor()

    # select database
    cn.select_db('test')

    # change charset
    cursor.execute("SET NAMES utf8")
except Exception as e:
    print(e)
    exit()

try:
    # select data from test
    cursor.execute("SELECT * FROM test")

    # fetch all
    results = cursor.fetchall()
    for result in results:
        print(result)

    # commit
    cn.commit()
except Exception as e:
    print(e)

    # rollback
    cn.rollback()
finally:
    # close
    cursor.close()
    cn.close()

3dc486cac0769919.jpg

数据分页

所谓数据翻页就是实现指定读取数据的起始位置和读取数据的条目数的技巧，通过这个技巧可以将一组数据均匀分成若干份，每一份相当于一页，这样的设计可以让用户像浏览图书一样的翻页效果

import pymysql
import math

try:
    # connection
    cn = pymysql.connect("192.168.1.168","tfpy","abcdef")
    cursor = cn.cursor()

    # select database
    cn.select_db('test')

    # change charset
    cursor.execute("SET NAMES utf8")
except Exception as e:
    print(e)
    exit()

try:
    # select data from test
    cursor.execute("SELECT * FROM test")

    # pages
    cursor.scroll(0, mode="absolute") # move to 0
    try:
        # count page numbers
        totalNum = cursor.rowcount
        pageNum = 1
        pageCount = math.ceil(float(totalNum)/3)
        while 1:
            # fetch 2 rows
            results = cursor.fetchmany(2)
            print ("页码：" + str(pageNum))
            for result in results:
                print(result)

            # page number +1
            pageNum = pageNum+1

            # page number more than page count then break
            if pageNum > pageCount:
                break
    except Exception as e:
        print(e)

    # commit
    cn.commit()
except Exception as e:
    print(e)

    # rollback
    cn.rollback()
finally:
    # close
    cursor.close()
    cn.close()

1562cf660820979b.jpg

总结

今天我们学习了通过pymysql操作MySQL数据库的方法，大家课后可以自己多多练习，一定要数量掌握这个技巧才可以哦~~