Python爬虫-关系型数据库 MySQL 存储（六）

一、 准备工作

?

1	pip3 install pymysql

二、 连接数据库

利用 PyMySQL 先连接 MySQL，然后创建一个新的数据库，名字叫作 spiders，代码如下：

?

1 2 3 4 5 6 7 8 9

import pymysql   db = pymysql.connect(host='localhost',user='root', password='123456', port=3306) cursor = db.cursor() cursor.execute('SELECT VERSION()') data = cursor.fetchone() print('Database version:', data) cursor.execute("CREATE DATABASE spiders DEFAULT CHARACTER SET utf8mb4") db.close()

运行结果如下：

?

1	Database version: ('8.0.19',)

三、案例展示01（创建数据库-创建数据表-插入表数据-更新表数据-删除表数据-查询表数据）　　

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141

#连接数据库 import pymysql db = pymysql.connect(host='14.116.152.57',user='root', password='root', port=3308,database='spiders')   def create_database():     #创建数据库     cursor = db.cursor()     cursor.execute('SELECT VERSION()')     data = cursor.fetchone()     print('Database version:', data)     cursor.execute("CREATE DATABASE spiders DEFAULT CHARACTER SET utf8mb4")     db.close()     def create_table():     #创建数据库表     cursor = db.cursor()     sql = 'CREATE TABLE IF NOT EXISTS students (id VARCHAR(255) NOT NULL, name VARCHAR(255) NOT NULL, age INT NOT NULL, PRIMARY KEY (id))'     cursor.execute(sql)     db.close()   def insert_table():     #插入表数据     id = '20120004'     user = 'Bob'     age = 22     cursor = db.cursor()     sql = 'INSERT INTO students(id, name, age) values(%s, %s, %s)'     try:         cursor.execute(sql, (id, user, age))         db.commit()     except:         db.rollback()     db.close()   def insert_table1():     '''动态插入数据'''     data = {         'id': '20120003',         'name': 'Bob',         'age': 22     }     table = 'students'     keys = ', '.join(data.keys())     values = ', '.join(['%s'] * len(data))     cursor = db.cursor()     sql = 'INSERT INTO {table}({keys}) VALUES ({values})'.format(table=table, keys=keys, values=values)     # print(keys)     # print(values)     try:         if cursor.execute(sql,tuple(data.values())):             print('success')             db.commit()     except:         print('faild')         db.rollback()     db.close()     def update_table():     '''更新数据方法1'''     sql='UPDATE students SET age = %s WHERE name = %s'     cursor = db.cursor()     try:         cursor.execute(sql,(25,'Bob'))         db.commit()     except:         db.rollback()     db.close()   def update_table1():     '''更新数据方法2-表存在就更新数据，不存在插入数据'''     cursor = db.cursor()     data={         'id': '20120001',         'name': 'Bob',         'age': 26     }     table = 'students'     keys=', '.join(data.keys())     values=', '.join(['%s']*len(data))     sql = 'INSERT INTO {table}({keys}) VALUES ({values}) ON DUPLICATE KEY UPDATE '.format(table=table, keys=keys,values=values)     print(keys)     print(values)     print(sql)     update = ','.join(["{key} = %s".format(key=key) for key in data])     print(update)     sql += update     try:         if cursor.execute(sql, tuple(data.values()) * 2):             print('Successful')             db.commit()     except:         print('Failed')         db.rollback()     db.close()     def delete_table():     '''删除表数据'''     cursor = db.cursor()     table = 'students'     condition = 'age > 25'     sql = 'DELETE FROM  {table} WHERE {condition}'.format(table=table, condition=condition)     try:         cursor.execute(sql)         db.commit()     except:         db.rollback()     db.close()   def select_table():     '''查询表数据'''     cursor = db.cursor()     sql = 'SELECT * FROM students WHERE age >= 20'     # try:     #     cursor.execute(sql)     #     print('Count-查询结果的条数:', cursor.rowcount)     #     one = cursor.fetchone()     #     print('One-获取结果的第一条数据:', one)     #     results = cursor.fetchall()     #     print('Results:获取结果的所有数据', results)     #     print('Results Type:', type(results))     #     for row in results:     #         print(row)     # except:     #     print('Error')       try:         cursor.execute(sql)         print('Count:', cursor.rowcount)         row = cursor.fetchone()         while row:             print('Row:', row)             row = cursor.fetchone()     except:         print('Error')     if __name__ == '__main__':     select_table()