Python基础 | MySQL数据存储

# -*- UTF-8 -*-
"""
@File:MySQL.py
@Description:
@Author:echohye
@Date:2022/01/28 19:46
"""
import pymysql

'''
@Author:echohye
@Description:连接数据库并创建数据库
@Date:2022-01-28, 周五, 19:47
'''
def connectDB():
    db = pymysql.connect(host='localhost', user='root', password='zhy123', port=3306)
    cursor = db.cursor()
    cursor.execute('select version()')
    data = cursor.fetchone()
    print(f'Database version:{data}')
    cursor.execute('create database spiders default character set utf8mb4')
    db.close()


'''
Author:echohye
@Description:创建表
@Date:2022-01-28, 周五, 19:56
'''
def createTable():
    db = pymysql.connect(host='localhost', user='root', password='zhy123', port=3306, db='spiders')
    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()


'''
@Author:echohye
@Description:插入数据
@Date:2022-01-28, 周五, 20:22
'''
def insertData():
    id = '10003'
    user = 'Mike'
    age = 22
    db = pymysql.connect(host='localhost', user='root', password='zhy123', port=3306, db='spiders')
    cursor = db.cursor()
    # sql = 'insert into students(id, names, age) values ("%s", "%s", %s)' % (id, user, age)
    sql = 'insert into students(id, name, age) values ("'+id+'", "'+user+'", '+str(age)+')'
    try:
        cursor.execute(sql)
        db.commit()
        print('插入成功')
    except Exception as e:
        db.rollback()
        print(e.args)
        print('插入失败')
    finally:
        db.close()

'''
@Author:echohye
@Description:更新数据
@Date:2022-01-28, 周五, 21:12
'''
def updateData():
    db = pymysql.connect(host='localhost', user='root', password='zhy123', port=3306, db='spiders')
    cursor = db.cursor()
    # sql = 'update students set age=21 where name="Mike"'
    sql = "update students set age=19 where name='Mike'"
    try:
        cursor.execute(sql)
        db.commit()
        print("successful")
    except Exception as e:
        db.rollback()
        print(e.args)
        print("failure")
    finally:
        db.close()


'''
@Author:echohye
@Description:删除数据
@Date:2022-01-28, 周五, 21:26
'''
def deleteData():
    db = pymysql.connect(host='localhost', user='root', password='zhy123', port=3306, db='spiders')
    cursor = db.cursor()
    sql = "delete from students where name='Mike'"
    try:
        cursor.execute(sql)
        db.commit()
        print("successful")
    except Exception as e:
        db.rollback()
        print(e.args)
        print("failure")
    finally:
        db.close()


'''
@Author:echohye
@Description:查询数据
@Date:2022-01-28, 周五, 21:27
'''
def queryData():
    db = pymysql.connect(host='localhost', user='root', password='zhy123', port=3306, db='spiders')
    cursor = db.cursor()
    sql = "select * from students where age>20"
    try:
        cursor.execute(sql)
        # query = cursor.fetchone()
        query = cursor.fetchall()
        print("query:" + query.__str__())
    except Exception as e:
        print(e.args)
        print("failure")
    finally:
        db.close()

if __name__ == '__main__':
    # connectDB()
    # createTable()
    # insertData()
    # updateData()
    # deleteData()
    queryData()