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

一、 准备工作

pip3 install pymysql

二、 连接数据库

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

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()

运行结果如下：

Database version: ('8.0.19',)

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

#连接数据库
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()