python3操作PyMySQL笔记

python3操作mysql需要先安装PyMySQL

pip install PyMySQL

在linux登录mysql ，并且在安装数据库时设置了数据库的用户名“root”和密码“root”，mysql安装的版本为mysql5.7

[root@web ~]# mysql -uroot -p -h192.168.10.100

在mysql里面创建一个mysql库

mysql> create database mrsoft;
Query OK, 1 row affected (0.00 sec)

下面是新建一个py文件远程创建一个mysql连接，下面通过connect()方法连接MySQL数据库mrsoft，具体代码如下：

import pymysql

#   打开数据库连接， 参数1:主机名或者ip；参数2：用户名；参数3：密码；参数4：数据库名称；参数5:用utf8格式打开数据库表防止出现中文乱码
db = pymysql.connect("192.168.10.100", "root", "root", "mrsoft", charset="utf8")

#   使用cursor()方法创建一个游标对象cursor
cursor = db.cursor()

#   使用execute()方法执行SQL查询
cursor.execute("SELECT VERSION()")

#   使用fetchone()方法获取单条数据
data = cursor.fetchone()

print("Database version : %s" % data)

#   关闭数据库连接
db.close()

 

1、创建一个books测试图书表

　　#books表包含id(主键)、name(图书名称),category(图书分类),price(图书价格)和publish_time(出版时间)5个字段。创建books图表的SQL如下：

mysql> CREATE TABLE books (
    -> id int(8) NOT NULL AUTO_INCREMENT,
    -> name varchar(50) NOT NULL,
    -> category varchar(50) NOT NULL,
    -> price decimal(10,2) DEFAULT NULL,
    -> publish_time date DEFAULT NULL,
    -> PRIMARY KEY (id)
    -> )ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

　　#在创建数据库表前，先在数据库里面使用如下语句；

mysql> DROP TABLE IF EXISTS `books`;
Query OK, 0 rows affected (0.00 sec)

　　#如果mysoft数据库中已经存在books，那么先删除books，然后再创建books数据库表。

import pymysql

#   打开数据库连接
db = pymysql.connect("192.168.10.100", "root", "root", "mrsoft", charset="utf8")

#   使用cursor()方法创建一个游标对象cursor
cursor = db.cursor()

#   使用 execute()方法执行SQL， 如果表存在则删除
cursor.execute("DROP TABLE IF EXISTS books")

#   使用预处理语句创建表
sql = """
CREATE TABLE books (
    id int(8) NOT NULL AUTO_INCREMENT,
    name varchar(50) NOT NULL,
    category varchar(50) NOT NULL,
    price decimal(10,2) DEFAULT NULL,
    publish_time date DEFAULT NULL,
    PRIMARY KEY (id)
    ) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

"""

#   执行SQL语句
cursor.execute(sql)

#   关闭数据库连接
db.close()

　　#运行后在数据库里面查看如下：

mysql> show tables;
+------------------+
| Tables_in_mrsoft |
+------------------+
| books |
+------------------+
1 row in set (0.00 sec)

mysql> desc books;
+--------------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+---------------+------+-----+---------+----------------+
| id | int(8) | NO | PRI | NULL | auto_increment |
| name | varchar(50) | NO | | NULL | |
| category | varchar(50) | NO | | NULL | |
| price | decimal(10,2) | YES | | NULL | |
| publish_time | date | YES | | NULL | |
+--------------+---------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)

　　#向books图书表中插入图书数据时，可以使用excute()方法添加一条记录，也可以使用executemany()方法批量添加多条记录，executemany()方法的语法格式如下：

　　　　executemany(operation,  seq_of_params)

　　　　　　operation：操作的SQL语句。

　　　　　　seq_of_params:　参数序列。

import pymysql

#   打开数据库连接
db = pymysql.connect("192.168.10.100", "root", "root", "mrsoft", charset="utf8")

#   使用cursor()方法创建一个游标对象cursor
cursor = db.cursor()

data = [
    ("零基础学python",'Python','79.80','2019-09-15'),
    ("python从入门到精通", 'Python', '69.80', '2018-09-15'),
    ("零基础学JAVA", 'JAVA', '89.80', '2017-09-15'),
    ("零基础学GO", 'GO', '79.80', '2019-05-15'),
    ("零基础学PHP", 'PHP', '69.80', '2015-09-15'),
]

#   使用预处理语句创建表
try:
    #   执行sql语句，插入多条数据，在使用insert语句插入数据时，使用%s作为占位符，可以防止SQL注入
    cursor.executemany("insert into books(name, category, price, publish_time) values (%s, %s, %s, %s)", data)
    #   提交数据
    db.commit()
except:
    #   发生错误时回滚
    db.rollback()

#   关闭数据库连接
db.close()

mysql> select * from books;
+----+--------------------------+----------+-------+--------------+
| id | name                     | category | price | publish_time |
+----+--------------------------+----------+-------+--------------+
|  1 | 零基础学python           | Python   | 79.80 | 2019-09-15   |
|  2 | python从入门到精通       | Python   | 69.80 | 2018-09-15   |
|  3 | 零基础学JAVA             | JAVA     | 89.80 | 2017-09-15   |
|  4 | 零基础学GO               | GO       | 79.80 | 2019-05-15   |
|  5 | 零基础学PHP              | PHP      | 69.80 | 2015-09-15   |
+----+--------------------------+----------+-------+--------------+
5 rows in set (0.00 sec)

　　#数据库批量插入操作如下：

import pymysql

#   打开数据库连接
db = pymysql.connect("192.168.10.100", "root", "root", "mrsoft", charset="utf8")

#   使用cursor()方法创建一个游标对象cursor
cursor = db.cursor()


#    创建sql语句
sql = "insert into books(name, category, price, publish_time) values (%s, %s, %s, %s)"

#    创建一个用于存放sql插入数据的列表
data = []

employ1 = ("零基础学python1",'Python1','79.80','2019-09-15')
employ2 = ("零基础学python2",'Python2','79.80','2019-09-15')
employ3 = ("零基础学python3",'Python3','79.80','2019-09-15')
employ4 = ("零基础学python4",'Python4','79.80','2019-09-15')

#添加数据到列表
data.append(employ1)
data.append(employ2)
data.append(employ3)
data.append(employ4)

#   使用预处理语句创建表
try:
    #   执行sql语句，插入多条数据
    cursor.executemany(sql, data)
    #   提交数据
    db.commit()
except:
    #   发生错误时回滚
    db.rollback()

#   关闭数据库连接
db.close()

mysql> select * from books;
+----+--------------------------+----------+-------+--------------+
| id | name                     | category | price | publish_time |
+----+--------------------------+----------+-------+--------------+
|  1 | 零基础学python           | Python   | 79.80 | 2019-09-15   |
|  2 | python从入门到精通       | Python   | 69.80 | 2018-09-15   |
|  3 | 零基础学JAVA             | JAVA     | 89.80 | 2017-09-15   |
|  4 | 零基础学GO               | GO       | 79.80 | 2019-05-15   |
|  5 | 零基础学PHP              | PHP      | 69.80 | 2015-09-15   |
|  6 | 零基础学python1          | Python1  | 79.80 | 2019-09-15   |
|  7 | 零基础学python2          | Python2  | 79.80 | 2019-09-15   |
|  8 | 零基础学python3          | Python3  | 79.80 | 2019-09-15   |
|  9 | 零基础学python4          | Python4  | 79.80 | 2019-09-15   |
+----+--------------------------+----------+-------+--------------+
9 rows in set (0.01 sec)

　　#python查询MySQL使用fetchone()方法获取单条数据，使用fetchall()方法获取多条数据

　　　　fetchone()：该方法获取下一个查询结果集。结果集是一个对象。

　　　　fetchall()：接受全部的返回结果行。

　　　　rowcount：这是一个只读属性，并返回执行execute()方法后影响的行数。

import pymysql

#   打开数据库连接
db = pymysql.connect("192.168.10.100", "root", "root", "mrsoft", charset="utf8")

#   使用cursor()方法创建一个游标对象cursor
cursor = db.cursor()

sql = "SELECT * FROM books WHERE price > '%d'" % (79)

#   使用预处理语句创建表
try:
    #   执行sql语句，查询数据
    cursor.execute(sql)
    #   获取所有记录列表
    results = cursor.fetchall()
    for row in results:
        id = row[0]
        name = row[1]
        category = row[2]
        price = row[3]
        publish_time = row[4]

        #   打印结果
            
print("id=%d,name=%s,category=%s,price=%s,publish_time=%s" % (id, name, category, price, publish_time))
except Exception as e:
    print(e)

#    无论是否有异常都执行
finally:
    #   关闭数据库连接
    db.close()

id=1,name=零基础学python,category=Python,price=79.80,publish_time=2019-09-15
id=3,name=零基础学JAVA,category=JAVA,price=89.80,publish_time=2017-09-15
id=4,name=零基础学GO,category=GO,price=79.80,publish_time=2019-05-15
id=6,name=零基础学python1,category=Python1,price=79.80,publish_time=2019-09-15
id=7,name=零基础学python2,category=Python2,price=79.80,publish_time=2019-09-15
id=8,name=零基础学python3,category=Python3,price=79.80,publish_time=2019-09-15
id=9,name=零基础学python4,category=Python4,price=79.80,publish_time=2019-09-15